【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/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
