PL/SQL - Cursor FOR loop

In the last post we have seen how to loop through cursor records by using its attributes to manually check for the different stages of cursor OPEN, FETCH and CLOSE. In this post we will write more compact code with CURSOR FOR loop statement. A FOR loop opening a cursor directly is known as a CURSOR FOR loop.

Following program will print the Salary for each Employee in EMP table.

/*Enable the SERVEROUTPUT to display messages in output console*/
SET SERVEROUTPUT ON

DECLARE
/*Declare an explicit cursor to select employee information*/
 CURSOR C_EMP IS
 SELECT EMP_NAME, SAL
 FROM EMP;
BEGIN
/*FOR Loop uses the cursor C_EMP*/
FOR EMP IN C_EMP
LOOP

 /*Display message*/
 DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME||' Salary = '||EMP.SAL);

END LOOP;
END;
/

You will get the output similar to this:

Nasir Naveed Salary = 30000
Aurangzaib Khan Salary = 40000
Muhammad Anwar Salary = 50000
...
PL/SQL procedure successfully completed.

Note that, with the help of CURSOR FOR loop we have:

• No need to declare the block variables for holding the cursor columns. The CURSOR FOR loop index implicitly acts as a record of the cursor type.
• No need to explicitly open or close the cursor in the PL/SQL program.

Related Post(s):

• PL/SQL Cursor - Overview
• PL/SQL - Cursor Attributes and Loop

PL/SQL - Cursor Attributes and Loop

In the last post we have seen an overview of the Cursor in PL/SQL. In this post we will write a sample program to use Cursor which helps us to understand its execution cycle and query the information about different stages by using its attributes.

Lets say we have an EMP table.

create table EMP(  
  emp_no    number(4,0),  
  emp_name    varchar2(10),  
  hiredate date,  
  sal      number(7,2),  
  deptno   number(2,0)
)

This program uses the cursor attributes %ISOPEN, %NOTFOUND, and %ROWCOUNT to fetch the data from the EMP table and display it:

/*Enable the SERVEROUTPUT to display messages in output console*/
SET SERVEROUTPUT ON

DECLARE

	/*Declare a cursor to select employees data*/
	CURSOR C_EMP IS
	SELECT EMP_NO,EMP_NAME
	FROM EMP;

	LOCAL_EMP_NO EMP.EMP_NO%TYPE;
	LOCAL_EMP_NAME EMP.EMP_NAME%TYPE;
BEGIN

/*Check if the cursor is already open*/
IF NOT C_EMP%ISOPEN THEN
	DBMS_OUTPUT.PUT_LINE('Employee Data:');
END IF;

/*Open the cursor and iterate in a loop*/
OPEN C_EMP;
LOOP

 /*Fetch the cursor data into local variables*/
 FETCH C_EMP INTO LOCAL_EMP_NO, LOCAL_EMP_NAME;
 EXIT WHEN C_EMP%NOTFOUND;
	
 /*Display the employee information*/
 DBMS_OUTPUT.PUT_LINE(chr(10)||'Employee Info:'||C_EMP%ROWCOUNT);
 DBMS_OUTPUT.PUT_LINE('Employee No:'||LOCAL_EMP_NO);
 DBMS_OUTPUT.PUT_LINE('Employee Name:'||LOCAL_EMP_NAME);

END LOOP;

END;
/

You will get the output similar to this:

Employee Data:
Employee Info:1
Employee No:101
Employee Name:Muhammad Idrees

Employee Info:2
Employee No:102
Employee Name:Kamran Khan

Employee Info:3
Employee No:103
Employee Name:Ejaz Ahmed

….
PL/SQL procedure successfully completed.

Related Post(s):

• PL/SQL - Cursor Overview
• PL/SQL - Cursor FOR loop

PL/SQL - Cursor Overview

The SQL statements embedded within a PL/SQL block are executed as a cursor. A cursor is a private memory area, which is allocated in the session's User Global Area (UGA).

Cursors can be classified as implicit and explicit cursors.

Implicit cursor is created by Oracle itself for all the SQL statements in executable section of a PL/SQL block. In this case, the cursor lifecycle is maintained by the Oracle Database.

The execution cycle of Explicit cursor can be controlled by the user. Database developers can explicitly declare an implicit cursor under the DECLARE section along with a SELECT query.

The cursor execution cycle

A cursor goes through the following stages during execution.

• The OPEN stage - a context area in the session's User Global Area is allocated, SQL statements are parsed and bounded. In case of the SELECT query, the record pointer points to the first record in the result set.
• The FETCH stage - fetches the data from the query result set. The record pointer moves incrementally towrads next record in case of multi-record set on every fetch. Cursor will be in fetch stage until the last record is reached in the result set.
• The CLOSE stage - closes the cursor, flushes the context area, and releases the memory back to the UGA.

Note that, in the case of an implicit cursor, all the steps are carried out by the Oracle Database.

Cursor attributes

You can find information about the cursor stages execution by looking into Cursor attributes:

• %ROWCOUNT: Applicble for Select and DML statements. It denotes the number of rows fetched until the last fetch(in case of SELECT) or impacted by the last DML operation.
• %ISOPEN: If the cursor is still open it return Boolean TRUE, otherwise FALSE. For an implicit cursor, this attribute is always FALSE.
• %FOUND: If the fetch operation switches and points to a record, it returns Boolean TRUE, otherwise FALSE.
• %NOTFOUND: Opposite to %FOUND attribute. Returns TRUE when the cursor pointer switches but does not point to a record in the result set.

Only %ISOPEN is the attribute that is accessible outside the execution cylce. Remaining three attributes you could not access outside.

Related Post(s):

• PL/SQL - Cursor Attributes and Loop
• PL/SQL - Cursor FOR loop