【DB笔试面试466】存储过程或函数如何返回集合类型？

CREATE TABLE TEST (ID VARCHAR2(20));
INSERT INTO TEST VALUES('1');
COMMIT;
EXPLAIN PLAN FOR SELECT * FROM TEST;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    12 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

CREATE OR REPLACE TYPE T_TEST AS OBJECT
(
  ID      INTEGER,
  IN_DATE DATE,
  NAME    VARCHAR2(60)
);
--创建TYPE
CREATE OR REPLACE TYPE T_TEST_TABLE AS TABLE OF T_TEST;
--创建函数
CREATE OR REPLACE FUNCTION F_TEST_PIPE(N IN NUMBER DEFAULT NULL)
  RETURN T_TEST_TABLE
  PIPELINED AS
  V_TEST T_TEST_TABLE := T_TEST_TABLE();
BEGIN
  FOR I IN 1 .. NVL(N, 100) LOOP
    PIPE ROW(T_TEST(I, SYSDATE, 'test' || I));
  END LOOP;
  RETURN;
END F_TEST_PIPE;
/
--查询
SELECT * FROM TABLE(F_TEST_PIPE(5)); 
SELECT * FROM THE(SELECT F_TEST_PIPE(5) FROM DUAL);
SYS@lhrdb> SELECT * FROM TABLE(F_TEST_PIPE(5)); 
        ID IN_DATE             NAME
---------- ------------------- --------
         1 2017-01-05 11:42:50 test1
         2 2017-01-05 11:42:50 test2
         3 2017-01-05 11:42:50 test3
         4 2017-01-05 11:42:50 test4
         5 2017-01-05 11:42:50 test5
下面给出TABLE()结合数组的例子：
CREATE OR REPLACE TYPE T_TEST AS OBJECT
(
  ID      INTEGER,
  IN_DATE DATE,
  NAME    VARCHAR2(60)
)
;
CREATE OR REPLACE TYPE T_TEST_TABLE AS TABLE OF T_TEST;
CREATE OR REPLACE FUNCTION F_TEST_ARRAY(N IN NUMBER DEFAULT NULL)
  RETURN T_TEST_TABLE AS
  V_TEST T_TEST_TABLE := T_TEST_TABLE();
BEGIN
  FOR I IN 1 .. NVL(N, 100) LOOP
    V_TEST.EXTEND();
    V_TEST(V_TEST.COUNT) := T_TEST(I, SYSDATE, 'TEST' || I);
  END LOOP;
  RETURN V_TEST;
END F_TEST_ARRAY;
/
SELECT * FROM TABLE(F_TEST_ARRAY(5)); 
SELECT * FROM THE(SELECT F_TEST_ARRAY(5) FROM DUAL);
SYS@lhrdb> SELECT * FROM TABLE(F_TEST_ARRAY(5)); 
        ID IN_DATE             NAME
---------- ------------------- ----------------------
         1 2017-01-05 11:48:50 TEST1
         2 2017-01-05 11:48:50 TEST2
         3 2017-01-05 11:48:50 TEST3
         4 2017-01-05 11:48:50 TEST4
         5 2017-01-05 11:48:50 TEST5

CREATE OR REPLACE PROCEDURE P_CURSOR_TLHR(CUR_ARG OUT SYS_REFCURSOR) AS
BEGIN
  OPEN CUR_ARG FOR
    SELECT * FROM USER_TABLES WHERE ROWNUM < 5;
END;
--调用测试
DECLARE
    VARCURSOR SYS_REFCURSOR;
    R         USER_TABLES%ROWTYPE;
BEGIN
    P_CURSOR_TLHR(VARCURSOR); --这样这个游标就有值了
    LOOP
        FETCH VARCURSOR
            INTO R;
        EXIT WHEN VARCURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(R.TABLE_NAME);
    END LOOP;
END;

--定义全局变量
CREATE OR REPLACE PACKAGE pkg_package AS
    TYPE type_cursor IS REF CURSOR;
    TYPE type_record IS RECORD(
        table_name VARCHAR2(32),
        TABLESPACE_NAME VARCHAR2(32) 
 );
END;
--创建返回游标的存储过程
CREATE OR REPLACE PROCEDURE P_TEMP_PROCEDURE(CUR_OUT_ARG OUT PKG_PACKAGE.TYPE_CURSOR) IS
BEGIN
  OPEN CUR_OUT_ARG FOR
    SELECT D.TABLE_NAME, D.TABLESPACE_NAME
      FROM USER_TABLES D
     WHERE ROWNUM <= 5;
END;
--调用
DECLARE
    cur_out_arg pkg_package.type_cursor;
    rec_arg     pkg_package.type_record;
BEGIN
    p_temp_procedure(cur_out_arg);
    FETCH cur_out_arg
        INTO rec_arg;
    dbms_output.put_line(rec_arg.table_name);
    dbms_output.put_line(rec_arg.TABLESPACE_NAME); 
END;

CREATE OR REPLACE FUNCTION F_GET_SYS_REFCURSOR_LHR(P_EMPNO NUMBER) RETURN SYS_REFCURSOR IS
    CUR_SYS SYS_REFCURSOR;
  BEGIN
    OPEN CUR_SYS FOR
      SELECT LEVEL P_LEVEL,
             T.EMPNO,
             T.ENAME,
             T.MGR,
             (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || T.ENAME || '(' ||
             T.EMPNO || ')') NAME_ALL,
             SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
             CONNECT_BY_ROOT(T.ENAME) ROOT,
             DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
        FROM SCOTT.EMP T
       START WITH T.EMPNO=P_EMPNO
      CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
    RETURN CUR_SYS;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

SELECT F_GET_SYS_REFCURSOR_LHR(7566)  FROM   DUAL;