Cursors

Cursors
  • Cursor is a temporary SQL work area which is used to fetch more than one record from existing table.
  • Cursors are classified into two types those are
1. Implicit Cursors

The cursor mechanism which is performed by the system internally those cursors can be called as implicit cursors.

2. Explicit Cursors

The Cursor mechanism which is performed by the user manually those cursors can be called as explicit cursors.

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

  1. Declare Cursor
  2. Open the cursor
  3. Close the cursor
  4. Fetch the records from the cursor.

Syntax to Create a Cursor

CURSOR<Cursor Name> IS SELECT * FROM <Table Name> WHERE <Condition>

E.g

cursor C is select * from emp where deptno=20;

Open the cursor

Syntax

Open <Cursor name>

E.g

Open c;

Fetch the records from the Cursor Syntax

Syntax

FETCH <Cursor Name> INTO <List of variables>

E.g

Fetch C into x,y,z;

Syntax to Close the Cursor Syntax 

CLOSE <Cursor Name>

E.g

Close C;

A program which have no cursor

declare
   a emp%rowtype;
begin
   select * into a from emp where deptno=&deptno;
   dbms_output.put_line(a.ename||' '||a.sal||' '||a.deptno);
end;
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

To overcome this program

Write a PL/SQL Block to display employee details such as employee no, ename, salary when department no is passed as in value.

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

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

  1. %Found
  2. %Not Found
  3. %isOpen
  4. %RowCount
1. %Found
  • This attribute is used to check whether the record is found or not in the memory it always returns Boolean values that is either true or false.
  • If the record is found then it is true if the records is not found then it is false

Write a PL/SQL Block display the employee details who are working under Sales Department.

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

Note 

We can use Fetch command for empno also but it will display only one value.

2. %Isopen 

This attribute is used to check whether the cursor is opened or not in memory.

E.g

declare
   a emp%rowtype;
   cursor c1 is select * from emp;
begin
   if c1% isopen then
       dbms_output.put_line('Cursor not opened');
   else
       open c1;
       dbms_output.put_line('Cursor  Opened');
   end if;
end;
3. %Not Found

This attribute is used to check whether the record is found or not in the cursor. This attribute always say us Boolean value that is true or false if the record is found then it is false. If the record is not found then it is true.

4. %Row Count

This attribute is used to count the number of records with in the cursor.

Reference Cursor

It is special type of cursor. We can assign more than one select statement to same cursor.

declare
   type curs_bhar is ref cursor;
   a emp%rowtype;
   b dept%rowtype;
   c curs_bhar;
begin
   open c for select * from emp;
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;
   open c for select * from dept;
loop
   fetch c into b;
   if c%  found then
      dbms_output.put_line(b.dname);
   else
      exit;
   end if;
end loop;
end;
Advantages of for loops using in the Cursors
  1. No need to open the Cursor.
  2. Fetch the records automatically.
  3. It automatically check the end of the rows.
  4. It automatically close the Cursor.
  5. No need to declare the variables.
  6. Code will be decreased.
  7. Execution will be faster.
  8. It is collection of information from cursor to variable (That is i).
For loop using the Cursor

E.g

declare
   cursor c is select * from emp;
begin
   for i in c loop
      dbms_output.put_line(i.deptno||' '||i.sal||' '||i.ename);
   end loop;
end;
CURSOR_ALREADY_OPEN

This exception will be raised if the user trying to open the cursor within the for loop program.

declare
   cursor c is select * from emp where deptno =(select deptno from dept where loc ='NEW YORK') and ename like '%A%';
begin
   for i in c loop
      open c;
      dbms_output.put_line(i.empno||' '||i.ename||' '||i.sal);
   end loop;
exception
   when cursor_already_open then
      dbms_output.put_line('It is not necessary to open cursor in for loop');
end;
Cursors using joins 
declare
   cursor c_emp is
      select ename,sal*12 ANNSAL,dname from emp,dept where emp.deptno = dept.deptno;
begin
   for i in c_emp
   loop
      dbms_output.put_line(i.ename || ' - ' || i.annsal || ' - ' || i.dname);
   end loop;
end;
Parameterized Cursors 
declare
   cursor c(dno number) is select * from dept where deptno=dno;
begin
   for i in c(10) loop
      dbms_output.put_line(i.deptno||' '||i.dname);
   end loop;
end;
Cursors
Scroll to top