13. 批绑定
13.1 本地批绑定
问题产生的原因：由于SQL引擎和PLSQL引擎来回切换而造成的上下切换而引发性能开销
批绑定：在SQL语句中为PLSQL变量赋值，而一次性将整个集合绑定，不是通过游标循环的方式，从而减少数据往返的次数。
用了批绑定之后，在SQL引擎和PLSQL引擎之间的上下文切换次数会减少，提高系统的性能
案例：
declare
type region_rec is record(region_id number(4),region_name varchar2(10)); --定义一个记录类型，region
type region_tb1 is table of region_rec index by binary_integer; --定义一个index-by表类型
region_resc region_tb1; --定义一个index-by变量 region_resc
ret_code number;
ret_errmsg varchar2(200);
procedure load_regions(region_resc in region_tb1,retcd out number,errmsg out varchar2) is
begin
delete from region_tab;
commit;
for i in region_resc.first..region_resc.last loop
insert into region_tab values(region_resc(i).region_id,region_resc(i).region_name);
end loop;
commit;
exception when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
begin
for i in 1..5 loop
region_resc(i).region_id:=i;
region_resc(i).region_name:=‘region‘||i;
end loop;
load_regions(region_resc,ret_code,ret_errmsg);
exception when others then
dbms_output.put_line(ret_errmsg);
end;
测试：
select * from region_tab;
将上面的程序改写为本地批绑定的方式：
declare
type region_id_tb1 is table of number index by binary_integer;
type region_name_tb1 is table of varchar2(10) index by binary_integer;
region_ids region_id_tb1;
region_names region_name_tb1;
ret_code number;
ret_errmsg varchar2(200);
procedure load_regions(region_ids in region_id_tb1,region_names in region_name_tb1,retcd out number,errmsg out varchar2) is
begin
delete from region_tab;
commit;
forall i in region_ids.first..region_ids.last insert into region_tab values(region_ids(i),region_names(i));
--forall不是循环，因为没有loop,是一种循环算法的替代，也不能将forall作为游标来使用
commit;
exception when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
begin
for i in 1..6 loop
region_ids(i):=i;
region_names(i):=‘region‘||i;
load_regions(region_ids,region_names,ret_code,ret_errmsg);
end loop;
exception when others then
dbms_output.put_line(ret_errmsg);
end;
测试forall语句的性能
(1) 将region_tab列的长度改长
SQL> alter table region_tab modify region_id number(8);
Table altered.
SQL> alter table region_tab modify region_name varchar2(100);
Table altered.
(2) 压力测试
declare
type region_id_tb1 is table of number index by binary_integer;
type region_name_tb1 is table of varchar2(100) index by binary_integer;
region_ids region_id_tb1;
region_names region_name_tb1;
ret_code number;
ret_errmsg varchar2(200);
time1 number;
time2 number;
time3 number;
procedure load_regions_bulk(region_ids in region_id_tb1,region_names in region_name_tb1,retcd out number,errmsg out varchar2) is
begin
delete from region_tab;
commit;
forall i in 1..1000000 insert into region_tab values(region_ids(i),region_names(i));
--forall不是循环，因为没有loop,是一种循环算法的替代，也不能将forall作为游标来使用
commit;
exception when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
procedure load_regions(region_ids in region_id_tb1,region_names in region_name_tb1,retcd out number,errmsg out varchar2) is
begin
delete from region_tab;
commit;
for i in 1..1000000 loop
insert into region_tab values(region_ids(i),region_names(i));
end loop;
commit;
exception when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
begin
for i in 1..1000000 loop
region_ids(i):=i;
region_names(i):=‘region‘||i;
end loop;
time1:=dbms_utility.get_time;
load_regions(region_ids,region_names,ret_code,ret_errmsg);
time2:=dbms_utility.get_time;
load_regions_bulk(region_ids,region_names,ret_code,ret_errmsg);
time3:=dbms_utility.get_time;
dbms_output.put_line(‘Time without bulk bind ‘||to_char((time2-time1)/100));
dbms_output.put_line(‘Time with bulk bind ‘||to_char((time3-time2)/100));
exception when others then
dbms_output.put_line(ret_errmsg);
end;
Time without bulk bind 138.31
Time with bulk bind 54.78
结论：
用本地批绑定，性能有大幅提升
注意：
A. 传入的参数如果是index－by表，那么index-by表必须是紧密的
B. 传入的参数必须是index-by,嵌套表，数组等必须是有下标PLSQL变量才可以实现批绑定
##########################################################################################
13.2 批绑定的异常处理
(1) 传统的异常处理的方法
declare
begin
for i in ... loop
insert into .... --执行成功
insert into .... --执行失败，跳转到exception处理
insert into .... --此句就得不到执行
end loop;
exception .....
end;
(1) save exceptions 方法捕获到错误的行为而程序不会终止，下面的数据还是可以被插入
save exceptions属性的值保存在一个隐式游标的属性中
sql%bulk_exceptions中保存错误的行，只有产生错误的这条数据可以被回滚，一旦执行成功，前面提交的数据不受影响，后面的继续执行，依赖上面forall语句继续处理
error_index给出forall语句失败的索引或者失败的行号 sql%bulk_exceptions(i).error_index
error_code给出forall语句失败的SQLCODE，引用方式 sqlerrm(-sql%bulk_exceptions(i).error_code);
引入一个错误：ORA－24381
[oracle@oracle254 ~]$ oerr ORA 24381
24381, 00000, "error(s) in array DML"
// *Cause: One or more rows failed in the DML.
// *Action: Refer to the error stack in the error handle.
把region_tab表的数据清空
SQL> truncate table region_tab;
Table truncated.
SQL> alter table region_tab modify region_id number(4);
Table altered.
SQL> alter table region_tab modify region_name varchar2(10);
Table altered.
declare
type region_id_tb1 is table of number index by binary_integer;
type region_name_tb1 is table of varchar2(100) index by binary_integer;
region_ids region_id_tb1;
region_names region_name_tb1;
ret_code number;
ret_errmsg varchar2(200);
procedure load_regions_bulk(region_ids in region_id_tb1,region_names in region_name_tb1,retcd out number, errmsg out varchar2) is
bulk_bind exception;
pragma exception_init(bulk_bind,-24381);
begin
delete from region_tab;
commit;
forall i in region_ids.first..region_ids.last save exceptions insert into region_tab values(region_ids(i),region_names(i));
commit;
retcd:=0;
errmsg:=‘successful!‘;
exception when bulk_bind then
for i in 1..sql%bulk_exceptions.count loop
dbms_output.put_line(to_char(sql%bulk_exceptions(i).error_index));
dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(i).error_code));
end loop;
retcd:=sqlcode;
errmsg:=sqlerrm;
when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
begin
for i in 1..10 loop
region_ids(i):=i;
region_names(i):=‘region‘||i;
end loop;
region_names(3):=‘very very very very very very very very very very very long‘;
region_names(6):=‘very very very very very very very very very very very very short‘;
load_regions_bulk(region_ids,region_names,ret_code,ret_errmsg);
commit;
exception when others then
dbms_output.put_line(ret_errmsg);
end;
输出：
3
ORA-12899: value too large for column (actual: , maximum: )
6
ORA-12899: value too large for column (actual: , maximum: )
SQL> select * from region_tab;
REGION_ID REGION_NAM
---------- ----------
1 region1
2 region2
4 region4
5 region5
7 region7
8 region8
9 region9
10 region10
8 rows selected.
去掉save exceptions，程序发生异常就不会再执行下去
forall语句的属性：
%found
%notfound
%rowcount --批绑定中，SQL语句全部执行完之后累计处理成功的行数
%bulk_rowcount --执行完一次批绑定之后的行数，使用的时候用下标来取值，例如：bulk_rowcount(i)
declare
type region_id_tb1 is table of number index by binary_integer;
type region_name_tb1 is table of varchar2(100) index by binary_integer;
region_ids region_id_tb1;
region_names region_name_tb1;
ret_code number;
ret_errmsg varchar2(200);
procedure load_regions_bulk(region_ids in region_id_tb1,region_names in region_name_tb1,retcd out number, errmsg out varchar2) is
bulk_bind exception;
pragma exception_init(bulk_bind,-24381);
begin
delete from region_tab;
commit;
forall i in region_ids.first..region_ids.last save exceptions insert into region_tab values(region_ids(i),region_names(i));
commit;
retcd:=0;
errmsg:=‘successful!‘;
exception when bulk_bind then
for i in 1..sql%bulk_exceptions.count loop
dbms_output.put_line(to_char(sql%bulk_exceptions(i).error_index));
dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(i).error_code));
end loop;
for i in 1..region_ids.count loop
if sql%bulk_rowcount(i)>0 then
dbms_output.put_line(to_char(sql%bulk_rowcount(i)));
elsif sql%bulk_rowcount(i)=0 then
dbms_output.put_line(‘NO ROWS‘);
end if;
end loop;
dbms_output.put_line(to_char(sql%rowcount));
retcd:=sqlcode;
errmsg:=sqlerrm;
dbms_output.put_line(sqlerrm);
when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
dbms_output.put_line(sqlerrm);
end;
begin
for i in 1..10 loop
region_ids(i):=i;
region_names(i):=‘region‘||i;
end loop;
region_names(3):=‘very very very very very very very very very very very long‘;
region_names(6):=‘very very very very very very very very very very very very short‘;
load_regions_bulk(region_ids,region_names,ret_code,ret_errmsg);
commit;
exception when others then
dbms_output.put_line(sqlerrm);
end;
3
ORA-12899: value too large for column (actual: , maximum: )
6
ORA-12899: value too large for column (actual: , maximum: )
1
1
NO ROWS --第三行出现错误
1
1
NO ROWS
1
1
1
1
8 --总共处理成功了多少行
##########################################################################################
13.3 本地批查询
可以用 bulk collect into collection_name进行批查询
collection_name可以是index-by,嵌套表，数据
(1) select into
之前写过的程序：
create or replace
procedure update_dyn_global(retcd out number,errmsg out varchar2)
authid current_user is
cursor csr_region is select region_name from region_tab;
begin
for idx in csr_region loop
update_dyn_all_table(idx.region_name,retcd,errmsg);
if retcd<>0 then
exit;
end if;
end loop;
retcd:=0;
errmsg:=‘successful!‘;
exception when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
改写用本地批查询的方式
create or replace
procedure update_dyn_global2(retcd out number,errmsg out varchar2) authid current_user is
type nametb1 is table of region_tab.region_name%type;
region_names nametb1;
begin
select region_name bulk collect into region_names from region_tab; --用本地批查询的方式取代游标
for idx in region_names.first..region_names.last loop
update_dyn_all_table(region_names(idx),retcd,errmsg);
if retcd<>0 then
exit;
end if;
end loop;
retcd:=0;
errmsg:=‘successful!‘;
exception when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
测试：
declare
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
update_dyn_global2(v_sqlcode,v_sqlerrm);
dbms_output.put_line(v_sqlerrm);
end;
(2) fetch语句使用批查询 －－在游标中去多行数据的时候
create or replace procedure update_dyn_global3(retcd out number,errmsg out varchar2) authid current_user is
type nametb1 is table of region_tab.region_name%type;
region_names nametb1;
cursor csr_region is select region_name from region_tab;
begin
open csr_region;
fetch csr_region bulk collect into region_names; --fetch了N行到集合中
for idx in region_names.first..region_names.last loop
dbms_output.put_line(region_names(idx));
end loop;
close csr_region;
retcd:=0;
errmsg:=‘successful!‘;
exception when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
end;
测试：
declare
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
update_dyn_global3(v_sqlcode,v_sqlerrm);
dbms_output.put_line(v_sqlerrm);
end;
(3) 使用returning into语句使用批查询 －－ update 语句中以批的方式返回新的修改值
declare
type region_id_tb1 is table of number index by binary_integer;
type region_name_tb1 is table of varchar2(100) index by binary_integer;
region_ids region_id_tb1;
region_names region_name_tb1;
ret_code number;
ret_errmsg varchar2(200);
out_region_names region_name_tb1;
procedure load_regions_bulk_bind(region_ids in region_id_tb1,region_names in region_name_tb1,retcd out number, errmsg out varchar2) is
bulk_bind_excep exception;
pragma exception_init(bulk_bind_excep,-24381);
begin
delete from region_tab;
commit;
forall i in region_ids.first..region_ids.last save exceptions insert into region_tab values(region_ids(i),region_names(i));
commit;
retcd:=0;
errmsg:=‘successful!‘;
exception when bulk_bind_excep then
for i in 1..sql%bulk_exceptions.count loop
dbms_output.put_line(to_char(sql%bulk_exceptions(i).error_index));
dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(i).error_code));
end loop;
for i in 1..region_ids.count loop
if sql%bulk_rowcount(i)>0 then
dbms_output.put_line(to_char(sql%bulk_rowcount(i)));
elsif sql%bulk_rowcount(i)=0 then
dbms_output.put_line(‘NO ROWS‘);
end if;
end loop;
dbms_output.put_line(to_char(sql%rowcount));
retcd:=sqlcode;
errmsg:=sqlerrm;
dbms_output.put_line(sqlerrm);
when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
dbms_output.put_line(sqlerrm);
end;
begin
for i in 1..10 loop
region_ids(i):=i;
region_names(i):=‘region‘||i;
end loop;
load_regions_bulk_bind(region_ids,region_names,ret_code,ret_errmsg);
forall i in 1..10 save exceptions update region_tab set region_name=‘N_‘||region_name where region_id=region_ids(i) returning region_name bulk collect into out_region_names;
--将update操作的新值用retunrning子句返回，返回的方式以批的方式返回，以集合为载体
commit;
for idx in out_region_names.first..out_region_names.last loop
dbms_output.put_line(out_region_names(idx));
end loop;
exception when others then
dbms_output.put_line(sqlerrm);
end;
N_region1
N_region2
N_region3
N_region4
N_region5
N_region6
N_region7
N_region8
N_region9
N_region10
(4) 在异常处理部分得到被拒绝的数据：
declare
type region_id_tb1 is table of number index by binary_integer;
type region_name_tb1 is table of varchar2(100) index by binary_integer;
region_ids region_id_tb1;
region_names region_name_tb1;
ret_code number;
ret_errmsg varchar2(200);
out_region_names region_name_tb1;
procedure load_regions_bulk_bind(region_ids in region_id_tb1,region_names in region_name_tb1,retcd out number, errmsg out varchar2) is
bulk_bind_excep exception;
pragma exception_init(bulk_bind_excep,-24381);
begin
delete from region_tab;
commit;
forall i in region_ids.first..region_ids.last save exceptions insert into region_tab values(region_ids(i),region_names(i));
commit;
retcd:=0;
errmsg:=‘successful!‘;
exception when bulk_bind_excep then
for i in 1..sql%bulk_exceptions.count loop
dbms_output.put_line(to_char(sql%bulk_exceptions(i).error_index));
dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(i).error_code));
end loop;
for i in 1..region_ids.count loop
if sql%bulk_rowcount(i)>0 then
dbms_output.put_line(to_char(sql%bulk_rowcount(i)));
elsif sql%bulk_rowcount(i)=0 then
dbms_output.put_line(‘NO ROWS‘);
dbms_output.put_line(to_char(region_ids(i)));
dbms_output.put_line(region_names(i));
end if;
end loop;
dbms_output.put_line(to_char(sql%rowcount));
retcd:=sqlcode;
errmsg:=sqlerrm;
dbms_output.put_line(sqlerrm);
when others then
retcd:=sqlcode;
errmsg:=sqlerrm;
dbms_output.put_line(sqlerrm);
end;
begin
for i in 1..10 loop
region_ids(i):=i;
region_names(i):=‘region‘||i;
end loop;
region_names(3):=‘very very very very very very very very long‘;
region_names(6):=‘very very very very very very very very short‘;
load_regions_bulk_bind(region_ids,region_names,ret_code,ret_errmsg);
forall i in 1..10 save exceptions update region_tab set region_name=‘N_‘||region_name where region_id=region_ids(i) returning region_name bulk collect into out_region_names;
--将update操作的新值用retunrning子句返回，返回的方式以批的方式返回，以集合为载体
commit;
for idx in out_region_names.first..out_region_names.last loop
dbms_output.put_line(out_region_names(idx));
end loop;
exception when others then
dbms_output.put_line(sqlerrm);
end;
输出：
3
ORA-12899: value too large for column (actual: , maximum: )
6
ORA-12899: value too large for column (actual: , maximum: )
1
1
NO ROWS
3
very very very very very very very very long
1
1
NO ROWS
6
very very very very very very very very short
1
1
1
1
8
ORA-24381: error(s) in array DML
N_region1
N_region2
N_region4
N_region5
N_region7
N_region8
N_region9
N_region10
原文：http://fengsonglin.blog.51cto.com/9860507/1615264