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

點選(此處)摺疊或開啟

CREATE OR REPLACE PACKAGE PKG_COLLECTION_LHR AUTHID CURRENT_USER AS
-----------------------------------------------------------------------------------
-- Created on 2013-05-24 14:37:41 by lhr
--Changed on 2013-05-24 14:37:41 by lhr
-- function：返回各種各樣的集合
/*
DROP TYPE obj_all_address_lhr FORCE;
DROP TYPE typ_all_address_lhr FORCE;
CREATE OR REPLACE TYPE obj_all_address_lhr AS OBJECT(
P_LEVEL NUMBER(18),
EMPNO NUMBER(18),
ENAME VARCHAR2(4000),
MGR NUMBER(18),
NAME_ALL VARCHAR2(4000),
ALL_NAME_LEVEL VARCHAR2(4000),
ROOT VARCHAR2(4000),
IS_LEAF VARCHAR2(10)
);
CREATE OR REPLACE TYPE typ_all_address_lhr AS TABLE OF obj_all_address_lhr;
*/
-----------------------------------------------------------------------------------
-----------------------------變數--------------------------------------
TYPE TYPE_CURSOR IS REF CURSOR;
TYPE TYPE_RECORD IS RECORD(
P_LEVEL NUMBER(18),
EMPNO NUMBER(18),
ENAME VARCHAR2(4000),
MGR NUMBER(18),
NAME_ALL VARCHAR2(4000),
ALL_NAME_LEVEL VARCHAR2(4000),
ROOT VARCHAR2(4000),
IS_LEAF VARCHAR2(10));
TYPE T_RECORD IS TABLE OF TYPE_RECORD;
-----------------------------存過--------------------------------------
--系統遊標 --推薦
PROCEDURE P_SYS_REFCURSOR_LHR(P_EMPNO IN NUMBER,
CUR_SYS OUT SYS_REFCURSOR);
-- 自定義遊標
PROCEDURE P_SYS_REFCURSOR_LHR_01(P_EMPNO IN NUMBER,
CUR_SYS OUT TYPE_CURSOR);
---索引表 --包級別
PROCEDURE P_INDEX_TABLE_PKG_LHR(P_EMPNO IN NUMBER,
O_T_RECORD OUT T_RECORD);
------------------------------函式-------------------------------------
--系統遊標
FUNCTION F_GET_SYS_REFCURSOR_LHR(P_EMPNO NUMBER) RETURN SYS_REFCURSOR;
--索引表 --包級別不能透過sql語句直接查詢
FUNCTION F_GET_INDEX_TABLE_PKG_LHR(P_EMPNO NUMBER) RETURN T_RECORD;
--索引表 --schema 級別可以直接查詢
/*select D.* from table( f_get_all_address_lhr(306628323)) D;
select * from the(select f_get_all_address_lhr(306628323) from dual);*/
FUNCTION F_GET_INDEX_TABLE_SCHEMA_LHR(P_EMPNO NUMBER)
RETURN TYP_ALL_ADDRESS_LHR;
---- 索引表 --schema 級別 --管道化可以直接查詢
FUNCTION F_GET_INDEX_TABLE_PIPE_LHR(P_EMPNO NUMBER)
RETURN TYP_ALL_ADDRESS_LHR
PIPELINED;
END PKG_COLLECTION_LHR;
/
CREATE OR REPLACE PACKAGE BODY PKG_COLLECTION_LHR AS
PROCEDURE P_SYS_REFCURSOR_LHR(P_EMPNO IN NUMBER,
CUR_SYS OUT SYS_REFCURSOR) IS
/*DECLARE
CUR_A SYS_REFCURSOR;
R_TYPE_RECORD PKG_COLLECTION_LHR.TYPE_RECORD;
BEGIN
PKG_COLLECTION_LHR.P_SYS_REFCURSOR_LHR(7900, CUR_A);
LOOP
FETCH CUR_A
INTO R_TYPE_RECORD;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R_TYPE_RECORD.EMPNO);
END LOOP;
END;
*/
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 MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
EXCEPTION
WHEN OTHERS THEN
NULL;
END P_SYS_REFCURSOR_LHR;
------------------------------------------------------------------------------------------------------
PROCEDURE P_SYS_REFCURSOR_LHR_01(P_EMPNO IN NUMBER,
CUR_SYS OUT TYPE_CURSOR) IS
/* --測試：
DECLARE
CUR_A PKG_COLLECTION_LHR.TYPE_CURSOR;
R_TYPE_RECORD PKG_COLLECTION_LHR.TYPE_RECORD;
BEGIN
PKG_COLLECTION_LHR.P_SYS_REFCURSOR_LHR_01(7809, CUR_A);
LOOP
FETCH CUR_A
INTO R_TYPE_RECORD;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R_TYPE_RECORD.EMPNO);
END LOOP;
END;
*/
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 MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
EXCEPTION
WHEN OTHERS THEN
NULL;
END P_SYS_REFCURSOR_LHR_01;
------------------------------------------------------------------------------------------------------
PROCEDURE P_INDEX_TABLE_PKG_LHR(P_EMPNO IN NUMBER,
O_T_RECORD OUT T_RECORD) IS
/* --測試：
DECLARE
CUR_A PKG_COLLECTION_LHR.TYPE_CURSOR;
R_TYPE_RECORD PKG_COLLECTION_LHR.TYPE_RECORD;
BEGIN
PKG_COLLECTION_LHR.P_SYS_REFCURSOR_LHR_01(306628323, CUR_A);
LOOP
FETCH CUR_A
INTO R_TYPE_RECORD;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R_TYPE_RECORD.EMPNO);
END LOOP;
END;
*/
R_TYPE TYPE_RECORD;
BEGIN
O_T_RECORD := T_RECORD(); -- 注意這句不能丟不然會報：ORA-06531: 引用未初始化的收集
FOR CUR IN (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 MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO) LOOP
R_TYPE.P_LEVEL := CUR.P_LEVEL;
R_TYPE.ALL_NAME_LEVEL := CUR.ALL_NAME_LEVEL;
R_TYPE.ROOT := CUR.ROOT;
R_TYPE.IS_LEAF := CUR.IS_LEAF;
O_T_RECORD.EXTEND;
O_T_RECORD(O_T_RECORD.LAST) := R_TYPE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END P_INDEX_TABLE_PKG_LHR;
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
FUNCTION F_GET_SYS_REFCURSOR_LHR(P_EMPNO NUMBER) RETURN SYS_REFCURSOR IS
-----------------------------------------------------------------------------------
-- Created on 2013-05-24 14:37:41 by lhr
--Changed on 2013-05-24 14:37:41 by lhr
-- function：
--測試： SELECT pkg_collection_lhr.f_get_SYS_REFCURSOR_lhr(306628323) FROM dual;
-----------------------------------------------------------------------------------
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 MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
RETURN CUR_SYS;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
------------------------------------------------------------------------------------------------------
FUNCTION F_GET_INDEX_TABLE_PKG_LHR(P_EMPNO NUMBER) RETURN T_RECORD IS
O_T_RECORD T_RECORD;
R_TYPE TYPE_RECORD;
/*DECLARE
RESULT pkg_collection_lhr.t_record;
BEGIN
-- Call the function
RESULT := pkg_collection_lhr.f_get_index_table_pkg_lhr(P_EMPNO => 306628323);
dbms_output.put_line(RESULT(1).id);
END;*/
BEGIN
O_T_RECORD := T_RECORD(); -- 注意這句不能丟不然會報：ORA-06531: 引用未初始化的收集
FOR CUR IN (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 MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO) LOOP
R_TYPE.P_LEVEL := CUR.P_LEVEL;
R_TYPE.ALL_NAME_LEVEL := CUR.ALL_NAME_LEVEL;
R_TYPE.ROOT := CUR.ROOT;
R_TYPE.IS_LEAF := CUR.IS_LEAF;
O_T_RECORD.EXTEND;
O_T_RECORD(O_T_RECORD.LAST) := R_TYPE;
RETURN O_T_RECORD;
END LOOP;
END F_GET_INDEX_TABLE_PKG_LHR;
-----------------------------------------------------------------------------------
-- Created on 2012/8/20 11:33:07 by lhr
--Changed on 2012/8/20 11:33:07 by lhr
-- function：
/*select D.* from table( f_get_all_address_lhr(306628323)) D;
select * from the(select f_get_all_address_lhr(306628323) from dual);*/
-----------------------------------------------------------------------------------
FUNCTION F_GET_INDEX_TABLE_SCHEMA_LHR(P_EMPNO NUMBER)
RETURN TYP_ALL_ADDRESS_LHR IS
SP_TABLE_LHR TYP_ALL_ADDRESS_LHR := TYP_ALL_ADDRESS_LHR();
-- sp_table_lhr typ_all_address_lhr ;
BEGIN
SELECT OBJ_ALL_ADDRESS_LHR(P_LEVEL,
EMPNO,
ENAME,
MGR,
NAME_ALL,
ALL_NAME_LEVEL,
ROOT,
IS_LEAF)
BULK COLLECT
INTO SP_TABLE_LHR
FROM (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 MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO);
---或者用如下的for迴圈
/* FOR cur IN (SELECT LEVEL p_level,
t.id,
t.parentid,
t.assemblename,
t.addresslevel,
(SELECT d.description
FROM x_dictionary d
WHERE d. classid = 'ADDRESS'
AND d.attributeid = 'ADDRESSLEVEL'
AND d.value = t.addresslevel) add_level_description,
(lpad(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || t.name || '(' || t.id || ')') NAME_ALL,
substr(sys_connect_by_path(t.name, '=>'), 3) all_name_level,
connect_by_root(t.name) root,
decode(connect_by_isleaf, 1, 'Y', 0, 'N') is_leaf
FROM xb_address t
START WITH t.id = P_EMPNO
CONNECT BY nocycle PRIOR t.parentid = id) LOOP
sp_table_lhr.EXTEND;
sp_table_lhr(sp_table_lhr.last) := obj_all_address_lhr('',
'',
'',
'',
'',
'',
'',
'',
'',
'');
sp_table_lhr(sp_table_lhr.last).p_level := cur.p_level;
sp_table_lhr(sp_table_lhr.last).id := cur.id;
sp_table_lhr(sp_table_lhr.last).parentid := cur.parentid;
END LOOP;
*/
---或者用如下的for迴圈
/* FOR cur IN (SELECT LEVEL p_level,
t.id,
t.parentid,
t.assemblename,
t.addresslevel,
(SELECT d.description
FROM x_dictionary d
WHERE d. classid = 'ADDRESS'
AND d.attributeid = 'ADDRESSLEVEL'
AND d.value = t.addresslevel) add_level_description,
(lpad(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || t.name || '(' || t.id || ')') NAME_ALL,
substr(sys_connect_by_path(t.name, '=>'), 3) all_name_level,
connect_by_root(t.name) root,
decode(connect_by_isleaf, 1, 'Y', 0, 'N') is_leaf
FROM xb_address t
START WITH t.id = P_EMPNO
CONNECT BY nocycle PRIOR t.parentid = id) LOOP
sp_table_lhr.EXTEND;
sp_table_lhr(sp_table_lhr.last) := obj_all_address_lhr(cur.p_level,
cur.id,
cur.parentid,
cur.assemblename,
cur.addresslevel,
cur.add_level_description,
cur.NAME_ALL,
cur.all_name_level,
cur.root,
cur.is_leaf);
END LOOP;
*/
RETURN SP_TABLE_LHR;
END F_GET_INDEX_TABLE_SCHEMA_LHR;
------------------------------------------------------------------------------------------------------
FUNCTION F_GET_INDEX_TABLE_PIPE_LHR(P_EMPNO NUMBER)
RETURN TYP_ALL_ADDRESS_LHR
PIPELINED IS
SP_TABLE_LHR OBJ_ALL_ADDRESS_LHR;
BEGIN
FOR CUR IN (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 MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO) LOOP
SP_TABLE_LHR := OBJ_ALL_ADDRESS_LHR(CUR.P_LEVEL,
CUR.Empno,
CUR.Ename,
CUR.Mgr,
cur.NAME_ALL,
CUR.ALL_NAME_LEVEL,
CUR.ROOT,
CUR.IS_LEAF);
PIPE ROW(SP_TABLE_LHR);
END LOOP;
RETURN;
END F_GET_INDEX_TABLE_PIPE_LHR;
END PKG_COLLECTION_LHR;
/

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，免費學習最實用的資料庫技術。

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

相關文章