Oracle数据库批量fetch批量处理

Oracle数据库批量fetch批量处理

–第三种处理方式，批量fetch批量处理
declare
t1 NUMBER(20);
t2 NUMBER(20);
CN_BATCH_SIZE constant pls_integer := 1000;
type typ_empno is table of emp.empno%type index by binary_integer;
empnos typ_empno;
type typ_name is table of emp.ename%type index by binary_integer;
enames typ_name;
type typ_job is table of emp.job%type index by binary_integer;
jobs typ_job;
type typ_mgr is table of emp.mgr%type index by binary_integer;
mgrs typ_mgr;
type typ_hiredate is table of emp.hiredate%type index by binary_integer;
hiredates typ_hiredate;
type typ_sal is table of emp.sal%type index by binary_integer;
sals typ_sal;
type typ_comm is table of emp.comm%type index by binary_integer;
comms typ_comm;
type typ_deptno is table of emp.deptno%type index by binary_integer;
deptnos typ_deptno;
type typ_rid is table of urowid index by binary_integer;
rids typ_rid;

cur_emp sys_refcursor;
vc_sql varchar(4000) := ‘select empno,ename,job,mgr,hiredate,sal,comm,deptno,rowid from emp where empno> :1’;
begin
t1 := dbms_utility.get_time;
open cur_emp for vc_sql using 1;
loop
fetch cur_emp bulk collect into empnos,enames,jobs,mgrs,hiredates,sals,comms,deptnos,rids limit CN_BATCH_SIZE;
for i in 1 … rids.count loop
sals(i) := sals(i)+1;
end loop;
forall i in 1…rids.count
insert into emp_forall(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(empnos(i),enames(i),jobs(i),mgrs(i),hiredates(i),sals(i),comms(i),deptnos(i));
exit when rids.count < CN_BATCH_SIZE;
end loop;
close cur_emp;
commit;
t2 := dbms_utility.get_time;
dbms_output.put_line('FORALL: ’ || TO_CHAR(t2 - t1)/100);
end;
/