The first case is that the returned cursor is the data of a specific table or view, 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 * 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.