秦始皇传 张分田:使用 Oracle 数据库 10g PL/SQL 新特性

目的
在本教程中，您将学习 Oracle 数据库 10g 中引入的新 PL/SQL 特性。
所需时间
大约 30 分钟
主题
本教程包括下列主题：
概述
前提条件
使用 PL/SQL 条件编译

通过 INDICES OF 和 VALUES OF 关键字了解批量绑定
总结
查看屏幕截图
将鼠标置于此图标上可以加载和查看本教程的所有屏幕截图。（警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。）
注意：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图来将其隐藏。
概述
在 Oracle 数据库 10g 中，PL/SQL 语言提供了几个增强功能。在本概述部分中，您将了解以下主题：
1.PL/SQL 条件编译
2.了解批量绑定功能增强
3.使用 JDeveloper 调试 PL/SQL
返回主题列表
PL/SQL 条件编译
Oracle 数据库 10g 第 2 版引入了对 PL/SQL 条件编译的支持。新语法允许编译器根据指定条件编译部分程序。应用程序开发人员通过将指令嵌入其 PL/SQL 源程序中来使用条件编译。在提交 PL/SQL 程序进行编译时，会运行指令来选择要编译的部分程序。这种特性在以下情况中非常有用：

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

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

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

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

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

在编写某些应用程序时不仅要考虑在最新的 Oracle 数据库版本上运行，而且要考虑在早期版本上运行。通常，开发团队只想保留单一的源代码。这迫使开发人员避免在最新版本中使用新的强大特性（由新的 PL/SQL 语法提供），妨碍了用户将代码部署 在最新的版本上。条件编译可用于在最新版本上和早期版上安装程序时选择最优的程序源代码。
Oracle 数据库 10g 版本 10.1.0.4 或更高版本中已引入了条件编译。
返回主题
了解批量绑定功能增强
Oracle 数据库 10g 扩展了 Oracle9i 在批量绑定领域中引入的增强功能。将 SAVE EXCEPTIONS 语法引进 Oracle9i，用于在批量插入（删除或更新）行时捕获异常。尽管此特性使数据操作语言 (DML) 能够继续将所有异常保存在 SQL%BULK_EXCEPTIONS 集合中，但操作的性能会受到显著影响。如果正在处理的集合因所涉及的应用程序逻辑而变得比较稀疏，则这可能是不必要的开销。在 Oracle 数据库 10g 中，您可以使用新的 INDICES OF 和 VALUES OF 特性解决这些问题。
返回主题
使用 JDeveloper 调试 PL/SQL
从 9.0.3 版开始，JDeveloper 支持 PL/SQL 调试。这是通过 Java 调试线路协议 (JDWP) 实现的。在 Oracle9i 第 2 版（以及更高版本）中，该调试器使用行业标准，而在早期版本（Oracle8i 和 Oracle9i 第 1 版）中，则使用 DBMS_DEBUG 程序包。通过此调试用户界面，您可以单步、步进和步过执行 PL/SQL 对象，还能够在 Watch 和 Inspector 窗口中设置 PL/SQL 表达式、设置条件断点以及查看集合数据。
返回主题
前提条件
开始本教程之前，您应该：
1. 完成了教程在 Windows 上安装 Oracle 数据库 10g。
2. 下载plsql.zip 文件并将其解压缩到您的工作目录（如 c:\wkdir）中。
返回主题列表
使用 PL/SQL 条件编译
本部分将向您介绍如何使用条件编译，并给出了几个用法示例。

预处理器指令

选择指令

查询指令
错误指令

预定义编译器参数

使用指令
返回主题列表
预处理器指令
指令由指令控制标记“$”和普通的 PL/SQL 文本组成。条件编译使用三个指令：选择、查询和错误。特殊的触发器字符“$”代表条件编译指令。选择指令是条件编译机制的重要组成部分，而查询和错误指令支持有用的附加功能。
返回主题
选择指令
选择指令对条件编译表达式进行评估，并根据评估的结果选择要包含在该编译中的代码。完全忽略未选中的代码。这不会干扰到现有程序，因为这些程序并未使用条件编译。条件选择指令以 $if 开始并使用常规语法：
$if $then  $elsif $then  …$else  $end其中， 表示静态布尔表达式。静态布尔表达式是一个或多个程序包常量或一个或多个查询指令的任意组合。以下是选择指令将程序包常量用作静态布尔表达式的示例：
$if Trace_Pkg.Trace > 2 $then $end 其中，Trace_pkg 是程序包的名称，Trace 是声明为 PLS_INTEGER 的常量。请注意，如果 PL/SQL 编译单元 U 内的选择指令中使用了在程序包 Trace_pkg 内声明的常量，那么，U 将在 Trace_pkg 上具有一个依赖项，就好像 Trace_pkg 已经在常规 PL/SQL 代码中进行了引用。
返回主题
查询指令允许访问编译环境，以便将选择基于当前环境。查询指令的形式为 $$。例如，要进行编译的单元的 PL/SQL 编译器参数的值（例如 PLSQL_OPTIMIZE_LEVEL）。此种查询可以与条件选择指令结合使用来选择要编译的程序部分。例如，可以使用查询指令查询 PL/SQL 编译器参数的值。查询指令以两个触发器字符 $$ 开头，例如：
$if $$debug_level > 3 $then … $end标识符 debug_level 是使用 plsql_ccflags 初始化参数（Oracle 数据库 10g 第 2 版中的新增内容）定义的，因此：
...plsql_ccflags = ‘debug_level:4, ...‘返回主题
错误指令
错误指令的形式如下所示
$error $end.它可以使编译器报告编译错误，包括 VARCHAR2 表达式中提供的消息。
返回主题
Oracle 数据库 10g 第 2 版提供了以下可以在条件查询指令中使用的 PL/SQL 编译器参数：

PLSQL_CCFLAGS

PLSQL_DEBUG

PLSQL_OPTIMIZE_LEVEL

PLSQL_CODE_TYPE

PLSQL_WARNINGS

NLS_LENGTH_SEMANTICS
编译时，PL/SQL 编译器参数的值存储在编译单元中，并可以使用 all_plsql_object_settings 系列视图进行查看。此外，还提供了预定义的查询指令 $$PLSQL_UNIT, $$PLSQL_LINE。有关条件编译指令语法的详细信息，请参阅 PL/SQL 用户指南和参考 手册。
返回主题
使用指令

示例 1：使用程序包常量进行跟踪和调试

示例 2：使用编译器警告和 PLSQL_CCFLAGS

示例 3：使用 DBMS_PREPROCESSOR 过程打印或检索源文本

示例 4：使用条件编译分支代码确定最佳性能版本

示例 5：将条件编译与不同版本的 Oracle 数据库结合使用
返回主题
示例 1：使用程序包常量进行跟踪和调试
在条件编译指令中使用程序包常量为通过单一机制控制一个或多个 PL/SQL 编译单元提供了一种方法。例如，假设应用程序由许多 PL/SQL 编译单元组成。在该应用程序内，已嵌入了执行调试或跟踪的 ed 方法。这些方法可以通过使用程序包常量的条件编译指令启用。因此，可以通过随时重新编译该程序包来更改该常量的值。在重新编译程序包时，所有的相关对象都将自动重新编译，以接受该程序包常量的新值。这可以用于在整个应用程序中启用跟踪和调试功能。在进行跟踪和调试时，利用保护跟踪和调试代码的新常量值重新编译程序包规范。这将使所有相关 PL/SQL 单元无效，以便下一次使用时，将不会选中跟踪和调试代码进行编译。程序包常量的使用是一种控制所有相关 PL/SQL 单元的有效机制，这些单元在选择指令内使用打包常量进行条件处理。以下示例演示了这一用法。
1. 打开一个终端窗口，执行以下命令：
cd\wkdir sqlplus hr/hr
2. 创建程序包 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 ;
/
3. 创建两个过程：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;
/
4. Set serveroutput on 使用的是 Oracle 数据库 10g 第 2 版中提供的新 SIZE UNLIMITED 语法。现在执行这两个过程。您将看到它显示跟踪和调试均被关闭。
注意：可以将每一行单独地复制到 sqlplus 中，但不要一起复制这 3 个语句。
set serveroutput on size unlimited exec check_debug exec check_trace
5. 现在，将更改程序包常量的值。从 SQL*Plus 会话中，执行以下脚本：
@reset_const
reset_const.sql 脚本包含以下内容：
CREATE OR REPLACE PACKAGE static_constants is
debug CONSTANT BOOLEAN := TRUE;
trace CONSTANT BOOLEAN := TRUE;
END ;
/
6. 现在，再次执行这两个过程。您将看到调试和跟踪自动启用了。在重新编译程序包时，所有相关的对象都变为无效，并在下一次执行时重新编译。因此，程序包常量的新值可即刻适用于所有相关对象。
exec check_debug exec check_trace
这可以扩展至任意数量的相关程序。在需要通过单一机制控制大量程序时，使用这个方法最为有效。用例的其他示例包括针对同一应用程序按州更改赋税，或根据许可选项更改软件特性等等。
返回子主题
通过设置 PLSQL_CCFLAGS 的值，可以使用条件参数 PLSQL_CCFLAGS 提供对指定程序的细粒度访问。为此，可以使用 ALTER SESSION 命令或 ALTER...COMPILE 命令。ALTER COMPILE 命令只影响正在编译的程序。利用 ALTER SESSION 语句之后的 CREATE or REPLACE 重新编译这些程序，ALTER SESSION 命令可用于影响多个程序。
在以下示例中，要创建一个称为 GET_RECORD 的过程，以接受 employee_id 的值，并显示相应的记录。为了保持良好的编程实践，该过程将调用 CHECK_UNIQUE 过程来验证每个员工记录的唯一性。只有极少数的情况下存在重复的记录，程序会发出一个用户友好的消息，并使用名为 SEND_MESSAGE_TO_DBA 的过程向 DBA 发送预警。然而，因为测试数据库中的数据并不包含任何重复项，所以可以在查询指令中使用带有静态布尔表达式的 PLSL_CCFLAGS 模拟重复项。
1.
更改会话以显示所有的编译器警告。从 SQL*Plus 会话执行以下语句：
ALTER SESSION SET  plsql_warnings = ‘enable:all‘;
请注意，已删除了所有条件编译指令。
2.
创建 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;
/
3. 现在，更改会话，将 $$force 的值设为 FALSE。从 SQL*Plus 执行以下命令。
ALTER SESSION SET PLSQL_CCFLAGS=‘force:FALSE‘;

4. 现在，再次执行 chk_unq.sql。

因为变量 $$force 现在的值是 FALSE，所以没有出现警告；
5. 创建 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;
/
6.
现在，执行 GET_RECORD 过程，传入 100 作为参数。
exec GET_RECORD(100)

因为没有重复记录，所以该过程成功执行。
7. 现在，测试是否在出现重复记录时显示消息。更改会话，并将 $$force 的值设为 TRUE，并再次执行 GET_RECORD。从 SQL*Plus 会话执行以下命令：
ALTER SESSION SET PLSQL_CCFLAGS=‘force:TRUE‘; exec GET_RECORD(100)

由于未重新编译该过程，因此它不会受到影响。
8. 使用 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 语句进行重新编译。
返回子主题
示例 3：使用 DBMS_PREPROCESSOR 过程打印或检索源文本
DBMS_PREPROCESSOR 子程序会在处理条件编译指令后，打印或检索 PL/SQL 单元的处理后源文本。这个处理后文本是用于编译有效 PL/SQL 单元的实际源。执行以下步骤：
1.
执行以下命令，查看用于编译有效 PL/SQL 程序的实际源代码：
EXEC DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE(‘PROCEDURE‘, ‘HR‘, ‘CHECK_UNIQUE‘);
请注意，已删除了所有条件编译指令。
返回子主题
示例 4：使用条件编译分支代码确定最佳性能版本
Oracle 数据库 10g 引入了 BINARY_DOUBLE 数据类型，该数据类型可用于算法密集的操作。本例中，将对 BINARY_DOUBLE 数据类型与 NUMBER 数据类型进行比较。需要在两个不同版本中创建同样的代码，一个使用 NUMBER，另一个使用 BINARY_DOUBLE。然后，可将两个版本包含在同一过程中，以使用 PLSQL_CCFLAGS 进行测试。执行以下步骤：
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

您可以看出二者性能间的差异。本例阐明了可以使用条件编译在相同程序内有选择地测试两个版本的代码。
返回子主题
示例 5：将条件编译与不同版本的 Oracle 数据库结合使用
以下示例演示了 DBMS_DB_VERSION 常量与条件编译的结合使用。其中对 Oracle 数据库版本和发布均进行了检查。这个 CHECK_VERSIONS 过程使用 COMMIT WRITE IMMEDIATE NOWAIT 命令（已在 Oracle 数据库 10g 第 2 版中引入）。如果在 Oracle 数据库早期版本中执行该过程，则无法识别该命令。因此，该过程 DBMS_DB_VERSION 程序包检查数据库的版本。如果返回的版本低于 10.2，那么将使用常规的 COMMIT 提交事务。当在版本为 10.2 或更高版本的数据库中执行该过程时，将使用 COMMIT WRITE IMMEDIATE NOWAIT 命令完成该事务。
1. 现在可以测试 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;
/
2. 现在，在 SQL*Plus 窗口中执行以下命令来运行 check_version 过程。
exec check_version quit
check_version 的输出说明：无论版本如何，该过程都能成功完成，而且对最终用户而言是透明的。因此，没有理由在不同的数据库版本中使用不同的程序单元。
返回子主题
通过 INDICES OF 和 VALUES OF 关键字了解批量绑定
以下示例演示了如何使用 INDICES OF 和 VALUES OF 关键字在 PL/SQL 中进行批量绑定。
当以编程方式对记录集合（紧密集合）进行验证，并从该集合中删除无效记录（不符合指定标准的记录）时，可以使用 INDICES OF 关键字。这会产生一个有效元素的稀疏集合，随后必须将该集合批量插入表中。通过使用 INDICES OF 关键字，避免了丢失记录异常的发生。
当必须将记录集合（稀疏或紧密集合）根据某个可以或不可以复制某些记录的条件复制到一个或多个集合变量，然后将这些记录插入到表中时，可以使用 VALUES OF 关键字。使用“VALUES OF”语法，结合其元素均为原始集合内选定记录的指针的指针数组，可以高效地完成此任务。这降低了创建多个数据副本的需要。异常处理按每个指针记录执行，这意味着如果两个或多个指针记录指向同一“原始”数据，则报告的任何异常将指示迭代号（指针元素号）。
本示例使用 EMPLOYEES 表构建一个稀疏的员工集合。
1. 必须先根据现有的 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);
2. 然后，创建 P_BULK_BIND 程序包。该程序包包含使用三种不同的方法创建稀疏集合并将记录批量插入到 NEW_EMPLOYEES 表中的各种过程。
@cr_p 单击cr_p.sql 查看该脚本中包含的内容。

3. 为了解批量绑定如何在 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 语法处理了剩余记录，但如果已删除的记录数很大，那么这也将导致性能降低。
4. 下一步是使用 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); ... 这次没有因缺失记录而引发异常。
5. 也可以通过将输入参数集设置为 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 中一个新的异常处理功能。
6. 另一个方法是使用 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 步中的结果相同：第五个和第十九个迭代引发异常。
7. 要查看报告迭代号（或指针元素号）的效果（与报告错误记录索引相对），可以在 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; ...
返回主题列表
总结
在本教程中，您学习了如何：
如何执行 PL/SQL 条件编译
了解如何放宽 DBMS_OUTPUT PL/SQL 程序包的总体限制。
了解批量绑定特性