The first scenario is that the returned cursor is the data for a specific table or view, such as:
Sql-code:
Copy Code code as follows:
CREATE OR REPLACE PROCEDURE P_testa (
PRESULT out Sys_refcursor
)
As
BEGIN
OPEN PRESULT for SELECT * from USERS;
End P_testa;
Where users are a table in the database. It is only possible to declare a rowtype type of the table at the time of the call:
Sql-code:
Copy Code code as follows:
CREATE OR REPLACE PROCEDURE p_testb
As
Varcursor Sys_refcursor;
R Users%rowtype;
BEGIN
P_testa (Varcursor);
LOOP
FETCH varcursor into R;
EXIT when Varcursor%notfound;
Dbms_output. Put_Line (R.name);
End LOOP;
End P_testb;
In the second case, we're not returning all the columns of the table, maybe just one or two columns, like:
Sql-code:
Copy Code code as follows:
CREATE OR REPLACE PROCEDURE P_testa (
PRESULT out Sys_refcursor
)
As
BEGIN
OPEN PRESULT for SELECT id,name from USERS;
End P_testa;
Here we only return the users table id,name these two columns, then the call must also make the appropriate changes:
Sql-code:
Copy Code code as follows:
CREATE OR REPLACE PROCEDURE p_testb
As
Varcursor Sys_refcursor;
CURSOR Tmpcursor is SELECT id,name from USERS WHERE rownum=1;
R Tmpcursor%rowtype;
BEGIN
P_testa (Varcursor);
LOOP
FETCH varcursor into R;
EXIT when Varcursor%notfound;
Dbms_output. Put_Line (r.id);
End LOOP;
End P_testb;
Unlike before, we declared a variable tmpcursor of a cursor type, noting that the structure of the tmpcursor must be consistent with the cursor structure returned by the stored procedure P_testa, or an error would occur. In the same vein, a free call can be achieved by maintaining a consistent two cursor type structure.