oracle 自带table,Oracle中table函数的使用详解

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

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

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 CURSORCREATE 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’)));