PL/SQL program unit - Stored Procedure and Function

PL/SQL is a procedural programming language supported by Oracle database server and it is highly integrated with pre existing SQL construct. PL/SQL program unit can be broadly classified in two category :- 

1. Unnamed PL/SQLprogram (Anonymous PL/SQL blocks) - Blocks of statements (with DECLARE , BEGIN, END construct) are anonymous PL/SQL program. In all previous posts, we have written various sample codes are Unnamed PL/SQL program. 
2. Named PL/SQL sub program (PL/SQL stored  procedure and function) - a PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly with different parameters and they can be stored in database. Stored procedure is same as procedure, in this procedure will be used. 

The main agenda of this post is to discuss named PL/SQL programs,difference between them and their usages. Named sub program can be of two types:- Standalone subprogram and Package subprogram. In following section we will learn about Standalone subprogram and Package subprogram is discussed in detail in next post.

Standalone PL/SQL sub program 

A PL/SQL subprogram(procedure and function) created at schema level are termed as standalone subprogram. The fundamental difference between procedure and function is - procedure does not return any value to caller , however function returns a value to caller. So, function is generally used when we want to return a value to caller, otherwise function behaves in similar fashion as procedure.
Syntactical difference between procedure and function:- Procedure is created using create procedure and function is creating using create function construct. Below is the general syntax applicable for both procedure and function creation followed by syntax for creation of procedure and function.

---Header (procedure/function name with parameters and return type(for function only))
IS/AS 
--declare section 
BEGIN
-- PL/SQL or SQL statements 
Exception 
-- Exception handling;
END;

Notice the difference, in anonymous PL/SQL block we use DECLARE keyword to indicate the declaration section however it is not required here.
Syntax for PL/SQL procedure and function creation:- Procedure is created using create procedure construct and no return clause is added in it. However, in function creation Create function is used and Return clause is added.

---Procedure creation 
create or replace PROCEDURE <procedure_name>
  (  [<parameter_name> <MODE_of_parameter>  <datatype> ,
      <parameter_name> <MODE_of_parameter>  <datatype>,
      ......]
   )
IS/AS
--[declare variables here between IS and BEGIN like : v_in NUMBER(12);] OPTIONAL block
BEGIN
  NULL; --Other Executable statements (This block cannot be empty, use NULL; if empty.)
EXCEPTION [Optional block]
  exception handler statements
END <procedure_name>;

---Function creation 
create or replace FUNCTION <function_name>
(
  <param1> <parameter_mode> <datatype>, 
  <param1> <parameter_mode> <datatype> 
) return <Datatype_of_return_type>
AS/IS
--declare variables here between IS and BEGIN like : v_in NUMBER(12); Optional block 
begin
  NULL;--executable statements, this block cannot empty use NULL; if empty.

EXCEPTION [Optional block]
  exception handler statements
end <function_name>;

Note:- PL/SQL is case insensitive language, both Create procedure or CReaTE PRoCEdure are same and similarly, begin/BEGIN or end/END are same too.

Mode of parameter passing :- In PL/SQL , parameter passed to function or procedure can be one of the three types:

1. IN mode -     It indicates parameter is input to the procedure/function.
2. OUT mode - It suggest that some value can be written to that parameter and caller of this procedure/function will be able to retrieve it.
3. IN OUT mode - It is combination of both In and OUT, it can act as input to procedure and if some value is written to it, caller will be able to retrieve it.

Procedure /function creation:- .
Question 1:-Write a procedure that takes department_id as input and open a cursor for all employee records(First_name and EMAIL) belonging to that department_id.

CREATE OR REPLACE PROCEDURE GET_ALL_EMPLOYEE_BY_DEPT 
(
  DEPT_ID IN NUMBER DEFAULT 90 --Default value 90 passed to procedure parameter 
, EMP_CUR OUT SYS_REFCURSOR 
) AS 
BEGIN
  OPEN EMP_CUR FOR SELECT First_NAME,EMAIL  FROM EMPLOYEES WHERE department_id = DEPT_ID;
END GET_ALL_EMPLOYEE_BY_DEPT;

In above code lines, a procedure is created with name GET_ALL_EMPLOYEE_BY_DEPT and it has two parameters - one is of IN type and another one is of OUT type. Since, cursor is of OUT parameter type, it is accessible to caller if we write or open it for some result set. In execution block we opened it with a select statement returning all employee records of dep_id passed as IN parameter. When we execute procedure from SQL*PLUS client/SQL developer client sample output obtained is:
SQL> variable rc REFCURSOR
SQL> execute HR.GET_ALL_EMPLOYEE_BY_DEPT(90,:rc); -- 90, :rc are called actual parameter.
PL/SQL procedure successfully completed.
SQL> print rc;
FIRST_NAME           EMAIL
-------------------- -------------------------
Steven               SKING
Neena                NKOCHHAR
Lex                  LDEHAAN
NIKHIL               NIKSR

Question:-  What will happen if we call print rc; again ?
Answer:- It will throw error "SP2-0625: Error printing variable "rc" because print command automatically closes cursor after reading result set from it. Since cursor is not closed so It should first opened before fetching records from it. Read life cycle of cursor.
Print command is specific to SQL*PLUS., it will not work in SQL Developer.

---

Question 2:- Write a function that takes employee_id as input and return an modified salary of that employee Return type NUMBER.

CREATE OR REPLACE FUNCTION GET_MOFIED_SALARY_OF_EMPLOYEE 
(
  EMP_ID IN NUMBER 
) RETURN NUMBER AS 
salary_return NUMBER(12);
bonus_percent NUMBER(2):= 50;
BEGIN
  select salary*(1+bonus_percent/100) INTO salary_return from
  employees where employee_id = EMP_ID;
  RETURN salary_return;
END GET_MOFIED_SALARY_OF_EMPLOYEE;

Above function has one IN parameter and return type NUMBER. Using SELECT INTO statement modified value is assigned to salary_return variable and it is returned to caller of this function. How do we test PL/SQL function ? - As contrast to PL/SQL procedure (it is as standalone executable statement) PL/SQL function call can only be part of an executable statement.Below is PL/SQL block to test above function: 

DECLARE
salary NUMBER(12);
BEGIN
    -- as executable statement 
    salary := GET_MOFIED_SALARY_OF_EMPLOYEE(7); 
    DBMS_OUTPUT.PUT_LINE('Modified salary is '|| salary);
END;

====sample output=======
Modified salary is 30600
=====================
PL/SQL function return type can be a scalar type (VARCHAR2,NUMBER, BINARY_INTEGER,BOOLEAN) , Composite data structure (TABLE , RECORD,NESTED TABLE,VARRAY) , Object type(Schema level object) and Cursor type.

Parameters passing to subprogram - Positional, Named, and Mixed Notation

The parameter which is passed during procedure/function call is termed as actual parameters and parameters which act as place holder in function/procedure are formal parameters. For example: in above function call 7 is actual parameter and EMP_ID is formal parameters. There are various ways of parameter passing in PL/SQL:- 
1. Positional notation:-  Actual parameters in the same order as the formal parameters are declared.
2. Named notation :- Actual parameters are passed to formal parameters place holder as :
    formal_parameter => actual_parameter (Refer following example for more detail)
3. Mixed notation:- Positional followed by named notation is termed as mixed notation. 
Consider following procedure and followed by PL/SQL block from where procedure is executed by passing parameters using different notation :

CREATE OR REPLACE PROCEDURE PARAMETER_PASSING_EXAMPLE 
(
  -- formal parameters 
  EMP_ID_IN IN VARCHAR2 
, DEPT_ID_IN IN NUMBER 
, SALARY_OUT OUT NUMBER 
) AS 
BEGIN
  select SALARY INTO SALARY_OUT from EMPLOYEEs where 
        employee_id = EMP_ID_IN and department_id = dept_id_in;
END PARAMETER_PASSING_EXAMPLE;

Below PL/SQL block call above procedure by passing parameters(using all three notations):- In Case1 parameters is passed in same order as the formal parameters. In case 2,  out parameter is passed as second parameter but name is associated with it so it helps to distinguish and value is assigned ot correct place holder. In case 3, positional followed by named.

DECLARE
SALARY_OUT_Actaul NUMBER(12);
SALARY_OUT NUMBER(12);
BEGIN
-- case 1:Positional notaion, actual parameters paassing
PARAMETER_PASSING_EXAMPLE(7, 90, SALARY_OUT_Actaul) ;
DBMS_OUTPUT.PUT_LINE(SALARY_OUT_Actaul);

-- case 2:Named notaion, actual parameters paassing
PARAMETER_PASSING_EXAMPLE (EMP_ID_IN=>7,DEPT_ID_IN=>90,
     SALARY_OUT => SALARY_OUT_Actaul ) ;
DBMS_OUTPUT.PUT_LINE(SALARY_OUT_Actaul);

-- case 3:Mixed notaion(7 is passed first, followed by Named notaion),
--actual parameters paassing
PARAMETER_PASSING_EXAMPLE (7,DEPT_ID_IN=>90,
   SALARY_OUT=> SALARY_OUT_Actaul ) ;
DBMS_OUTPUT.PUT_LINE(SALARY_OUT_Actaul);
END;

Note:-

1. Procedure and function both are compiled and stored in database. Both procedure and function are database object.
2. Information of procedure and function are stored in system table like  SYS.ALL_PROCEDURES, SYS.USER_PROCEDURES , SYS.DBA_PROCEDURES.
3. Standalone subprograms cannot be overloaded. However,nested subprogram(Anonymous PL/SQL procedure) and Package subprogram can be overloaded.

In next section we will discuss about PL/SQL package and package subprogram.

  Previous: PL/SQL Records - Cursor variable Next:PL/SQL Package