表函數(shù)可接受查詢語句或游標(biāo)作為輸入?yún)?shù)，并可輸出多行數(shù)據(jù)。該函數(shù)可以平行執(zhí)行，并可持續(xù)輸出數(shù)據(jù)流，被稱作管道式輸出。應(yīng)用表函數(shù)可將數(shù)據(jù)轉(zhuǎn)換分階段處理，并省去中間結(jié)果的存儲和緩沖表。
1. 用游標(biāo)傳遞數(shù)據(jù)
利用游標(biāo) REF CURSOR 可將數(shù)據(jù)集（多行記錄）傳遞到PL/SQL函數(shù)：
SELECT * FROM TABLE (myfunction (CURSOR (SELECT * FROM mytab)));
2. 利用兩個(gè)實(shí)體化視圖（或表）作為樣板數(shù)據(jù)
CREATE MATERIALIZED VIEW sum_sales_country_mvBUILD IMMEDIATEREFRESH COMPLETEENABLE QUERY REWRITEASSELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR, c.country_id country, SUM (sum_amount_sold) sum_amount_sold FROM sum_sales_month_mv s, customers c WHERE s.cust_id = c.cust_id AND c.country_id IN ('US', 'UK', 'FR', 'ES', 'JP', 'AU')GROUP BY SUBSTR (s.calendar_month_desc, 1, 4), c.country_idCREATE MATERIALIZED VIEW sum_es_gend_mvBUILD DEFERREDREFRESH FASTENABLE QUERY REWRITEASSELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR, s.calendar_month_desc cal_month, c.cust_gender, SUM (sum_amount_sold) sum_amount_sold FROM sum_sales_month_mv s, customer c WHERE s.cust_id = c.cust_id AND c.country_id = 'ES' AND sunstr (s.calendar_month_desc, 1, 4) = '2000'GROUP BY SUBSTR (s.calendar_month_desc, 1, 4), s.calendar_month_desc, c.cust_gender;
3. 定義對象類型和基于對象類型的表類型
定義對象類型并且為進(jìn)一步引用做好準(zhǔn)備。
（1）定義對象類型：TYPE sales_country_t
CREATE MATERIALIZED VIEW sum_es_gend_mvBUILD DEFERREDREFRESH FASTENABLE QUERY REWRITEASSELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR, s.calendar_month_desc cal_month, c.cust_gender, SUM (sum_amount_sold) sum_amount_sold FROM sum_sales_month_mv s, customer c WHERE s.cust_id = c.cust_id AND c.country_id = 'ES' AND sunstr (s.calendar_month_desc, 1, 4) = '2000'GROUP BY SUBSTR (s.calendar_month_desc, 1, 4), s.calendar_month_desc, c.cust_gender;
（2）定義表類型：TYPE SUM_SALES_COUNTRY_T_TAB
CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;
（3）定義對象類型：TYPE sales_gender_t
CREATE TYPE sales_gender_t AS OBJECT ( YEAR VARCHAR2 (4), country_id CHAR (2), cust_gender CHAR (1), sum_amount_sold NUMBER);
（4）定義表類型：TYPE SUM_SALES_GENDER_T_TAB
CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;
（5）定義對象類型：TYPE sales_roll_t
CREATE TYPE sales_roll_t AS OBJECT ( channel_desc VARCHAR2 (20), country_id CHAR (2), sum_amount_sold NUMBER);
（6）定義表類型：TYPE SUM_SALES_ROLL_T_TAB
CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;
（7）檢查一下建立的類型
SELECT object_name, object_type, status FROM user_objects WHERE object_type = 'TYPE';
4. 定義包：Create package and define REF CURSOR
CREATE OR REPLACE PACKAGE cursor_pkgI TYPE sales_country_t_rec IS RECORD ( YEAR VARCHAR (4), country CHAR (2), sum_amount_sold NUMBER ); TYPE sales_gender_t_rec IS RECORD ( YEAR VARCHAR2 (4), country_id CHAR (2), cust_gender CHAR (1), sum_amount_sold NUMBER ); TYPE sales_roll_t_rec IS RECORD ( channel_desc VARCHAR2 (20), country_id CHAR (2), 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;
5. 定義表函數(shù)
（1）定義表函數(shù)：FUNCTION Table_Ref_Cur_Week
CREATE OR REPLACE FUNCTION table_ref_cur_week (cur CURSOR.refcur_t) RETURN sum_sales_country_t_tabIS YEAR VARCHAR (4); country CHAR (2); sum_amount_sold NUMBER; objset sum_sales_country_t_tab := sum_sales_country_t_tab (); i NUMBER := 0;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 + 1; objset.EXTEND; objset (i) := sales_country_t (YEAR, country, sum_amount_sold); END LOOP; CLOSE cur; RETURN objset;END;/
（2）定義表函數(shù)：FUNCTION Table_Ref_Cur_Strong
CREATE OR REPLACE FUNCTION table_ref_cur_strong (cur cursor_pkg.strong_refcur_t) RETURN sum_sales_country_t_tab PIPELINEDIS YEAR VARCHAR (4); country CHAR (2); sum_amount_sold NUMBER; i NUMBER := 0;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;/
（3）定義表函數(shù)：FUNCTION Table_Ref_Cur_row
CREATE OR REPLACE FUNCTION table_ref_cur_row (cur cursor_pkg.row_refcur_t) RETURN sum_sales_country_t_tab PIPELINEDIS 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_rec.YEAR := in_rec.YEAR; out_rec.country := in_rec.country; out_rec.sum_amount_sold := in_rec.sum_amount_sold; PIPE ROW (out_rec); END LOOP; CLOSE cur; RETURN;END;/
（4）定義表函數(shù)：FUNCTION Gender_Table_Ref_Cur_Week
CREATE OR REPLACE FUNCTION gender_table_ref_cur_week (cur cursor_pkg.refcur_t) RETURN sum_sales_gender_t_tabIS YEAR VARCHAR2 (4); country_id CHAR (2); cust_gender CHAR (1); sum_amount_sold NUMBER; objset sum_sales_gender_t_tab := sum_sales_gender_t_tab (); i NUMBER := 0;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 + 1; objset.EXTEND; objset (i) := sum_sales_gender_t (YEAR, country_id, cust_gender, sum_amount_sold); END LOOP; CLOSE cur; RETURN objset;END;/
6. 調(diào)用表函數(shù)
下列 SQL 查詢語句調(diào)用已被定義的表函數(shù)。
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')));
以上所述是小編給大家介紹的Oracle 中 table 函數(shù)的應(yīng)用淺析，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時(shí)回復(fù)大家的。在此也非常感謝大家對武林網(wǎng)網(wǎng)站的支持！
新聞熱點(diǎn)
疑難解答
圖片精選
