Procedure and Function in PL/SQL

PL/SQL i.e. “Procedural Language extensions to SQL" is an extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in a procedural format. It combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries.

PL/SQL means instructing the compiler 'what to do' through SQL and 'how to do' through its procedural way. Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object-oriented concepts.

Now before moving to know what is PL/SQL procedure and function let us see what is the architecture of PL/SQL.

PL/SQL Architecture 

The following picture illustrates the PL/SQL architecture:

The PL/SQL architecture mainly consists of following three components:

•          PL/SQL block: This is the component which has the actual PL/SQL code.
•          PL/SQL Engine: PL/SQL engine is the component where the actual processing of the codes takes place.
•       Database Server: This is the most important component of Pl/SQL unit which stores the data.

PL/SQL – Procedure and function

A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.

PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms –

Functions: The Functions is a kind of subprogram which returns single value and it is mainly used to compute and return a value.

Procedures: The Procedure is a kind of subprogram that does not return a value directly and is mainly used to perform action.

Creating a procedure

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows –

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS} BEGIN < procedure_body > END procedure_name;

Here,

• procedure-name specifies the name of the procedure.
• [OR REPLACE] option allows the modification of an existing procedure.
• The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
• procedure-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Let us take a simple example to clear the syntax of the PL/SQL Procedure.

We’re going to develop a procedure named adjust_salary() in HR sample database provided by Oracle. We’ll update the salary information of employees in the employees table by using SQL UPDATE statement.

The following is the source code of the adjust_salary() procedure :

CREATE OR REPLACE PROCEDURE adjust_salary(

in_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,

    in_percent IN NUMBER

) IS

BEGIN

   -- update employee's salary

   UPDATE employees

   SET salary = salary + salary * in_percent / 100

   WHERE employee_id = in_employee_id;

END;

How it works.

• The procedure has two parameters: IN_EMPLOYEE_ID and IN_PERCENT.

• The procedure adjusts the salary of a particular employee specified the IN_EMPLOYEE_ID by a given percentage IN_PERCENT.

• In the procedure body, we use the UPDATE statement to update the salary information.

Creating a Function

A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows –

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];

Here,

•      function-name specifies the name of the function.
•          [OR REPLACE] option allows the modification of an existing function.
•          The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
•          The function must contain a return statement.
•          The RETURN clause specifies the data type you are going to return from the function.
•          function-body contains the executable part.
•          The AS keyword is used instead of the IS keyword for creating a standalone function.

Let's take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL function which will compute and return the maximum of two values.

DECLARE  

   a number;  

   b number;  

   c number;  

FUNCTION findMax(x IN number, y IN number)   

RETURN number  

IS  

    z number;  

BEGIN  

   IF x > y THEN  

      z:= x;  

   ELSE  

      Z:= y;  

   END IF;  

  

   RETURN z;  

END;   

BEGIN  

   a:= 23;  

   b:= 45;  

  

   c := findMax(a, b);  

  dbms_output.put_line(' Maximum of (23,45): ' || c);  

END; 

So, this is all about the basics that we need to know to start with the Procedure and Functions of PL/SQL. Hope you like this blog :)