Oracle中table函数的应用

表函数可接受查询语句或游标作为输入参数，并可输出多行数据。该函数可以平行执行，并可持续输出数据流，被称作管道式输出。应用表函数可将数据转换分阶段处理，并省去中间结果的存储和缓冲表。

1。 用游标传递数据
利用游标 REF CURSOR 可将数据集（多行记录）传递到PL/SQL函数：
SELECT 
FROM TABLE （myfunction （CURSOR （SELECT 
FROM mytab）））;

2。 利用两个实体化视图（或表）作为样板数据
CREATE MATERIALIZED VIEW sum_sales_country_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT 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_id;

CREATE MATERIALIZED VIEW sum_es_gend_mv
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT 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。 定义对象类型和基于对象类型的表类型
定义对象类型并且为进一步引用做好准备。
（1）定义对象类型：TYPE sales_country_t
CREATE TYPE sales_country_t AS OBJECT （
YEAR VARCHAR2 （4），
country CHAR （2），
sum_amount_sold NUMBER
）;
（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_pkg
IS
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。 定义表函数
（1）定义表函数：FUNCTION Table_Ref_Cur_Week
CREATE OR REPLACE FUNCTION table_ref_cur_week （cur CURSOR。refcur_t）
RETURN sum_sales_country_t_tab
IS
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）定义表函数：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 PIPELINED
IS
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）定义表函数：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 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_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）定义表函数：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_tab
IS
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。 调用表函数
下列 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‘）））;

本文转自左正博客园博客，原文链接：http://www.cnblogs.com/soundcode/archive/2012/01/04/2311933.html，如需转载请自行联系原作者