Oracle forall bulk collect批量数据更新

对于数据量较大的插入操作可采用此种方法操作,注意:

  1. limit减少内存占用,如果数据量较大一次性全部加载到内存中,对PGA来说压力太大,可采用limit的方法一次加载一定数量的数据,建议值通常为1000。使用limit时注意,循环的时候如果用while cursor_name%found loop,对于最后一次fetch的数据量不足设定值1000,%found条件就会不成立。示例使用v_oid_lst.count > 0作为判断条件。
  2. 在写plsql代码块,定义数值变量时,建议采用pls_integer类型,或者simple_integer类型,区别:

    oracle9i之前有binary_integer类型,和11g中引入的pls_integer数值范围相同:-2147483647~+2147483647,但pls_integer有更高的性能。两者性能均优于number类型。

    Oracle中也引入了simple_integer类型,不过不能包含null值,范围:-2147483648~2147483647,性能优于pls_integer。

  3. 使用ref cursor。
  4. 使用绑定变量。
  5. 自定义table类型。
  6. Bulk collect into加载到内存中,处理完业务逻辑后forall批量插入到数据表中。
  7. Forall可以使用returning bulk collect into,且可使用sql%rowcount返回其更新行数。
  8. 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。

  9. 关于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_objectstest1有两个字段:col字符型最大长度10num数值型。
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
      --
批量插入,test1col字段大小为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.countsql%bulk_exceptions(i).error_indexsql%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;

posted @ 2015-08-11 17:54  mellowsmile  阅读(3251)  评论(0编辑  收藏  举报