欢迎来到 黑吧安全网 聚焦网络安全前沿资讯,精华内容,交流技术心得!

Oracle中table函数的应用有哪些

来源:本站整理 作者:佚名 时间:2017-01-05 TAG: 我要投稿

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

[1] [2]  下一页

【声明】:黑吧安全网(http://www.myhack58.com)登载此文出于传递更多信息之目的,并不代表本站赞同其观点和对其真实性负责,仅适于网络安全技术爱好者学习研究使用,学习中请遵循国家相关法律法规。如有问题请联系我们,联系邮箱admin@myhack58.com,我们会在最短的时间内进行处理。
  • 最新更新
    • 相关阅读
      • 本类热门
        • 最近下载