高效的 PL/SQL 編碼

可在發(fā)生不同觸發(fā)事件時(shí)觸發(fā)多次的觸發(fā)器，可以強(qiáng)制同一類型的觸發(fā)器按序執(zhí)行，新的 CONTINUE 語句是令 PL/SQL 編程更加輕松的新法寶。

下載 Oracle 數(shù)據(jù)庫 11g

自推出以來，PL/SQL 就一直是在 Oracle 數(shù)據(jù)庫中編程的首選語言。經(jīng)過一段時(shí)間的發(fā)展，我們看到，由于該語言可以實(shí)現(xiàn)越來越多需要較少編碼的功能，它已經(jīng)演變?yōu)橐粋€(gè)綜合的開發(fā)平臺。Oracle 數(shù)據(jù)庫 11g 使得 PL/SQL 編碼對程序員更加高效。在本文中，您將通過某些示例簡單了解這個(gè)新功能。

復(fù)合觸發(fā)器

請考慮一個(gè)賓館數(shù)據(jù)庫：賓館房間的預(yù)訂記錄在名為 BOOKINGS 的表中。您還希望將對該表的更改記錄到一個(gè)跟蹤表 — 有些類似于審計(jì)，但稍有不同：您希望該操作是事務(wù)性的。在這種情況下，觸發(fā)器非常適用。

您可以使用一個(gè)小型的 after-update 行觸發(fā)器，將舊值和新值連同更改者一起記錄到 BOOKINGS_HIST 表中。到目前為止，一切都沒問題。

但這里有一個(gè)小問題。after-update 行觸發(fā)器將針對每一行觸發(fā)，但某些預(yù)訂是批量更改的，在一個(gè)事務(wù)中更新幾百行。單獨(dú)的 after-update 行觸發(fā)器針對這些行中的每一行觸發(fā)，并且每個(gè)執(zhí)行在 bookings_hist 表中插入一條記錄，因此性能不是最佳的。

更好的方法是批處理這些插入，并將它們批量插入 bookings_hist 表。您可以使用一系列復(fù)雜的觸發(fā)器來完成這個(gè)任務(wù)。方法是：在行觸發(fā)器中，將要插入 bookings_hist 表的值放到一個(gè)集合中，然后在 after-update-statement 觸發(fā)器（只觸發(fā)一次）中，將集合中的數(shù)據(jù)加載到 bookings_hist 表。由于實(shí)際插入只發(fā)生一次，因此該過程比在每一行上插入要快。

但它們是不同代碼片段中的兩個(gè)不同的觸發(fā)器。將集合變量從一個(gè)觸發(fā)器傳遞到另一個(gè)觸發(fā)器的唯一方式是，在程序包規(guī)范中創(chuàng)建一個(gè)帶有集合變量（如 VARRAY 或 PL/SQL TABLE）的程序包，在 after-update 行觸發(fā)器上填充該程序包，然后在 after-statement 觸發(fā)器上讀取它 — 這可不是一項(xiàng)簡單的任務(wù)。相反，如果您將所有觸發(fā)器都放到一個(gè)代碼片段中，不是更簡單嗎？

在 Oracle 數(shù)據(jù)庫 11g 中，您可以這么做（使用復(fù)合觸發(fā)器）。復(fù)合觸發(fā)器實(shí)際上是作為一個(gè)整體定 義的四個(gè)不同的觸發(fā)器。例如，UPDATE 復(fù)合觸發(fā)器將 before statement、before row、after statement 和 after row 都合并到一個(gè)復(fù)合觸發(fā)器中。這是一個(gè)單一代碼片段，因此您可以像任何其他單一 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
... 在語句前執(zhí)行一次 ...
Before Row
... 在操作前每行執(zhí)行一次 ...
After Row
... 在操作后每行執(zhí)行一次 ...
After Statement
... 每條語句執(zhí)行一次 ...

您將看到，該代碼是單一代碼片段，但每個(gè)部分都在不同的點(diǎn)執(zhí)行。

在前面的示例中，我將 dbms_output 語句放在了不同的點(diǎn)上，以顯示每個(gè)部分沿著哪些點(diǎn)執(zhí)行。我更新了四行，其 booking_id 是 100、101、102 和 103，您可以看到，它調(diào)用了 before-statement 和 after-statement 觸發(fā)器（每個(gè)一次）以及行觸發(fā)器（before 和 after，每行一次）。（在前面的示例中，不需要 before-statement 或 before-row 觸發(fā)器，但我還是將它們放在那里以演示該功能。）

如果您查看 bookings_hist 表，將看到現(xiàn)在有四條記錄（每個(gè) 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

復(fù)合觸發(fā)器的一個(gè)真正有用的功能是，PL/SQL 代碼中的狀態(tài)對象（如變量、程序包等）在觸發(fā)器被觸發(fā)時(shí)進(jìn)行實(shí)例化，而在觸發(fā)器觸發(fā)結(jié)束后，狀態(tài)將被清除干凈。在上面的示例中，您可以看到我既沒有初始化 集合，也沒有刪除集合中的內(nèi)容。所有這些都是自動完成的，不需要我干預(yù)。

觸發(fā)器中的按序執(zhí)行

自 Oracle8 以來，您就能夠在一個(gè)表上定義同一類型的多個(gè)觸發(fā)器 — 例如，兩個(gè)都是在同一個(gè)表的每行后執(zhí)行插入操作的觸發(fā)器。觸發(fā)器的類型決定了執(zhí)行順序：before statement、before row、after statement 和 after row。但是，如果您有兩個(gè) after-row 觸發(fā)器（T1 和 T2），應(yīng)該先觸發(fā)哪個(gè)呢？

同一類型的觸發(fā)器的執(zhí)行有些隨機(jī)，或者至少不保證遵循某個(gè)模式。這會引發(fā)問題嗎？我們來看一個(gè)名為 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)

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;
/

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

在這種情況下，觸發(fā)器的執(zhí)行順序 非常重要。如果 tr_pay_risk_rating 沒有在另一個(gè)觸發(fā)器之前觸發(fā)，就無法設(shè)置正確的變量，并且設(shè)置將無法正確實(shí)現(xiàn)要求。在以前，唯一可能的方法是，將所有邏輯放在一段代碼中，并通過在代碼中對邏輯進(jìn)行排序來強(qiáng)制執(zhí)行。

在 Oracle 數(shù)據(jù)庫 11g 中，您可以在觸發(fā)器創(chuàng)建腳本中放置一條子句，來強(qiáng)制對觸發(fā)器進(jìn)行排序。以下是帶有該子句的觸發(fā)器的上半部分：

create or replace trigger tr_pay_follow_up
before update
on payments
for each row
follows tr_pay_risk_rating
begin
... and so on ...

該子句 (FOLLOWS <triggerName>) 強(qiáng)制觸發(fā)器在指定觸發(fā)器之后觸發(fā)。您可以通過運(yùn)行在前面看到的更新腳本來進(jìn)行測試。

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.

觸發(fā)器排序可讓您獲得模塊化代碼的優(yōu)勢，同時(shí)確保它們能夠以正確的順序執(zhí)行。

如果什么都不做，使用 CONTINUE

在全部功能中，直到現(xiàn)在，PL/SQL 還缺少一個(gè)重要語法：如何指示它什么都不做，轉(zhuǎn)至循環(huán)結(jié)尾，然后再次循環(huán)。

在 Oracle 數(shù)據(jù)庫 11g 中，PL/SQL 具有一個(gè)名為 CONTINUE 的新結(jié)構(gòu)，可在循環(huán)中使用。該語句可將邏輯移到循環(huán)結(jié)尾，然后再移到循環(huán)開頭。下面是一個(gè)小型示例，演示了當(dāng)計(jì)數(shù)器不是 10 的倍數(shù)時(shí)，控制如何移到循環(huán)結(jié)尾。

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 ...

begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line (‘-> outer=‘||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;
/

-> outer=1
..-> inner=1
..-> inner=2
-> outer=2
..-> inner=1
..-> inner=2
-> outer=3
..-> inner=1
..-> inner=2
... and so on ...

begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line (‘-> outer=‘||outer);
for inner in 1..10 loop
continue OuterLoop when (myfunc = 1);
dbms_output.put_line (‘..-> inner=‘||inner);
end loop;
end loop;
end;
/

勿庸置疑，您只能在循環(huán)內(nèi)（它只有在這里才有意義）使用這個(gè)結(jié)構(gòu)。如果您嘗試在循環(huán)外使用它，將出現(xiàn)編譯器錯誤。

井井有條的序列

以前，如果您需要在 PL/SQL 程序中使用序列，則在該版本推出之前，您需要使用 SELECT <Seq>.NEXTVAL INTO <VariableName> FROM DUAL 之類的結(jié)構(gòu)。

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 執(zhí)行某些操作

許多 PL/SQL 程序員都采用忽略 OTHERS 異常的危險(xiǎn)做法，如下所示：

when OTHERS then
NULL;

Oracle 數(shù)據(jù)庫 11g 在這方面很有幫助。它具有一個(gè)名為 PLW-06009 的新警告，可以在編譯時(shí)向您警告此類問題。下面是一個(gè)例子。

create or replace procedure myproc as
l_dummy varchar2(1);
begin
select dummy
into l_dummy
from dual;
exception
when OTHERS then
null;
end;

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

禁用觸發(fā)器

在可用性較高的生產(chǎn)系統(tǒng)中，您經(jīng)常會看到一個(gè)用于應(yīng)用更改的狹窄的更改窗口。以下是這些環(huán)境中常見的、令人沮喪的“Catch-22”案例：您希望 在表中添加一個(gè)觸發(fā)器以便執(zhí)行腳本，但在更改窗口中創(chuàng)建觸發(fā)器時(shí)，會由于某個(gè)愚蠢的、可避免的原因（例如，缺少同義詞）而出現(xiàn)編譯錯誤。您希望以前已經(jīng)創(chuàng) 建了觸發(fā)器，但在您創(chuàng)建觸發(fā)器時(shí)，它是啟用狀態(tài)，這無法在更改窗口以外進(jìn)行操作。您可以做些什么？

在 Oracle 數(shù)據(jù)庫 11g 中，這種情況不再是一個(gè)問題；您可以創(chuàng)建一個(gè)最初為禁用狀態(tài)的觸發(fā)器，從而允許您測試所有編譯錯誤。稍后，在更改窗口中，您可以啟用它。以下是該觸發(fā)器的創(chuàng)建方法：

create or replace trigger tr_t
after insert on t
for each row
disable
begin
insert into t1 (a) values (:new.col_a);
end;
/

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

函數(shù)中的參數(shù)名

請考慮以下簡單函數(shù)：

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)

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

SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
MYFUNC(P_PARAM1=>1,P_PARAM2=>1)
-------------------------------
2
1 row selected.

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

動態(tài)游標(biāo)和 REF CURSOR 的可交換性

您一定知道本機(jī)動態(tài)游標(biāo)的作用是多么重要，特別是在進(jìn)行調(diào)用之前不知道要查詢的確切內(nèi)容的情況下。您可能還通過 DBMS_SQL 使用過動態(tài) PL/SQL。這兩種方法均有其各自的優(yōu)勢。但是，如果您開始使用其中一種方法開發(fā)程序，稍后又希望切換到另一種方法，該怎么辦呢？

在 Oracle 數(shù)據(jù)庫 11g 中，這個(gè)過程相當(dāng)簡單。所提供的程序包 DBMS_SQL 具有一個(gè)新函數(shù) TO_REFCURSOR，該函數(shù)可將 DBMS_SQL 動態(tài)游標(biāo)轉(zhuǎn)換為 ref cursor。以下是此類轉(zhuǎn)換的一個(gè)示例：

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;

同樣，如果您希望將本機(jī)動態(tài) SQL 轉(zhuǎn)換為 REF CURSOR，需要調(diào)用另一個(gè)函數(shù) TO_CURSOR_NUMBER：

cur_handle := dbms_sql.to_cursor_number (c_ref_cur);

假設(shè)您在編譯時(shí)知道綁定，但不知道選擇列表；您可以在開始通過 ref cursor 使用本機(jī)動態(tài) sql，稍后將其更改為 dbms_sql，以描述并通過游標(biāo)獲取列。

結(jié)論