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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

MySQL数据类型 -- 字符型

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/art...

702
来自专栏青玉伏案

Oracle之PL/SQL学习笔记

  自己在学习Oracle是做的笔记及实验代码记录,内容挺全的,也挺详细,发篇博文分享给需要的朋友,共有1w多字的学习笔记吧。是以前做的,一直在压箱底,今天拿出...

1828
来自专栏乐沙弥的世界

PL/SQL 嵌套记录与记录集合

    将多个逻辑上不相关列组合到一起形成了PL/SQL的记录类型,从而可以将记录类型作为一个整体对待来处理。而且PL/SQL记录类型可以进行 嵌套以及基于PL...

592
来自专栏技术碎碎念

sql server 2008 数据库的完整性约束

一、数据库完整性概述 1.数据库的完整性: ①数据库的完整性是指数据的正确性和相容性 ②数据库完整性是防止不合语义或不正确的数据进入数据库 ③完整性体现了是否真...

3064
来自专栏大前端_Web

SQL笔记

版权声明:本文为吴孔云博客原创文章,转载请注明出处并带上链接,谢谢。 https://blog.csdn.net/wkyseo/articl...

872
来自专栏流柯技术学院

MySQL执行计划解读

id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

821
来自专栏xcywt

学习SQLite之路(二)

  下面就是真正关于数据库的一些知识了: 20160614更新  参考: http://www.runoob.com/sqlite/sqlite-tutoria...

1787
来自专栏Youngxj

emlog网站信息统计代码

2764
来自专栏技术之路

sqlserver 各种判断是否存在(表名、函数、存储过程.......)

库是否存在 if exists(select * from master..sysdatabases where name=N'库名') print 'exi...

17810
来自专栏性能与架构

mysql 索引无效的情况

下面几种情况下,索引是不会被使用的 (1)组合索引,查询时的条件列不是组合索引中的第一个列 例如 组合索引 (a,b),查询中使用了b作为查询条件,这时是不会用...

3347

扫码关注云+社区