关于oracle快速删除和快速插入的方法代码

会计考友

-快速插入  
* [! Q/ h; Q$ H4 j0 J
SQL> insert /**//*+append*/ into t select * from dba_objects nologging;  
  ~6 Q" G6 R' G! j- ?1 G+ I' R* H+ x
' Q1 j# d1 B$ s3 ?$ `3 x9157 rows created.  
' v+ t" L# r$ \9 L

SQL> select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username  
2 from v$transaction a,v$session b,v$mystat c  
7 l" n- E/ @& Y1 _& _. d 3 where a.addr = b.taddr and c.statistic# = 1  
' V9 E3 j9 e+ m  x0 |* Z! C 4 /  
+ Q# z9 e* G4 b& m) p$ H
XIDUSN XIDSLOT USED_UBLK USED_UREC USERNAME  
% y* n) j' H% E" W---------- ---------- ---------- ---------- ------------------------------  
2 21 1 1 CUST  

0 }; v% T1 G+ ]6 z) `SQL> commit;  
9 s% u. x7 {  s7 h1 W0 V1 N  l
4 a* k: i+ ~, Q' a: C/ H! D# sCommit complete.  

4 z- I. L& P0 }" Y0 ^SQL> insert into t select * from dba_objects;  
& X- C: g/ x  [" J0 y
: e3 j; B. }! q* }9157 rows created.  

SQL> select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username  
2 from v$transaction a,v$session b,v$mystat c  
3 where a.addr = b.taddr and c.statistic# = 1  
7 H6 Z. G) H3 A8 U0 y 4 /  
) ?% E; d* S( G6 J( i& a$ q
XIDUSN XIDSLOT USED_UBLK USED_UREC USERNAME  
3 J7 Z! D( i, M3 N---------- ---------- ---------- ---------- ------------------------------  
1 1 13 423 CUST  
9 G5 j) e2 R, Z$ T
; ?/ w" A2 H$ e  @
快速删除  
2 J  z9 z2 T. a4 p- K+ e5 d2 Q建个存储过程,达到2000条或者更多条,提交一次.  
create or replace procedure p_delete  
as  
n number(10);  
. t- L$ {/ Y. Y3 ?! w2 @  Acursor my_cur is select * from t1;  
1 f& z/ i+ m) Q  A! v/ j/ p* Ubegin  
- Q( O- R% L5 E2 Kn:=0;  
for i in my_cur loop   
6 c+ Y4 E+ z2 z6 M0 idelete from t1 where ;  
n:=n+1;  
if (mod(n,2000))=0 then  
commit;  
end if;  
1 D2 w* q! ^" T: qend loop;  
# P% e& @; U4 yend;  
/