表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表。3 t+ b" T8 ]; P3 A
1. 用游标传递数据5 g( V Y$ n; v( N4 v
利用游标 REF CURSOR 可将数据集(多行记录)传递到PL/SQL函数:
9 W; A6 p8 T/ H3 w* I8 G SELECT *4 Z3 D7 c9 b/ s& V
FROM TABLE (myfunction (CURSOR (SELECT *FROM mytab)));
6 g& T0 g/ V/ | 2. 利用两个实体化视图(或表)作为样板数据; j/ b2 W- E, @* k4 W
CREATE MATERIALIZED VIEW sum_sales_country_mv" g& `6 I R* D* a7 t
BUILD IMMEDIATE
* S4 R+ G$ _/ h+ n x REFRESH COMPLETE
6 ?! x) h( @6 J5 q; x# v5 M$ d ENABLE QUERY REWRITE- B' j/ z; @: c" s( F8 ^( R
AS2 k- Z3 e' j, l& h2 Q
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR, c.country_id country,
, V) ` K; ^5 z" r7 q SUM (sum_amount_sold) sum_amount_sold
/ t& ^7 W1 G( j& X. { C" i FROM sum_sales_month_mv s, customers c
5 @ C5 M/ ~, ?1 C WHERE s.cust_id = c.cust_id
$ V8 Y- Q- f/ E4 D1 O9 j" M AND c.country_id IN (’US’, ’UK’, ’FR’, ’ES’, ’JP’, ’AU’)9 {4 Q# g0 N# w: ^4 Y
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4), c.country_id;
J3 ]; N: I6 M4 h$ Y" f CREATE MATERIALIZED VIEW sum_es_gend_mv
! e. h0 Y0 Q$ w9 f# O+ N BUILD DEFERRED
' e5 Y- y: _2 H5 g9 I3 _ REFRESH FAST' k: G" B, K, k' k" T6 D/ r
ENABLE QUERY REWRITE+ ^; t" d o$ H* ~/ P/ t' a: l
AS( V# s. w0 o( m# \5 E
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR,) @) h7 \2 P) T* I: X" q4 S
s.calendar_month_desc cal_month, c.cust_gender,6 s6 s/ j( V9 _* B3 H$ p" W9 Y
SUM (sum_amount_sold) sum_amount_sold6 X) P, N# T+ {
FROM sum_sales_month_mv s, customer c
/ z+ V1 @1 a7 N3 ]3 c4 ?0 i WHERE s.cust_id = c.cust_id' D/ y8 o( L6 r( ?
AND c.country_id = ’ES’
9 |" P* n W( k- ?+ S- w1 L' |4 r AND sunstr (s.calendar_month_desc, 1, 4) = ’2000’
/ Z5 S6 l7 N+ t0 @6 ] GROUP BY SUBSTR (s.calendar_month_desc, 1, 4),
) X" f: z+ A7 t" B' ~ F s.calendar_month_desc,! k" }% p/ z! r- R
c.cust_gender; |