可以在 PL/SQL 块的内部运行 SQL 语句，一般的方法是：

A 使用通常位于服务器端的 PL/SQL 引擎，做纯粹的 PL/SQL 执行。但是，客户端程序有它自己的 PL/SQL 引擎。

B 所有的 SQL DML 语句都被送到 SQL 引擎进行执行，结果和数据或 DML 执行后结果再传送加 PL/SQL 引擎。

 

这样导至的结果：当 SQL 语句在 PL/SQL 引擎和 SQL 引擎之间进行传送的时候，可能会发生上下文切换。在 PL/SQL 内执行 SQL 时，一件很重要的事情就是要保证查询最优化。由于代码往往是 PL/SQL 代码与 SQL 代码混合一起的，所以其执行过程还应该包括两种代码的解析。

 

不同引擎之间传送语句的操作被称为上下文切换，而上下文切换可能导致代码的执行达不到最佳。

 

解决这种情能问题的方法之一就是通过使用批绑定。在 Oracle 8i 中引入的批绑定，全面提高了 PL/SQL 的执行性能，在 Oracle9i 中又做了以下加强：

       如果涉及到 DML 的批绑定发生异常，使用 SAVE EXEPTIONS 子句处理仍可以连续运行。

       直接在单个 DML 语句中支持批动态 SQL 处理多行数据的能力。

可以与 RETURNING 子句一起使用动态 SQL ，支持多行更新和删除。在 oracle 9i 以前本地动态 SQL 只在单行输出才支持 RETURNING 子句。

 

 

接下来介绍可以提高程序执行性能的批绑定的各种特性，然后介绍以下语句的技术：

       批 DML

批查询

批动态 SQL

 

批 DML

       批 DML 是由使用 PL/SQL 的 index-by 表或集合作为 DML 语句的输入、批量地执行 INSERT 、 UPDATE 或 DELETE 的语句组成的。可以使用 FORALL 语句执行批 DML ，如：

create or replace procedure batch_dml(errnum outnumber,errtext outvarchar2)
is
   type user_record isrecord(  -- 定义记录类型
     id number,
     namevarchar2( 200 ),
     sex varchar2( 2 )
   );
   type user_tab istableof user_record indexbybinary_integer; -- 定义记录类型的 index-by

   user_recs user_tab;
begin
    for idx in 1 .. 5 loop   -- 初始化数据
         user_recs(idx).id:=idx;
         user_recs(idx).name:= 'xxxxx' ||idx;
         user_recs(idx).sex:= 'F' ;
    endloop;
    deletefrom t_user;
    for idx in user_recs.first..user_recs.lastloop
        insertinto t_user values(user_recs(idx).id, user_recs(idx).name,user_recs(idx).sex);
    endloop;
    commit;
    errnum:= 0 ;
    errtext:= '' ;
exception
    whenothersthen
         errnum:=sqlcode;
         errtext:=sqlerrm; 
end batch_dml;
对于这段代码， FOR LOOP 循环每执行一次，其中的 insert 语句就会被送到 SQL 引擎一次，也就发生一次上下文切换。这意味着当数据量很大的时候阻塞就会很严重，而这时 insert 语句也就会影响数据库更多的记录行，而使用批绑定可以很大程度上提高程的执行性能。

绑定指的是在 SQL 语句中为 PL/SQL 变量赋值，而一次性地将整个值集合绑定称为批绑定。批绑定能减少数据往返的次数，因些在 PL/SQL 和 SQL 引擎之间上下文切换次数也会减少。这样显著提高程序的执行性能。可以使用 FORALL 语句来执行 DML 批绑定。其语法如下：

       FORALL index IN index-by.first..index-by.last  [save exceptions]

                     Sql_statement;

下面我们把上面的 batch_dml 改成批绑定的 DML ，代码如下 ：

create or replace procedure batch_dml(errnum outnumber,errtext outvarchar2)
is
   --type user_record is record(  --¶¨Òå¼Ç¼ÀàÐÍ
    -- id number,
   --  name varchar2(200),
   --  sex varchar2(2)
  --  );
   --type user_tab is table of user_record index by binary_integer; --¶¨Òåuser_recordÀàÐ͵Äindex-by±íÀàÐÍ
   --user_recs user_tab;
  
   type user_id_tab istableofnumberindexbybinary_integer;
   type user_name_tab istableofvarchar2( 20 ) indexbybinary_integer;
   type user_sex_tab istableofvarchar2( 2 ) indexbybinary_integer;
  
   user_id user_id_tab;
   user_name user_name_tab;
   user_sex user_sex_tab;
begin
    for idx in 1 .. 50000 loop   -- 初始化数据
         user_id(idx):=idx;
         user_name(idx):= 'xxx' ||idx;
         user_sex(idx):= 'F' ;
    endloop;
    deletefrom t_user;
    forall idx in user_id.first..user_id.last
        insertinto t_user values(user_id(idx), user_name(idx),user_sex(idx));
    commit;
    errnum:= 0 ;
    errtext:= '' ;
exception

    when others then
         commit;

     errnum:=sqlcode;
         errtext:=sqlerrm; 
end batch_dml;
插入 50000 条记录仅用了 2.16 秒

 

使用 FORALL 时，多个 INSERT 、 UPDATE 和 DELETE 语句是在一个数据库调用中完成的。这就缩短了程序的执行时间。只有单条引用了集合元素的 INSERT 、 UPDATE 或 DELETE 语句才可以使用 FORALL 进行执行。

 

SQL 出错以后

当发生 SQL 错误的时候，产生错误的那条 SQL 操作是以一种自动回滚的方式终止的，而接着程序的执行就停止下来。只有产生错误的这条 SQL 才可以回滚。而一旦执行成功，前的 SQL 语句是不再回滚了。

 

SAVE EXCEPTION 子句

Oracle 9i 中增强了批 DML 在发生异常时处理的错误及失败的程序执行能力。这是通过 FORALL 语句的 SAVE EXCEPTION 子句来实现，其语法如下：

 FORALL  index in lower..upper  save exceptions

 

SAVE EXCEPTIONS 子句是 oracle 9i 中引入的，它在一个隐式游标属性 SQL%BULK_EXCEPTIONS 中保存错误行，允许 FORALL 语句继续处理其余行。

下面这一段代码是 SQL%BULK_EXCEPTIONS 的应用例子：

create or replace procedure batch_dml(errnum outnumber,errtext outvarchar2)
is

 
   type user_id_tab istableofnumberindexbybinary_integer;
   type user_name_tab istableofvarchar2( 20 ) indexbybinary_integer;
   type user_sex_tab istableofvarchar2( 2 ) indexbybinary_integer;
  
   user_id user_id_tab;
   user_name user_name_tab;
   user_sex user_sex_tab;
  
   bulk_bind_excep EXCEPTION;
   pragmaexception_init(bulk_bind_excep,- 24381 );
begin
    for idx in 1 .. 50000 loop   --³õʼ»¯index-by¼Ç¼
         user_id(idx):=idx;
         user_name(idx):= 'xxx' ||idx;
         user_sex(idx):= 'F' ;
    endloop;
    user_id( 40000 ):= 39999 ;
    user_id( 10000 ):= 9999 ;
    deletefrom t_user;
    forall idx in user_id.first..user_id.lastsaveexceptions
        insertinto t_user values(user_id(idx), user_name(idx),user_sex(idx));
    errnum:= 0 ;
    errtext:= '' ;
exception
    when bulk_bind_excep then
         for i in 1 ..sql%bulk_exceptions.countloop
             dbms_output.put_line( 'Iteration ' ||SQL%bulk_exceptions(i).error_index|| 'failed with error ' ||sqlerrm(sql%bulk_exceptions(i).error_code));
         endloop;
         commit;
    whenothersthen
         commit;
         errnum:=sqlcode;
         errtext:=sqlerrm; 
end batch_dml;
这个例子是修改了上面的程序，加上 save exceptions 异常处理，当批 dml 发生异常时也能正常运行。

 

FORALL 语句属性

与隐式游标的属性一样，批绑定操作也有与之关联的标量属性，这些标量属性有 %found 、 %NOTFOUND 、 %ROWCOUNT 。 %ROWCOUND 属性是基于批绑定的 SQL 语句全部执行完成以后的。 %FOUND 、 %NOTFOUND 指的是刚刚执行的那一条 SQL 语句。

 

批查询

可以使用 bulk collect into collection_name ，其中 collect_name 是 index-by 表、嵌套表或 Varray 的名称。可以在 select into 、 fetch into 和 returning into 子句中使用 bulk collect 。可以在 into 例表中经引用多个类集。

  在 select ..into 语句中使用 bulk  collect

语法如下：

Select column_name bulk collect into collect_name

例子：

create or replace procedure bulkcollectdemo(
    errnum outnumber,
    errtext outvarchar2
)
authidcurrent_user
is
 type uidtab istableof t_user.id%type;
 type unametab istableof t_user.name%type;
 id_tab uidtab;
 name_tab unametab;
begin
   select a.id,a.name  bulkcollectinto id_tab,name_tab from t_user a orderby a.id;
   for i in id_tab.first..id_tab.lastloop
      null;
   endloop;
   errnum:= 0 ;
   errtext:= '' ;
exception
   whenothersthen
     errnum:=sqlcode;
     errtext:=sqlerrm;  
end;

使用 bulk collect 的主要优点是：它减少在 SQL 和 PL/SQL 引擎之间的上下文切换次数，最大程度的提高程序执行的性能，在选择记录里可以使用 rownum 来限制加载的行数。实现这种功能的代码如下：

select id bulk collect into region_name where rownum<51;

 

在 Fetching 中使用 BULK COLLECT

从游标中取多行数据时，可以使用 bulk collect 。其语法如下：

 Fetch cursor_name bulk collect into collection_name

下面的例子是修改上一个存储过程的，其代码如下：

create or replace procedure bulkcollectdemo(
    errnum outnumber,
    errtext outvarchar2
)
authidcurrent_user
is
 type uidtab istableof t_user.id%type;
 type unametab istableof t_user.name%type;
 id_tab uidtab;
 name_tab unametab;
 cursor cur_tuser is
    select id,namefrom t_user orderby id;
begin
   open cur_tuser;
   fetch cur_tuser bulkcollectinto id_tab,name_tab;
   dbms_output.put_line(id_tab.count);
   for i in id_tab.first..id_tab.lastloop
      if i= 37 then
        dbms_output.put_line( '------' );
      endif;
   endloop;
   close cur_tuser;
   errnum:= 0 ;
   errtext:= '' ;
exception
   whenothersthen
     errnum:=sqlcode;
     errtext:=sqlerrm;  
end;

不能在 forall 语句中使用 select ... bulk collect 语句

在 returning into 中使使用 bulk collect

 

弄不明白暂时不管

 

 

批动态 SQL

本地动态 SQL 比静态 SQL 更优，它的执行也更快，在 Oracle 9i 之前，只能使用 DBMS_SQL 来执行批动态 SQL 。 Oralce 9i 已经增强了本地动态 SQL ，使其支持批处理操作。

1 、通过使用 SELECT INTO 语句，将 BULK COLLECT 与 EXECUTE IMMEDIATE 结合在一起进行批查询，其语法如下：

Execute immediate ‘select statement’ bulk collect into collection_name;

 

2 、将 execute immediate 与 forall 语句结合起来一起使用进行批 DML, 语法如下：

   Forall index in lower..upper

        Execute  immediate ‘sql_statement ‘  using collection_name;

  例子：

       Forall I in  region_ids.first..region_ids.last

         Execute immediate ‘insert into region_tab value (:ip_ids,:ip_name)’ using region_ids(i),region_names(i);

 

3 、使用 fetch..bulk collect into... 从使用本地动态 SQL 打开的游标中进行批取值，语法如下：

       Open cursor_variable_name for dyn_sql_statement;

Fetch cursor variable_name bulk collect into collection_name;

例子：

Cursor cur_tuser is select tname form t_user order by id;

Begin

  open cur_tuser;

  fetch cur_tuser bulk collect into  tname;

使用动态语句，把以上代码改成如下：

Type rc is ref cursor;

Cur_tuser rc;

Begin

  Open cur_tuser form ‘select name form t_user’;

  Fetch cur_tuser bulk collect into region_names;