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

相关文章

来自专栏Hadoop数据仓库

Inlist的绑定优化(书摘备查)

 -- 建立类型 CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER; CREATE OR REPLA...

1555
来自专栏抠抠空间

MySQL之表操作

一、创建表   1、创建新表 #语法: create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名...

3187
来自专栏程序生活

MySQL的JOIN用法

数据库中的JOIN称为连接,连接的主要作用是根据两个或多个表中的列之间的关系,获取存在于不同表中的数据。连接分为三类:内连接、外连接、全连接。另外还有CROSS...

602
来自专栏技术专栏

mybatis association 多参数查询

1123
来自专栏飞扬的花生

SQL创建流水号

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

17410
来自专栏Ryan Miao

oracle创建表相关

1 --创建表 2 create table person( 3 id number primary key, 4 name varchar2(40),...

2575
来自专栏大内老A

T-SQL Enhancement in SQL Server 2005[下篇]

在第一部分中,我们讨论了APPLY和CTE这两个T-SQL Enhancement。APPLY实现了Table和TVF的Join,CTE通过创建“临时的View...

1808
来自专栏java系列博客

关于ORACLE merge into 的两个常见错误

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

走索引扫描的慢查询(r3笔记45天)

今天查看awr报告的时候,发现一条sql语句异常。 Elapsed Time (s) Executions Elapsed Time per Exec (s)...

3338
来自专栏c#开发者

递归查询

oracle -------------------------------------------------------------------------...

2464

扫码关注云+社区