PL/SQL Cursors

Cursors in PL/SQL

A Cursors is a temporary work area created in the system memory when a SQL statement is executed. It is a temporary memory which is used to fetch more than one record at a time from existing table.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Why use Cursor ?

It is a temporary memory which is used to fetch more than one record at a time from existing table.

Type of cursor

Cursor are broadly divided into two types;

• Implicit cursor
• Explicit cursor

Implicit cursor

This type of cursor is perform by the system internally those cursor can be called as implicit cursor.

Explicit cursor

This type of cursor is performed by the user manually or programatically those cursor can be called as explicit cursor.

Steps to perform cursor

Declare the cursor

declare
a emp %rowtype;
cursor c is select * from emp where depno=&deptno;
begin
open c;
fetch c into a;
dbms_output.put_line(a.empno || ' ' a.ename || ' ' || a.sal);
close c;
end;

Note: In above exaple ruturn only one record because the statements are lies within the scopes.

% found

This attribute is used for check whether the record is found or not in the cursor. It return boolean value either true or false. If the record found then it return ture otherwise false.

declare
a emp %rowtype;
cursor c is select * from emp where depno=&deptno;
begin
open c;
loop fetch c into a;
if c % found then
dbms_output.put_line(a.empno || ' ' a.ename || ' ' || a.sal);
else
exit;
end if;
end loop;
close c;
end;