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
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;
Example
CURSOR emp_cur IS
SELECT empno, ename, job, sal
FROM emp;
A Cursor can be manipulated using
– OPEN
– FETCH
– CLOSE
Cursor must be declared before it can be referenced using the OPEN, CLOSE or FETCH statements
The OPEN Statement
Initializes or opens a cursor
Cursor must be opened before any rows are returned by the query
OPEN <cursorname>
Example --
OPEN emp_cur;
The FETCH Statement
Can be executed repeatedly until all rows have been retrieved
FETCH <cursorname> INTO var1, …, varN;
OR
FETCH <cursorname> INTO record_variable;
Example
FETCH emp_cur INTO mrec;
The CLOSE Statement
Closes the cursor and makes the active set undefined
CLOSE <cursorname>;
Example
CLOSE emp_cur;
Once a cursor is closed, it can be reopened by using the OPEN statement
Attributes of Explicit Cursors
Every cursor has four attributes that can be used to access the cursor’s context area
%NOTFOUND
%FOUND
%ROWCOUNT
%ISOPEN
To use these attributes, simple append them to the name of the cursor
%NOTFOUND
evaluates to TRUE if last FETCH failed because no more rows were available
evaluates to FALSE if last FETCH returned a row
%FOUND
evaluates to TRUE if last FETCH returned a row
evaluates to FALSE if last FETCH failed because no more rows were available
%ROWCOUNT
returns the number of rows FETCHed from the active set so far
%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
(SERIAL_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 Loo;
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);
--EXIT;
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;
for MREC in (select * from DEPT)
Loop
-- if MREC. = 'MANAGER' then
dbms_output.put_line('Name is ' ||MREC.DNAME);
-- 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(‘ANALYST à ‘|| M.ename);
end loop;
close cf;
open cf('CLERK');
LOOP
FETCH CF INTO M;
EXIT WHEN CF%NOTFOUND;
dbms_output.put_line(‘CLERK à ‘ || M.ename);
end loop;
close cf;
open cf(‘MANAGER’);
LOOP
FETCH CF INTO M;
EXIT WHEN CF%NOTFOUND;
dbms_output.put_line(‘MANAGER à ‘||M.ename);
end loop;
close cf;
END;
Cursor FOR UPDATE OF and CURRENT OF
CURRENT OF clause is used in an UPDATE or DELETE statement to refer to the current row of the cursor
The cursor must be declared with the FOR UPDATE OF clause and must be open and positioned on a row
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
Although OPEN, CLOSE and FETCH statements cannot be used to manipulate the SQL% cursor, the attributes can be used to access its context area
Attributes evaluate to NULL, before the cursor is opened automatically
The following four cursor attributes can be used to access the SQL% cursor’s context area
SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT
SQL%ISOPEN
SQL%NOTFOUND
evaluates to TRUE if an INSERT, UPDATE or DELETE statement affected no rows, else it evaluates to FALSE
SQL%FOUND
logical opposite of SQL%NOTFOUND
evaluates to TRUE if an INSERT, UPDATE or DELETE affected one or more rows, else it evaluates to FALSE
SQL%ROWCOUNT
returns the number of rows affected by an INSERT, UPDATE or DELETE statement
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;
Discuss about Cursors
| Author | Body |
Rahul
7/12/2009 6:56 AM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you.
|