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 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

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

以上所述是小编给大家介绍的Oracle 中 table 函数的应用浅析，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

# oracle  # table函数  # Oracle Table Demo语句应用介绍  # oracle中变长数组varray  # 嵌套表  # 集合使用方法  # sql – Oracle中匿名TABLE/VARRAY类型示例详解  # 并可  # 可将  # 小编  # 在此  # 已被  # 给大家  # 所述  # 给我留言  # 感谢大家  # 可接受  # 分阶段  # 行数  # 疑问请  # 有任何  # 为进一步  # 被称作  # FR  # UK  # GROUP  # ES 

相关文章： 如何通过PHP快速构建高效问答网站功能？  学校为何禁止电信移动建设网站？  如何快速完成中国万网建站详细流程？  建站之星导航配置指南：自助建站与SEO优化全解析  想学网站制作怎么学,建立一个网站要花费多少？  如何在Windows服务器上快速搭建网站？  如何快速配置高效服务器建站软件？  湖北网站制作公司有哪些,湖北清能集团官网？  制作网站软件推荐手机版,如何制作属于自己的手机网站app应用？  建站之星伪静态规则如何设置？  制作旅游网站html,怎样注册旅游网站？  建站之星后台密码遗忘？如何快速找回？  广州建站公司哪家好？十大优质服务商推荐  如何通过远程VPS快速搭建个人网站？  青浦网站制作公司有哪些,苹果官网发货地是哪里？  如何通过山东自助建站平台快速注册域名？  西安专业网站制作公司有哪些,陕西省建行官方网站？  红河网站制作公司,红河事业单位身份证如何上传？  简单实现Android文件上传  西安市网站制作公司,哪个相亲网站比较好?西安比较好的相亲网站？  定制建站流程步骤详解：一站式方案设计与开发指南  如何在阿里云ECS服务器部署织梦CMS网站？  如何通过建站之星自助学习解决操作问题？  建站之星会员如何解锁更多建站功能？  手机网站制作与建设方案,手机网站如何建设？  建站主机类型有哪些？如何正确选型  公司网站设计制作厂家,怎么创建自己的一个网站？  建站之星备案是否影响网站上线时间？  怎么制作一个起泡网,水泡粪全漏粪育肥舍冬季氨气超过25ppm，可以有哪些措施降低舍内氨气水平？  Avalonia如何实现跨窗口通信 Avalonia窗口间数据传递  如何配置WinSCP新建站点的密钥验证步骤？  实例解析angularjs的filter过滤器  惠州网站建设制作推广,惠州市华视达文化传媒有限公司怎么样？  如何零基础在云服务器搭建WordPress站点？  小米网站链接制作教程,请问miui新增网页链接调用服务有什么用啊？  建站主机服务器选购指南：轻量应用与VPS配置解析  建站之星安装失败：服务器环境不兼容？  高性能网站服务器配置指南：安全稳定与高效建站核心方案  香港服务器网站卡顿？如何解决网络延迟与负载问题？  公司网站制作需要多少钱,找人做公司网站需要多少钱？  历史网站制作软件,华为如何找回被删除的网站？  网站制作哪家好,cc、.co、.cm哪个域名更适合做网站？  网站制作和推广的区别,想自己建立一个网站做推广，有什么快捷方法马上做好一个网站？  ,制作一个手机app网站要多少钱？  成都响应式网站开发,dw怎么把手机适应页面变成网页？  公司网站建设制作费用,想建设一个属于自己的企业网站，该如何去做？  如何在建站之星绑定自定义域名？  香港服务器建站指南：外贸独立站搭建与跨境电商配置流程  如何用IIS7快速搭建并优化网站站点？  怀化网站制作公司,怀化新生儿上户网上办理流程？