表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表。+ G% E1 l+ t+ h% R8 E2 A) ?2 i" a' m
1. 用游标传递数据
. O$ b) K2 ]/ D! g1 O3 F 利用游标 REF CURSOR 可将数据集(多行记录)传递到PL/SQL函数:: e. @5 h4 |+ h3 e/ W# F% h9 I) d
SELECT *+ ~9 b; N9 g4 r6 H+ a3 D3 n2 z4 |
FROM TABLE (myfunction (CURSOR (SELECT *FROM mytab))); & W9 A$ R* G4 G
2. 利用两个实体化视图(或表)作为样板数据
& T. L4 q! ?% U* v9 U, c9 v CREATE MATERIALIZED VIEW sum_sales_country_mv
7 g& _. \5 s; Z) k% x BUILD IMMEDIATE4 l% M0 L) K5 d1 }3 N
REFRESH COMPLETE3 x4 p) o6 I% [! j* I* s
ENABLE QUERY REWRITE8 a+ ^% `( K6 M# d/ M$ r, k% u K
AS, u/ d( O- T9 {: t' J- L
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR, c.country_id country,) T0 x5 U, V% S* ?* q3 |" R
SUM (sum_amount_sold) sum_amount_sold- X u. v. J" E: f1 D6 ^' A
FROM sum_sales_month_mv s, customers c
/ G5 Z& X7 L; m* L WHERE s.cust_id = c.cust_id
9 G4 Z1 h/ k4 R3 j6 c AND c.country_id IN (’US’, ’UK’, ’FR’, ’ES’, ’JP’, ’AU’)! I F& |: p, ~$ \4 k# O- M
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4), c.country_id;
: Z# f/ s; m/ K _2 F3 l U" ? CREATE MATERIALIZED VIEW sum_es_gend_mv
! Z* o1 y3 f: `0 R# M5 l BUILD DEFERRED
) E+ i, z1 s( q+ d REFRESH FAST9 _4 d& O2 Q; g7 X$ h( | M& \
ENABLE QUERY REWRITE
, `& A8 q$ L d% p# \- G7 `( l AS# c6 n( ^- U2 q b& a5 r
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR,
: l2 f7 ?( {7 H, N" s7 i9 s- m s.calendar_month_desc cal_month, c.cust_gender,
3 @+ e% ^. ~1 M; Y' p a SUM (sum_amount_sold) sum_amount_sold
3 n% `& x! b9 b; D FROM sum_sales_month_mv s, customer c6 g) H ]( N$ k1 W1 X8 p" X: G
WHERE s.cust_id = c.cust_id- ?4 f) p9 u# F/ ?( Y$ n9 }
AND c.country_id = ’ES’
; X B2 x- t/ ^* O; _/ F AND sunstr (s.calendar_month_desc, 1, 4) = ’2000’
$ Y; m: m* K" |9 ^, c6 J GROUP BY SUBSTR (s.calendar_month_desc, 1, 4),
' E! y( X- \0 X1 U/ F1 G s.calendar_month_desc, s2 ] {5 j5 }& C
c.cust_gender; |