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

题目部分

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

答案部分

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/

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

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

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

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-12-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券