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.建立轉換

  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);

      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;