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 条评论
登录 后参与评论

相关文章

来自专栏黑泽君的专栏

day04_MySQL学习笔记_01

数据库(DataBase,DB):指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。(文件系统)

531
来自专栏电光石火

导出表结构(数据字典)

-- 数据字典 SELECT (case when a.colorder=1 then d.name else '' end)表名, a.co...

1806
来自专栏跟着阿笨一起玩NET

sqlserver 存储过程中拼接sql语句 动态执行

4081
来自专栏飞扬的花生

SQL创建流水号

创建流水号表 CREATE TABLE SystemSerialNo ( SerialNoId INT PRIMARY KEY IDENTITY, ...

20310
来自专栏AhDung

【SQL】统计所有表的行数

原理:遍历所有用户表,用sp_spaceused过程分别获取每张表的行数并写入临时表,最后返回临时表

1452
来自专栏杨建荣的学习笔记

使用copy命令解决LONG类型的困扰(r2第24天)

在oracle的数据类型中,long类型算是一个比较另类的典型,早就不建议使用了,但是在数据字典里还是能看到long 类型的影子。 如果在一些工作中碰到long...

3046
来自专栏calmound

操作数据表中的记录

插入记录   语法:INSERT [INTO] [(tbl_name,...)] {VALUE|VALUE} ({expr|DEFAULT},...),(......

3307
来自专栏问天丶天问

Mysql 中英文排序

1634
来自专栏Java架构师历程

Mysql常用查询语句

一查询数值型数据: SELECT * FROM tb_name WHERE sum > 100; 查询谓词:>,=,<,<>,!=,!>,!<,=>,=<

812
来自专栏互联网开发者交流社区

SQL-ROW_NUMBER() OVER函数的基本用法(源码案例)

1103

扫码关注云+社区