PL/SQL - Cursors

Cursors:

  • To process a SQL statement, PL/SQL opens a work area called a context area.
  • PL/SQL uses this area to execute SQL statements and store processing information
  • A PL/SQL construct called ‘Cursor’ allows you to name a context area, access its information and in some cases, control its processing

Explicit Cursors
Defined by the user to keep track of which row is being processed, when a query returns multiple rows

Defining a Cursor
u A cursor is defined in the declarative part of the PL/SQL block by naming it and associating it with a query
CURSOR <cursorname> IS
<SELECT statement>;
u Example
CURSOR emp_cur IS
SELECT empno, ename, job, sal
FROM emp;

u A Cursor can be manipulated using
      OPEN
      FETCH
      CLOSE
u Cursor must be declared before it can be referenced using the OPEN, CLOSE or FETCH statements

The OPEN Statement
u Initializes or opens a cursor
u Cursor must be opened before any rows are returned by the query
OPEN <cursorname>
Example --
           OPEN emp_cur;

The FETCH Statement
u Can be executed repeatedly until all rows have been retrieved
FETCH <cursorname> INTO var1, …, varn;
OR
FETCH <cursorname> INTO record_variable;
u Example
FETCH emp_cur INTO mrec;

The CLOSE Statement
u Closes the cursor and makes the active set undefined
CLOSE <cursorname>;
u Example
CLOSE emp_cur;

u Once a cursor is closed, it can be reopened by using the OPEN statement

Attributes of Explicit Cursors

u Every cursor has four attributes that can be used to access the cursor’s context area
      %NOTFOUND
      %FOUND
      %ROWCOUNT
      %ISOPEN
u To use these attributes, simple append them to the name of the cursor
u %NOTFOUND
      evaluates to TRUE if last FETCH failed because no more rows were available
      evaluates to FALSE if last FETCH returned a row
u %FOUND
      evaluates to TRUE if last FETCH returned a row
      evaluates to FALSE if last FETCH failed because no more rows were available
u %ROWCOUNT
      returns the number of rows FETCHed from the active set so far
u %ISOPEN
      evaluates to TRUE if an explicit cursor is open
      evaluates to FALSE if an explicit cursor is closed
Examples of Cursor –
1)
To transfer names and sal of employees from emp table where sal >= 2500 in table try1
create table try1
(no number,
ename varchar2(50),
sal number);

Declare
      Cursor cf is
        select ename,sal
        from emp
        where sal >= 2500;
       M cf%rowtype;
       N number;
Begin
         Open cf;
          N := 0;
          Loop
                    Fetch cf into M;
                     Exit when cf%notfound;
                     N := cf%rowcount;
                       Insert into try1 values(N,M.ename,M.sal);
         End Loop;
           Close cf;
End;


       2) Use of %FOUND attribute  

Accepting the job from user and finally showing how many such jobs are there.

Declare
       Cursor cf is select * from emp where job ='&J';
        M cf%rowtype;
        N number;
        J Emp.Job%type;
 Begin
        Open cf;
           N := 0;
            Loop
                    Fetch cf into M;
                     Exit when cf%notfound;
                              If cf%found then
                                    N := N + 1;
                              End if;
           End Loop;
       Close cf;
                         If N > 0 then
                        dbms_output.put_line('Total number of job ' || J || '  is '|| N);
                         else
                           dbms_output.put_line('No such job');
                          End If;
End;
         ----------------------------------------------------------------------

3) Use of IsOpen attribute
Declare
   Cursor cf is
          select ename, deptno
             from emp
          where deptno = 20;
 M cf%rowtype;
/*The cursor is not opened before Loop. So using IsOpen attribute to open the
   cursor if it is not open.*/
 Begin
/*   Cursor is not opened!!! */
    Loop
                         If cf%IsOpen then
               Fetch cf into M;
             else
                Open cf;
                dbms_output.put_line('Cursor is now opened');
              End if;
              exit when cf%notfound;
              dbms_output.put_line(M.ename ||'--' || M.deptno);
                    End Loop;
End;
       --------------------------------------------------------------------------------------
4) Transferring the first five records from emp table into another table FirstFive

create table firstfive
as
  select empno,ename,sal,deptno
   from emp
   where 1=2;

Declare
  Cursor cf is
        Select *
        from emp;
    M cf%rowtype;
    N number;
 Begin
          Open cf;
          N := 1;
                while  N <= 5
                     Loop
                                Fetch cf into M;
                                 Insert into firstfive
                                 values(M.empno,M.ename,M.sal,M.deptno);
                                 N := N  +1;
                     End Loop;
End;


5) Displaying the 3rd record entered in the table emp –

Declare
  Cursor cf is
    select *
     from emp;
    M cf%rowtype;
Begin
  Open cf;
        Loop
                 fetch cf into M;
                    if cf%rowcount =  3 then
                       dbms_output.put_line(M.empno||'-'||M.ename||'-'||M.sal||'-'||M.deptno);
                   end if;
                  exit when cf%notfound;
        End Loop;
End;


6) To see the first person( or first record entered) who has got salary  > 2800
Declare
  Cursor cf is select * from emp where sal > 2800;
    M cf%rowtype;
Begin
  Open cf;
        Loop
                 fetch cf into M;
                    if cf%rowcount =  1 then
                       dbms_output.put_line(M.empno||'-'||M.ename||'-'||M.sal||'-'||M.deptno);
                           exit;
                   end if;

        End Loop;
End;

Cursor FOR Loop
  • Implicitly declares its loop index as a record of %ROWTYPE,
  • Implicitly opens the cursor
  • Repeatedly fetches rows of values from the active set into fields in the record
  • Implicitly closes the cursor when all rows have been processed or the loop is exited
  • The statements in the loop construct are executed once for each row that satisfies the query associated with the cursor name
  • Cursor FOR loop is used to simplify coding
  • No need of --
1)Open cursor
2)Fetch
3)Exit
4)Close cursor

7) To show records where salary is > 3000
Declare
  Cursor cf is select * from emp where sal >= 3000;
Begin
   For mrec in cf
        Loop
                  dbms_output.put_line(mrec.ename||'  '||mrec.sal||'  '||mrec.deptno);
         End Loop;
End;


For Loops using sub queries

       No need of declaring cursor.

A private cursor within an anonymous block can be created.

       To show names of employees who have job MANAGER.


begin
  for MREC in (select * from emp)
    Loop
          if MREC.job = 'MANAGER' then
           dbms_output.put_line('Name is ' ||MREC.ename);
           end if;
    END LOOP;
 end;
Parameterized Cursor
The same cursor can be reopened and closed with different active sets.

declare
cursor cf(pjob emp.job%type) is select empno,ename,job,sal from emp where job = pjob;
M cf%rowtype;
begin
 open cf('ANALYST');
  LOOP
     FETCH CF INTO M;
     EXIT WHEN CF%NOTFOUND;
     dbms_output.put_line(M.ename);
 end loop;
 close cf;
open cf('CLERK');
  LOOP
     FETCH CF INTO M;
     EXIT WHEN CF%NOTFOUND;
     dbms_output.put_line(M.ename);
 end loop;
 close cf;
open cf(‘MANAGER’);
LOOP
     FETCH CF INTO M;
     EXIT WHEN CF%NOTFOUND;
     dbms_output.put_line(M.ename);
 end loop;
 close cf;
 END;


Cursor FOR UPDATE OF and CURRENT OF
u CURRENT OF clause is used in an UPDATE or DELETE statement to refer to the current row of the cursor
u The cursor must be declared with the FOR UPDATE OF clause and must be open and positioned on a row
u If the cursor is not open, CURRENT OF clause results in an error
8) Example of Cursor FOR UPDATE OF and CURRENT OF
  
create table esal (empno number, sal number);

insert into esal values(1,16000);
insert into esal values(2,14000);
insert into esal values(3,8000);
insert into esal values(4,6500);
insert into esal values(5,9000);
insert into esal values(6,11000);
insert into esal values(7,5500);
insert into esal values(8,3500);
insert into esal values(9,2200);
insert into esal values(10,7000);

Multiple updations depending on the salary clause in one pl/sql block –

Declare
               Cursor cf is select * from esal For Update of sal;
                   M   cf%rowtype;
 Begin
            Open cf;
                     Loop
                              Fetch cf into M;
                               exit when cf%notfound;
                                If M.Sal >= 16000 Then
                                   M.Sal := 20000;
                                  ElsIf M.Sal >= 14000 Then
                                   M.Sal := 18200;
                                  ElsIf M.Sal >= 12000 Then
                                    M.Sal := 16700;
                                   ElsIf M.Sal  >= 10000 Then
                                    M.Sal := 13500;
                                   ElsIf M.Sal >= 8000 Then
                                    M.Sal := 11000;
                                         ElsIf M.Sal >= 6000 Then
                                    M.Sal := 9500;
                                    ElsIf M.Sal >= 4000 Then
                                     M.Sal := 7500;
                                    Else
                                      M.Sal := 5000;
                              End If;
                                              Update esal
                                              set sal = M.Sal
                  Where Current Of cf;
                    End Loop;
End;


Implicit Cursors
      Automatically defined and opened, by Oracle,  to process each SQL statement
      most recently opened context area is referred to as a ‘SQL%’ cursor




Attributes of Implicit Cursors

u Although OPEN, CLOSE and FETCH statements cannot be used to manipulate the SQL% cursor, the attributes can be used to access its context area
u Attributes evaluate to NULL, before the cursor is opened automatically
u The following four cursor attributes can be used to access the SQL% cursor’s context area
u SQL%NOTFOUND
u SQL%FOUND
u SQL%ROWCOUNT
u SQL%ISOPEN
u SQL%NOTFOUND
u evaluates to TRUE if an INSERT, UPDATE or DELETE statement affected no rows, else it evaluates to FALSE
u SQL%FOUND
u logical opposite of SQL%NOTFOUND
u evaluates to TRUE if an INSERT, UPDATE or DELETE affected one or more rows, else it evaluates to FALSE

u SQL%ROWCOUNT
      returns the number of rows affected by an INSERT, UPDATE or DELETE statement
u SQL%ISOPEN
      Oracle automatically closes an implicit cursor after executing its associated SQL statement
      For an implicit cursor SQL%ISOPEN always evaluates to FALSE

9) Example of Implicit Cursors

Begin
    Delete from emp where ename = '&name';
    If SQL%Found Then
        dbms_output.put_line('Record found and it is deleted');
   End If;
   If SQL%NotFound Then
           dbms_output.put_line('No record is present of the given name.');
  End If;
 End;





10) Implicit Cursor for rowcount

Declare
C number; := 0;
Begin
   Update Emp
     set sal =  sal +  500
     where deptno = &deptno;

/*If no record is updated since the deptno supplied is wrong then giving
     the customised error message.*/
      If SQL%Rowcount  = 0 then
         dbms_output.put_line('No records are updated since the department number entered is not in the table.');
     End if;

/*To prevent sal to be updated where deptno is > 3 */
       If SQL%RowCount > 3 then
         Rollback;
             dbms_output.put_line('Cannot update since there are more than 3 deptnos');
        End If;

        If SQL%RowCount Between  1 and 3 then
              c := SQL%RowCount;
              dbms_output.put_line(c || '  records updated.');
        End If;
End;



    REF CURSORS
Limitations of a normal cursors are --
1) A PL/SQL program cannot pass a cursor as a parameter to another program.
2)  A PL/SQL program can only open the cursor and process the information within the program itself.

To overcome these limitations there is a concept of REF CURSOR.

  Features of REF CURSOR --
1) There can be a TYPE of ref cursor. The variable of this TYPE can be used to
pass the parameters to a program and return value from the cursor.
2) The variable of REF CURSOR type returns the same data type as the cursor variable.
3) The cursor variable is passed as a parameter to a procedure.
4) The cursor variable takes all the rows from the specified table.
5) These rows are given to the bind variable.
   So the parameter passed should satisfy two conditions --
    a) The parameter should be taken from the TYPE of Ref Cursor.
    b) It should be of IN OUT mode.
6) Finally the data retrieved by the cursor variable can be seen through the bind variable. For this the data type of the bind variable should be REFCURSOR.
7) While executing the procedure bind variable should be directly given. And then by print statement the data is displayed.
8) The cursor variable's data structure and the procedure block's data structure should be same.

                 
Advantage of REF CURSOR--

   Actually we can get the view of the entire data of the table with simplicity using REF CURSOR.
  Without using ref cursor if we have to achieve this then, the parameter passed will be of variable type
and then the user has to manual loop using cursor to fetch all the records. Here in REF CURSOR there is no need of looping.
Example of REF CURSOR

Package Specification

create or replace package PRC as

TYPE EmpRC IS REF CURSOR RETURN emp%rowtype;
TYPE DeptRC IS REF CURSOR RETURN dept%rowtype;

Procedure EmpDetails(ve IN OUT EmpRC);
Procedure DeptDetails(vd IN OUT DeptRC);

End PRC;
Package Body –

create or replace package Body PRC as

   Procedure EmpDetails(ve IN OUT EmpRC)
     is
     Begin
       Open ve FOR select * from emp;
    End EmpDetails;

    Procedure DeptDetails(vd IN OUT DeptRC)
     is
      Begin
        Open vd FOR select * from dept;

    End DeptDetails;

End PRC;

For executing the procdure –
1) SQL > variable   E   REFCURSOR
     SQL > variable   D  REFCURSOR

2) To  see the data from the bind variable --
         SQL > Set AutoPrint ON

 3) SQL > Execute PRC.EmpDetails(:E);

 4) SQL > Execute PRC.DeptDetails(:D);
<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial

*/