PL/SQL Developer 中建立、呼叫與測試 Oracle 預存程序完整操作指南

前言

PL/SQL Developer 是 Oracle 開發最常用的 IDE，掌握其建立、編譯、除錯、測試預存程序的完整流程，能大幅提升開發效率。本文以實際操作步驟與代碼範例，涵蓋建表、建立預存程序（含 REF CURSOR 輸出）、刪除程序、Test Window 執行、Variable 面板綁定游標、右鍵測試功能，以及 DML 類程序的參數傳遞與執行。

一、環境準備：建立測試表

CREATE TABLE A (
    USERID NUMBER(38),
    PWD    VARCHAR2(30)
) TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
);

二、建立查詢類預存程序（輸出 REF CURSOR）

CREATE OR REPLACE PROCEDURE up_sel (
    cur_test OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN cur_test FOR SELECT * FROM A;
END;
/

三、在 PL/SQL Developer 中執行與測試

方法 1：Test Window（推薦新手）

1. 選單 File → New → Test Window
2. 輸入匿名區塊呼叫程序：

   BEGIN
       up_sel(cur_test => :cur_test);
   END;

3. 點擊執行按鈕（或 F8）
4. 下方 Variable 面板會出現 cur_test，雙擊或點擊右側 ... 圖示即可檢視游標結果集

方法 2：右鍵程序直接測試（最快）

1. 左側 Object Browser 找到 UP_SEL 程序
2. 右鍵選擇 Test
3. 自動產生測試腳本，直接修改參數值執行

四、刪除預存程序

DROP PROCEDURE up_sel;

五、DML 類預存程序範例

1. 刪除資料程序（IN 參數）

CREATE OR REPLACE PROCEDURE up_del (
    userid IN VARCHAR2
) IS
BEGIN
    DELETE FROM A WHERE USERID = userid;
END;
/

測試：在 Test Window 或右鍵 Test 介面的 Value 欄位填入要刪除的 USERID 值執行。

2. 新增資料程序（多 IN 參數 + 事務提交）

CREATE OR REPLACE PROCEDURE up_add (
    userid IN VARCHAR2,
    pwd    IN VARCHAR2
) IS
BEGIN
    INSERT INTO A (USERID, PWD) VALUES (userid, pwd);
    COMMIT; -- 記得提交
END;
/

3. 匿名區塊執行 DML 程序

DECLARE
BEGIN
    up_add('11', '222');
END;
/

六、進階技巧與注意事項

• SYS_REFCURSOR vs 強型別游標： SYS_REFCURSOR 是弱型別，通用性強；複雜系統建議在 Package 定義強型別 TYPE t_cur IS REF CURSOR RETURN table%ROWTYPE; 提供編譯期檢查。
• 參數模式： IN（預設，唯讀）、OUT（回傳值，初始為 NULL）、IN OUT（傳入並回傳）。
• 例外處理： 生產環境程序務必加入 EXCEPTION WHEN OTHERS THEN ... 記錄錯誤日誌並視需求 RAISE 或回滾。
• 提交策略： 單一 DML 操作可在程序內 COMMIT；複雜業務邏輯建議由呼叫端控制交易邊界。
• 除錯技巧： 使用 DBMS_OUTPUT.PUT_LINE 列印關鍵變數，或啟用 PL/SQL Developer 的 Debug 模式（需編譯加入 Debug Information）。

總結

從建表、建立程序、Test Window 綁定游標變數、右鍵快速測試，到 DML 程序的參數傳遞與匿名區塊執行，本文涵蓋了 PL/SQL Developer 開發預存程序的完整生命週期操作。熟練這些技能，讓你專注於業務邏輯實現而非工具操作。