首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Oracle 中 table 函数的应用

Oracle 中 table 函数的应用

作者头像
用户1148526
发布2018-01-03 14:27:46
2.2K0
发布2018-01-03 14:27:46
举报
文章被收录于专栏:Hadoop数据仓库Hadoop数据仓库

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

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

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2016年12月29日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
对象存储
对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档