PL/SQL Cursors

Cursor is a private SQL memory area, which is used to process multiple records and also this is a record by record process, all database systems having two types of static cursors.

  1. Implicit Cursor
  2. Explicit Cursor

Implicit Cursor

Implicit cursor for SQL statements returns single record is called implicit cursor, implicit cursor memory area is also called as ‘ CONTEXT AREA ’, when a PL/SQL block contains ‘ SELECT INTO clause ’ or DML statement then oracle server automatically creates a memory area.

This memory area returns single record when PL/SQL block contain ‘SELECT INTO Clause’ where as this memory area returned multiple records when PL/SQL block contain DML statements, but these multiple records process at a time by SQL engine

DECLARE

v_ename varchar2(10);

v_sal number(10);

BEGIN

Select ename,sal into v_ename,v_sal from emp

Where empno = 7839;

Dbms_output.put_line(v_ename||’ ’||v_sal);

END;

Output=> KING 5000

Explicit Cursors

For SQL statements returns multiple records is called explicit cursor and also this is a record by record process, explicit cursor memory area is also called as ‘ACTIVE SET AREA’, In explicit cursor we are storing multiple records and also these records are controlled by database developers explicitly.

Explicit Cursor Life Cycle

DECLARE

OPEN

FETCH

CLOSE

DECLARE: In declare section of the PL/SQL block we are defining the cursor using following syntax.

CURSOR CursorName IS Select Statement;

DECLARE

Cursor c1 is select * from emp

Where deptno = 10;

OPEN:-  Whenever we are opening the cursor, then only oracle server fetch data from table into cursor memory area, because in all database system, whenever we are opening the cursor, then only cursor select statement are executed, this statement used in executable section of the PL/SQL block

OPEN CursorName;

Note: Whenever we are opening the cursor implicitly cursor pointer points to the first record in the cursor.

FETCH:- (Fetching from the cursor) using fetch statements we are fetching data from cursor into PLS/SQL variables, Below is the syntax

FETCH CursorName INTO Variable1, Variable2;

CLOSE: Whenever we are closing the cursor al the resources allocated from the cursor memory area automatically released, below is the syntax

CLOSE CursorName;

DECLARE

Cursor C1 is select ename, sal from emp;

i emp%rowtype;

BEGIN

OPEN C1;

FETCH C1 into i.ename, i.sal;

Dbms_output.put_line(i.ename||’ ‘||i.sal);

FETCH C1 into i.ename, i.sal;

Dbms_output.put_line(i.ename||’ ‘||i.sal);

CLOSE C1;

END;

Output=>  KING 5000

                     BLAKE 2850

One fetch statement is equal to one record.

EXPLICIT Cursor Attributes: Every Explicit cursor having four attributes, these are

%NOTFOUND

%FOUND

%ISOPEN

%ROWCOUNT  

These all attributes are used in PL/SQL block when we are using these attributes, we must specify ‘CursorName’ Along with these attributes, below is the syntax

CursorName%AttributeName;

%NOTFOUND Attribute:

Except ‘%ROWCOUNT’ all other cursor attributes returns Boolean values either true or false, whereas ‘%NOTFOUND’ attribute always returns Boolean value either true or false, this attribute returns true if fetch statement does not return row and this attribute returns false if fetch statement returns at least one row.

Write a PL/SQL Program to display all names and their salary from employee table using ‘%NOTFOUND’ attribute.

DECLARE

Cursor C1 is select * from emp;

i emp%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO i;

EXIT when C1%NOTFOUND;

Dbms_output.put_line(i.ename||’ ‘||i.sal);

END LOOP;

CLOSE C1;

END;

Write a program to display first five highest salary from employee table using %ROWCOUNT

DECLARE

Cursor C1 is select * from emp order by sal desc;

i emp%TYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 into i;

EXIT when C1%ROWCOUNT > 5;

Dbms_output.put_line(i.sal);

END LOOP;

CLOSE C1;

END;

Output=>            5000

                                4000

                                2700

                                3000

2975

Write a PL/SQL cursor program to display the even number of records from emp table using %ROWCOUNT attribute.

DECLARE

Cursor C1 is select * from emp;

i emp%rowtype;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO i;

EXIT when C1%NOTFOUND;

IF MOD(C1%ROWCOUNT,2) = 0 THEN

Dbms_output.put_line(i.ename||’ ‘||C1%ROWCOUNT);

END IF;

END LOOP;

CLOSE C1;

END;

Whenever we are creating a cursor then oracle server automatically created four memory location along with cursor, these memory location are identified through Cursor Attributes, It behaves like a variable and also these variables stores a single value at a time.

%ROWCOUNT Attribute:

This attribute always returns number datatype therefore it counts number of records number fetched from the cursor not from the cursor memory area

DECLARE

Cursor C1 is select * from emp;

i emp%ROWTYPE;

BEGIN

OPEN C1;

FETCH C1 INTO i;

Dbms_output.put_line(i.ename||’ ‘||i.sal||’ ‘||C1%ROWCOUNT);

FETCH C1 into i;

Dbms_output.put_line(i.ename||’ ‘||i.sal||’ ‘||C1%ROWCOUNT);

Dbms_output.put_line(‘No of records fetched form the cursor is ’||’ ‘||C1%ROWCOUNT);

CLOSE C1;

END;

Output=>            KING 5000 1

                                BLAKE 2850 2

                                No of Records fetched from the cursor is 2

In oracle you can also use cursors to transfer data from one table into another or transfer data into arrays or transfer data into external files.

Write a PL/SQL cursor program to transfer data from emp table into another table.

Create Table XX(ename varchar2(10), sal number(10), serial number(10));

DECLARE

Cursor C1 is select * from EMP;

I emp%ROWTYPE;

R NUMBER(10);

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO I;

EXIT when C1%NOTFOUND;

R := C1%ROWCOUNT;

Insert into XX values(i.ename,i.sal,R);

END LOOP;

CLOSE C1;

END;

DECLARE

Cursor C1 is select * from emp;

i emp%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO i;

EXIT when C1%NOTFOUND;

IF i.sal >= 3000 then

Dbms_output.put_line(i.sal||’ ‘||i.ename||’ ‘||’HIGHSAL’);

END IF;

END LOOP;

CLOSE C1;

END;

Output=>            KING 5000 HIGHSAL

                                SCOTT 3000 HIGHSAL

                                FORD 3000 HIGHSAL

SAM 4000 HIGHSAL

%FOUND Attribute:

This attributes also returns Boolean value either true or false, it returns true if cursor memory having records after fetching data from the cursor.

CursorName%FOUND;

Write a PL/SQL program to display al employees name and their salaries using %FOUND Attribute.

DECALRE

Cursor C1 is select * from emp;

I emp%ROWTYPE;

BEGIN

OPEN C1;

FETCH C1 INTO I;

WHILE C1%FOUND

LOOP

FETCH C1 INTO I;

Dbms_output.put_line(i.ename||’  ‘||i.sal);

END LOOP;

CLOSE C1;

END;

Note: Whenever we are returning summarized data if column having a null value then automatically total result becomes null, to overcome this problem we must use ‘NVL’ function

Write a PL/SQL program to display total salary from emp table using %NOTFOUND and without SUM Function.

DECLARE

CURSOR C1 is select * from emp;

I emp%ROWTYPE;

A number := 0;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO I;

EXIT when C1%NOTFOUND;

A := A + NVL(i.sal,0);

Dbms_output.put_line(A);

END LOOP;

CLOSE C1;

END;

%ISOPEN Attribute:

This attribute always returns Boolean value either true or false, it returns true if cursor is already open otherwise it returns false.

DECLARE

CURSOR C1 IS select * from emp;

I emp%ROWTYPE;

BEGIN

IF NOT C1%ISOPEN THEN

OPEN C1;

END IF;

LOOP

FETCH C1 INTO I;

Dbms_output.put_line(i.ename||’ ‘||i.sal);

EXIT when C1%NOTFOUND;

END LOOP;

CLOSE C1;

END;