表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表。
1. 用游标传递数据
利用游标 REF CURSOR 可将数据集(多行记录)传递到PL/SQL函数:
1
2
3
|
SELECT *
FROM TABLE (myfunction ( CURSOR ( SELECT *
FROM mytab)));
|
2. 利用两个实体化视图(或表)作为样板数据
1
2
3
4
5
6
7
8
9
10
11
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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
1
|
CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;
|
(3)定义对象类型:TYPE sales_gender_t
1
2
3
4
5
6
|
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
1
|
CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;
|
(5)定义对象类型:TYPE sales_roll_t
1
2
3
4
5
|
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
1
|
CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;
|
(7)检查一下建立的类型
1
2
3
|
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'TYPE' ;
|
4. 定义包:Create package and define REF CURSOR
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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 查询语句调用已被定义的表函数。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 函数的应用浅析,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!