Oracle 中 table 函数的应用浅析

数据库  /  管理员 发布于 6年前   205

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

1. 用游标传递数据

利用游标 REF CURSOR 可将数据集（多行记录）传递到PL/SQL函数：

SELECT * FROM TABLE (myfunction (CURSOR (SELECT *         FROM mytab)));  

2. 利用两个实体化视图（或表）作为样板数据

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_id

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;

3. 定义对象类型和基于对象类型的表类型

定义对象类型并且为进一步引用做好准备。

（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. 定义表函数

（1）定义表函数：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）定义表函数：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）定义表函数：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）定义表函数：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. 调用表函数

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

以上所述是小编给大家介绍的Oracle 中 table 函数的应用浅析，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！