Ref
cursors/Cursor variable
Ref cursor is a reference or pointer to
cursor
It is explicitly named and can be used for
multiple queries
We can open the multiple ref cursors in
the same block if one ref cursor is open no need to close before opening the
next ref cursor. I will just release the memory of the 1st ref
cursor before using 2nd ref cursor.
we need to close the ref cursor to release the memory.
Ref cursor can be used in the procedures
and functions
Ref cursors can be used as parameters.
They can be used as value and used as an
expression.
They can’t accept the parameters
Ref cursor can’t be used in the cursor for
loops
They can be strongly typed when the return
type is mentioned and weakly typed when the return type is not mentioned.
Advantage
of ref cursor is reusability and returns the different
data types. It can pass the results of one sub program to another sub program
so that it can be used for the centralized data retrieval.
Syntax
for Ref Cursor:
TYPE <Ref_cur_name> IS REF CURSOR
[RETURN return_type]
Cursor
Attributes:
-><cursor>%FOUND
-><cursor>%NOTFOUND
-><cursor>%ISOPEN
-><cursor>%ROWCOUNT
Example for Strongly typed ref cursor:
DECLARE
TYPE rc_dept is ref cursor
return department%rowtype;
Rc_dept_cur rc_dept;
L_dept_rowtype department%rowtype;
L_id department.deptno%type:=1;
L_dept_id department.deptno%type;
L_dept_name department.dname%type;
BEGIN
OPEN rc_Dept_cur for
OPEN rc_Dept_cur for
Select *from department
Where deptno=L_id;
L_id:=2;
LOOP
FETCH rc_dept_cur into L_dept_rowtype
EXIT WHEN rc_dept_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(L_dept_rowtype.deptno);
END LOOP;
OPEN rc_dept_cur FOR
SELECT * FROM Department
Where dname=’Accounting’;
LOOP
FETCH rc_dept_cur into L_dept_id,L_dept_name;
FETCH rc_dept_cur into L_dept_id,L_dept_name;
EXIT WHEN rc_dept_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(L_dept_id);
DBMS_OUTPUT.PUT_LINE(L_dept_name);
END LOOP;
CLOSE rc_dept_cur;
END;
USING RECORD TYPE:
DECLARE
TYPE dept_rec IS RECORD(deptno department.deptno%TYPE,dname
department.dname%TYPE);
TYPE rc_Dept is REF CURSOR RETURN dept_rec;
rc_dept_cur rc_Dept;
l_dept_rec dept_rec;
l_choice number:=1;
BEGIN
IF l_choice=1 then
OPEN rc_Dept_cur for
Select deptno,dname from
department
Where deptno=20;
Else
Open rc_dept_cur for
Select deptno,dname from department
Where dname='ACCOUNTING';
End if;
Loop
Fetch rc_dept_cur into l_dept_rec;
Exit when rc_dept_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(L_dept_rec.deptno);
DBMS_OUTPUT.PUT_LINE(L_dept_rec.dname);
End loop;
Close rc_dept_cur;
End;
/
Cursor Variables can be
assigned and can be used
DECLARE
TYPE rc_dept is REF CURSOR
return department%rowtype;
rc_dept_cur1 rc_dept;
rc_dept_cur2 rc_dept;
l_dept_rowtype department%rowtype;
BEGIN
OPEN rc_dept_cur1 for
Select * from department
Where deptno=10;
rc_dept_cur2:= rc_dept_cur1;
LOOP
FETCH rc_dept_cur2 INTO l_dept_rowtype;
EXIT WHEN rc_dept_cur2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_dept_rowtype.dname);
END LOOP;
CLOSE rc_dept_cur2;
End;
/
WEAK REF CURSOR:
->For weak type ref cursor no need to specify the return type
->They are flexible same cursor variable can be used against the
different tables like(dept table or emp table).
->But the trade off is there is no compile time check. there is a
possibility of run time error.
->SYS_REFCURSOR predefined week ref cursor.
->weak ref cursor can be assigned to week or strong ref cursor.
Example for weak ref cursor
DECLARE
TYPE rc_weak IS REF CURSOR
TYPE rc_weak IS REF CURSOR
Rc_weak_cur rc_week
L_dept_rowtype department%rowtype;
L_emp_rowtype employee%rowtype;
BEGIN
OPEN Rc_weak_cur for
Select * from department
Where deptno=10
Loop
Fetch Rc_weak_cur into L_dept_rowtype
Exit when Rc_weak_cur%NOTFOUND;
DEMS_OUTPUT.PUT_LINE(L_dept_rowtype.deptno);
End loop;
OPEN Rc_weak_cur for
Select * from employee
Where deptno=20
Loop
Fetch Rc_weak_cur into L_emp_rowtype
Exit when Rc_weak_cur%NOTFOUND;
DEMS_OUTPUT.PUT_LINE(L_dept_rowtype.deptno);
End loop;
CLOSE Rc_weak_cur;
END;
Example for weekly typed ref
cursor using the SYS_REFCURSOR
DECLARE
TYPE rc_weak IS REF CURSOR;
Rc_weak_cur rc_weak;
Rc_sys_cur SYS_REFCURSOR;
L_dept_rowtype department%rowtype;
L_lower number:=10;
L_upper number:=30;
BEGIN
OPEN Rc_sys_cur for
'Select * from department
Where deptno BETWEEN :1 and :2' USING L_lower, L_upper;
Rc_weak_cur :=Rc_sys_cur;
Loop
FETCH Rc_weak_cur INTO L_dept_rowtype;
EXIT WHEN Rc_weak_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(L_dept_rowtype.dname);
END LOOP;
CLOSE Rc_weak_cur;
END;
/
No comments:
Post a Comment