</p> 2、REF CURSOR游标7 R h7 n( K: Y
动态游标,在运行的时候才能确定游标使用的查询。可以分为:: J& `3 _9 r( o6 s( N
create or replace procedure TEST is
8 Q7 p# I9 a) V, P sqlstr varchar2(500);
: Y+ {# }. G, U+ f type RefCur is ref cursor;
- U6 P9 D5 C$ a* e5 f c1 refcur;0 p, C: U, J) |" L
begin
( Y3 t8 }. Z$ w sqlstr := 'select * from tab';0 v% }" l0 \7 h1 U
open c1 for sqlstr;5 q. V- z, ^/ z! k
close c1;
, ?8 Q2 C Z' b end;
( g1 T% h8 H, K4 o/ ?. m8 C 用REF CURSOR实现BULK功能7 P; H$ D* G! {) n/ f
1. 可以加速INSERT, UPDATE, DELETE语句的执行,也就是用FORALL语句来替代循环语句。! ^( K4 }2 U% i0 C0 o$ B1 t0 v/ z
2. 加速SELECT,用BULK COLLECT INTO 来替代INTO。
3 ]) K% {2 F& |/ X; o3 l( M& X SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;6 A, ^) j$ m# d% U$ K% @+ V
create or replace procedure REF_BULK is
9 ^6 \% n+ ]1 X /* 定义复杂类型 */
G9 ~: c8 s" H, o' |; E6 q type empcurtyp is ref cursor;+ q8 j7 M6 G0 `2 B* i3 P( C
type idlist is table of emp.empno%type;# }. E a. i6 _. I/ u, E2 e
type namelist is table of emp.ename%type;
- m4 K( G8 J* W, w type sallist is table of emp.sal%type;
& k/ T/ v- X) m, |: m3 a4 Z5 {/ E /* 定义变量 */
! O& |+ o& W! e: m emp_cv empcurtyp;
) l# Z* ?! ]3 u( ~8 H; X ids idlist;* N, Q$ a. {6 q' B& Y+ {4 O
names namelist;
- T! r* B5 f/ i' t, X sals sallist;
/ B4 T- Q, F A% ?+ a [ row_cnt number;& T' a) ?8 q2 a. ] c
begin7 g1 _, C% ~' o8 ^' `) p
open emp_cv for select empno, ename, sal from emp;
+ d- W1 M! y7 Z0 ^( X, V: P fetch emp_cv BULK COLLECT INTO ids, names, sals;
3 O& }# Y; W3 } m6 E --将字段成批放入变量中,此时变量是一个集合
6 r8 d3 o, _6 V close emp_cv;
; ~: d8 c# T8 p* [. l for i in ids.first .. ids.last loop
/ q3 M+ N( h8 H2 D dbms_output.put_line(' || ids(i) || ' || names(i) ||' salary=' || sals(i));. `5 O/ ^: F3 ?" O' v; x+ c
end loop;- ^! O, n1 g. ~; Z; g0 Z0 ~
FORALL i IN ids.first .. ids.last# E5 x( F3 H3 V h2 V
insert into tab2 values (ids(i), names(i), sals(i));; M' z8 H& y& r/ o6 `' n
commit;
2 b4 ~( H9 w; d$ I3 [9 e select count(*) into row_cnt from tab2;0 u; G L2 |, e+ a9 h, p
dbms_output.put_line('-----------------------------------');9 |9 `9 o2 R3 Y5 Y
dbms_output.put_line('The row number of tab2 is ' || row_cnt);( @, d" }6 Z* r" X
! L; U' C7 D3 E2 Y% q% c" f
end REF_BULK; |