Oracle 如何在过程中使用匿名打印ref游标和嵌套表作为out参数

CREATE TABLE EMP
(
empno number(4,0) not null,
ename varchar2(10 byte),
job varchar2(9 byte),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0)
);
/


SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12-80','DD-MM-RR'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-02-81','DD-MM-RR'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-02-81','DD-MM-RR'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-04-81','DD-MM-RR'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-09-81','DD-MM-RR'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-05-81','DD-MM-RR'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-06-81','DD-MM-RR'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('09-12-82','DD-MM-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-11-81','DD-MM-RR'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-09-81','DD-MM-RR'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('12-01-83','DD-MM-RR'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-12-81','DD-MM-RR'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-12-81','DD-MM-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-01-82','DD-MM-RR'),1300,null,10);

-------------------------------------------------------------------------------------------------------------
create or replace type varchar_table is table of varchar2(10 byte);

--------------------------------------------------------------------------
create or replace package pk_test
as
procedure p1_test(i_emp_dptid in number,
o_cur_emp_dtls out sys_refcursor,
o_emp_f_name out varchar_table,
o_emp_l_job out varchar_table
);

end pk_test;
/

create or replace PACKAGE BODY PK_TEST AS

procedure p1_test
(
  i_emp_dptid in number,
  o_cur_emp_dtls out sys_refcursor,
  o_emp_f_name out varchar_table,
  o_emp_l_job out varchar_table
)

AS
  BEGIN
   open o_cur_emp_dtls for
   select *
   from emp where deptno = i_emp_dptid;
   
  SELECT
    ( SELECT (CAST(collect(ename) AS varchar_table)) FROM emp WHERE deptno = 20 ) AS emp_name,
    ( SELECT (CAST(collect(job) AS varchar_table)) FROM emp WHERE deptno = 20 ) AS job_name
    INTO
    o_emp_f_name,
    o_emp_l_job  
  FROM dual;
  END p1_test;

END PK_TEST;
/
复制

var cur refcursor;

declare
  cur sys_refcursor;
  name varchar_table;
  job varchar_table;
begin

  pk_test.p1_test ( 
    10,
    :cur,
    name, job
  );

end;
/

print :cur

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450              10
      7839 KING       PRESIDENT      17-NOV-1981 00:00:00       5000              10
      7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300              10
复制