PL/SQL Cursors:
In this post we will be Learning Cursors in PL/SQL,
Cursor is a private SQL area provided by the oracle engine. It is mainly used
to
retrieve
the data from more than one column. There are two types of cursors they are
given
below.
- Implicit Cursors
- Explicit Cursors
Implicit
Cursors:
Implicit cursor raises implicitly when we use INSERT, UPDATE,DELETE and SELECT..INTO statements. Because the Implicit cursor is opened and closed by the PL/SQL engine.
Implicit cursor raises implicitly when we use INSERT, UPDATE,DELETE and SELECT..INTO statements. Because the Implicit cursor is opened and closed by the PL/SQL engine.
Ex:
SQL> declare
v_edata
emp%ROWTYPE;
begin
select
* into v_edata from emp where empno=7788;
if
sql%notfound then
dbms_output.put_line('Record
Not Found');
else
dbms_output.put_line('Record
Found');
end
if;
end;
Processing
Explicit Cursors: The four PL/SQL steps necessary for explicit cursor
processing
are as follows.
- Declare the cursor.
- Open the cursor for a Query.
- Fetch the results into PL/SQL variables.
- Close the Cursor.
Declaration
of Cursor: The declaration of the cursor is associated with the select
statement.
The syntax for the cursor is given below.
Syntax:
CURSOR cursor_name IS select_statement;
Ex:
-- To print the Cumulative SAL of the Employee table order by empno
SQL>declare
v_sal
number;
v_dno
number;
l_count
number;
cursor
test_cursor is select * from scott.emp order by empno;
begin
v_sal:=0;
v_dno:=0;
for
i in test_cursor
loop
if(i.deptno!=v_dno)
then
v_sal:=
0;
end
if;
v_sal:=v_sal+i.sal;
dbms_output.put_line('Ename:
' || i.ename||' Sal: '|| i.sal || '_
Cum_sal:
'|| v_sal || 'Deptno: ' || i.deptno);
l_count:=test_cursor%rowcount;
end
loop;
dbms_output.put_line(l_count||'
Row(s) Fetched by the Cursor');
Oracle
Applications 11.5.9 CS – Software Solutions
8
end
test_cursor;
Cursor
Attributes: There are four cursors available in PL/SQL that can be applied to
cursors.
Those are given below.
- %FOUND
- %NOTFOUND
- %ISOPEN
- %ROWCOUNT
%FOUND:
%FOUND is a Boolean attribute. It returns TRUE if the previous FETCH
returned
a row and FALSE if it didn’t.
%NOTFOUND:
%NOTFOUND is also a Boolean attribute. It returns FALSE if the
previous
FETCH returned a row and TRUE if it didn’t. It behaves opposite to the
%FOUND.
%ISOPEN:
%ISOPEN is a Boolean attribute. It returns TRUE if the associated cursor is
open
other wise it will return FALSE.
%ROWCOUNT:
%ROWCOUNT is a Numeric attribute. It returns number of rows
returned
by the cursor so far.
Declaring,
Opening Fetching and Closing the Cursor:
Ex:
SQL> declare
v_eno
number;
v_ename
varchar2(20);
l_count
number;
cursor
razia_cursor is select empno,ename from scott.emp;
begin
open
razia_cursor;
loop
fetch
razia_cursor into v_eno, v_ename;
exit
when razia_cursor%notfound;
l_count:=razia_cursor%rowcount;
dbms_output.put_line('Ename:
' || v_ename||' ENUM: '|| v_eno);
end
loop;
close
razia_cursor;
Cursors in PL/SQL Tutorial :
dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor');
end;
Cursors in PL/SQL Tutorial :
Ex:
SQL> -- Example to print the odd rows in the Table EMP
declare
n
number;
l_count
number;
cursor
razia_cursor is select empno,ename,rownum from scott.emp;
begin
for
i in razia_cursor
loop
n:=mod(i.rownum,2);
if
(n>0) then
dbms_output.put_line('Empno:
' || i.empno||' NAME: '|| i.ename||'
ROWNUM:
'|| i.Rownum);
end
if;
l_count:=razia_cursor%rowcount;
end
loop;
dbms_output.put_line(l_count||'
Number of Row(s) Fetched by the Cursor');
end
razia_cursor;
