【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