对于数据量较大的插入操作可采用此种方法操作,注意:
- limit减少内存占用,如果数据量较大一次性全部加载到内存中,对PGA来说压力太大,可采用limit的方法一次加载一定数量的数据,建议值通常为1000。使用limit时注意,循环的时候如果用while cursor_name%found loop,对于最后一次fetch的数据量不足设定值1000,%found条件就会不成立。示例使用v_oid_lst.count > 0作为判断条件。
在写plsql代码块,定义数值变量时,建议采用pls_integer类型,或者simple_integer类型,区别:
oracle9i之前有binary_integer类型,和11g中引入的pls_integer数值范围相同:-2147483647~+2147483647,但pls_integer有更高的性能。两者性能均优于number类型。
Oracle中也引入了simple_integer类型,不过不能包含null值,范围:-2147483648~2147483647,性能优于pls_integer。
- 使用ref cursor。
- 使用绑定变量。
- 自定义table类型。
- Bulk collect into加载到内存中,处理完业务逻辑后forall批量插入到数据表中。
- Forall可以使用returning bulk collect into,且可使用sql%rowcount返回其更新行数。
type numbers is table of number index by binary_integer/pls_integer/simple_integer; 其作用是:
加了"index by binary_integer "后,numbers类型的下标就是自增长,numbers类型在插入元素时,不需要初始化,不需要每次extend增加一个空间。
而如果没有这句话"index by binary_integer",那就得要显示对初始化,且每插入一个元素到numbers类型的table中时,都需要先extend。
- 关于table、record、varray的详细使用,参考:http://blog.csdn.net/liangweiwei130/article/details/38223319
示例代码:
SQL Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | | declare type v_t_oid is table of ljz_all_objects.object_id%type; v_oid_lst v_t_oid; type v_t_cur_oid is ref cursor; v_cur_oid v_t_cur_oid; v_cnt simple_integer := 1000; begin open v_cur_oid for 'select object_id from ljz_all_objects where object_id>:1' using 1; fetch v_cur_oid bulk collect into v_oid_lst limit v_cnt; while v_oid_lst.count > 0 loop for i in 1 .. v_oid_lst.count loop --业务处理逻辑 v_oid_lst(i) := v_oid_lst(i) + 1; end loop; forall i in v_oid_lst.first .. v_oid_lst.last insert into ljz_test (col) values (v_oid_lst(i)); fetch v_cur_oid bulk collect into v_oid_lst limit 1000; commit; end loop; close v_cur_oid; end; |
定义record类型,多列插入:
SQL Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | | declare type v_t_r is record( object_id ljz_all_objects.object_id%type, object_name ljz_all_objects.object_name%type); v_r v_t_r; type v_t_oid is table of v_t_r index by simple_integer; v_oid_lst v_t_oid; v_oid_lst1 v_t_oid; type v_t_cur_oid is ref cursor; v_cur_oid v_t_cur_oid; v_cnt simple_integer := 1000; begin open v_cur_oid for 'select object_id,object_name from ljz_all_objects where object_id>:1' using 1; fetch v_cur_oid bulk collect into v_oid_lst limit v_cnt; while v_oid_lst.count > 0 loop for i in 1 .. v_oid_lst.count loop --业务处理逻辑 v_oid_lst(i).object_id := v_oid_lst(i).object_id + 1; end loop; forall i in v_oid_lst.first .. v_oid_lst.last insert into ljz_test (num, col) values (v_oid_lst(i).object_id, v_oid_lst(i).object_name) returning num, col bulk collect into v_oid_lst1; dbms_output.put_line('v_oid_lst1.count=' || v_oid_lst1.count || ',隐式游标rowcount:' || sql%rowcount); fetch v_cur_oid bulk collect into v_oid_lst limit 1000; commit; end loop; close v_cur_oid; end; |
批量update、delete、insert,异常捕获:
SQL Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
| | --表说明:ljz_all_objects数据来自:all_objects;test1有两个字段:col字符型最大长度10,num数值型。 declare type v_tp_rec is record( object_name varchar2(50), object_id number); type v_tp_obj is table of v_tp_rec index by simple_integer; v_objs v_tp_obj; type v_cur_tp_obj is ref cursor; v_cur_obj v_cur_tp_obj; v_lmt_cnt simple_integer := 1000; v_rn simple_integer := 10; begin open v_cur_obj for 'select object_name,object_id from ljz_all_objects where rownum<=:1 order by decode(object_id,117,300)' using v_rn; fetch v_cur_obj bulk collect into v_objs limit v_lmt_cnt; while v_objs.count > 0 loop dbms_output.put_line('v_objs.first=' || v_objs.first); for i in v_objs.first .. v_objs.last loop v_objs(i).object_id := v_objs(i).object_id + 1; end loop; begin --批量插入,test1表col字段大小为10,这里有异常 forall i in v_objs.first .. v_objs.last insert into test1 (col, num) values (v_objs(i).object_name, v_objs(i).object_id); exception --如果不对forall执行异常捕获,数据执行过程中如果出错,会全部回滚, --如果捕获异常,假如数据在执行第5条时出错,则前4条数据执行成功,第5条及其后面所有数据都不再执行。 when others then --sql%bulk_exceptions.count记录异常数量,如果没有使用save exceptions,若有异常该值为1,如下输出是1 dbms_output.put_line('sql%bulk_exceptions.count:' || sql%bulk_exceptions.count); end; --批量更新 forall i in v_objs.first .. v_objs.last update test1 set num = v_objs(i).object_id where col = v_objs(i).object_name; --对于批量更新,除了sql%rowcount几个隐式游标属性外,另具有sql%bulk_rowcount属性,用来记录第N行更新影响行数。 if sql%bulk_rowcount(2) > 0 then dbms_output.put_line('第二行更新影响行数:' || sql%bulk_rowcount(2)); end if; begin --批量删除,使用save exceptions,和之前异常捕获区别:使用save exceptions异常后可继续执行直至结束。 --新属性:sql%bulk_exceptions.count、sql%bulk_exceptions(i).error_index、sql%bulk_exceptions(i).error_code forall i in v_objs.first .. v_objs.last save exceptions delete from test1 where v_objs(i).object_id / 0 > 1; exception when others then for i in 1 .. sql%bulk_exceptions.count loop dbms_output.put_line('sql%bulk_exceptions(i).error_index:' || sql%bulk_exceptions(i) .error_index); dbms_output.put_line('sqlerrm sql%bulk_exceptions(i).error_code:' || sqlerrm(sql%bulk_exceptions(i).error_code)); end loop; end; fetch v_cur_obj bulk collect into v_objs limit v_lmt_cnt; end loop; close v_cur_obj; end; |