Oracle中table函數的應用

　　表函數可接受查詢語句或游標作為輸入參數並可輸出多行數據該函數可以平行執行並可持續輸出數據流被稱作管道式輸出應用表函數可將數據轉換分階段處理並省去中間結果的存儲和緩沖表

　　 用游標傳遞數據

　　利用游標 REF CURSOR 可將數據集（多行記錄）傳遞到PL/SQL函數

　　SELECT *

　　FROM TABLE (myfunction (CURSOR (SELECT *

　　FROM mytab)));

　　 利用兩個實體化視圖（或表）作為樣板數據

　　CREATE MATERIALIZED VIEW sum_sales_country_mv

　　BUILD IMMEDIATE

　　REFRESH COMPLETE

　　ENABLE QUERY REWRITE

　　AS

　　SELECT   SUBSTR (scalendar_month_desc ) YEAR untry_id country

　　SUM (sum_amount_sold) sum_amount_sold

　　FROM sum_sales_month_mv s customers c

　　WHERE scust_id = ccust_id

　　AND untry_id IN (US UK FR ES JP AU)

　　GROUP BY SUBSTR (scalendar_month_desc ) untry_id;

　　CREATE MATERIALIZED VIEW sum_es_gend_mv

　　BUILD DEFERRED

　　REFRESH FAST

　　ENABLE QUERY REWRITE

　　AS

　　SELECT   SUBSTR (scalendar_month_desc ) YEAR

　　scalendar_month_desc cal_month ccust_gender

　　SUM (sum_amount_sold) sum_amount_sold

　　FROM sum_sales_month_mv s customer c

　　WHERE scust_id = ccust_id

　　AND untry_id = ES

　　AND sunstr (scalendar_month_desc ) =

　　GROUP BY SUBSTR (scalendar_month_desc )

　　scalendar_month_desc

　　ccust_gender;

　　 定義對象類型和基於對象類型的表類型

　　定義對象類型並且為進一步引用做好准備

　　（）定義對象類型TYPE sales_country_t

　　CREATE TYPE sales_country_t AS OBJECT (

　　YEAR              VARCHAR ()

　　country           CHAR ()

　　sum_amount_sold   NUMBER

　　);

　　（）定義表類型TYPE SUM_SALES_COUNTRY_T_TAB

　　CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

　　（）定義對象類型TYPE sales_gender_t

　　CREATE TYPE sales_gender_t AS OBJECT (

　　YEAR              VARCHAR ()

　　country_id        CHAR ()

　　cust_gender       CHAR ()

　　sum_amount_sold   NUMBER

　　);

　　（）定義表類型TYPE SUM_SALES_GENDER_T_TAB

　　CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;

　　（）定義對象類型TYPE sales_roll_t

　　CREATE TYPE sales_roll_t AS OBJECT (

　　channel_desc      VARCHAR ()

　　country_id        CHAR ()

　　sum_amount_sold   NUMBER

　　);

　　（）定義表類型TYPE SUM_SALES_ROLL_T_TAB

　　CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;

　　（）檢查一下建立的類型

　　SELECT object_name object_type status

　　FROM user_objects

　　WHERE object_type = TYPE;

　　 定義包Create package and define REF CURSOR

　　CREATE OR REPLACE PACKAGE cursor_pkg

　　IS

　　TYPE sales_country_t_rec IS RECORD (

　　YEAR              VARCHAR ()

　　country           CHAR ()

　　sum_amount_sold   NUMBER

　　);

　　TYPE sales_gender_t_rec IS RECORD (

　　YEAR              VARCHAR ()

　　country_id        CHAR ()

　　cust_gender       CHAR ()

　　sum_amount_sold   NUMBER

　　);

　　TYPE sales_roll_t_rec IS RECORD (

　　channel_desc      VARCHAR ()

　　country_id        CHAR ()

　　sum_amount_sold   NUMBER

　　);

　　TYPE sales_country_t_rectab IS TABLE OF sales_country_t_rec;

　　TYPE sales_roll_t_rectab IS TABLE OF sales_roll_t_rec;

　　TYPE strong_refcur_t IS REF CURSOR

　　RETURN sales_country_t_rec;

　　TYPE row_refcur_t IS REF CURSOR

　　RETURN sum_sales_country_mv%ROWTYPE;

　　TYPE roll_refcur_t IS REF CURSOR

　　RETURN sales_roll_t_rec;

　　TYPE refcur_t IS REF CURSOR;

　　END corsor_pkg;

　　 定義表函數

　　（）定義表函數FUNCTION Table_Ref_Cur_Week

　　CREATE OR REPLACE FUNCTION table_ref_cur_week (cur CURSORrefcur_t)

　　RETURN sum_sales_country_t_tab

　　IS

　　YEAR              VARCHAR ();

　　country           CHAR ();

　　sum_amount_sold   NUMBER;

　　objset            sum_sales_country_t_tab := sum_sales_country_t_tab ();

　　i                 NUMBER                  := ;

　　BEGIN

　　LOOP

　　 Fetch from cursor variable

　　FETCH cur

　　INTO YEAR country sum_amount_sold;

　　EXIT WHEN cur%NOTFOUND;

　　 exit when last row is fetched

　　 append to collection

　　i := i + ;

　　objsetEXTEND;

　　objset (i) := sales_country_t (YEAR country sum_amount_sold);

　　END LOOP;

　　CLOSE cur;

　　RETURN objset;

　　END;

　　/

　　（）定義表函數FUNCTION Table_Ref_Cur_Strong

　　CREATE OR REPLACE FUNCTION table_ref_cur_strong (cur cursor_pkgstrong_refcur_t)

　　RETURN sum_sales_country_t_tab PIPELINED

　　IS

　　YEAR              VARCHAR ();

　　country           CHAR ();

　　sum_amount_sold   NUMBER;

　　i                 NUMBER      := ;

　　BEGIN

　　LOOP

　　FETCH cur

　　INTO YEAR country sum_amount_sold;

　　EXIT WHEN cur%NOTFOUND;                   exit when last row fetched

　　PIPE ROW (sales_country_t (YEAR country sum_amount_sold));

　　END LOOP;

　　CLOSE cur;

　　RETURN;

　　END;

　　/

　　（）定義表函數FUNCTION Table_Ref_Cur_row

　　CREATE OR REPLACE FUNCTION table_ref_cur_row (cur cursor_pkgrow_refcur_t)

　　RETURN sum_sales_country_t_tab PIPELINED

　　IS

　　in_rec    cur%ROWTYPE;

　　out_rec   sales_country_t := sales_country_t (NULL NULL NULL);

　　BEGIN

　　LOOP

　　FETCH cur

　　INTO in_rec;

　　EXIT WHEN cur%NOTFOUND;                exit when last row is fetched

　　out_recYEAR := in_recYEAR;

　　untry := untry;

　　out_recsum_amount_sold := in_recsum_amount_sold;

　　PIPE ROW (out_rec);

　　END LOOP;

　　CLOSE cur;

　　RETURN;

　　END;

　　/

　　（）定義表函數FUNCTION Gender_Table_Ref_Cur_Week

　　CREATE OR REPLACE FUNCTION gender_table_ref_cur_week (cur cursor_pkgrefcur_t)

　　RETURN sum_sales_gender_t_tab

　　IS

　　YEAR              VARCHAR ();

　　country_id        CHAR ();

　　cust_gender       CHAR ();

　　sum_amount_sold   NUMBER;

　　objset            sum_sales_gender_t_tab := sum_sales_gender_t_tab ();

　　i                 NUMBER                 := ;

　　BEGIN

　　LOOP

　　FETCH cur

　　INTO YEAR country_id cust_gender sum_amount_sold;

　　EXIT WHEN cur%NOTFOUND;                exit when last row is fetched

　　i := i + ;

　　objsetEXTEND;

　　objset (i) :=

　　sum_sales_gender_t (YEAR country_id cust_gender sum_amount_sold);

　　END LOOP;

　　CLOSE cur;

　　RETURN objset;

　　END;

　　/

　　 調用表函數

　　下列 SQL 查詢語句調用已被定義的表函數

　　SELECT *

　　FROM TABLE (table_ref_cur_week (CURSOR (SELECT *

　　FROM sum_sales_country_mv)));

　　SELECT *

　　FROM TABLE (table_ref_cur_strong (CURSOR (SELECT *

　　FROM sum_sales_country_mv)));

　　SELECT *

　　FROM TABLE (table_ref_cur_row (CURSOR (SELECT *

　　FROM sum_sales_country_mv)));

　　SELECT *

　　FROM TABLE (table_ref_cur_week (CURSOR (SELECT *

　　FROM sum_sales_country_mv

　　WHERE country = AU)));

From:http://tw.wingwit.com/Article/program/Oracle/201311/18328.html