Oracle 数据库11g新特性之高效 PL/SQL 编码
转自：http://space.itpub.net/1384/viewspace-217959
自推出以来，PL/SQL 就一直是在 Oracle 数据库中编程的首选语言。经过一段时间的发展，我们看到，由于该语言可以实现越来越多需要较少编码的功能，它已经演变为一个综合的开发平台。Oracle 数据库 11g使得 PL/SQL 编码对程序员更加高效。在本文中，您将通过某些示例简单了解这个新功能。
复合触发器
请考虑一个宾馆数据库：宾馆房间的预订记录在名为 BOOKINGS 的表中。您还希望将对该表的更改记录到一个跟踪表 — 有些类似于审计，但稍有不同：您希望该操作是事务性的。在这种情况下，触发器非常适用。
您可以使用一个小型的 after-update 行触发器，将旧值和新值连同更改者一起记录到 BOOKINGS_HIST 表中。到目前为止，一切都没问题。
但这里有一个小问题。after-update 行触发器将针对每一行触发，但某些预订是批量更改的，在一个事务中更新几百行。单独的 after-update 行触发器针对这些行中的每一行触发，并且每个执行在 bookings_hist 表中插入一条记录，因此性能不是最佳的。
更好的方法是批处理这些插入，并将它们批量插入 bookings_hist 表。您可以使用一系列复杂的触发器来完成这个任务。方法是：在行触发器中，将要插入 bookings_hist 表的值放到一个集合中，然后在 after-update-statement 触发器（只触发一次）中，将集合中的数据加载到 bookings_hist 表。由于实际插入只发生一次，因此该过程比在每一行上插入要快。
但它们是不同代码片段中的两个不同的触发器。将集合变量从一个触发器传递到另一个触发器的唯一方式是，在程序包规范中创建一个带有集合变量（如 VARRAY 或 PL/SQL TABLE）的程序包，在 after-update 行触发器上填充该程序包，然后在 after-statement 触发器上读取它 — 这可不是一项简单的任务。相反，如果您将所有触发器都放到一个代码片段中，不是更简单吗？
在 Oracle 数据库 11g中，您可以这么做（使用复合触发器）。复合触发器实际上是作为一个整体定义的四个不同的触发器。例如，UPDATE 复合触发器将 before statement、before row、after statement 和 after row 都合并到一个复合触发器中。这是一个单一代码片段，因此您可以像任何其他单一 PL/SQL 代码一样来传递变量。
下面我们来考虑一个示例。添加了行编号，以帮助说明。1 create or replace trigger tr_bookings_track
2 for update of booking_dt
3 on bookings
4 compound trigger
5 type ty_bookings_hist is table of bookings_hist%rowtype
6 index by pls_integer;
7 coll_bookings_hist ty_bookings_hist;
8 ctr pls_integer := 0;
9 before statement is
10 begin
11 dbms_output.put_line('In before statement');
12 end before statement;
13 before each row is
14 begin
15 dbms_output.put_line('In before each row');
16 end before each row;
17 after each row is
18 begin
19 ctr := ctr + 1;
20 dbms_output.put_line('In after each row. booking_id='||:new.booking_id);
21 coll_bookings_hist(ctr).booking_id := :new.booking_id;
22 coll_bookings_hist(ctr).mod_dt := sysdate;
23 coll_bookings_hist(ctr).mod_user := user;
24 coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
25 coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
26 end after each row;
27 after statement is
28 begin
29 dbms_output.put_line('In after statement');
30 forall counter in 1..coll_bookings_hist.count()
31 insert into bookings_hist
32 values coll_bookings_hist(counter);
33 end after statement;
34 end tr_bookings_track;
为了更好地了解触发器的工作方式，我们来执行一个示例更新操作，该操作将更新四行。
update bookings set booking_dt = sysdate where booking_id between 100 and 103;输出如下：
In before statement In before each row In after each row. booking_id=100 In before each row In after each row. booking_id=101 In before each row In after each row. booking_id=102 In before each row In after each row. booking_id=103 In after statement注意复合触发器的操作方式。粗略地说，它具有四个部分：
Before Statement
...在语句前执行一次...
Before Row
...在操作前每行执行一次...
After Row
...在操作后每行执行一次...
After Statement
...每条语句执行一次...
您将看到，该代码是单一代码片段，但每个部分都在不同的点执行。
在前面的示例中，我将 dbms_output 语句放在了不同的点上，以显示每个部分沿着哪些点执行。我更新了四行，其 booking_id 是 100、101、102 和 103，您可以看到，它调用了 before-statement 和 after-statement 触发器（每个一次）以及行触发器（before 和 after，每行一次）。（在前面的示例中，不需要 before-statement 或 before-row 触发器，但我还是将它们放在那里以演示该功能。）
如果您查看 bookings_hist 表，将看到现在有四条记录（每个 booking_id 一条），但这四条记录是在语句末尾批量插入的，而不是针对每一行更新：
BOOKING_ID MOD_DT MOD_USER OLD_BOOKI NEW_BOOKI ---------- --------- ------------------------------ --------- --------- 100 27-SEP-07 ARUP 28-AUG-07 27-SEP-07 101 27-SEP-07 ARUP 06-AUG-07 27-SEP-07 102 27-SEP-07 ARUP 04-SEP-07 27-SEP-07 103 27-SEP-07 ARUP 15-JUN-07 27-SEP-07
复合触发器的一个真正有用的功能是，PL/SQL 代码中的状态对象（如变量、程序包等）在触发器被触发时进行实例化，而在触发器触发结束后，状态将被清除干净。在上面的示例中，您可以看到我既没有初始化集合，也没有删除集合中的内容。所有这些都是自动完成的，不需要我干预。
触发器中的按序执行
自 Oracle8 以来，您就能够在一个表上定义同一类型的多个触发器 — 例如，两个都是在同一个表的每行后执行插入操作的触发器。触发器的类型决定了执行顺序：before statement、before row、after statement 和 after row。但是，如果您有两个 after-row 触发器（T1 和 T2），应该先触发哪个呢？
同一类型的触发器的执行有些随机，或者至少不保证遵循某个模式。这会引发问题吗？我们来看一个名为 PAYMENTS 的表的示例，如下所示：
Name Null?Type ----------------------------------------- -------- ---------------------------- PAY_ID NUMBER(10) CREDIT_CARD_NO VARCHAR2(16) AMOUNT NUMBER(13,2) PAY_MODE VARCHAR2(1) RISK_RATING VARCHAR2(6) FOLLOW_UP VARCHAR2(1)需要根据支付类型和数量来计算风险率，并将其存储在 RISK_RATING 列中。下面这个简单的 before update 行触发器很好地完成了这个任务：
create or replace trigger tr_pay_risk_rating
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_risk_rating');
if (:new.amount) < 1000 then
:new.risk_rating := 'LOW';
elsif (:new.amount < 10000) then
if (:new.pay_mode ='K') then
:new.risk_rating := 'MEDIUM';
else
:new.risk_rating := 'HIGH';
end if;
else
:new.risk_rating := 'HIGH';
end if;
end;
/
现在，假设某人增加了另一个要求：应该标记某些基于 RISK_RATING、PAY_MODE 等列的项，以便在名为 FOLLOW_UP 的新列中跟踪。您可能已经修改了上述触发器，但保留现有代码不变并创建同一类型的新触发器（before update 行）始终是一个良好的策略，如下所示。（我已经将 dbms_output 语句放在代码中，以演示触发器的触发方式。）
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_follow_up');
if (
(:new.risk_rating = 'HIGH' and :new.pay_mode = 'C')
or (:new.risk_rating = 'MEDIUM' and :new.pay_mode = 'K')
or (substr(:new.credit_card_no,1,5) = '23456')
) then
:new.follow_up := 'Y';
else
:new.follow_up := 'N';
end if;
end;
/
现在，如果您更新表：
SQL> get upd_pay 1 update payments set 2 credit_card_no = '1234567890123456', 3 amount = 100000, 4* pay_mode = 'K' SQL> @upd_pay This is tr_pay_follow_up This is tr_pay_risk_rating 1 row updated. SQL> select * from payments; PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F ---------- ---------------- ---------- - ------ - 1 1234567890123456 100000 C HIGH N发生了什么事？risk_rating 列是 HIGH，pay_mode 列是“C”，这意味着，FOLLOW_UP 列应该是“Y”，但它却是“N”。为什么？要回答这个问题，请看一下触发器的触发顺序：tr_pay_follow_up 在 tr_pay_risk_rating 之前触发。后者将列值设为高风险。这样，当前者触发时，risk_rating 列值为空（或“N”），因此它认为条件满足。
在这种情况下，触发器的执行顺序非常重要。如果 tr_pay_risk_rating 没有在另一个触发器之前触发，就无法设置正确的变量，并且设置将无法正确实现要求。在以前，唯一可能的方法是，将所有逻辑放在一段代码中，并通过在代码中对逻辑进行排序来强制执行。
在 Oracle 数据库 11g中，您可以在触发器创建脚本中放置一条子句，来强制对触发器进行排序。以下是带有该子句的触发器的上半部分：
create or replace trigger tr_pay_follow_up before update on payments for each rowfollows tr_pay_risk_ratingbegin ... and so on ...
该子句 (FOLLOWS <triggerName>) 强制触发器在指定触发器之后触发。您可以通过运行在前面看到的更新脚本来进行测试。
SQL> @upd_pay This is tr_pay_risk_rating This is tr_pay_follow_up 1 row updated. SQL> select * from payments; PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F ---------- ---------------- ---------- - ------ - 1 1234567890123456 100000 C HIGH Y 1 row selected.列按照预期方式正确填充。另外，注意触发器的正确排序，以确定您打算执行的操作。
触发器排序可让您获得模块化代码的优势，同时确保它们能够以正确的顺序执行。
如果什么都不做，使用 CONTINUE
在全部功能中，直到现在，PL/SQL 还缺少一个重要语法：如何指示它什么都不做，转至循环结尾，然后再次循环。
在 Oracle 数据库 11g中，PL/SQL 具有一个名为 CONTINUE 的新结构，可在循环中使用。该语句可将逻辑移到循环结尾，然后再移到循环开头。下面是一个小型示例，演示了当计数器不是 10 的倍数时，控制如何移到循环结尾。
begin
for ctr in 1..100 loop
continue when mod(ctr,10) != 0;
dbms_output.put_line ('ctr='||ctr);
end loop;
end;
/
输出如下：
ctr=10 ctr=20 ctr=30 ... and so on ...CONTINUE 的另一个变体是使用循环名称。
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> uter='||outer);
for inner in 1..10 loop
continue OuterLoop when mod(inner,3) = 0;
dbms_output.put_line ('..-> inner='||inner);
end loop;
end loop;
end;
/
输出如下：
-> uter=1 ..-> inner=1 ..-> inner=2 -> uter=2 ..-> inner=1 ..-> inner=2 -> uter=3 ..-> inner=1 ..-> inner=2 ... and so on ...如果不使用静态结构，如 mod(inner,3)，您还可以使用执行某种计算的函数。
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> uter='||outer);
for inner in 1..10 loop
continue OuterLoop when (myfunc = 1);
dbms_output.put_line ('..-> inner='||inner);
end loop;
end loop;
end;
/
勿庸置疑，您只能在循环内（它只有在这里才有意义）使用这个结构。如果您尝试在循环外使用它，将出现编译器错误。
井井有条的序列
以前，如果您需要在 PL/SQL 程序中使用序列，则在该版本推出之前，您需要使用 SELECT <Seq>.NEXTVAL INTO <VariableName> FROM DUAL 之类的结构。
declare trans_id number(10); begin select myseq.nextval into trans_id from dual; end;现在不再需要这样了。您可以将序列的下一个值直接赋值给一个变量：
declare trans_id number(10); begin trans_id := myseq.nextval; end; /这就是我所说的简单性。
When OTHERS Then 执行某些操作
许多 PL/SQL 程序员都采用忽略 OTHERS 异常的危险做法，如下所示：
when OTHERS then NULL;这就好像说“当错误发生时，什么也不做；只要忽略或假装它从未发生过，它就不会再发生了。”如果世界有这么简单就好了！这个做法可能会导致漏洞百出、不稳定的代码。
Oracle 数据库 11g在这方面很有帮助。它具有一个名为 PLW-06009 的新警告，可以在编译时向您警告此类问题。下面是一个例子。
create or replace procedure myproc as l_dummy varchar2(1); begin select dummy into l_dummy from dual; exception when OTHERS then null; end;在您编译该过程时，它编译良好，没有出现任何警告，就像以前使用 10g时一样好。要启用这个警告，您必须设置该会话参数。
SQL> alter session set plsql_warnings = 'enable:all' 2 / Session altered. SQL> @others1 SP2-0804:Procedure created with compilation warnings SQL> show error Errors for PROCEDURE MYPROC: LINE/COL ERROR -------- ----------------------------------------------------------------- 8/7 PLW-06009:procedure "MYPROC" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR请注意在编译期间引发的新警告 PLW-06009。注意，这只是一个警告；编译顺利进行。您可以执行该过程，但应考虑警告状况！
禁用触发器
在可用性较高的生产系统中，您经常会看到一个用于应用更改的狭窄的更改窗口。以下是这些环境中常见的、令人沮丧的“Catch-22”案例：您希望在表中添加一个触发器以便执行脚本，但在更改窗口中创建触发器时，会由于某个愚蠢的、可避免的原因（例如，缺少同义词）而出现编译错误。您希望以前已经创建了触发器，但在您创建触发器时，它是启用状态，这无法在更改窗口以外进行操作。您可以做些什么？
在 Oracle 数据库 11g中，这种情况不再是一个问题；您可以创建一个最初为禁用状态的触发器，从而允许您测试所有编译错误。稍后，在更改窗口中，您可以启用它。以下是该触发器的创建方法：
create or replace trigger tr_t after insert on t for each rowdisablebegin insert into t1 (a) values (:new.col_a); end; /现在，如果您检查状态：SQL> select status 2> from user_triggers 3> where trigger_name = 'TR_T' 4> / STATUS -------- DISABLED 即使创建的触发器为禁用状态，它也不能带有错误。因此，如果您尝试创建带有错误的触发器（例如，使用不存在的“M”表）：
1 create or replace trigger tr_t 2 after insert on t 3 for each row 4 disable 5 begin 6 insert into m (a) values (:new.col_a); 7* end; SQL> / Warning:Trigger created with compilation errors. SQL> show error Errors for TRIGGER TR_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/3 PL/SQL:SQL Statement ignored 2/15 PL/SQL:ORA-00942:table or view does not exist对于更改控制过程来说，该特性非常有用。另一个出色的应用是在特定点启用触发器。例如，假设您要使用触发器构建一个审计解决方案，而 audit_table 的旧记录尚未清除。您可以先将触发器创建为禁用状态，并在稍后表就绪时启用它。
函数中的参数名
请考虑以下简单函数：
create or replace function myfunc ( p_param1 number, p_param2 number ) return number is begin return p_param1 + p_param2; end; /该函数执行的操作很简单，但足以说明概念。由于有两个参数，您可以通过两个方法来调用函数：即，将参数作为位置值传递，如：
myfunc (1,2)或者，作为命名参数传递：
myfunc ( p_param1 => 1, p_param2 => 2)但是，如果在 select 语句中使用，后者会导致问题出现。在 Oracle 数据库 10g中，如果您执行以下语句：
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;将出现错误：
select myfunc (p_param1=>1,p_param2=>1) from dual
*
ERROR at line 1:
ORA-00907:missing right parenthesis
在 Oracle 数据库 11g中，您可以随意使用以下表示法：
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
MYFUNC(P_PARAM1=>1,P_PARAM2=>1)
-------------------------------
2
1 row selected.
...这非常有效。您可以为后一个指定命名表示法；第一个必须是位置值。例如，以下语句有效，其中，参数 p_param1 设为 1：
select myfunc (1,p_param2=>2) from dual但是，以下语句无效（位置参数在结尾）：
SQL> select myfunc (p_param1=>1,2) from dual;
select myfunc (p_param1=>1,2) from dual
*
ERROR at line 1:
ORA-06553:PLS-312:a positional parameter association may not follow a named association
动态游标和 REF CURSOR 的可交换性
您一定知道本机动态游标的作用是多么重要，特别是在进行调用之前不知道要查询的确切内容的情况下。您可能还通过 DBMS_SQL 使用过动态 PL/SQL。这两种方法均有其各自的优势。但是，如果您开始使用其中一种方法开发程序，稍后又希望切换到另一种方法，该怎么办呢？
在 Oracle 数据库 11g中，这个过程相当简单。所提供的程序包 DBMS_SQL 具有一个新函数 TO_REFCURSOR，该函数可将 DBMS_SQL 动态游标转换为 ref cursor。以下是此类转换的一个示例：
1 create or replace procedure list_trans_by_store 2 ( 3 p_store_id number 4 ) 5 is 6 type num_tab is table of number index by binary_integer; 7 type type_refcur is ref cursor; 8 c_ref_trans_cur type_refcur; 9 c_trans_cur number; 10 trans_id num_tab; 11 trans_amt num_tab; 12 ret integer; 13 l_stmt clob; 14 begin 15 c_trans_cur := dbms_sql.open_cursor; 16 l_stmt := 17 'select trans_id, trans_amt from trans where store_id = :store_id'; 18 dbms_sql.parse(c_trans_cur, l_stmt, dbms_sql.native); 19 dbms_sql.bind_variable(c_trans_cur, 'store_id', p_store_id); 20 ret := dbms_sql.execute(c_trans_cur); 21 c_ref_trans_cur := dbms_sql.to_refcursor(c_trans_cur); 22 fetch c_ref_trans_cur bulk collect into trans_id, trans_amt; 23 for ctr in 1 .. trans_id.count loop 24 dbms_output.put_line(trans_id(ctr) || ' ' || trans_amt(ctr)); 25 end loop; 26 close c_ref_trans_cur; 27* end;假设您希望编写一个通用过程，但您在编译时不知道 select 子句中的列列表。这就是本机动态 SQL 的用武之地；您可以为此定义一个 ref cursor。现在，要使其更有趣，假设您也不知道绑定变量，这样 dbms_sql 将更加适用。如何以最少的代码完成这个复杂要求呢？很简单：只需在开始使用 dbms_sql 完成绑定部分，然后稍后再针对另一部分将其转换为 ref cursor。
同样，如果您希望将本机动态 SQL 转换为 REF CURSOR，需要调用另一个函数 TO_CURSOR_NUMBER：
cur_handle := dbms_sql.to_cursor_number (c_ref_cur);在进行该调用之前，必须先打开 c_ref_cur 变量指定的 ref cursor。进行该调用之后，ref cursor 的使命就完成了；可以将其仅作为一个 dbms_sql 游标来操纵。
假设您在编译时知道绑定，但不知道选择列表；您可以在开始通过 ref cursor 使用本机动态 sql，稍后将其更改为 dbms_sql，以描述并通过游标获取列。
结论
您可以看到，Oracle 数据库 11g包含了多项改进，可以帮助您编写高效的 PL/SQL 代码。