oracle 利用bulk collect into 批量insert

以下為9i中實現的過程declare<br /> type t_rn is table of tmp.rn%type index by binary_integer;<br /> type t_ran is table of tmp.num%type index by binary_integer;<br /> rn t_rn;<br /> ran t_ran;<br /> cursor cur_num is<br /> select rownum, trunc(dbms_random.value,2) * 100<br /> from dual<br /> connect by rownum <= 100000;<br />begin<br /> dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));<br /> open cur_num;<br /> loop<br /> fetch cur_num bulk collect<br /> into rn, ran limit 5000;<br /> for i in 1 .. rn.count loop<br /> insert into tmp values (rn(i), ran(i));<br /> commit;<br /> end loop;<br /> exit when cur_num%notfound;<br /> end loop;<br /> close cur_num;<br /> dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));<br />end;<br />

還有10g實現的過程