Oracle invokes another stored procedure that returns a cursor in one stored procedure

http://www.itpub.net/forum.php?mod=viewthread&tid=1776483&pid=21223451&page=1&extra=# pid21223451

In the actual project, it is often necessary to invoke the cursor returned by another stored procedure in one stored procedure, and this article enumerates two situations to describe the specific operation method.

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.

DECLARE
  out_cur sys_refcursor;
  CURSOR Tmpcursor is
    SELECT Administrativeid,
           NAME as Administrativename,
           administrative_class_id,
           IsLeaf from
      v_administrative
     WHERE rownum = 1;

  VAR Tmpcursor%rowtype;
BEGIN
  Out_cur: = Fn_get_administrative_leaf (11903000);
  LOOP
    FETCH out_cur into
      VAR;
    EXIT when Out_cur%notfound;
    Dbms_output. Put_Line (VAR. Administrativeid);
  End LOOP;
  Close out_cur;
End;