Oracle 資料庫11g新特性之高效 PL/SQL 編碼

自推出以來，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 row	
follows tr_pay_risk_rating
begin
... and so on ...

該子句 (FOLLOWS ) 強制觸發器在指定觸發器之後觸發。您可以通過執行在前面看到的更新指令碼來進行測試。

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
<>
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
<>
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 .NEXTVAL INTO 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 row
disable
begin
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 程式碼。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/1384/viewspace-217959/，如需轉載，請註明出處，否則將追究法律責任。