通过 INDICES OF 和 VALUES OF 关键字了解批量绑定

PL/SQL 条件编译

应用程序通常包含调试或跟踪代码，该代码是在开发周期内专门针对测试编写的。在生产环境中运行应用程序时并不需要这些代码。在早期版本中，所能做的就是针对生产完全删除这些代码（这会阻碍后续的调试），或者用运行时 IF 测试将其包围（导致在程序大小和执行速度方面耗费运行时）。现在，可以在开发环境中启用调试或跟踪功能，并在生产应用程序中禁用该功能。

应用程序通常包含自检查代码，以遵从良好的编程实践。如果程序设计所依赖的不变量被破坏，则这种代码将引发异常。理论上，在生产环境中应该永远不会出现这种异常。然而，这些自测试会导致运行时消耗，尤其是当它们出现在嵌套很深的循环中时。但是，在系统测试完成并启用应用程序前，将其全部删除将妨碍调试小组对首次在生产环境中出现的错误做出诊断。只有少数开发团队会保留应用程序源代码的并行副本：一个用于调试，一个用于生产。PL/SQL 条件编译支持新的最佳实践：通过有选择地编译自检查，保留了诊断功能，且不耗费生产环境。

程序包通常包含私有“帮助”子程序，当然，在开发期间，需要对这些子程序进行测试。但是，由于它们是私有的，因此不能用外部测试工具对其进行测试。通过在程序包规范中的条件指令内声明这些“私有”子程序，可以在开发和测试期间从程序包外调用这些程序，然后在生产环境中将其隐藏。

应用程序代码经常调用服务子程序。这种服务子程序能返回 PL/SQL 记录，以描述员工何时通过其唯一标识符进行了调用。最佳实践指出，如果假设唯一关键字找到多个纪录，则该服务例程将引发定义完善的异常。（如果数据损坏是由整个系统的一个有缺陷的不同组件所导致，则可能会出现此异常。）最佳实践进一步指出，调用服务子程序的应用程序代码必须在异常出现时表现正常，例如向用户显示一个友好的、相对而言非特定的“出现问题”消息，并记录所有相关信息以向跟踪文件详细说明出现异常的来龙去脉。当然，必须在开发周期内对应用程序代码的此种强健特性进行测试。设法损坏数据使服务子程序引发异常通常有一定难度，并且非常耗时。条件编译通过有条件地将代码包含在服务子程序中随意引发给定异常，使您能够以低成本获得同样的效果。

在开发期间，程序员通常创建两个版本、获得相同结果的编译单元。两种版本必须经过测试才能确定哪个最有效。一般而言，两个版本在文本上基本相同，只是在分布于整个源文件上的小关键点处略有不同。在此种情况下，与编写两个单独的程序相比，编写一个包含两种版本的程序要更容易（不易犯人为错误）。条件编译对此方法提供了丰富的支持，而这起初需要手工生成两种版本的源代码。

在编写某些应用程序时不仅要考虑在最新的 Oracle 数据库版本上运行，而且要考虑在早期版本上运行。通常，开发团队只想保留单一的源代码。这迫使开发人员避免在最新版本中使用新的强大特性（由新的 PL/SQL 语法提供），妨碍了用户将代码部署 在最新的版本上。条件编译可用于在最新版本上和早期版上安装程序时选择最优的程序源代码。

完成了教程在 Windows 上安装 Oracle 数据库 10g。

下载 plsql.zip 文件并将其解压缩到您的工作目录（如 c:\wkdir）中。

打开一个终端窗口，执行以下命令：

cd\wkdir
sqlplus hr/hr

创建程序包 STATIC_CONSTANTS 以声明可用于条件编译的程序包常量。从 SQL*Plus 会话中，执行以下脚本：

@static_constants

static_constants.sql 脚本包含下内容：

CREATE OR REPLACE PACKAGE static_constants is
debug CONSTANT BOOLEAN := FALSE; 
trace CONSTANT BOOLEAN := FALSE;
END ; 
/

创建两个过程：CHECK_DEBUG（用于检查程序包常量调试的值是否是 TRUE）和 CHECK_TRACE（用于检查程序包跟踪的值是否是 TRUE）。从 SQL*Plus 会话中，执行以下脚本：

@debug_trc

debug_trc.sql 脚本包含以下内容：

CREATE OR REPLACE PROCEDURE check_Debug IS
BEGIN
  $IF static_constants.debug $THEN DBMS_OUTPUT.put_line('Debugging ON'); 
  $ELSE DBMS_OUTPUT.put_line('Debugging OFF'); $END
END;
/
CREATE or REPLACE PROCEDURE Check_trace IS
BEGIN
  $IF static_constants.trace $THEN DBMS_OUTPUT.put_line('Tracing ON'); 
  $ELSE DBMS_OUTPUT.put_line('Tracing OFF'); $END
END;
/

Set serveroutput on 使用的是 Oracle 数据库 10g 第 2 版中提供的新 SIZE UNLIMITED 语法。现在执行这两个过程。您将看到它显示跟踪和调试均被关闭。

注意：可以将每一行单独地复制到 sqlplus 中，但不要一起复制这 3 个语句。

set serveroutput on size unlimited
exec check_debug
exec check_trace

现在，将更改程序包常量的值。从 SQL*Plus 会话中，执行以下脚本：

@reset_const

reset_const.sql 脚本包含以下内容：

CREATE OR REPLACE PACKAGE static_constants is
debug CONSTANT BOOLEAN := TRUE; 
trace CONSTANT BOOLEAN := TRUE;
END ; 
/

现在，再次执行这两个过程。您将看到调试和跟踪自动启用了。在重新编译程序包时，所有相关的对象都变为无效，并在下一次执行时重新编译。因此，程序包常量的新值可即刻适用于所有相关对象。

exec check_debug 
exec check_trace

这可以扩展至任意数量的相关程序。在需要通过单一机制控制大量程序时，使用这个方法最为有效。用例的其他示例包括针对同一应用程序按州更改赋税，或根据许可选项更改软件特性等等。

更改会话以显示所有的编译器警告。从 SQL*Plus 会话执行以下语句：

ALTER SESSION SET  plsql_warnings = 'enable:all';

请注意，已删除了所有条件编译指令。

创建 SEND_MESSAGE_TO_DBA 和 CHECK_UNIQUE 过程。CHECK_UNIQUE 过程包含两个不同的代码段，一个用于开发中的测试，另一个部署在最终生产环境中。开发代码使用条件编译指令，而生产代码使用传统的 IF-THEN-ELSE 逻辑检查重复的记录。从 SQL*Plus 会话中，执行以下脚本：

@dba_email
@chk_unq
show errors

dba_email.sql 脚本包以下内容：

CREATE OR REPLACE PROCEDURE send_message_to_DBA(emp_id number)
IS
    mailhost    VARCHAR2(64) := 'mailhost.fictional-domain.com';
    sender      VARCHAR2(64) := 'HR_APP@fictional-domain.com';
    recipient   VARCHAR2(64) := 'DBA@fictional-domain.com';
    mail_conn  utl_smtp.connection;
BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, recipient);
-- open_data(), write_data(), and close_data() into a single call to data().
    utl_smtp.open_data(mail_conn);
    utl_smtp.write_data(mail_conn, 'A primary key violation has occurred for record '||
   emp_id || 'in the EMPLOYEES table. This is an automatically generated e-mail 
   message. Please do not respond to this, this is an alert.' || chr(13));
    utl_smtp.write_data(mail_conn, 'This is line 2.' || chr(13));
    utl_smtp.close_data(mail_conn);
    utl_smtp.quit(mail_conn);
    EXCEPTION
        WHEN OTHERS THEN       
           NULL;
END;
/

chk_unq.sql 脚本包含以下内容：

Create or replace Procedure check_unique(emp_id NUMBER) is
v_num number;
force_pk_violation exception;
Begin
Select count(*) into v_num from employees where employee_id = emp_id;
-- production code
         If v_num > 1 
         then SEND_MESSAGE_TO_DBA(emp_id);
              raise force_pk_violation;        
         END IF ;
-- Development code 
         $if $$FORCE     
         $then SEND_MESSAGE_TO_DBA(emp_id);
               raise force_pk_violation;
   $end
END;    
/

现在，更改会话，将 $$force 的值设为 FALSE。从 SQL*Plus 执行以下命令。

ALTER SESSION SET PLSQL_CCFLAGS='force:FALSE';

现在，再次执行 chk_unq.sql。

因为变量 $$force 现在的值是 FALSE，所以没有出现警告；

创建 GET_RECORD 过程。该过程接受 employee id 并返回员工记录。如果 CHECK_UNIQUE 过程检查出存在重复记录，则会显示一个友好消息。从 SQL*Plus 会话执行以下脚本：

@get_record

get_record.sql 脚本包含以下内容：

Create or replace procedure get_record(emp_id IN NUMBER)
as
emp_record  employees%rowtype;
Begin
check_unique(emp_id);
Select * into emp_record from employees where employee_id =emp_id;
Dbms_output.put_line ( 'Name: '|| emp_record.first_name||' 
                       '||emp_record.last_name||' '||'Hiredate: 
                       ' ||emp_record.hire_date||'
                       '||'Job: '||' '||emp_record.job_id);
Exception
When others then 
DBMS_OUTPUT.PUT_LINE('We are unable to process your request at this time. 
  Please try again later. We apologize for any inconvenience');
End;
/

现在，执行 GET_RECORD 过程，传入 100 作为参数。

exec GET_RECORD(100)      

因为没有重复记录，所以该过程成功执行。

现在，测试是否在出现重复记录时显示消息。更改会话，并将 $$force 的值设为 TRUE，并再次执行 GET_RECORD。从 SQL*Plus 会话执行以下命令：

ALTER SESSION SET PLSQL_CCFLAGS='force:TRUE';
exec GET_RECORD(100) 

由于未重新编译该过程，因此它不会受到影响。

使用 ALTER...COMPILE 语句重新编译 CHECK_UNIQUE 过程，并为 PLSQL_CCFLAGS 参数提供新值。使用 REUSE SETTINGS 选项。然后，以值 100 再次执行 GET_RECORD。从 SQL*Plus 会话中，执行以下命令。

ALTER PROCEDURE CHECK_UNIQUE compile 
  plsql_ccflags = 'force:TRUE' REUSE SETTINGS;
exec GET_RECORD(100) 

现在，PLSQL_CCFLAGS 值生效，因而程序发出消息。该方法可以用于影响会话内的特定程序，比如后生产调试。然而，每个需要新设置的程序都必须使用 CREATE OR REPLACE 或 ALTER...COMPILE 语句进行重新编译。

1.

执行以下命令，查看用于编译有效 PL/SQL 程序的实际源代码：

EXEC DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PROCEDURE', 'HR', 'CHECK_UNIQUE');

请注意，已删除了所有条件编译指令。

1.

启用 ALTER SESSION 以设置标记，从而在两个版本之间进行选择。首先，选择使用 NUMBER 数据类型。从 SQL*Plus 会话中，执行以下命令：

ALTER SESSION SET PLSQL_CCFLAGS = ' numversion:TRUE';

2.

您希望知道哪个代码执行速度更快。创建一个 CALC_CIRCLE 过程计算给定半径的圆的周长和面积。从 SQL*Plus 会话中，执行以下脚本：

@num_bdbl

num_bdbl.sql 脚本包含以下内容：

CREATE or REPLACE PROCEDURE Calc_circle( RADIUS $IF $$NUMVERSION $THEN NUMBER 
$ELSE BINARY_DOUBLE $END)
as 
SUBTYPE my_real IS
    $IF $$numversion $THEN 
         NUMBER; 
    $ELSE                               
         BINARY_DOUBLE;
    $END
 num_circ my_real;
 num_area my_real;
 BDBL_circ my_real;
 BDBL_AREA my_real;
BEGIN
 num_CIRC:=  (3.14016408289008292431940027343666863227 * 2 * RADIUS); 
 NUM_AREA := (3.14016408289008292431940027343666863227*radius*radius);
 DBMS_OUTPUT.PUT_LINE('The circumference is: '||num_circ);
 DBMS_OUTPUT.PUT_LINE('The area is: '||num_area);
END ;
/

3.

执行 CALC_CIRCLE 过程，使用数字 1234567890 作为半径。

set timing on
exec calc_circle(1234567890)

4.

使用 ALTER COMPILE 命令将 $$numversion 的值更改为 FALSE 来编译 CALC_CIRCLE，并使用相同的参数再次执行 CALC_CIRCLE。

ALTER PROCEDURE  calc_circle COMPILE plsql_ccflags = 'numversion :false' REUSE SETTINGS; 
exec calc_circle(1234567890)
set timing off

您可以看出二者性能间的差异。本例阐明了可以使用条件编译在相同程序内有选择地测试两个版本的代码。

现在可以测试 Oracle 数据库版本了。从终端窗口中执行以下脚本：

@check_version

check_version.sql 脚本包含以下内容：

CREATE OR REPLACE PROCEDURE check_version AS
BEGIN
 -- some code which performs transaction processing ...
$if DBMS_DB_VERSION.VER_LE_10_2 $then 
-- traditional commit
COMMIT;
DBMS_OUTPUT.PUT_LINE ('The transaction has been successfully committed.');
$else 
-- faster COMMIT supported in 10.2
COMMIT WRITE IMMEDIATE NOWAIT;
DBMS_OUTPUT.PUT_LINE ('The transaction has been successfully committed.');
$end
END;
/

现在，在 SQL*Plus 窗口中执行以下命令来运行 check_version 过程。

exec check_version
quit

check_version 的输出说明：无论版本如何，该过程都能成功完成，而且对最终用户而言是透明的。因此，没有理由在不同的数据库版本中使用不同的程序单元。

必须先根据现有的 EMPLOYEES 表（在 hr 模式中）创建一个 NEW_EMPLOYEES 表，并为 EMPLOYEE_ID 列创建一个唯一索引。这将在插入重复记录时生成异常。从终端窗口中执行以下命令：

sqlplus hr/hr
@setup

setup.sql 脚本包含以下内容：

drop table new_employees;
create table new_employees as select * from employees where 1=2;
create unique index new_employees_employee_id 
  on new_employees (employee_id);

然后，创建 P_BULK_BIND 程序包。该程序包包含使用三种不同的方法创建稀疏集合并将记录批量插入到 NEW_EMPLOYEES 表中的各种过程。

@cr_p

单击 cr_p.sql 查看该脚本中包含的内容。

为了解批量绑定如何在 Oracle 数据库 10g 中工作，我们先看一下它是如何在 Oracle9i 中工作的。FORALL 语句使用 SAVE EXCEPTIONS 语法抑制因为缺失记录（稍后在 EXCEPTION 块中打印）而引发的异常。执行下列命令：

set serverout on size 100000
exec p_bulk_bind.Bulk_Insert_Pre_10g (i_make_sparse => true)

-- this code is located in the Bulk_Insert_Pre_10g procedure
...
forall j in g_emp_recs.First()..g_emp_recs.Last()
save exceptions
  insert into new_employees values g_emp_recs(j);

exception when bulk_errors then
  for j in 1..sql%bulk_exceptions.Count()
  loop
    Dbms_Output.Put_Line ( 'Error from element #' ||
    To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
    Sqlerrm(SQL%bulk_exceptions(j).error_code) );
  end loop;
...

从输出中，您看到每个缺失的记录都生成了一个异常 (ORA-22160)。正如前面指出的，即使通过 SAVE EXCEPTIONS 语法处理了剩余记录，但如果已删除的记录数很大，那么这也将导致性能降低。

下一步是使用 Oracle 数据库 10g INDICES OF 语法忽略缺失的记录，从而获得更好的性能。执行下列命令：

exec p_bulk_bind.Bulk_Insert_With_Indices_Of (i_cause_exception => false)

在此情况下，布尔输入值用于忽略负责创建异常条件（您将在下个步骤中看到）的代码块。

-- this code is located in the Bulk_Insert_With_Indices_Of 
-- procedure
...
forall j in indices of g_emp_recs
save exceptions
  insert into new_employees values g_emp_recs(j);
...

这次没有因缺失录而引发异常。

也可以通过将输入参数集设置为 true 来运行前面的过程。这将导致在插入时生成一个异常；必须捕获该异常（通过 SAVE EXCEPTIONS）并稍后对其进行处理。导致异常的条件通过一个过程调用（将姓氏为“Ernst”和“Urman”的员工的 EMAIL 列取空）进行模拟。由于 EMAIL 列有一个 NOT NULL 限制，因此这将导致在插入两个相应的记录时生成异常。该异常稍后从 SQL%BULK_EXCEPTIONS 集合中处理。执行下列命令：

exec p_bulk_bind.Bulk_Insert_With_Indices_Of (i_cause_exception => true)

-- this code is located in the exception area
-- in the Bulk_Insert_With_Indices_Of procedure
...
forall j in indices of g_emp_recs
-- between g_emp_recs.First() and g_emp_recs.Last() 
-- optional
save exceptions
  insert into new_employees values g_emp_recs(j);

exception when bulk_errors then
  declare
    v_iteration pls_integer;
    n pls_integer;
    k pls_integer;
  begin
    for j in 1..sql%bulk_exceptions.Count()

    loop
      v_iteration := SQL%bulk_exceptions(j).error_index;
      Dbms_Output.Put_Line (
        'Error on the ' || To_Char(v_iteration) 
         || 'th iteration' );
 ...
         

注意：错误针对迭代号进行报告。这是 Oracle 数据库 10g 中一个新的异常处理功能。

另一个方法是使用 VALUES OF 子句创建第二个指向原始数组元素的指针集合。执行下列命令：

exec p_bulk_bind.Bulk_Insert_With_Values_Of -
   (i_null_email => true, i_violate_pk => false)

Point_To_Sparse 过程用于根据与前面相同的逻辑创建一个指针集合。也就是说，它指向包含姓氏中每个字母的第一个匹配项的元素。该过程从 Bulk_Insert_With_Values_Of 过程中调用。

-- this code is located in the Bulk_Insert_With_Values_Of 
-- procedure
...
forall j in values of g_values_of_tab
save exceptions
  insert into new_employees values g_emp_recs(j);
...

此结果与第 5 步中的结果相同：第五个和第十九个迭代引发异常。

要查看报告迭代号（或指针元素号）的效果（与报告错误记录索引相对），可以在 i_violate_pk => true 的情况下运行前面的过程。为了模拟错误条件，使用 Cause_Exception_For_Values_Of 过程将边界集合 (G_EMP_RECS) 中的重复索引条目插入到指针集合 (G_VALUES_OF_TAB) 中。因此，这违反了唯一键约束（在第 1 步中创建），并在批量插入到 NEW_EMPLOYEES 表时引发异常。执行下列命令：

exec p_bulk_bind.Bulk_Insert_With_Values_Of -
   (i_null_email => false, i_violate_pk => true)

-- this code is located in the Bulk_Insert_With_Values_Of 
-- procedure
...
exception when bulk_errors then
  declare

     v_iteration pls_integer;
   begin
     for j in 1..sql%bulk_exceptions.Count()
     loop
       v_iteration := SQL%bulk_exceptions(j).error_index;
       Dbms_Output.Put_Line (
         'Error on the ' || To_Char(v_iteration) ||
         'th iteration' );

-- Find the index of the offending element 
-- from the iteration number
       Dbms_Output.Put_Line (
         'last_name for error element: ' ||
          g_emp_recs(g_values_of_tab(v_iteration)).last_name );

       Dbms_Output.Put_Line (
         'Error was: ' || 
          Sqlerrm(SQL%bulk_exceptions(j).error_code) );
     end loop;
   end;
...