欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

oracle 管道表函数

程序员文章站 2022-06-06 10:22:39
...

表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表。

 

 


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

网管下载dl.bitscn.com

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; 
网管联盟bitsCN_com

(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; 网管网www.bitscn.com

      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;

网管u家u.bitsCN.com

   CLOSE cur;

网管bitscn_com

   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; 网管朋友网www_bitscn_net

      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;

   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))); 中国网管论坛bbs.bitsCN.com

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