PKG_COLLECTION_LHR 儲存過程或函式返回集合型別

lhrbest發表於2017-01-05
儲存過程函式型別

儲存過程或函式可以返回集合型別，方法很多，今天整理在一個包中，其它情況可照貓畫虎。

點選(此處)摺疊或開啟

  1. CREATE OR REPLACE PACKAGE PKG_COLLECTION_LHR AUTHID CURRENT_USER AS

  3.   -----------------------------------------------------------------------------------
  4.   -- Created on 2013-05-24 14:37:41 by lhr
  5.   --Changed on 2013-05-24 14:37:41 by lhr
  6.   -- function： 返回各種各樣的集合
  7.     /*
  8.     DROP TYPE obj_all_address_lhr FORCE;
  9. DROP TYPE typ_all_address_lhr FORCE;
  10.  
  11. CREATE OR REPLACE TYPE obj_all_address_lhr AS OBJECT(
  12.     P_LEVEL NUMBER(18),
  13.     EMPNO NUMBER(18),
  14.     ENAME VARCHAR2(4000),
  15.     MGR NUMBER(18),
  16.     NAME_ALL VARCHAR2(4000),
  17.     ALL_NAME_LEVEL VARCHAR2(4000),
  18.     ROOT VARCHAR2(4000),
  19.     IS_LEAF VARCHAR2(10)
  20.     );
  21. CREATE OR REPLACE TYPE typ_all_address_lhr AS TABLE OF obj_all_address_lhr;
  22. */
  23.   -----------------------------------------------------------------------------------

  25.   -----------------------------變數--------------------------------------
  26.   TYPE TYPE_CURSOR IS REF CURSOR;
  27.   TYPE TYPE_RECORD IS RECORD(
  28.     P_LEVEL NUMBER(18),
  29.     EMPNO NUMBER(18),
  30.     ENAME VARCHAR2(4000),
  31.     MGR NUMBER(18),
  32.     NAME_ALL VARCHAR2(4000),
  33.     ALL_NAME_LEVEL VARCHAR2(4000),
  34.     ROOT VARCHAR2(4000),
  35.     IS_LEAF VARCHAR2(10));
  36.   TYPE T_RECORD IS TABLE OF TYPE_RECORD;

  38.   -----------------------------存過--------------------------------------
  39.   --系統遊標 --推薦
  40.   PROCEDURE P_SYS_REFCURSOR_LHR(P_EMPNO IN NUMBER,
  41.                                 CUR_SYS OUT SYS_REFCURSOR);
  42.   -- 自定義遊標
  43.   PROCEDURE P_SYS_REFCURSOR_LHR_01(P_EMPNO IN NUMBER,
  44.                                    CUR_SYS OUT TYPE_CURSOR);

  46.   ---索引表 --包級別
  47.   PROCEDURE P_INDEX_TABLE_PKG_LHR(P_EMPNO IN NUMBER,
  48.                                   O_T_RECORD OUT T_RECORD);

  50.   ------------------------------函式-------------------------------------
  51.   --系統遊標
  52.   FUNCTION F_GET_SYS_REFCURSOR_LHR(P_EMPNO NUMBER) RETURN SYS_REFCURSOR;

  54.   --索引表 --包 級別 不能通過sql語句直接查詢
  55.   FUNCTION F_GET_INDEX_TABLE_PKG_LHR(P_EMPNO NUMBER) RETURN T_RECORD;

  57.   --索引表 --schema 級別 可以直接查詢
  58.   /*select D.* from table( f_get_all_address_lhr(306628323)) D;
  59.   select * from the(select f_get_all_address_lhr(306628323) from dual);*/
  60.   FUNCTION F_GET_INDEX_TABLE_SCHEMA_LHR(P_EMPNO NUMBER)
  61.     RETURN TYP_ALL_ADDRESS_LHR;

  63.   ---- 索引表 --schema 級別 --管道化 可以直接查詢
  64.   FUNCTION F_GET_INDEX_TABLE_PIPE_LHR(P_EMPNO NUMBER)
  65.     RETURN TYP_ALL_ADDRESS_LHR
  66.     PIPELINED;

  68. END PKG_COLLECTION_LHR;
  69. /
  70. CREATE OR REPLACE PACKAGE BODY PKG_COLLECTION_LHR AS

  72.   PROCEDURE P_SYS_REFCURSOR_LHR(P_EMPNO IN NUMBER,
  73.                                 CUR_SYS OUT SYS_REFCURSOR) IS
  74.   
  75.     /*DECLARE
  76.   CUR_A SYS_REFCURSOR;
  77.   R_TYPE_RECORD PKG_COLLECTION_LHR.TYPE_RECORD;
  78. BEGIN
  79.   PKG_COLLECTION_LHR.P_SYS_REFCURSOR_LHR(7900, CUR_A);
  80.   LOOP
  81.     FETCH CUR_A
  82.       INTO R_TYPE_RECORD;
  83.     EXIT WHEN CUR_A%NOTFOUND;
  84.     DBMS_OUTPUT.PUT_LINE(R_TYPE_RECORD.EMPNO);
  85.   END LOOP;
  86. END;
  87. */
  88.   BEGIN
  89.     OPEN CUR_SYS FOR
  90.       SELECT LEVEL P_LEVEL,
  91.              T.EMPNO,
  92.              T.ENAME,
  93.              T.MGR,
  94.              (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || T.ENAME || '(' ||
  95.              T.EMPNO || ')') NAME_ALL,
  96.              SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
  97.              CONNECT_BY_ROOT(T.ENAME) ROOT,
  98.              DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  99.         FROM SCOTT.EMP T
  100.        START WITH MGR IS NULL
  101.       CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
  102.   
  103.   EXCEPTION
  104.     WHEN OTHERS THEN
  105.       NULL;
  106.   END P_SYS_REFCURSOR_LHR;

  108.   ------------------------------------------------------------------------------------------------------
  109.   PROCEDURE P_SYS_REFCURSOR_LHR_01(P_EMPNO IN NUMBER,
  110.                                    CUR_SYS OUT TYPE_CURSOR) IS
  111.   
  112.     /* --測試：
  113.   DECLARE
  114.   CUR_A PKG_COLLECTION_LHR.TYPE_CURSOR;
  115.   R_TYPE_RECORD PKG_COLLECTION_LHR.TYPE_RECORD;
  116. BEGIN
  117.   PKG_COLLECTION_LHR.P_SYS_REFCURSOR_LHR_01(7809, CUR_A);
  118.   LOOP
  119.     FETCH CUR_A
  120.       INTO R_TYPE_RECORD;
  121.     EXIT WHEN CUR_A%NOTFOUND;
  122.     DBMS_OUTPUT.PUT_LINE(R_TYPE_RECORD.EMPNO);
  123.   END LOOP;
  124. END;
  125. */
  126.   BEGIN
  127.     OPEN CUR_SYS FOR
  128.       SELECT LEVEL P_LEVEL,
  129.              T.EMPNO,
  130.              T.ENAME,
  131.              T.MGR,
  132.              (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || T.ENAME || '(' ||
  133.              T.EMPNO || ')') NAME_ALL,
  134.              SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
  135.              CONNECT_BY_ROOT(T.ENAME) ROOT,
  136.              DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  137.         FROM SCOTT.EMP T
  138.        START WITH MGR IS NULL
  139.       CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
  140.   
  141.   EXCEPTION
  142.     WHEN OTHERS THEN
  143.       NULL;
  144.   END P_SYS_REFCURSOR_LHR_01;

  146.   ------------------------------------------------------------------------------------------------------

  148.   PROCEDURE P_INDEX_TABLE_PKG_LHR(P_EMPNO IN NUMBER,
  149.                                   O_T_RECORD OUT T_RECORD) IS
  150.            /* --測試：
  151.   DECLARE
  152.   CUR_A PKG_COLLECTION_LHR.TYPE_CURSOR;
  153.   R_TYPE_RECORD PKG_COLLECTION_LHR.TYPE_RECORD;
  154. BEGIN
  155.   PKG_COLLECTION_LHR.P_SYS_REFCURSOR_LHR_01(306628323, CUR_A);
  156.   LOOP
  157.     FETCH CUR_A
  158.       INTO R_TYPE_RECORD;
  159.     EXIT WHEN CUR_A%NOTFOUND;
  160.     DBMS_OUTPUT.PUT_LINE(R_TYPE_RECORD.EMPNO);
  161.   END LOOP;
  162. END;
  163. */

  165.     R_TYPE TYPE_RECORD;
  166.   
  167.   BEGIN
  168.     O_T_RECORD := T_RECORD(); -- 注意這句不能丟 不然會報：ORA-06531: 引用未初始化的收集
  169.     FOR CUR IN (SELECT LEVEL P_LEVEL,
  170.                        T.EMPNO,
  171.                        T.ENAME,
  172.                        T.MGR,
  173.                        (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' ||
  174.                        T.ENAME || '(' || T.EMPNO || ')') NAME_ALL,
  175.                        SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
  176.                        CONNECT_BY_ROOT(T.ENAME) ROOT,
  177.                        DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  178.                   FROM SCOTT.EMP T
  179.                  START WITH MGR IS NULL
  180.                 CONNECT BY NOCYCLE MGR = PRIOR EMPNO) LOOP
  181.       R_TYPE.P_LEVEL := CUR.P_LEVEL;
  182.       R_TYPE.ALL_NAME_LEVEL := CUR.ALL_NAME_LEVEL;
  183.       R_TYPE.ROOT := CUR.ROOT;
  184.       R_TYPE.IS_LEAF := CUR.IS_LEAF;
  185.       O_T_RECORD.EXTEND;
  186.       O_T_RECORD(O_T_RECORD.LAST) := R_TYPE;
  187.     END LOOP;
  188.   
  189.   EXCEPTION
  190.     WHEN OTHERS THEN
  191.       NULL;
  192.   END P_INDEX_TABLE_PKG_LHR;
  193.   ------------------------------------------------------------------------------------------------------

  195.   ------------------------------------------------------------------------------------------------------

  197.   FUNCTION F_GET_SYS_REFCURSOR_LHR(P_EMPNO NUMBER) RETURN SYS_REFCURSOR IS
  198.   
  199.     -----------------------------------------------------------------------------------
  200.     -- Created on 2013-05-24 14:37:41 by lhr
  201.     --Changed on 2013-05-24 14:37:41 by lhr
  202.     -- function：
  203.     --測試： SELECT pkg_collection_lhr.f_get_SYS_REFCURSOR_lhr(306628323) FROM dual;
  204.     -----------------------------------------------------------------------------------
  205.   
  206.     CUR_SYS SYS_REFCURSOR;
  207.   BEGIN
  208.     OPEN CUR_SYS FOR
  209.       SELECT LEVEL P_LEVEL,
  210.              T.EMPNO,
  211.              T.ENAME,
  212.              T.MGR,
  213.              (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || T.ENAME || '(' ||
  214.              T.EMPNO || ')') NAME_ALL,
  215.              SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
  216.              CONNECT_BY_ROOT(T.ENAME) ROOT,
  217.              DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  218.         FROM SCOTT.EMP T
  219.        START WITH MGR IS NULL
  220.       CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
  221.   
  222.     RETURN CUR_SYS;
  223.   EXCEPTION
  224.     WHEN OTHERS THEN
  225.       NULL;
  226.   END;

  228.   ------------------------------------------------------------------------------------------------------

  230.   FUNCTION F_GET_INDEX_TABLE_PKG_LHR(P_EMPNO NUMBER) RETURN T_RECORD IS
  231.     O_T_RECORD T_RECORD;
  232.     R_TYPE TYPE_RECORD;
  233.   
  234.     /*DECLARE
  235.     RESULT pkg_collection_lhr.t_record;
  236.     BEGIN
  237.     -- Call the function
  238.     RESULT := pkg_collection_lhr.f_get_index_table_pkg_lhr(P_EMPNO => 306628323);
  239.     
  240.     dbms_output.put_line(RESULT(1).id);
  241.     END;*/
  242.   
  243.   BEGIN
  244.     O_T_RECORD := T_RECORD(); -- 注意這句不能丟 不然會報：ORA-06531: 引用未初始化的收集
  245.     FOR CUR IN (SELECT LEVEL P_LEVEL,
  246.                        T.EMPNO,
  247.                        T.ENAME,
  248.                        T.MGR,
  249.                        (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' ||
  250.                        T.ENAME || '(' || T.EMPNO || ')') NAME_ALL,
  251.                        SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
  252.                        CONNECT_BY_ROOT(T.ENAME) ROOT,
  253.                        DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  254.                   FROM SCOTT.EMP T
  255.                  START WITH MGR IS NULL
  256.                 CONNECT BY NOCYCLE MGR = PRIOR EMPNO) LOOP
  257.     
  258.       R_TYPE.P_LEVEL := CUR.P_LEVEL;
  259.       R_TYPE.ALL_NAME_LEVEL := CUR.ALL_NAME_LEVEL;
  260.       R_TYPE.ROOT := CUR.ROOT;
  261.       R_TYPE.IS_LEAF := CUR.IS_LEAF;
  262.       O_T_RECORD.EXTEND;
  263.       O_T_RECORD(O_T_RECORD.LAST) := R_TYPE;
  264.     
  265.       RETURN O_T_RECORD;
  266.     END LOOP;
  267.   END F_GET_INDEX_TABLE_PKG_LHR;

  269.   -----------------------------------------------------------------------------------
  270.   -- Created on 2012/8/20 11:33:07 by lhr
  271.   --Changed on 2012/8/20 11:33:07 by lhr
  272.   -- function：

  274.   /*select D.* from table( f_get_all_address_lhr(306628323)) D;
  275.   select * from the(select f_get_all_address_lhr(306628323) from dual);*/
  276.   -----------------------------------------------------------------------------------
  277.   FUNCTION F_GET_INDEX_TABLE_SCHEMA_LHR(P_EMPNO NUMBER)
  278.     RETURN TYP_ALL_ADDRESS_LHR IS
  279.   
  280.     SP_TABLE_LHR TYP_ALL_ADDRESS_LHR := TYP_ALL_ADDRESS_LHR();
  281.   
  282.     -- sp_table_lhr typ_all_address_lhr ;
  283.   BEGIN
  284.   
  285.     SELECT OBJ_ALL_ADDRESS_LHR(P_LEVEL,
  286.                                EMPNO,
  287.                                ENAME,
  288.                                MGR,
  289.                                NAME_ALL,
  290.                                ALL_NAME_LEVEL,
  291.                                ROOT,
  292.                                IS_LEAF)
  293.       BULK COLLECT
  294.       INTO SP_TABLE_LHR
  295.       FROM (SELECT LEVEL P_LEVEL,
  296.                    T.EMPNO,
  297.                    T.ENAME,
  298.                    T.MGR,
  299.                    (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || T.ENAME || '(' ||
  300.                    T.EMPNO || ')') NAME_ALL,
  301.                    SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
  302.                    CONNECT_BY_ROOT(T.ENAME) ROOT,
  303.                    DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  304.               FROM SCOTT.EMP T
  305.              START WITH MGR IS NULL
  306.             CONNECT BY NOCYCLE MGR = PRIOR EMPNO);
  307.   
  308.     ---或者用如下的for迴圈
  309.     /* FOR cur IN (SELECT LEVEL p_level,
  310.     t.id,
  311.     t.parentid,
  312.     t.assemblename,
  313.     t.addresslevel,
  314.     (SELECT d.description
  315.     FROM x_dictionary d
  316.     WHERE d. classid = 'ADDRESS'
  317.     AND d.attributeid = 'ADDRESSLEVEL'
  318.     AND d.value = t.addresslevel) add_level_description,
  319.     (lpad(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || t.name || '(' || t.id || ')') NAME_ALL,
  320.     substr(sys_connect_by_path(t.name, '=>'), 3) all_name_level,
  321.     connect_by_root(t.name) root,
  322.     decode(connect_by_isleaf, 1, 'Y', 0, 'N') is_leaf
  323.     FROM xb_address t
  324.     START WITH t.id = P_EMPNO
  325.     CONNECT BY nocycle PRIOR t.parentid = id) LOOP
  326.     
  327.     sp_table_lhr.EXTEND;
  328.     sp_table_lhr(sp_table_lhr.last) := obj_all_address_lhr('',
  329.     '',
  330.     '',
  331.     '',
  332.     '',
  333.     '',
  334.     '',
  335.     '',
  336.     '',
  337.     '');
  338.     sp_table_lhr(sp_table_lhr.last).p_level := cur.p_level;
  339.     sp_table_lhr(sp_table_lhr.last).id := cur.id;
  340.     sp_table_lhr(sp_table_lhr.last).parentid := cur.parentid;
  341.     END LOOP;
  342.     */
  343.   
  344.     ---或者用如下的for迴圈
  345.     /* FOR cur IN (SELECT LEVEL p_level,
  346.     t.id,
  347.     t.parentid,
  348.     t.assemblename,
  349.     t.addresslevel,
  350.     (SELECT d.description
  351.     FROM x_dictionary d
  352.     WHERE d. classid = 'ADDRESS'
  353.     AND d.attributeid = 'ADDRESSLEVEL'
  354.     AND d.value = t.addresslevel) add_level_description,
  355.     (lpad(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || t.name || '(' || t.id || ')') NAME_ALL,
  356.     substr(sys_connect_by_path(t.name, '=>'), 3) all_name_level,
  357.     connect_by_root(t.name) root,
  358.     decode(connect_by_isleaf, 1, 'Y', 0, 'N') is_leaf
  359.     FROM xb_address t
  360.     START WITH t.id = P_EMPNO
  361.     CONNECT BY nocycle PRIOR t.parentid = id) LOOP
  362.     
  363.     sp_table_lhr.EXTEND;
  364.     sp_table_lhr(sp_table_lhr.last) := obj_all_address_lhr(cur.p_level,
  365.     cur.id,
  366.     cur.parentid,
  367.     cur.assemblename,
  368.     cur.addresslevel,
  369.     cur.add_level_description,
  370.     cur.NAME_ALL,
  371.     cur.all_name_level,
  372.     cur.root,
  373.     cur.is_leaf);
  374.     END LOOP;
  375.     */
  376.     RETURN SP_TABLE_LHR;
  377.   END F_GET_INDEX_TABLE_SCHEMA_LHR;

  379.   ------------------------------------------------------------------------------------------------------
  380.   FUNCTION F_GET_INDEX_TABLE_PIPE_LHR(P_EMPNO NUMBER)
  381.     RETURN TYP_ALL_ADDRESS_LHR
  382.     PIPELINED IS
  383.   
  384.     SP_TABLE_LHR OBJ_ALL_ADDRESS_LHR;
  385.   BEGIN
  386.   
  387.     FOR CUR IN (SELECT LEVEL P_LEVEL,
  388.                        T.EMPNO,
  389.                        T.ENAME,
  390.                        T.MGR,
  391.                        (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' ||
  392.                        T.ENAME || '(' || T.EMPNO || ')') NAME_ALL,
  393.                        SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
  394.                        CONNECT_BY_ROOT(T.ENAME) ROOT,
  395.                        DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  396.                   FROM SCOTT.EMP T
  397.                  START WITH MGR IS NULL
  398.                 CONNECT BY NOCYCLE MGR = PRIOR EMPNO) LOOP
  399.     
  400.       SP_TABLE_LHR := OBJ_ALL_ADDRESS_LHR(CUR.P_LEVEL,
  401.                                           CUR.Empno,
  402.                                           CUR.Ename,
  403.                                           CUR.Mgr,
  404.                                           cur.NAME_ALL,
  405.                                           CUR.ALL_NAME_LEVEL,
  406.                                           CUR.ROOT,
  407.                                           CUR.IS_LEAF);
  408.       PIPE ROW(SP_TABLE_LHR);
  409.     
  410.     END LOOP;
  411.   
  412.     RETURN;
  413.   END F_GET_INDEX_TABLE_PIPE_LHR;

  415. END PKG_COLLECTION_LHR;
  416. /

About Me

...............................................................................................................................

● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2131977/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest/p/6254053.html

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 聯絡我請加QQ好友(642808185)，註明新增緣由

● 於 2017-01-05 08:00 ~ 2017-01-05 24:00 在農行完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr，免費學習最實用的資料庫技術。

wpsF8C8.tmp

 

PKG_COLLECTION_LHR 儲存過程或函式返回集合型別



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2131977/，如需轉載，請註明出處，否則將追究法律責任。

相關文章