Here is some example of re cursor
Introduction to REF CURSOR
A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
en emp.ename%type;
begin
open c_emp for select ename from emp;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;
Let me explain step by step. The following is the first statement you need to understand:
type r_cursor is REF CURSOR;
The above statement simply defines a new data type called "r_cursor," which is of the type REF CURSOR. We declare a cursor variable named "c_emp" based on the type "r_cursor" as follows:
c_emp r_cursor;
Every cursor variable must be opened with an associated SELECT statement as follows:
open c_emp for select ename from emp;
To retrieve each row of information from the cursor, I used a loop together with a FETCH statement as follows:
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
I finally closed the cursor using the following statement:
close c_emp;
%ROWTYPE with REF CURSOR
In the previous section, I retrieved only one column (ename) of information using REF CURSOR. Now I would like to retrieve more than one column (or entire row) of information using the same. Let us consider the following example:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
end;
In the above example, the only crucial declaration is the following:
[code]
er emp%rowtype;
[/CODE
The above declares a variable named "er," which can hold an entire row from the "emp" table. To retrieve the values (of each column) from that variable, we use the dot notation as follows:
dbms_output.put_line(er.ename || ' - ' || er.sal);
Now let me tell you some more examples.
CREATE OR REPLACE PACKAGE PKG_TEST
IS
-- Record de type EMP --
TYPE emp_rec IS RECORD(
empno emp.empno%TYPE,
ename emp.ename%TYPE,
job emp.job%TYPE,
sal emp.sal%TYPE,
comm emp.comm%TYPE);
-- REF CURSOR declaration --
TYPE emp_cursor IS REF CURSOR RETURN emp_rec;
PROCEDURE Populate ( emp_data IN OUT emp_cursor ) ;
END PKG_TEST ;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS
PROCEDURE Populate ( emp_data IN OUT emp_cursor )
Is
Begin
OPEN emp_data FOR SELECT empno, ename, job, sal, comm
FROM emp
ORDER BY ENAME ;
End Populate ;
END PKG_TEST ;
/
And in Forms :
Declare
cur PKG_TEST.emp_cursor ;
rec PKG_TEST.emp_rec ;
Begin
PKG_TEST.Populate( cur ) ;
Loop
fetch cur into rec ;
exit when cur%notfound ;
Message(rec.ename) ;
End loop ;
End ;
Another one:
-- in the package specification
FUNCTION populate2 Return sys_refcursor ;
-- in the package specification
FUNCTION populate2 Return sys_refcursor
Is
cur sys_refcursor ;
Begin
Open cur For 'Select empno, ename, job, sal, comm From EMP' ;
Return cur ;
End Populate2 ;
In the form:
declare
cur sys_refcursor ;
rec pkg_test.emp_rec ;
Begin
cur := pkg_test.populate2 ;
loop
fetch cur Into rec ;
exit when cur%notfound ;
Message( rec.ename ) ;
End loop ;
close cur ;
End ;
Hope everything is clear in your mind.
BABA
[Updated on: Wed, 14 March 2012 07:19]
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
