簡介
本文為在以下場景在 DB2 9.7 中使用匿名塊提供指導：
測試、診斷和開發新的 PL/SQL 存儲過程
使用 PL/SQL 模擬應用程序運行
使用 PL/SQL 動態地 構建復雜的即席查詢和報告
先決條件和系統需求
本文是為從 Oracle 遷移到 DB2 的 PL/SQL 應用程序開發人員和數據庫管理員准備的。您應該理解 PL/SQL 過程語言。SQL PL 開發人員應該使用 DB2 原生復合 SQL 語句提供的對應函數。
為了使用本文提供的例子，您必須安裝 DB2 9.7 Workgroup 或 Enterprise Edition for Linux, UNIX, and Windows。從 參考資料 部分可以下載 DB2 9.7 for Linux, UNIX, and Windows 的免費試用版。
使用例子
您可以使用各種工具運行本文的例子，包括 DB2 命令行處理器（CLP）、命令行實用程序（CLPPLUSU）或可視化工具（比如 Optim Development Studio）。如果您計劃從 CLP 運行本文的例子，那麼需要運行 SET SQLCOMPAT PLSQL 命令，以將新行上的前斜槓字符（/）識別為 PL/SQL 語句的終止字符。
為了在 DB2 中支持 PL/SQL 和 Oracle 數據類型，創建數據庫時 DB2_COMPATIBILITY_VECTOR 注冊表變量必須設置為 ORA，如清單 1 所示。
清單 1. 設置 DB2_COMPATIBILITY_VECTOR 注冊表變量
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2 create db test
在您創建數據庫之後，清單 2 提供的代碼創建了一個簡單的電子商務 PL/SQL 應用程序，用於管理在線訂單。您還將用樣例數據填充數據庫的表。
清單 2. 示例代碼
訪問示例代碼
理解匿名塊
匿名塊是能夠動態地創建和執行過程代碼的 PL/SQL 結構，而不需要以持久化的方式將代碼作為數據庫對象儲存在系統目錄中。匿名塊的概念類似於 UNIX shell 腳本，它支持將幾個手動輸入的命令合並到一個組，然後作為一個步驟執行。顧名思義，匿名塊沒有名稱，因此不能從其他對象引用它們。盡管匿名塊是動態地構建的，但可以輕松地將它們儲存為操作系統文件中的腳本，以重復執行。
匿名塊是標准的 PL/SQL 塊。它們的語法和遵循的規則適用於所有 PL/SQL 塊，包括聲明、變量范圍、執行、異常處理以及 SQL 和 PL/SQL 的使用。
匿名塊的編譯和執行被合並到一個步驟中，而 PL/SQL 存儲過程的定義改變時，在使用它之前必須進行重新定義。這是匿名塊與持久化命名數據庫對象（比如存儲過程和用戶定義函數）相比的一個顯著優勢，因為它縮短了在代碼中實現更改和實際執行之間的時間間隔。這個優點讓匿名塊在診斷問題、原型化和測試過程代碼時發揮重要作用，因為這些任務通常需要多個更改-執行過程。
匿名塊的另一個好處是它們不需要創建任何依賴項，在創建對象時也不需要任何特權，從而避免在生產環境中出現沖突。匿名塊能夠靈活地基於簡單的選擇特權運行任何操作序列，並且允許您在不創建或指向任何現有數據庫對象的情況下進行測試。
可以從以下位置執行匿名塊：
SQL（例如，在 EXECUTE IMMEDIATE 語句中）
DB2 API，比如 JDBC 和 ODBC
各種 DB2 工具，包括 CLP、CLPPlus、Optim Database Administrator 和 Optim Development Studio
使用匿名塊原型化 PL/SQL 代碼
在清單 3 中，應用程序開發人員希望實現一個機制來讓業務需求與在 CUSTOMER 表中定義的客戶進行通信（通過電子郵件）。為了滿足需求，他決定編寫一個簡單的原型 PL/SQL 匿名塊，以向 CUSTOMER 表中的客戶發送包含消息的電子郵件。當業務需求最終實現之後，可以改進原型化匿名塊並輕松將其轉換成新的 PL/SQL 存儲過程。注意，這個匿名塊使用新的內置包，包括 UTL_SMTP（用於發送電子郵件的包）和 DBMS_OUTPUT（向標准輸出寫入消息的包），這兩個包都是 DB2 9.7 的一部分。
清單 3. 一個簡單的原型 PL/SQL 匿名塊，它向 CUSTOMER 表中的客戶發送包含消息的電子郵件
SET SERVEROUTPUT ON
/
DECLARE
conn UTL_SMTP.connection;
reply UTL_SMTP.reply;
msg VARCHAR2(1024);
sender VARCHAR2(255) DEFAULT 'demo\@ca.ibm.com';
recipIEnts VARCHAR2(255);
subject VARCHAR2(255) DEFAULT 'Quick notification';
crlf VARCHAR2(2);
BEGIN
crlf := UTL_TCP.CRLF;
FOR row IN (SELECT first_name, email FROM customer) LOOP
DBMS_OUTPUT.PUT_LINE('Sending test email to customer ' || row.first_name || '...');
recipIEnts := row.email;
msg := 'FROM: ' || sender || crlf ||
'TO: ' || recipIEnts || crlf ||
'SUBJECT: ' || subject || crlf ||
crlf ||
'Hi ' || row.first_name || ', this is a test notification.';
UTL_SMTP.OPEN_CONNECTION('smtp_server.ibm.com', 25, conn, 10, reply );
UTL_SMTP.HELO(conn, 'localhost');
UTL_SMTP.MAIL(conn, sender);
UTL_SMTP.RCPT(conn, recipIEnts);
UTL_SMTP.DATA(conn, msg);
UTL_SMTP.QUIT(conn);
END LOOP;
END;
/
Output:
Sending test email to customer Mike...
Sending test email to customer Joan...
Sending test email to customer Colin...
Sending test email to customer Graham...
Sending test email to customer Patsy...
使用匿名塊模擬應用程序運行
如前所述，匿名塊的最常見用法就是調用過程語言對象，通常是為了進行測試。清單 4 顯示了如何通過 PL/SQL 匿名塊的幫助模擬應用程序的運行。清單 4 的代碼在模擬應用程序運行的同時捕獲性能度量指標。該匿名塊模擬為來自現有 CUSTOMER 表的隨機客戶創建 10 個隨機訂單。它還輸出測試的開始和終止時間，以及每次運行的訂單細節。您可以輕松地將訂單數改為從 10 到 20，然後重新運行這個匿名塊而不需要再進行編譯。您還可以為測試添加更多的性能度量指標。
清單 4. 在 PL/SQL 匿名塊的幫助下模擬應用程序的運行
SET SERVEROUTPUT ON
/
DECLARE
v_customer_id customer.customer_id%TYPE;
product_id product.product_id%TYPE:=1;
o_order_id orders.order_id%TYPE;
v_test_start TIMESTAMP;
BEGIN
SELECT CURRENT TIMESTAMP INTO v_test_start FROM dual;
FOR k IN 1..10 LOOP
SELECT customer_id INTO v_customer_id FROM customer ORDER BY RAND() FETCH FIRST 1
ROW ONLY;
FOR i IN (
SELECT product_id, CAST(RAND()*50 as integer)+1 as quantity
FROM product
WHERE ROWNUM < CAST(RAND()*10 as integer))
LOOP
add_item_to_shopping_cart(i.product_id, i.quantity);
END LOOP;
create_order(v_customer_id, o_order_id);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('Test start: ' || v_test_start);
DBMS_OUTPUT.PUT_LINE('Test end : ' || CURRENT TIMESTAMP);
END;
/
Output:
Customer : Mike, Smith
Order creation : 07-06-2009
Estimated Delivery : 07-09-2009
Status : Shipped
Total price : $ 150,615.44
--------------------------------------------
Customer : Joan, Jett
Order creation : 07-06-2009
Estimated Delivery : 07-09-2009
Status : Shipped
Total price : $ 159,445.77
...
...
...
Customer : Colin, Taylor
Order creation : 07-06-2009
Estimated Delivery : 07-09-2009
Status : Shipped
Total price : $ 266,242.78
--------------------------------------------
Test start: 2009-07-06-11.10.11.500000
Test end : 2009-07-06-11.10.11.546000
使用匿名塊生成即席報告
一個常見的報告需求就是將數據從多個列合並到一個字符串中。這可以通過編寫帶有復雜遞歸的純 SQL 語句來實現。不過，您可以使用帶有動態格式化選項和簡單邏輯流程的匿名塊來更快地實現該目的。
清單 5 顯示了如何在匿名塊的幫助下創建一個即席報告。清單 5 的代碼獲取一個包含所有從商店訂購產品的客戶的列表，以及上個月的所有訂單的總價值。在一行顯示名稱，名稱之間用逗號分隔。
清單 5. 在匿名塊的幫助下創建即席報告
SET SERVEROUTPUT ON
/
DECLARE
v_customer_names VARCHAR2(4000);
v_total_sales NUMBER(19,2);
BEGIN
DBMS_OUTPUT.PUT_LINE(' Last Month Sales Report ');
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
DBMS_OUTPUT.PUT('Customer List: ');
FOR row IN
(SELECT distinct(a.customer_id),first_name, last_name FROM customer a, orders b
WHERE a.customer_id=b.order_id AND b.creation_time>CURRENT DATE -1 month)
LOOP
v_customer_names := v_customer_names || '"' || row.first_name || ' ' ||
row.last_name || '", ';
END LOOP;
IF(LENGTH(v_customer_names) > 0) THEN
v_customer_names := SUBSTR(v_customer_names,1, LENGTH(v_customer_names)-2);
ELSE
v_customer_names := 'None';
END IF;
DBMS_OUTPUT.PUT_LINE(v_customer_names);
SELECT NVL(SUM(total_price),0) INTO v_total_sales FROM orders WHERE creation_time>
CURRENT DATE - 1 month;
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
DBMS_OUTPUT.PUT_LINE('Total Sales: ' || TO_CHAR(v_total_sales, '$99,999,999.99'));
END;
/
Output:
Last Month Sales Report ---------------------------------------
Customer List: "Mike Smith", "Joan Jett", "Colin Taylor", "Graham Norton", "Patsy Stone"
---------------------------------------
Total Sales: $ 49,772.56
結束語
本文介紹了以下內容：
在 DB2 9.7 中引入的 PL/SQL 匿名塊特性。
匿名塊的概念。
匿名塊如何為過程代碼的測試、原型化和問題診斷提供便利。
匿名塊如何模擬應用程序的運行。
如何使用匿名塊實現強大的即席報告。
在 PL/SQL 匿名塊的幫助下，您可以通過使用現有的 PL/SQL 腳本，或通過使用能夠在其他數據庫管理系統中工作的 PL/SQL 和 SQL 語句，在 DB2 環境中快速實現 PL/SQL 解決方案。
