Oracle table() 表函数

清淑1654

表函数可接受查询语句或游标作为输入参数，并可输出多行数据。该函数可以平行执行，并可持续输出数据流，被称作管道式输出。应用表函数可将数据转换分阶段处理，并省去中间结果的存储和缓冲表。 　　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_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')));
　　
　　例子：
　　首先你要创建一个type类存储结果
create or replace type type_jax_varc2tab is table of varchar2(2000)


然后创建一个如下的function来返回结果
CREATE OR REPLACE FUNCTION f_jax_returntab(p_str IN VARCHAR2
) RETURN type_jax_varc2tab IS
  /******************************************************************
  Ver1.0 Created by jaxzhang on 2011-01-13

  create or replace type type_jax_varc2tab is table of varchar2(2000);
  测试用例:SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS'));
  ******************************************************************/
  v_numtab type_jax_varc2tab := type_jax_varc2tab(); --返回内存表
BEGIN 

  FOR rec IN (select rownum r1,table_name from user_tables where table_name like p_str||'%' ) LOOP
  v_numtab.EXTEND;
  v_numtab(rec.r1) := rec.table_name;
  END LOOP;

  RETURN v_numtab;
EXCEPTION
  WHEN OTHERS THEN
  v_numtab.DELETE;
END;


查询样式如下

SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS'));