</p> 2、REF CURSOR游标
, J6 k2 R/ D2 V. F- v3 }: ` 动态游标,在运行的时候才能确定游标使用的查询。可以分为:
/ x% M: K( p% h2 {8 [+ E create or replace procedure TEST is: a* p' Z9 ?6 q
sqlstr varchar2(500);1 F. z$ p& q3 q& h+ _3 D* B5 A
type RefCur is ref cursor;% }, t" L, p1 S& q5 o! ^8 u; I
c1 refcur;
" V/ H3 P$ Q- Q, v8 K/ L begin2 h- H7 _+ O5 [( R% ?8 I- \8 o
sqlstr := 'select * from tab';* q: j+ f8 }$ p; F
open c1 for sqlstr;
! s, @0 m. K+ O8 c+ X7 ^- d close c1;
5 ^3 s1 D/ l4 x& B4 | end;
% k. {" W, a y* N' [. o+ ?* F 用REF CURSOR实现BULK功能/ U, g# ]* i. u/ A. r1 R! m
1. 可以加速INSERT, UPDATE, DELETE语句的执行,也就是用FORALL语句来替代循环语句。0 y6 X2 h% y% I2 q7 J0 p
2. 加速SELECT,用BULK COLLECT INTO 来替代INTO。. p& F6 A2 Q8 j; V/ o
SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;3 \0 t: c( p# x7 A
create or replace procedure REF_BULK is9 X: `7 ]6 \ M1 t2 u
/* 定义复杂类型 */
, y6 x/ z& e' M) o6 \! { type empcurtyp is ref cursor;
; d, |6 T0 z# r& }- G type idlist is table of emp.empno%type;
) t- k0 d3 H. L/ q' i type namelist is table of emp.ename%type;; W! d9 E$ }& j4 {8 W, z
type sallist is table of emp.sal%type;
% f& a. T$ S; b+ z /* 定义变量 */
8 S% e- ^ Q$ \+ P" v emp_cv empcurtyp;
* C B% K" y9 {" D" q ids idlist;- K; E1 v/ Z! m, K) c. C1 p) F
names namelist;
9 d$ S8 L- a3 X) K+ O8 r; ? sals sallist;' ~2 U' s6 }% W
row_cnt number;, m+ k ~4 c; r" z
begin
$ Y4 B. {5 p8 M open emp_cv for select empno, ename, sal from emp;0 U' N* D% Y* T+ a/ u
fetch emp_cv BULK COLLECT INTO ids, names, sals; @% @; ^ B3 Y% x
--将字段成批放入变量中,此时变量是一个集合
) s$ e& n3 ^/ a: z6 J3 l close emp_cv;
2 e; y* B1 [. }8 W. U; ~+ @, ^0 K4 z( Q for i in ids.first .. ids.last loop" F+ g) e7 A3 J3 E8 j
dbms_output.put_line(' || ids(i) || ' || names(i) ||' salary=' || sals(i));
8 m. s6 h4 O! }* s' [$ I end loop;0 Y" j, C) |$ o1 U) }. O
FORALL i IN ids.first .. ids.last
- [3 E/ o. V4 W( r8 \ insert into tab2 values (ids(i), names(i), sals(i));
7 @, \: _* b# h commit;
+ @9 X, {* u+ a- Y select count(*) into row_cnt from tab2;
! ~4 L) l2 |& D, Y dbms_output.put_line('-----------------------------------');
- B/ b- c0 [% V0 A7 I dbms_output.put_line('The row number of tab2 is ' || row_cnt);
6 A m: K3 h( w( c, q2 M
. ?' s4 c, C5 |8 D7 j end REF_BULK; |