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 

相关文章： 建站之星好吗？新手能否轻松上手建站？  微信h5制作网站有哪些,免费微信H5页面制作工具？  子杰智能建站系统｜零代码开发与AI生成SEO优化指南  建站之星CMS建站配置指南：模板选择与SEO优化技巧  如何通过宝塔面板实现本地网站访问？  英语简历制作免费网站推荐,如何将简历翻译成英文？  韩国服务器如何优化跨境访问实现高效连接？  如何快速生成可下载的建站源码工具？  威客平台建站流程解析：高效搭建教程与设计优化方案  制作网站软件推荐手机版,如何制作属于自己的手机网站app应用？  建站之星北京办公室：智能建站系统与小程序生成方案解析  免费网站制作appp,免费制作app哪个平台好？  如何使用Golang table-driven基准测试_多组数据测量函数效率  如何快速搭建高效WAP手机网站？  杭州银行网站设计制作流程,杭州银行怎么开通认证方式？  5种Android数据存储方式汇总  商务网站制作工程师,从哪几个方面把握电子商务网站主页和页面的特色设计？  详解jQuery中基本的动画方法  如何快速搭建高效可靠的建站解决方案？  测试制作网站有哪些,测试性取向的权威测试或者网站？  如何选择长沙网站建站模板？H5响应式与品牌定制哪个更优？  黑客如何通过漏洞一步步攻陷网站服务器？  linux top下的 minerd 木马清除方法  如何通过IIS搭建网站并配置访问权限？  免费的流程图制作网站有哪些,2025年教师初级职称申报网上流程？  建站之星代理如何获取技术支持？  建站之星官网登录失败？如何快速解决？  早安海报制作网站推荐大全,企业早安海报怎么每天更换？  非常酷的网站设计制作软件,酷培ai教育官方网站？  如何在阿里云虚拟机上搭建网站？步骤解析与避坑指南  车管所网站制作流程,交警当场开简易程序处罚决定书，在交警网站查询不到怎么办？  如何在阿里云ECS服务器部署织梦CMS网站？  如何通过NAT技术实现内网高效建站？  html制作网站的步骤有哪些,iapp如何添加网页？  广州网站制作的公司,现在专门做网站的公司有没有哪几家是比较好的，性价比高，模板也多的？  如何高效生成建站之星成品网站源码？  枣阳网站制作,阳新火车站打的到仙岛湖多少钱？  如何在万网ECS上快速搭建专属网站？  如何选择PHP开源工具快速搭建网站？  行程制作网站有哪些,第三方机票电子行程单怎么开？  建站主机空间推荐 高性价比配置与快速部署方案解析  官网网站制作腾讯审核要多久,联想路由器newifi官网  动图在线制作网站有哪些,滑动动图图集怎么做？  如何通过二级域名建站提升品牌影响力？  南阳网站制作公司推荐,小学电子版试卷去哪里找资源好？  专业制作网站的公司哪家好,建立一个公司网站的费用.有哪些部分,分别要多少钱？  如何高效配置香港服务器实现快速建站？  网站图片在线制作软件,怎么在图片上做链接？  网站制作怎么样才能赚钱,用自己的电脑做服务器架设网站有什么利弊，能赚钱吗？  音响网站制作视频教程,隆霸音响官方网站？