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;