姿姿霸霸~~!
贵在坚持!
posts - 106,  comments - 50,  trackbacks - 0

        TABLE函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用TABLE函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表.
        TABLE函数(它是9i中的新特性),实际上是将一个存储在内存中的对象(以流的方式存储的)结构化以后,使这个对象能以表的方式查询。他对流对象的结构化转换就决定的它的效率大大逊于对普通表和临时表的查询效率.
        TABLE函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称

1. 用游标传递数据
利用游标 REF CURSOR 可将数据集(多行记录)传递到PL/SQL函数:

1SELECT *
2  FROM TABLE (myfunction (CURSOR (SELECT *
3                                    FROM mytab)));

2.利用两个实体化视图(或表)作为样板数据
CREATE MATERIALIZED VIEW sum_sales_country_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT   SUBSTR (s.calendar_month_desc, 14YEAR, 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, 14), c.country_id;

CREATE MATERIALIZED VIEW sum_es_gend_mv
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT   SUBSTR (s.calendar_month_desc, 14YEAR,
         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, 14= '2000'
GROUP BY SUBSTR (s.calendar_month_desc, 14),
         s.calendar_month_desc,
         c.cust_gender;

3.定义对象类型和基于对象类型的表类型
定义对象类型并且为进一步引用做好准备
(1)定义对象类型:
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 (NULLNULLNULL);
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')));


 

posted on 2010-12-19 22:53 xrzp 阅读(1651) 评论(1)  编辑  收藏 所属分类: oracle-基础

FeedBack:
# re: 什么是table函数(收集)
2012-11-29 10:57 | Jcat
也就是说,我好不容易达到了你2年前的水平  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航:
 

<2012年11月>
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

常用链接

留言簿(4)

随笔分类

随笔档案

好友的blog

搜索

  •  

积分与排名

  • 积分 - 115055
  • 排名 - 505

最新评论

阅读排行榜

评论排行榜