一、声明变量类型的几种方式:
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;