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

PL/SQL Record Type - a composite data structure

In previous post we discussed about PL/SQL Collections (Associative array, VARRAY and Nested Tables) and main agenda of this post is to discuss another composite data structure - Record type in PL/SQL. Record type finds extensive uses when dealing with cursor variable ,while fetching data (rows) from result set.

Record Type - Table-based, Cursor based and programmer defined

A record is a composite data structure composed of more than one atomic elements/fields and each with its own value.In PL/SQL, Record type is local type(declared in PL/SQL block or in package). Records in PL/SQL are very similar in concept and structure to the rows of a database table. Each filed of record can be accessed by name.

Different types of PL/SQL Record :-

We have three different types of records.
1. Table based -  A record based on structure of columns of database tables. (TABLE%ROWTYPE)
2.Cursor based  - A record based on the cursor's SELECT statement.(CURSOR%ROWTYPE)
3. Programmer/Used defined records - Programmer defines structure of record.
Lets see each of them one by one and understand how it is used in PL/SQL.

Table based record :- A record based on table type(termed as table record) is created using %ROWTYPE attribute of table. general syntax is as follows:

--rcord_name is a valid identifier, table_name is database table 
<record_name> <table_name>%ROWTYPE;

Note:- %TYPE provides structure of a column of table, where as %ROWTYPE provides the datatypes of each of the columns in a table for the record's fields. Following code declares a table record based on LOCATIONS table.

DECLARE
  --TABLE BASED RECORD 
  V_LOC_REC  LOCATIONS%ROWTYPE;
BEGIN
   SELECT * into V_LOC_REC FROM LOCATIONS WHERE POSTAL_CODE = '500081';
   DBMS_OUTPUT.PUT_LINE(V_LOC_REC.STREET_ADDRESS  || '  ' 
      || V_LOC_REC.STATE_PROVINCE);
END;

Cursor based record :-  A record whose structure are drawn from the SELECT list of a cursor is termed as cursor record. General syntax of  cursor based record is :

/*Create a cursor*/
CURSOR cursor_name IS <SELECT_STMT_with_fields_retrival>;
/*Create a record based cursor- record name is a valid identifier*/
<record_name> <cursor_name>%ROWTYPE;

Cursor based record is more flexible in terms of giving proper  names for fields in cursor. It means, we can assign proper name to record fields other than table column name.(It is not possible in table based record). However, more code lines over head required - create cursor and alias the column.Following example creates an cursor based record and display record details.

--cursor based record
DECLARE
--Declare an parameterized explicit cursor emp_cur 
CURSOR emp_cur(emp_id NUMBER) IS SELECT FIRST_NAME, EMAIL FROM HR.EMPLOYEES
   WHERE EMPLOYEE_ID = emp_id;
emp_rec emp_cur%ROWTYPE;
BEGIN
 -- Open cursor by passing a parameter
  OPEN emp_cur(7);
 --Fetch record and pass it to record of cursor type
  FETCH emp_cur INTO emp_rec;
      IF emp_rec.FIRST_NAME IS NOT NULL THEN  
        DBMS_OUTPUT.PUT_LINE(emp_rec.FIRST_NAME  || ' and ' || emp_rec.EMAIL);
      else 
        DBMS_OUTPUT.PUT_LINE(' NO record found!!!' );
    END IF;
    CLOSE emp_cur;
END;

Programmer/user defined record :-  PL/SQL offers programmer to create their own record structure(complete control over - number, names, and datatypes of fields in the record.) and it is handy, when we deal with multiple tables and views.Below is the general syntax of creating programmer record: 

TYPE <type_name> IS RECORD
  (<field_name> <datatype>,
   <field_name> <datatype>,
    ...
  <field_name> <datatype>
);

Data type of above record declaration includes primitive types,declaration using %TYPE and %ROWTYPE attributes.Consider following example which creates a record based on two different tables and display fields value.

DECLARE
TYPE emp_compny_record_type IS RECORD(
EMP_NAME varchar(50), 
DEPARTMENT_ID VARCHAR(34), 
EMAIL employees.email%TYPE,
MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE
);
emp_compny_rec emp_compny_record_type;
BEGIN
SELECT first_name||' '||last_name "Name", DEPARTMENT_ID,EMAIL, MANAGER_ID
   INTO emp_compny_rec FROM employees 
   WHERE EMPLOYEE_ID = 7;
DBMS_OUTPUT.put_line('NAME AND EMAIL OF EMPLOYEE is: ' || emp_compny_rec.EMP_NAME
 || ' and ' || emp_compny_rec.EMAIL||'@devinline.com');
END;

Note:- 

1. A RECORD type defined in a PL/SQL block is a local type.A RECORD type defined in a package specification is a public item, can be accessed in PL/SQL context with pkgname[.]record_name.
2. A RECORD type defined in a package specification is incompatible with an identically defined local RECORD type. i.e: Package level record type cannot be same as local record even if name is name.  
3. A RECORD type cannot be created at schema level. Record type is not standalone type.
4. A record can be part of another record that is termed as nested record. Following code sahow how it can be declared and used:

   DECLARE
   TYPE location_rec_type IS RECORD(
     LOCATION_ID NUMBER(9),
     ORGANIZATION_NAME VARCHAR(23)
   );
   TYPE emp_compny_record_type IS RECORD(
   EMP_NAME varchar(50), 
   DEPARTMENT_ID VARCHAR(34), 
   EMAIL employees.email%TYPE,
   MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE,
   location_rec location_rec_type --Nested record 
   );
   emp_compny_record emp_compny_record_type;
   BEGIN
   --access nested record like this 
   emp_compny_record.location_rec.LOCATION_ID := 123;
   emp_compny_record.location_rec.ORGANIZATION_NAME := 'ABC';
   DBMS_OUTPUT.put_line('Nested record LOCATION_ID and ORGANIZATION_NAME  ' 
           || emp_compny_record.location_rec.LOCATION_ID 
           ||'and'||emp_compny_record.location_rec.ORGANIZATION_NAME);
   END;
   

5. Record is only accessible in PL/SQL context, not in SQL context except when INTO clause is used with select statement.The INTO clause of an implicit query is the only part of a SQL DML statement in which a PL/SQL record can be referenced.

   -- emp_rec is PL/SQL record
   emp_rec employees%ROWTYPE
   SELECT *  INTO emp_rec FROM EMPLOYEES; 
   

6. Default values for individual fields in a record can be assigned with the DEFAULT or := syntax.

   TYPE emp_rec_type IS RECORD (
       ORG_NAME  employees.orgname%TYPE DEFAULT 'CMK',
    Address  employees.address%TYPE := 'XYZ, #306'
     );
   

7.  Record be NOT NULL (in which case you must also assign a default value).
8. NULL assignment is allowed for record, all fields reset to NULL.(valid from oracle 7.3)

Values assignment to and from Record 

There are four ways to alter record(modify values of  fields) in PL/SQL. Consider following record for  further description.

1. Direct field assignment with the assignment operator :- Using assignment operator (:= ) values of each filed can be modified. Refer case 1 in following code lines.
2. SELECT INTO from an implicit cursor:- INTO clause of select statement passes query result into record. Query result and record should be structurally similar.Refer case 2 in following code line. 
3. FETCH INTO from an explicit cursor:- Explicit cursor uses FETCH to populate record and  it can be done in two ways - directly fetch and assign to record  or assign value to each field of record. Refer below code line case 3.
4. Aggregate assignment:- Values of one record can be assigned to another in one shot, no explicit for each field mapping(provided structure of both records are same).

Following sample code shows all above four ways to assign/alter record values:-

DECLARE
TYPE emp_compny_record_type IS RECORD(
  FIRST_NAME employees.FIRST_NAME%TYPE, 
  EMAIL employees.email%TYPE
);
 emp_record emp_compny_record_type;
 emp_record_duplicate emp_compny_record_type;
 CURSOR emp_cur(emp_id NUMBER) IS SELECT FIRST_NAME, EMAIL FROM HR.EMPLOYEES
   WHERE EMPLOYEE_ID = emp_id;  
BEGIN
  /*case 1-Direct field assignment */
  emp_record.FIRST_NAME := 'NIKHIL';
  emp_record.EMAIL := 'DUMMY@devinline.com';
  
  /*case 2 - SELECT INTO from an implicit cursor*/
  SELECT FIRST_NAME,EMAIL INTO emp_record from HR.EMPLOYEES where EMPLOYEE_ID = 7; 
  
  /*case 3 - FETCH INTO from an explicit cursor*/
  OPEN emp_cur(7);
    FETCH emp_cur INTO emp_record;
    --OR
    FETCH emp_cur
      INTO emp_record.FIRST_NAME,
           emp_record.EMAIL;
  
  /*case 4 - Aggregate assignment - no field by field mapping*/
  emp_record_duplicate := emp_record;
  DBMS_OUTPUT.put_line('Duplicate record FIRST_NAME and EMAIL '||
      emp_record_duplicate.FIRST_NAME || ' and ' ||emp_record_duplicate.EMAIL); 
  
END;

This is all about record type in PL/SQL. In next post we will learn about reference type in PL/SQL-   PL/SQL cursors.

Previous: PL/SQL Collections(Array, VARRAY and Nested Tables) Next: PL/SQL Implicit and Explicit cursor

PL/SQL Cursor variable - REF CURSOR

In previous post we discussed about implicit & explicit cursor and also understood how it is different from cursor variable.Lets reiterate it again, both explicit cursor and implicit cursor are associated with a specific SQL DML statement(they are tied to specific queries), whereas cursor variable can refer to multiple DML statements (off course SELECT statement) throughout session. Read The main agenda of this post is to understand the declaration and uses of cursor variable.

Cursor variable -- a reference to a cursor object or query in the database.

Cursor variable is a pointer/reference to SQL work area and its value is the address of the that work area.Since it has address of SQL work area, it can accommodate multiple select statements(as shown here) returning different result sets(rows from different table).

Syntax and life cycle of cursor variable:-

Cursor variable is of type REF CURSOR/ SYS_REFCURSOR. General syntax of cursor type & variable declaration and life cycle of cursor variable is as  is as follows: first create type of cursor then create variable of that type termed as cursor variable followed by FETCH and close.
Note:- Once cursor is closed, if try to fetch rows it will throws error ORA-01001: invalid cursor.

/* Create the cursor type.- Return type is optional */
TYPE cur_type IS REF CURSOR [RETURN table%ROWTYPE];

/* Declare a cursor variable of that type. */
cur_var cur_type;

/* Open the cursor variable, associating with it a SQL statement. */
OPEN cur_type FOR <SELECT statement>;

/* Fetch from the cursor variable. */
FETCH cur_var INTO <table_rec_type/use%ROWTYPE>;

/* Close the cursor object associated with variable. */
CLOSE cur_var;

Cursor variable and Cursor object :- 

Cursor variable dynamically binds with select statement when OPEN FOR syntax is executed
Note:- When a cursor variable is created in PL/SQL program unit, it is just a reference variable. Actual cursor object (the result set identified by the cursor SQL statement) is created when OPEN FOR is executed for select query and assigned to a cursor variable. See the following diagram to understand difference between cursor variable and cursor object:-

Cursor variable and cursor Object creation in PL/SQL- cursor variable is reference to cursor Object

Lets write a sample program to understand how do we create concrete cursor type and  its variable to fetch records:-

DECLARE 
  /*Cursor type declaration*/
  TYPE v_empcur_type IS REF CURSOR;-- RETURN EMPLOYEES%ROWTYPE;
  /*cursor variable declaration - no select statement binding here*/
  cur_var v_empcur_type;
  v_deptId   NUMBER(5) := 90;
  v_locId NUMBER(5) := 1800;
  v_emp_row EMPLOYEES%ROWTYPE;
  v_dept_row DEPARTMENTS%ROWTYPE;
BEGIN
  /*First binding:- OPEN refcursor for a select statement */
  OPEN cur_var FOR select * from employees where
                DEPARTMENT_ID = v_deptId;
  dbms_output.put_line('--First binding output--');
  dbms_output.put_line('FIRST_NAME' || '        '|| 'EMAIL');
  dbms_output.put_line('-------------     ---------------');
LOOP
    FETCH cur_var INTO v_emp_row;
      EXIT WHEN cur_var%NOTFOUND;
    dbms_output.put_line(v_emp_row.FIRST_NAME || '         
              ' || v_emp_row.EMAIL||'@devinline.com');
  END LOOP;
/*Second binding :- OPEN refcursor for another select statement */
  OPEN cur_var FOR select * from DEPARTMENTS where
                LOCATION_ID = v_locId;
  dbms_output.put_line('--Second binding output --');
  dbms_output.put_line('DEPARTMNET_NAME' || '        '|| 'MANAGER_ID');
  dbms_output.put_line('-------------     ---------------');
  LOOP
      FETCH cur_var INTO v_dept_row;
        EXIT WHEN cur_var%NOTFOUND;
      dbms_output.put_line(v_dept_row.DEPARTMENT_NAME || '         
                ' || v_dept_row.MANAGER_ID);
    END LOOP;
  CLOSE cur_var;
END;

==========Sample output========================
--First binding output--
FIRST_NAME        EMAIL
-------------     ---------------
Steven      
              SKING@devinline.com
Neena      
              NKOCHHAR@devinline.com
Lex      
              LDEHAAN@devinline.com
NIKHIL      
              NIKSR@devinline.com

--Second binding output --
DEPARTMNET_NAME        MANAGER_ID
-------------       ---------------
Marketing       201

Purchasing       114
========================================
In above program, cursor type is created followed by a cursor variable and two variable one of type employee row and another of department row type is created. In begin block, cursor is opened and associated it with a select statement. Fetch cursor in Loop and display name and email. Again same cursor is bind with another select statement and again fetch and display rows from it. Above program unit uses same cursor variable to refer two select statement one after another, it is most important and noticeable feature of cursor variable.

Question:- Why error "ORA-01001: invalid cursor " is thrown, if try to fetch rows after cursor has been closed ?
Answer:- Always remember, cursor does nor contain value, it is just a pointer to result set. Once we execute close command, pointer to result set is removed so , cursor is just a variable now and no cursor object is attached to it, so it throws error.

Note:-

1. Cursor variable also has same set of attributes as explicit cursor : ISOOPEN, FOUND, NOTFOUND, ROWCOUNT. Refer this table for more detail about cursor attributes.
2. Cursor variable can be of two types: Strong type and Weak type.If RETURN clause is added while creating cursor type it is termed as Strong type, however if is missing then that type is called Weak type.Read Difference between strong type and weak type. in more detail.
3. We can perform assignment operations with cursor variables and also pass these variables as arguments to procedures and functions. If either of cursor variable participating in assignment operation are weak then compile time check cannot be achieved. And if there is a type mismatch or both cursor variable are not structurally same then error is reported at runtime.
   If both cursor variable is strong type, compile time check is done and error is reported if both are structurally not same.

   --Both cursor variable are strong type,compiler time check for structure compitablity
   Type emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE;
   Type dept_cur_type IS REF CURSOR RETURN departments%ROWTYPE;
   emp_cur_var emp_cur_type;
   dept_cur_var dept_cur_type;
   .....
   /*compile time error for below assignment: PLS-00382: expression is of wrong type*/
   emp_cur_var := dept_cur_var;
   

   Assignment operation is allowed but cursor variable equality, inequality, or nullity check using comparison operators is not allowed. 
4. A cursor object(a query/result-set) may be referenced by two cursor variable.
5. NULL cannot be assigned to a cursor variable.
6. Cursor variables cannot be declared in a package since they do not have a persistent state.

Read also:
1. Difference between strong cursor type and weak cursor type
2. Difference between SYS_REFCURSOR and REF CURSOR
3. Difference between explicit cursor and cursor variable
4. REF CURSOR, SQL and PL/SQL interaction
Previous: PL/SQL Implicit & Explicit Cursor Next: PL/SQL program unit- Procedure and Function

How to test a oracle stored procedure with ref cursor from SQL*PLUS or SQL Developer

Oracle stored procedure is one kind of PL/SQL program unit. Consider the following cases :
1. stored procedure with scalar type - NUMBER, VARCHAR
2. stored procedure with REF CURSOR or SYS_REFCURSOR. What is difference between REF CURSOR and SYS_REFCURSOR?

Execute/Test stored procedure with primitive type:-

Step 1:- Create a stored procedure with primitive type.Below is sample code for the same ignore this step if you already have some stored procedure created. Execute it, SQL*PLUS/SQL Develope worksheet.

create or replace procedure get_employee_total_saalry 
(
  base_salary IN NUMBER 
, bonus  IN NUMBER 
, total_sal OUT NUMBER 
) as 
begin
  total_sal :=base_salary + .12*base_salary + bonus; 
end get_employee_total_saalry;

Step 2:- Create anonymous block to execute the stored procedure.Execute following blocks and see the output as total salary.

--Stored procedure having primitive type as paraemter 
declare
type rc is ref cursor;
v_base_sal NUMBER(5) := 1000;
v_bonus NUMBER(5)  := 2300;
v_total NUMBER(10) ;
begin
--call stored procedure with two IN and one OUT type parameter
HR.get_employee_total_saalry(v_base_sal,v_bonus,v_total);
    dbms_output.put_line(' Total sal is ' ||v_total);
end;

Execute/Test stored procedure with REF_CURSOR/SYS_REFCURSOR type:-

Step 1:- Create a stored procedure  with REF CURSOR. Below is the sample code for the same, ignore this step if you already have procedure created.

create or replace procedure get_employees_name(
v_deptId_in NUMBER,
v_cur OUT SYS_REFCURSOR ) 
IS
begin
  open v_cur for select FIRST_NAME,LAST_NAME from
        employees where DEPARTMENT_ID = v_deptId_in;
end get_employees_name;

Step 2:- Now write execution block from where program unit with REF CURSOR is tested.

--using refcursor --
declare

-declare ref cursor type 
type rc is ref cursor;
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur rc; -declare ref cursor variable
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,v_cur);
dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
dbms_output.put_line('---------------------------------');
LOOP
    FETCH v_cur INTO v_fname, v_lname;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(v_fname || '     ' || v_lname);
  END LOOP;
  CLOSE v_cur;
end;

Using SYS_REFCURSOR also, we can write program blocks to test same stored procedure. (Remember, SYS_REFCURSOR is just a predefined weak REF CURSOR, so we do not have to declare a cursor type like "type rc is ref cursor". Following is testing program(differences from above program has been highlighted):

declare
--type rc is ref cursor; NOT required 
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur SYS_REFCURSOR; --rc replaced with predefined type SYS_REFCURSOR
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,v_cur);
dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
dbms_output.put_line('---------------------------------');
LOOP
    FETCH v_cur INTO v_fname, v_lname;
    EXIT WHEN v_cur%NOTFOUND;
    
    dbms_output.put_line(v_fname || '      ' || v_lname);
  END LOOP;
  CLOSE v_cur;
end;

Alternate way execute stored procedure with REF_CURSOR/SYS_REFCURSOR type:-

Declare the cursor variable of type refcursor and run stored procedure with exec. In SQL developer/SQL*PLUS it can be executed like this :-

--cursor variable declaration 
variable v_emp_cur refcursor;
--execute procedure 
execute get_employees_name(90,:v_emp_cur);
--display result referenced by ref cursor.
print v_emp_cur;

=====End of post=======

Read : Difference between SYS_REFCURSOR and REF CURSOR.

PL/SQL Cursor - Explicit and Implicit cursor, Cursor variable

It is quite inevitable to do a sincere PL/SQL programming without sound understanding of cursors. The main agenda of this post is to discuss about different types of cursors and its uses in PL/SQL. It is the reference data type of PL/SQL as mentioned in previous post where we discussed about Data types in PL/SQL.

What is cursor:- When a SQL statement is executed from PL/SQL program unit the Oracle database server assigns a private work area for that statement and that area contains processing info of that SQL statement and information about data returned. The PL/SQL cursor is a pointer to this private SQL area - i.e : Using this cursor we assign name to that private area and we have a handle to control the information stored.

Select statement execution in PL/SQL and result set pointed by cursor. 

For example:- when we execute a select statement in PL/SQL, select statement returns a result-set(a virtual table which has same structure as table(rows and columns)). The cursor points to this virtual table and this virtual table is transient(not permanent), it only exist for the duration of SELECT statement execution .

Note:- In PL/SQL we have two different type of SQL statements,Static SQL and Dynamic SQL. Read difference between Static and Dynamic SQL. The discussion of the cursors in PL/SQL is restricted to Static SQL ONLY.

In PL/SQL cursors can be broadly classified in following category :-
1. Static cursor - cursor bound to a specific SQL statement(SQL decided at compile time itself), it is similar to final reference in Java. Static cursor can be further classified in two category-

• Implicit cursor - Managed by PL/SQL itself and programmer has limited control over it.
  PL/SQL assign an implicit cursor for each SQL DML statement like Insert, Update, Delete and Select statement returning only one row.  
• Explicit cursor.- Managed by programmer and has programmer has complete control over it.   

2. Cursor variable :- It is also associated with Static SQL, however one cursor variable can refer to multiple SQL statements(defined at compile time). All SQL statement must be defined at compile time.
Both Static cursor and cursor Variable are collectively termed as Session cursors and it's existence is util session persist.Session cursor lives in session memory until the session ends.Before going into detail of each cursor type, let understand life cycle of cursors / operations performed on cursor.

Life cycle of PL/SQL Cursor:- Each cursor regardless of its types passes through same set of operation when used in PL/SQL program unit - 1. OPEN 2. FETCH 3. CLOSE. Following diagram captures life cycle of Cursor.

Life cycle of PL/SQL Cursor - OPEN, FETCH and CLOSE operation.

OPEN operation:- When you open a cursor,SQL statement in private memory area is ready to execute inside SQL engine and after execution, pointer to the active row is set to the first row of result set.
FETCH operation:- FETCH command retrieves current row from result set and PL/SQL moves pointer to next row. If there are no more rows to retrieve, then FETCH does not raise any exception, it simply does nothing.
CLOSE operation:- The CLOSE command closes the cursor and releases all memory used by the cursor.Once the cursor is closed, the cursor no longer has a result set.
Note:- For implicit cursor all the above operation is performed by PL/SQL engine, however all operation is carried out by programmer in the case of Explicit cursor.

Implicit Cursor- Managed by PL/SQL engine 

When any DML statement including select statement returning single row is executed in PL/SQL program and programmer has not assigned an explicit cursor (pointer) for that DML statement then PL/SQL assigns an implicit cursor to it and controls its life cycle, so programmer does not have any control over it.
Programmer does not have control over life cycle of implicit cursor(cannot perform OPEN, FECT and CLOSE operation), however for each SQL DML statements (INSERT, UPDATE, DELETE and  one row returning SELECT) that database server executes opens an implicit cursor called "SQL" and attributes associated with it can be used to get information about the most recently executed SQL statement. The SQL cursor has the four attributes :

• SQL%FOUND - returns true if UPDATE, DELETE, or INSERT affected at least one record. And returns true for SELECT only when it returns only one row, else false. 
• SQL%NOTFOUND- For UPDATE, DELETE, or INSERT statements it  is the opposite of %FOUND. 
• SQL%ROWCOUNT - If RDBMS has opened a SQL cursor in current session then return TRUE else NULL. See following example for better understanding.
• SQL%ISOPEN - Always return false because for all DML statements the implicit cursor will already have been opened and closed implicitly by PL/SQL.

Note:- 1. PL/SQL creates cursor implicitly for every UPDATE, DELETE and INSERT statement execution, programmer cannot create explicit cursor. No Explicit cursor for UPDATE, INSERT and DELETE.
2. When a SELECT statement is executed and it returns single row, here programmer has a choice to use implicit or explicit cursor. It is recommended that always use explicit cursor for select statement even for single row query. (Why? -  Read point# 3).
3. If an implicit SELECT statement returns more than one row, it raises the TOO_MANY_ROWS exception and current block execution terminates and control reaches in exception block.

Now lets write a sample PL/SQL program and summarise implicit cursor understanding and familiarise with implicit cursor "SQL" attributes handling:- Execute the following PL/SQL program in SQLPLUS or SQL developer. How to execute PL/SQL program in SQL*PLUS or SQL developer?

set serveroutput on
DECLARE 
v_orgId NUMBER(3) := 90;
BEGIN
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,
   HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) 
values (007,'NIKHIL','RANJAN','NIKSR','515.123.4568',
  to_date('21-SEP-11','DD-MON-RR'),'AD_VP',17000,null,100,90);
-- if commit is enabled , SQL%FOUND will return false, since SQL cursor is closed
-- However, SQL%ROWCOUNT will be True since , cursor was opened in this session
--commit;
---inserted a row so, SQL cursor open for it implicitly 
IF SQL%FOUND THEN -- affected at least one row
  DBMS_OUTPUT.PUT_LINE('Yes, one row inserted successfully!!');
ELSE 
DBMS_OUTPUT.PUT_LINE('No,insert failed!');
END IF;

update HR.EMPLOYEES set salary  = salary * 1.2 where Department_ID = v_orgId;
-- if commit is enabled , SQL%FOUND will return false, since SQL cursor closed
-- However, SQL%ROWCOUNT will be True since , cursor was opened in this session
--commit;   
---Updated rows so, SQL cursor open for it implicitly
IF SQL%FOUND THEN -- affected at least one row
  DBMS_OUTPUT.PUT_LINE('Yes,rows updated successfully!!');
ELSE 
 DBMS_OUTPUT.PUT_LINE('No,updated occurred!');
END IF;

--SQL Cursor is opened, it returns TRUE else NULL 
IF SQL%ROWCOUNT IS NULL THEN -- affected at least one row
  DBMS_OUTPUT.PUT_LINE('SQL cursor not opened!!');
ELSE 
 DBMS_OUTPUT.PUT_LINE('SQL cursor opened!!');
END IF;
END;
/ 

Here I have executed it from SQL*PLUS(Created a file cursor_1.sql with above sample codes and executed via SQL*PLUS) and sample output is as follwos.
=========Sample output==============
SQL> @cursor_1.sql
Yes, one row inserted successfully!!
Yes,rows updated successfully!!
SQL cursor opened!!

PL/SQL procedure successfully completed.
==================================
In above program, an insert statement is executed and check for SQL%FOUND returns true and display output accordingly. Similarly, when an update query is executed, it will updated multiple rows in database so, check of SQL%FOUND returns true. Finally,when check for SQL%ROWCOUNT
is done it also return true since SQL cursor has been opened in this session(for Insert and Update).Please take a note of following points about above program.

1. When commit is enabled in above program, SQL%FOUND returns false in both Insert and Update check because after commit SQL cursor has flushed its attributes however, SQL%ROWCOUNT still return true because cursor was opened in this session. 
2. When SQL%ROWCOUNT will give NULL, Just comment Insert and Update statements and run this program again in new session, you will get NULL this time because no SQL cursor was opened for any SQL DML statements.

Explicit cursor - a reference/pointer to SELECT statement 

An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of our PL/SQL program and that select stament is assigned a name. Explicit cursor is not possible for UPDATE, DELETE, and INSERT statements, as stated in implicit cursor section.Explicit cursor is only for SELECT statement, No explicit cursor for UPDATE, DELETE, and INSERT statements. 
Do we really need explicit cursor for update,delete and insert ?- No, attributes associated with implicit cursor "SQL" has sufficient information to understand whether these operation has been performed of not, as we saw in previous sample example.
With explicit cursor programmer has complete control over state of cursor and its attributes like FOUND, NOTFOUND, ISOPEN and ROWCOUNT gives information for efficient cursor processing. Let's write a sample program to understand life cycle of cursor and how does PL/SQ Loop can be used to iterate over a cursor.

---Explicit cursor Anonymous program unit 
DECLARE
--Declare a explicit cursor with static SQL and
--"employee_cursor_type" is a valid identifier 
CURSOR employee_cursor_type IS SELECT FIRST_NAME, LAST_NAME, 
    EMAIL FROM HR.EMPLOYEES where Department_ID = 90;  
v_emplpoyee HR.EMPLOYEES%ROWTYPE;
BEGIN
--Check whether cursor is open or not 
IF NOT employee_cursor_type%ISOPEN THEN 
  OPEN employee_cursor_type;
   -- iterare rows pointed by cursor and 
   -- exit loop when no more record found.
    LOOP
      -- FETCH ROWS from cursor until, it reaches end of virtaul table(ResultSet)
      FETCH employee_cursor_type INTO v_emplpoyee.FIRST_NAME,
                    v_emplpoyee.LAST_NAME,v_emplpoyee.EMAIL;
      -- Exit when no record found
      EXIT WHEN employee_cursor_type%NOTFOUND; 
      DBMS_OUTPUT.PUT_LINE ('First name of employee is ' ||v_emplpoyee.FIRST_NAME 
                ||' and email is: '|| v_emplpoyee.EMAIL||'@devinline.com');
   END LOOP;
  END IF;
END;

===================Sample output==========================
First name of employee is Steven and email is SKING@devinline.com
First name of employee is Neena and email is NKOCHHAR@devinline.com
First name of employee is Lex and email is LDEHAAN@devinline.com
First name of employee is NIKHIL and email is NIKSR@devinline.com
========================================================
In above program, an explicit cursor is declared with a select statement in declaration block along with a variable declaration of Employee row type. Before opening cursor, it is verified that cursor is open or not , if it is not open then open it and fetch rows and assign values into employee row type variable. NOTFOUND attribute of cursor is checked to exit from loop(if no more record is available in result set poined by cursor).

Explicit cursor declaration :-  An explicit cursor can be created in various ways in declaration bock. General syntax of cursor declaration is as follows:

CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]
   [ RETURN return_specification ]
   IS SELECT_statement;

Above declaration can be read as, cursor_name is name of cursor, followed by a optional parameter passing which can be used in select statement and an optional return declaration and finally a select statement. For example :
1. Cursor without parameters: Here employee_cur pointing to employees with department_id= 90.

--a cursor without parameters. 
CURSOR employee_cur IS 
   SELECT FIRST_NAME FROM HR.EMPLOYEES where department_id = 90;

2. Cursor without parameters:- Parameter is passed with cursor pointing to select statement and it can be used to get varied result-set. Here dept_id is passed and it is used to query database.

--a cursor with parameters. 
CURSOR employee_cur2 (dept_id_in IN NUMBER) IS
   SELECT FIRST_NAME FROM HR.EMPLOYEES where department_id = dept_id_in;

3. Cursor without return clause:- Return clause specifies what type of rows the cursor pointing. Here, return clause specifies that it will return employees rows. 

--a cursor with return clause.
CURSOR employee_cur(dept_id_in IN NUMBER) RETURN employees%ROWTYPE
  IS SELECT * FROM employees where department_id = dept_id_in;

Properties and constraints with explicit cursor:-
1. Cursor cannot be used as a variable.It means, it cannot be assigned to another variable and it cannot be assigned any value other than a select statement.
2.  SELECT statement pointed by cursor does not necessarily only contain columns of table, it can be a aliased values or modified value too. Generally, column aliases are done when we manipulate column and want to display effective value as column name like following example:

CURSOR employee_cur IS
  SELECT first_name,email, 
  salary + bonus_var new_salary, /*new_salary is aliased name */
   FROM employees
   WHERE department_id = 90;

3.In PL/SQL table column always gets preference over local variable of same name, so naming convention is important to follow to avoid such problem. Local variable not used here, database column gets preference.

DECLARE
salary NUMBER := 1000;
CURSOR employee_sal_cur
  IS
  SELECT salary + salary  
/*database value is added two times, local variable not used */
 FROM employees where first_name= 'Nikhil';
BEGIN
OPEN ...
FETCH... 
END;

4. It is good practice to close opened cursor when they are not needed any more, because each cursor consume some memory in shared global area depending on size of result set it it refereeing to.When an instance of database server starts parameter file (init.ora) specifies maximum no of  cursors allowed to be open (default value is 50) in a given session. If at any occasion, maximum limit crosses it throws an exception "ORA-01000: maximum open cursors exceeded".
Note: No error or exception arise, if we do not close cursor. However, in order to avoid unfortunate condition it is good to close cursor after its use.
5. Explicit cursor attributes:- The information about current status of cursor is obtained from cursor attributes.Following table describes them in detail:

Cursor attributes	Description
%FOUND	Returns TRUE if record was fetched successfully, FALSE otherwise. If cursor has not yet been opened, a reference to the %FOUND attribute raises the INVALID_CURSOR exception.
%NOTFOUND	Returns TRUE if record was not fetched successfully, FALSE otherwise.If cursor has not yet been opened, a reference to the %FOUND attribute raises the INVALID_CURSOR exception.
%ROWCOUNT	Returns number of records fetched from cursor at that point in time.
%ISOPEN	Returns TRUE if cursor is open, FALSE otherwise.If cursor is already open , it throws ORA-06511: PL/SQL: cursor already open

Always use these attributes in PL/SQL code , if used in SQL context it will throw exception.For detail refer this. As explained earlier implicit cursor SQl also posses these attributes, however there are noticeable differences between them. Read Difference between implicit and explicit cursor attributes.

This is all about explicit and implicit cursor in PL/SQL. In next post a versitile cursor type termed as - cursor variable, will be discussed.

  Previous: PL/SQL Record  Next: PL/SQL Cursor variable