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;

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 –

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

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 3^rd 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)

Begin

Open ve FOR select * from emp;

End EmpDetails;

Procedure DeptDetails(vd IN OUT DeptRC)

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);

PL/SQL - Cursors

Cursors:

For Loops using sub queries

Complete Tutorial

No comments:

Post a Comment