表函数可接受查询语句或游标作为输入参数,并可输出多行数据。这篇文章主要介绍了 Oracle 中 table 函数的应用浅析, 需要的朋友可以参考下
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。
表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表。
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 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;
(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
- I 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')));
以上所述是小编给大家介绍的 Oracle 中 table 函数的应用浅析,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 PHPERZ 网站的支持!
来源: