pl/sql中使用table()函数的例子

1.创建type，必须是单独的type，在pkg中的不行

CREATE OR REPLACE TYPE obj_txb_xb97 as TxbLE OF rec_xb97;

CREATE OR REPLACE TYPE rec_xb97 AS OBJECT
(
xoe001 NUMBER(18),
xxe901 NUMBER(18),
xxe140 VARCHAR2(3),
xxe210 VARCHAR2(3),
xxb210 NUMBER(18, 2),
xxb130 NUMBER(18, 2),
xxb019 VARCHAR2(3),
xxb020 VARCHAR2(3),
xxb021 VARCHAR2(3),
xxb022 VARCHAR2(3),
xxb200 VARCHAR2(20),
xxe151 VARCHAR2(3),
xxe099 VARCHAR2(3)
);

2.创建转换

/****************************
返回xb97对象用于txble函数
written by：wonder
modified by:
****************************/
FUNCTION fun_a_returnxb97type(prm_txb_xb97 IN typ_txb_xb97)
RETURN obj_txb_xb97 IS
v_row rec_xb97 := NULL;
t_xb97 obj_txb_xb97 := obj_txb_xb97();
BEGIN
-- 初始化

FOR i IN 1 .. prm_txb_xb97.COUNT LOOP
t_xb97.EXTEND();
v_row := NEW rec_xb97(prm_txb_xb97(i).xoe001,
prm_txb_xb97(i).xxe901,
prm_txb_xb97(i).xxe140,
prm_txb_xb97(i).xxe210,
prm_txb_xb97(i).xxb210,
prm_txb_xb97(i).xxb130,
prm_txb_xb97(i).xxb019,
prm_txb_xb97(i).xxb020,
prm_txb_xb97(i).xxb021,
prm_txb_xb97(i).xxb022,
prm_txb_xb97(i).xxb200,
prm_txb_xb97(i).xxe151,
prm_txb_xb97(i).xxe099);
/* v_row.xoe001 := prm_txb_xb97(i).xoe001;
v_row.xxe901 := prm_txb_xb97(i).xxe901;
v_row.xxe140 := prm_txb_xb97(i).xxe140;
v_row.xxe210 := prm_txb_xb97(i).xxe210;
v_row.xxb210 := prm_txb_xb97(i).xxb210;
v_row.xxb130 := prm_txb_xb97(i).xxb130;
v_row.xxb019 := prm_txb_xb97(i).xxb019;
v_row.xxb020 := prm_txb_xb97(i).xxb020;
v_row.xxb021 := prm_txb_xb97(i).xxb021;
v_row.xxb022 := prm_txb_xb97(i).xxb022;
v_row.xxb200 := prm_txb_xb97(i).xxb200;
v_row.xxe151 := prm_txb_xb97(i).xxe151;
v_row.xxe099 := prm_txb_xb97(i).xxe099;*/
t_xb97(i) := v_row;
END LOOP;

RETURN t_xb97;

END;

3.使用table()

-- 对table函数的使用

obj_xb97 := fun_a_returnxb97type(prm_txb_xb97);
SELECT 0 xoe001,
xxe901,
xxe140,
xxe210,
SUM(nvl(xxb210, 0)),
0,
xxb019,
xxb020,
xxb021,
xxb022,
xxb200,
xxe151,
xxe099 BULK COLLECT
INTO prm_txb_xb97
FROM TAbLE(obj_xb97)
HAVING SUM(nvl(xxb210, 0)) <> 0
GROUP BY xxe901,
xxe140,
xxe210,
xxb019,
xxb020,
xxb021,
xxb022,
xxb200,
xxe151,
xxe099;