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

  • 2019 年 10 月 11 日
  • 筆記

题目部分

存储过程或函数如何返回集合类型？

答案部分

TABLE()函数可接受查询语句或游标作为输入参数，并可输出多行数据，称为表函数。所以，存储过程或函数返回集合类型主要采用的是表函数和PIPELINED函数（管道化表函数）及数组结合的方式。当然，也可以采用存储过程返回系统游标SYS_REFCURSOR或自定义游标的方式。

下面先看一个使用表函数的最简单的例子：

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)

可以看到，一个简单的表函数可以返回多行数据。下面再看一个TABLE()结合PIPELINED函数的例子：

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

下面来看使用存储过程如何返回游标。下面的例子是返回一个系统游标SYS_REFCURSOR：

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;

当然，P_CURSOR_TLHR也可以返回多个系统游标，需要设置多个OUT类型的参数。

下面的例子返回自定义游标：

--定义全局变量  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;

结果如下所示：

有关存储过程或函数返回集合类型的写法有多种，作者把这多种方式写成了一个包，已发布到博客（地址：http://blog.itpub.net/26736162/viewspace-2131977/）和公众号上，读者可自行下载阅读。

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

VirMach 便宜 VPS

QNews

QNews