bulk collect 和 forall 联合应用写起来显得有些啰嗦, 不过为了速度, 多写两句又何妨
建立两个临时表
- create table T_TEST
- (TESTID NUMBER(19) not null,
- TESTNAME VARCHAR2(512),
- TESTTYPE VARCHAR2(512),
- TESTLEVEL VARCHAR2(512),
- ADDFLAG VARCHAR2(512)
- );
- create table T_TEST2
- (
- TESTID NUMBER(19) not null,
- TESTNAME VARCHAR2(512),
- TESTTYPE VARCHAR2(512),
- TESTLEVEL VARCHAR2(512),
- );
存储过程使用示例 1:
- create or replace procedure FAST_UP_TEST is
- TYPE TARR is table of T_TEST%ROWTYPE;
- vvrr TARR;
- CURSOR curr is select * from T_TEST where ADDFLAG='add';
- begin
- open curr;
- loop
- fetch curr bulk collect into vvrr limit 1000;
- dbms_output.put_line('增加:'||vvrr.count);
- forall i in 1..vvrr.count
- update T_TEST2
- set TESTTYPE=vvrr(i).TESTTYPE,TESTLEVEL=vvrr(i).TESTLEVEL
- where TESTNAME=vvrr(i).TESTNAME and TESTID=vvrr(i).TESTID;
- commit;
- exit when curr%NOTFOUND;
- end loop;
- close curr;
- end;
换一个方式使用示例 (注意游标返回类型变化):
- create or replace procedure FAST_DEL_TEST is
- TYPE TARR is table of T_TEST.TESTID%TYPE;
- vvrr TARR;
- CURSOR curr is select TESTID from T_TEST where ADDFLAG='delete';
- begin
- dbms_output.enable(90000);
- open curr;
- loop
- fetch curr bulk collect into vvrr limit 1000;
- dbms_output.put_line('delete T_TEST'||vvrr.count);
- forall i in 1..vvrr.count
- delete T_TEST2
- where TESTID=vvrr(i);
- commit;
- exit when curr%NOTFOUND;
- end loop;
- close curr;
- end;
多参数返回的另一种用法和动态 sql 运行办法
- create or replace procedure FAST_AD_TEST (
- tabtest in VARCHAR2) is
- TYPE TARR_ID is table of T_TEST.TESTID%TYPE;
- TYPE TARR_NM is table of T_TEST.TESTNAME%TYPE;
- TYPE TARR_TP is table of T_TEST.TESTTYPE%TYPE;
- vvid TARR_ID;
- vvnm TARR_NM;
- vvtp TARR_TP;
- CURSOR curr is select TESTID,TESTNAME,TESTTYPE from T_TEST where ADDFLAG='add';
- CURSOR curd is select TESTID from T_TEST where ADDFLAG='delete';
- begin
- execute immediate 'truncate table'||tabtest;
-- forall 只能写一条 sql...
- open curr;
- loop
fetch curr bulk collect into vvid,vvnm,vvtp limit 1000;
- forall i in 1..vvid.count
- insert into T_TEST2(TESTID,TESTNAME,TESTTYPE) values (vvid(i),vvnm(i),vvtp(i));
- commit;
- exit when curr%NOTFOUND;
- end loop;
- close curr;
-- 动态传入表名和参数
- open curd;
- loop
- fetch curd bulk collect into vvid limit 1000;
- forall i in 1..vvid.count
- execute immediate 'delete'||tabtest||'where TESTID=:tabid' using in vvid(i);
- commit;
- exit when curd%NOTFOUND;
- end loop;
- close curd;
- end;
bulk collect 和 forall 的联合应用
基本上压榨出了 oracle 中增删改的极限速度.
比用游标一条条处理要快太多了.
来源: http://www.bubuko.com/infodetail-2606162.html