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 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;
(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 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), 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')));
(资源库 www.zyku.net)
上一篇:Oracle中转义字符的详细介绍
栏 目:Oracle
下一篇:Oracle使用触发器和mysql中使用触发器的案例比较
本文地址:https://www.zyku.net/oracle/616.html
您可能感兴趣的文章
- 03-28Python 执行函数的九种方法
- 03-15微信小程序 onLoad 函数
- 03-13帝国CMS提示信息函数printerror()
- 03-13帝国CMS常用函数介绍(二次开发参考)
- 03-13帝国CMS常用函数
- 03-13帝国CMS获取信息内容页地址函数sys_ReturnBqTitleLink
- 05-05SQLServer 2016安装时的错误:Polybase要求安装Oracle J
- 08-30织梦DedeCMS获取文章链接的函数GetOneArchive使用方法
- 11-30$_SERVER函数中QUERY_STRING和REQUEST_URI区别详解
- 07-27PHP自定义函数判断是否为Get、Post及Ajax提交的方法
- 03-13通过PHP代码将网站保存到桌面的方法
- 07-05Linux spell命令
- 03-22vivox60微信视频开启美颜教程
- 01-27华为mate40pro振动触动反馈设置方法
- 01-08皮革人才网-皮革人才网应用软件功能介
- 11-14nginx设置目录浏览及中文乱码问题解决
- 04-20小米11ultra开启背屏通知内容方法
- 02-17正则验证不能含有中文的实现方法【jQu
- 10-27抖音未读消息怎么关闭
- 01-16天城府苑-天城府苑应用软件功能介绍
最近更新
阅读排行
猜你喜欢
- 04-27vivox60桌面组件添加方法
- 09-23钉钉直播回放在哪看
- 10-12苹果13promax私密相册如何关闭
- 06-20mysql实现随机获取几条数据的方法
- 10-03vivox70怎么开启来电闪光灯
- 02-19苹果手机地图更改语言方法
- 02-20notepad++设置默认为UTF-8无BOM格式
- 07-22OneinStack安装GoGetSSL Comodo Free
- 03-02华为手机更换翻页效果教程
- 12-29自制表情包-自制表情包应用软件功能介