oracle bulk collect into 的几种用法

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中增删改的极限速度。

比用游标一条条处理要快太多了。