PL/SQL - 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 typeand 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)
is
Begin
Open ve FOR select * from emp;
End EmpDetails;
Procedure DeptDetails(vd IN OUT DeptRC)
is
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);
Discussion about this article
| Author | Body |
Rahul
8/29/2009 8:00 PM
|
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
|