ORACLE批量更新大数据量操作bulk collect与forall

一、声明变量类型的几种方式：

1.常用基本数据类型变量的声明与赋值

I_love_China varchar2(20) :='万岁';
I_year date :=sysdate;
I_flag boolean :=true;
--number(p,s),p为总位数，s为小数位数，p:1-38,s:-84-127
I_number number(10,2) :=2.23;
I_age integer :=20;	

2.%TYPE的声明方式

--规则：变量名 表名.字段%TYPE  (表示声明的该字段类型与my_order_info中的字段order_no为同一类型)
var_order_no my_order_info.order_no%TYPE;

3.%ROWTYPE

--规则：变量名 表名%ROWTYPE  （表示声明的该字段的类型与my_order_info中的一条记录的类型相同）
var_order_info my_order_info%ROWTYPE;

4.扩充几种变量的声明方式（为下面批处理做准备）

--PLS_INTEGER精度范围在-2^31~2^31，超时精度范围会抛异常，但在算数运算时其速度快，常用作计数器
I_count PLS_INTEGER :=0;
--声明一个集合类型的变量，该集合中的每个元素的类型与表my_order_info中的字段order_no是同一类型
TYPE type_order_no IS TABLE OF my_order_info.order_no%TYPE;
--使用时实例化
new_order_no      type_order_no;

二、bulk collect与forall

1.前提：表中数据量大，要更新某个字段的值，需要更新的数据量也很大，这时可以考虑使用批量提取游标，批量更新的操作。

至于bulk collect和forall in提高性能的原因可参考：https://www.jianshu.com/p/4f06b943ff73

涉及使用到的关键字及语法如下：

--语法规则：从游标cursorXX批量提取xxx条记录放入集合变量col1、col2中
--效率：比循环逐条从游标中取数的效率要高
fetch cursorXX bulk collect into col1, col2 limit xxx

--批绑定，相当于同时执行count个select、update、delete的操作，对性能有显著提高
forall i in 1 .. new_order_no.count
	   update my_order_info set xx=xx where xx=xx;

2.示例使用

declare
  
  --声明一个集合类型的变量，该集合中的每个元素的类型与表my_order_info中的字段order_no是同一类型
  TYPE type_order_no IS TABLE OF my_order_info.order_no%TYPE;
  TYPE type_order_channel IS TABLE OF my_order_info.order_channel%TYPE;
  --使用前先实例化
  new_order_no      type_order_no;
  new_order_channel type_order_channel;
  --定义计数器
  v_counter PLS_INTEGER := 0;
  --定义游标
  cursor otherChannel is
    select o.order_no, o.order_channel
      from my_order_info o
     where (o.order_channel in ('5', '6', '9', '10') or
           (o.order_channel in ('1', '2', '3', '4') and
           (lottery_flag != '1' or lottery_flag is null) and
           (present_flag != '1' or present_flag is null)));
begin
  open otherChannel;
  loop
	--每次从游标中提取20000条数据
    fetch otherChannel bulk collect
      into new_order_no, new_order_channel limit 20000;
	--循环到最后一次时，防止游标中的记录数不满足20000条而提取不出来
    exit when new_order_no.count = 0;
	--批量更新操作
    forall i in 1 .. new_order_no.count
      update my_order_info oi
         set oi.order_source = new_order_channel(i)
       where oi.order_no = new_order_no(i);
    commit;
    v_counter := v_counter + 1;
    DBMS_OUTPUT.PUT_LINE('提交了' || v_counter || '次');
  end loop;
  close otherChannel;
end;