plsql动态绑定

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