DB2 9.7 進階開發：善用 PL/SQL 匿名區塊加速原型開發與測試

前言

DB2 9.7 引入 PL/SQL 匿名區塊，為從 Oracle 遷移的開發者與 DBA 帶來強大利器。匿名區塊無需持久化儲存於系統目錄，即可動態編譯執行程序碼，極適合測試、診斷、原型開發、模擬應用程式行為及產生臨時報表等場景。

環境前置準備

需安裝 DB2 9.7 Workgroup/Enterprise Edition，並設定相容性向量：

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop && db2start
db2 create db test

若使用 CLP 執行範例，須先執行 SET SQLCOMPAT PLSQL 以識別「/」為 PL/SQL 終止符。

核心優勢：為何選擇匿名區塊？

• 編譯執行合一：修改即執行，無需重新建立物件，大幅縮短「改碼-驗證」迴圈。
• 零依賴、零權限衝突：不建立資料庫物件，避免生產環境鎖定與權限爭用。
• 彈性執行入口：支援 CLP、CLPPlus、JDBC/ODBC、Optim Development Studio 等多種工具。

三大實戰應用場景

場景一：快速原型化 PL/SQL 邏輯

需求：向 CUSTOMER 表所有客戶發送測試郵件。使用 UTL_SMTP 與 DBMS_OUTPUT 內建套件，幾分鐘完成原型：

SET SERVEROUTPUT ON
/
DECLARE
  conn UTL_SMTP.connection;
  msg  VARCHAR2(1024);
BEGIN
  FOR r IN (SELECT first_name, email FROM customer) LOOP
    DBMS_OUTPUT.PUT_LINE('Sending to ' || r.first_name);
    -- 組裝郵件內容並發送
    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, r.email);
    UTL_SMTP.DATA(conn, msg);
    UTL_SMTP.QUIT(conn);
  END LOOP;
END;
/
輸出：Sending test email to customer Mike... Joan... Colin...

驗證無誤後，可直接重構為預存程序上線。

場景二：模擬應用程式負載與效能基準

模擬 10 筆隨機訂單，自動記錄開始/結束時間與每筆訂單明細：

DECLARE
  v_test_start TIMESTAMP;
BEGIN
  SELECT CURRENT TIMESTAMP INTO v_test_start FROM dual;
  FOR k IN 1..10 LOOP
    -- 隨機挑選客戶、商品、數量
    add_item_to_shopping_cart(...);
    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;
/
輸出含每筆訂單金額、狀態及總測試耗時 46ms

修改迴圈上限即可擴大壓測規模，無需重新編譯。

場景三：動態產生臨時報表

需求：彙總上月所有客戶名單（逗點分隔）與總銷售額。以程序邏輯取代複雜遞迴 SQL：

DECLARE
  v_names VARCHAR2(4000);
  v_total NUMBER(19,2);
BEGIN
  FOR r IN (SELECT DISTINCT first_name, last_name FROM customer c, orders o
            WHERE c.customer_id=o.customer_id AND o.creation_time > CURRENT DATE - 1 MONTH) LOOP
    v_names := v_names || '"' || r.first_name || ' ' || r.last_name || '", ';
  END LOOP;
  v_names := SUBSTR(v_names, 1, LENGTH(v_names)-2);
  SELECT NVL(SUM(total_price),0) INTO v_total FROM orders WHERE creation_time > CURRENT DATE - 1 MONTH;
  DBMS_OUTPUT.PUT_LINE('Customer List: ' || v_names);
  DBMS_OUTPUT.PUT_LINE('Total Sales: ' || TO_CHAR(v_total, '$99,999,999.99'));
END;
/
輸出：Customer List: "Mike Smith", "Joan Jett"... Total Sales: $49,772.56

結語

PL/SQL 匿名區塊以「即寫即跑」特性，成為 DB2 9.7 中程式碼驗證、負載模擬與臨時報表的利器。善用此特性，配合 UTL_SMTP、DBMS_OUTPUT 等 Oracle 相容套件，可大幅降低遷移與開發門檻，加速交付高品質資料庫應用。