日期:2014-05-16  浏览次数:20451 次

【转】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_so