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