PL/SQL复习六 批量绑定

一、效率比较

1.不适用批量：

declare

    type id_table_type is table of number(6) index by binary_integer;

    type name_table_type is table of varchar2(10) index by binary_integer;

    id_table id_table_type;

    name_table name_table_type;

    start_time number(10);

    end_time number(10);

begin

  for i in 1..5000 loop

    id_table(i) := i;

    name_table(i) := 'name' || to_char(i);

  end loop;

  start_time := dbms_utility.get_time;

  for i in 1..id_table.count loop

    insert into demo values(id_table(i),name_table(i));

  end loop;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('总时间：'||to_char((end_time - start_time)/100));

end;

/

执行时间：0.25秒；

2.使用批量绑定：

declare

    type id_table_type is table of number(6) index by binary_integer;

    type name_table_type is table of varchar2(10) index by binary_integer;

    id_table id_table_type;

    name_table name_table_type;

    start_time number(10);

    end_time number(10);

begin

  for i in 1..5000 loop

    id_table(i) := i;

    name_table(i) := 'name' || to_char(i);

  end loop;

  start_time := dbms_utility.get_time;

    --使用批量提取：

  forall i in 1..id_table.count 

    insert into demo values(id_table(i),name_table(i));

  end_time := dbms_utility.get_time;

  dbms_output.put_line('总时间：'||to_char((end_time - start_time)/100));

end;

/

执行时间：0.03秒  快了将近8倍 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

二、批量绑定语法：

bulk collect: 用于取得批量数据，只能用于select、fetch和DML返回子句中

forall:只适用于执行批量的DML操作

1.forall语句:

语法：

1)  forall index in lower_bound..upper_bound 

             sql_statement;

2) forall index in indices of collection [between lower_bound and upper_bound]

           sql_statement;

    indices of 用于指定取得对应于collection集合元素下标的index值

3) forall index in values of index_collection

         sql_statement;

    value of 用于指定index值从集合变量index_collection中取得

注意：9i只能使用第一种

示例：

使用部分：

forall i in 8..10

  insert into demo(id) values(id_table(i));

indices of:用于指定取得对应于collection集合元素下标的index值

id_table := id_table_type(1,null,3,null,5);

forall i in indices of id_table

  delete from demo where id=id_table(i);

values of:用于指定index值从集合变量index_collection中取得

index_pointer := index_pointer_type(6,8,10);

forall i in values of index_pointer

  insert into new_demo values(in_table(i),name_table(i));

--插入的是表的6 8 10位元素 因为i是从表里面取的

sql%bulk_rowcount:

专门为forall语句提供，作用是取得在执行批量绑定操作时第i个元素所作用的行数

用法：sql%bulk_rowcount(2);--返回第2个元素的SQL所作用的行数

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

三、批量提取

declare

  type emp_table_type is table of emp%rowtype index by binary_integer;

  emp_table emp_table_type;

begin

  select * bulk collect into emp_table from emp where deptno = &no;

  ....

end;

/

returning：返回受影响的数据

declare

  type ename_table_type is table of emp.ename%type index by binary_integer;

  ename_table ename_table_type;

begin

  delete from emp where deptno = &no returning ename bulk collect into ename_table;

end;

/