Cursors Mechanism in Oracle PL/SQL Programs

---

Cursors:
======
A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it, which is used to fetch more than one record at a time.

Cursors are classified into two types

• Implicit cursor
• Explicit cursor

Implicit cursor:
=========
The cursor mechanism which is maintained by the system automatically when we are trying to retrieve more than one record using select statement.

Explicit cursor:
=========
The cursor mechanism which is maintained by the user manually those cursors can be called as explicit cursors.

Whenever we are working with explicit curser we need to perform the following operations.

STEP-1 : declare the cursor

Syntax : cursor <cursor_name> is select * from <table_name> where condition;
Example: cursor c is select * from emp where deptno=10;

STEP-2 : open the cursor

Syntax : open <cursor_name>;
Example: open c;

STEP-3 : Fetch records from the cursor

Syntax : fetch <cursor_name> into <list of variables>;
Example: fetch c into a,b;

STEP-4 : Close the cursor

Syntax : close <cursor_name>;
Example: close c;

we consider the following two tables i.e emp, dept to write SQL queries in the coming PL/SQL programs.

Double click on the image to view the full image with clarity
Example :
======

SQL>declare
a emp%rowtype;
cursor c is select * from emp where deptno=&deptno; --Step-1
begin
open c; ----Step-2
fetch c into a; ----Step-3
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
close c; ----Step-4
end;

NOTE : '--' represents comments in PL/SQL Program.

The above example fetches only one record because the statements are not lies within the loops. Whenever we are working with loops then we use the following attributes.

• %found
• %notfound
• %rowcount
• %isopen

%found:
=====
This attribute is used for to check whether the record is found or not in the memory, it returns Boolean value either true or false.
If the record is found then it returns true
If the record is not found then it returns false

Example :
======

SQL>declare
a emp%rowtype;
cursor c is select * from emp where deptno=&deptno; --Step-1
begin
open c; ----Step-2
loop
fetch c into a; ----Step-3
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
exit;
end if;
end loop;
close c; ----Step-4
end;

%notfound:
=======
This attribute is used for to check whether the record is found or not in the memory.
If the record is found then it returns false
If the record is not found then it returns true

%rowcount:
=======
This attribute is used for to count the number of records in the cursor.

%isopen:
======
This attribute is used for to check whether the cursor is opened or not in the memory.

Example :
======

SQL>declare
cursor c is select * from emp;
e emp%rowtype;
begin
if c%isopen then
dbms_output.put_line(‘Cursor is already opened’);
else
open c;
end if;
loop
fetch c into e;
dbms_output.put_line(‘Mr.’||e.ename||’whose job is’||e.job);
exit when c%notfound;
end loop;
dbms_output.put_line(c%rowcount||’ rows were displayed’);
if c%isopen then
close c;
else
dbms_output.put_line(‘cursor is opened’);
end if;
end;

Example : PL/SQL block contains two cursors

SQL>declare
a emp%rowtype;
b dept%rowtype;
cursor c is select * from emp;
cursor c1 is select * from dept;
begin
open c;
loop
fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
exit;
end if;
end loop;
close c;
open c1;
loop
fetch c1 into b;
if c1%found then
dbms_output.put_line(b.empno);
dbms_output.put_line(b.ename);
dbms_output.put_line(b.sal);
dbms_output.put_line(b.hiredate);
else
exit;
end if;
end loop;
close c1;
end;

Reference Cursor:
============
Using reference cursor we can fetch more than one table data, it means that we can assign more than one select statement to one cursor.

Example :
======

SQL>declare
type emp_dept_cur is refcursor;
c emp_dept_cur;
a emp%rowtype;
b dept%rowtype;
begin
open c for select * from emp where deptno=&deptno;
loop
fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
exit;
end if;
end loop;
close c;
open c for select * from dept;
loop
fetch c into b;
if c%found then
dbms_output.put_line(b.dname);
dbms_output.put_line(b.loc);
dbms_output.put_line(b.dno);
else
exit;
end if;
end loop;
close c;
end;

---