Cursors In Oracle
CURSOR : A cursors is a pointer used to fetch rows from a result set
Two types of classification s:
I.STATIC CURSOR S:
Static : Normal cursor (implicit or explicit)
Cursor attributes for implicit and explicit:
%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open
a. Implicit :
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)
Example Implicit Cursor:
select * from emp
If SQL%FOUND then
v_count:= SQL%ROWCOUNT
end if;
b. Explicit : 2 network round trips. Store data first then retrieve data.
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.
Syntax:
open c1; — cursor c1 is select <stmt>
fetch <>
exit when c1%NOTFOUND
Example Explicit cursor:
Without Using Loop s
Declare
Cursor cur1 is
select ename,empno,sal from emp
where sal<50000 and deptno=50
begin
open cur1;
fetch cur1 into v_ename,v_empno,v_sal;
exit when cur1%notfound;
—<do processing>
close cur1;
end;
Using Loops:
Declare
Cursor cur1 is
select ename,empno,sal from emp
where sal<50000 and deptno=50
begin
For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;
end;
Using Loops with Cursor Parameters:
Declare
Cursor cur1( cp_deptNo Number)
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo
l_deptNo Number :=50;
begin
For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;
end;
II. DYNAMIC CURSOR s :
Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures.
a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.
Example For the Ref Cursor :
–SPECK PACKAGE
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
AS
TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
END REFCURSOR_PKG;
The pl/sql procedure that returns a ref-cursor looks like this:
–BODY PACKAGE
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor:
PROCEDURE
WEAK_REF_CUR_PRC( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
—- From 9i (p_cursor OUT SYS_REFCURSOR )—-
)
IS
BEGIN
OPEN p_cursor FOR
SELECT * FROM emp
WHERE deptno = p_deptno;
end WEAK_REF_CUR_PRC;
— For Strong Ref Cursor:
PROCEDURE
STRONG_REF_CUR_PRC( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
)
IS
BEGIN
SELECT * FROM emp
WHERE deptno = p_deptno;
end STRONG_REF_CUR_PRC;
END REFCURSOR_PKG;
Two types of classification s:
I.STATIC CURSOR S:
Static : Normal cursor (implicit or explicit)
Cursor attributes for implicit and explicit:
%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open
a. Implicit :
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)
Example Implicit Cursor:
select * from emp
If SQL%FOUND then
v_count:= SQL%ROWCOUNT
end if;
b. Explicit : 2 network round trips. Store data first then retrieve data.
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.
Syntax:
open c1; — cursor c1 is select <stmt>
fetch <>
exit when c1%NOTFOUND
Example Explicit cursor:
Without Using Loop s
Declare
Cursor cur1 is
select ename,empno,sal from emp
where sal<50000 and deptno=50
begin
open cur1;
fetch cur1 into v_ename,v_empno,v_sal;
exit when cur1%notfound;
—<do processing>
close cur1;
end;
Using Loops:
Declare
Cursor cur1 is
select ename,empno,sal from emp
where sal<50000 and deptno=50
begin
For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;
end;
Using Loops with Cursor Parameters:
Declare
Cursor cur1( cp_deptNo Number)
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo
l_deptNo Number :=50;
begin
For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;
end;
II. DYNAMIC CURSOR s :
Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures.
a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.
Example For the Ref Cursor :
–SPECK PACKAGE
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
AS
TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
END REFCURSOR_PKG;
The pl/sql procedure that returns a ref-cursor looks like this:
–BODY PACKAGE
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor:
PROCEDURE
WEAK_REF_CUR_PRC( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
—- From 9i (p_cursor OUT SYS_REFCURSOR )—-
)
IS
BEGIN
OPEN p_cursor FOR
SELECT * FROM emp
WHERE deptno = p_deptno;
end WEAK_REF_CUR_PRC;
— For Strong Ref Cursor:
PROCEDURE
STRONG_REF_CUR_PRC( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
)
IS
BEGIN
SELECT * FROM emp
WHERE deptno = p_deptno;
end STRONG_REF_CUR_PRC;
END REFCURSOR_PKG;
Leave a Reply
Want to join the discussion?Feel free to contribute!