Oracle 中 table 函数的应用浅析

前端之家收集整理的这篇文章主要介绍了Oracle 中 table 函数的应用浅析前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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

1. 用游标传递数据

利用游标 REF CURSOR 可将数据集(多行记录)传递到PL/sql函数

sql;"> SELECT * FROM TABLE (myfunction (CURSOR (SELECT * FROM mytab)));

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

sql;"> 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,4),c.country_id
sql;"> CREATE MATERIALIZED VIEW sum_es_gend_mv BUILD DEFERRED REFRESH FAST ENABLE QUERY REWRITE AS SELECT SUBSTR (s.calendar_month_desc,s.calendar_month_desc cal_month,c.cust_gender,customer c WHERE s.cust_id = c.cust_id AND c.country_id = 'ES' AND sunstr (s.calendar_month_desc,4) = '2000' GROUP BY SUBSTR (s.calendar_month_desc,s.calendar_month_desc,c.cust_gender;

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

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

(1)定义对象类型:TYPE sales_country_t

sql;"> CREATE MATERIALIZED VIEW sum_es_gend_mv BUILD DEFERRED REFRESH FAST ENABLE QUERY REWRITE AS SELECT SUBSTR (s.calendar_month_desc,c.cust_gender;

(2)定义表类型:TYPE SUM_SALES_COUNTRY_T_TAB

sql;"> CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

(3)定义对象类型:TYPE sales_gender_t

sql;"> 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

sql;"> CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;

(5)定义对象类型:TYPE sales_roll_t

sql;"> CREATE TYPE sales_roll_t AS OBJECT ( channel_desc VARCHAR2 (20),sum_amount_sold NUMBER );

(6)定义表类型:TYPE SUM_SALES_ROLL_T_TAB

sql;"> CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;

(7)检查一下建立的类型

sql;"> SELECT object_name,object_type,status FROM user_objects WHERE object_type = 'TYPE';

4. 定义包:Create package and define REF CURSOR

sql;"> CREATE OR REPLACE PACKAGE cursor_pkg I 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),sum_amount_sold NUMBER ); TYPE sales_roll_t_rec IS RECORD ( channel_desc VARCHAR2 (20),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 h3_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

sql;"> 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,sum_amount_sold); END LOOP; CLOSE cur; RETURN objset; END; /

(2)定义表函数:FUNCTION Table_Ref_Cur_h3

sql;"> CREATE OR REPLACE FUNCTION table_ref_cur_h3 (cur cursor_pkg.h3_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,sum_amount_sold; EXIT WHEN cur%NOTFOUND; -- exit when last row fetched PIPE ROW (sales_country_t (YEAR,sum_amount_sold)); END LOOP; CLOSE cur; RETURN; END; /

(3)定义表函数:FUNCTION Table_Ref_Cur_row

sql;"> 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

sql;"> 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,sum_amount_sold); END LOOP; CLOSE cur; RETURN objset; END; /

6. 调用函数

下列 sql 查询语句调用已被定义的表函数

sql;"> SELECT * FROM TABLE (table_ref_cur_week (CURSOR (SELECT * FROM sum_sales_country_mv))); SELECT * FROM TABLE (table_ref_cur_h3 (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 函数的应用浅析,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对编程之家网站的支持

猜你在找的Oracle相关文章