Oracle中table函数的使用详解-优快云博客

本文介绍如何使用表函数处理多行数据，包括通过游标传递数据、利用实体化视图作为样板数据、定义对象类型和表类型等步骤。此外，还详细讲解了如何创建包和定义REFCURSOR，以及定义不同类型的表函数，并展示了具体的调用示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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 SELECTSUBSTR (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
SELECTSUBSTR (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 ( YEARVARCHAR2 (4), countryCHAR (2), sum_amount_soldNUMBER ); （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 ( YEARVARCHAR2 (4), country_idCHAR (2), cust_genderCHAR (1), sum_amount_soldNUMBER ); （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_descVARCHAR2 (20), country_idCHAR (2), sum_amount_soldNUMBER ); （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 ( YEARVARCHAR (4), countryCHAR (2), sum_amount_soldNUMBER );

TYPE sales_gender_t_rec IS RECORD ( YEARVARCHAR2 (4), country_idCHAR (2), cust_genderCHAR (1), sum_amount_soldNUMBER );

TYPE sales_roll_t_rec IS RECORD ( channel_descVARCHAR2 (20), country_idCHAR (2), sum_amount_soldNUMBER );

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 YEARVARCHAR (4); countryCHAR (2); sum_amount_soldNUMBER; objsetsum_sales_country_t_tab := sum_sales_country_t_tab (); iNUMBER:= 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 YEARVARCHAR (4); countryCHAR (2); sum_amount_soldNUMBER; iNUMBER:= 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_reccur%ROWTYPE; out_recsales_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 YEARVARCHAR2 (4); country_idCHAR (2); cust_genderCHAR (1); sum_amount_soldNUMBER; objsetsum_sales_gender_t_tab := sum_sales_gender_t_tab (); iNUMBER:= 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')));