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