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