The first case is that the returned cursor is the data of a specific table or view, for example:
SQL-code:
CopyCode The Code is as follows: Create or replace procedure p_testa (
Presult out sys_refcursor
)
As
Begin
Open presult for select * from users;
End p_testa;
Users is a table in the database. You only need to declare the rowtype of the table during the call:
SQL-code:Copy codeThe Code is 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 do not return all columns of the table, but perhaps only one or two of them, for example:
SQL-code:Copy codeThe Code is 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 and name columns, so you must modify the values when calling them:
SQL-code:Copy codeThe Code is 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;
The difference is that we have declared a cursor type variable tmpcursor. Note that the structure of tmpcursor must be consistent with the cursor structure returned by the Stored Procedure p_testa; otherwise, an error will occur. Similarly, as long as the two cursor types have the same structure, they can be freely called.