Oracle 存儲過程開發入門：建立、編譯、執行、修改、除錯、刪除全流程

存儲過程是封裝在資料庫內的 PL/SQL 程式單元，能減少網路往返、重用邏輯、強化安全。本文以 **最小骨架** 為起點，手把手帶你完成建立、編譯、執行、修改、除錯、刪除全流程，工具僅需 Notepad + SQL*Plus。

環境準備

• 文字編輯器：Notepad / VS Code
• Oracle SQL*Plus (含 10g XE 免費版)
• 基礎 SQL 與 PL/SQL 語法

第一步：寫一個「什麼都不做」的骨架

檔名 skeleton.sql

CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
  NULL;  -- PL/SQL 區塊至少需一句，NULL 佔位
END;
/

逐行解析

1. CREATE OR REPLACE PROCEDURE skeleton：建立或覆蓋名為 skeleton 的過程。
2. IS：宣告區開始 (無變數可留空)。
3. BEGIN：執行區開始。
4. NULL;：空操作，不可省略。
5. END;：執行區結束。
6. /：SQL*Plus 執行緩衝區指令。

第二步：在 SQL*Plus 建立並編譯

SQL> @skeleton
SQL> /
Procedure created.

出現 Procedure created. 代表編譯成功，已存入資料庫字典。

第三步：執行存儲過程

-- 方式 1：EXECUTE 命令
SQL> EXECUTE skeleton;
PL/SQL procedure successfully completed.

-- 方式 2：匿名區塊呼叫
SQL> BEGIN
  skeleton;
END;
/
PL/SQL procedure successfully completed.

第四步：修改 — 印出 Hello World

編輯 skeleton.sql：

CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/

重新載入：

SQL> @skeleton
SQL> /
Procedure created.

第五步：開啟輸出並再次執行

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE skeleton;
Hello World!
PL/SQL procedure successfully completed.

⚠️ SET SERVEROUTPUT ON **必須在執行前開啟**，否則 DBMS_OUTPUT 內容不會顯示。

第六步：除錯 — 查看編譯錯誤

-- 故意寫錯
CREATE OR REPLACE PROCEDURE bad_proc IS BEGIN SELECT * FROM dual; END;
/
-- 顯示警告：Procedure created with compilation errors.

-- 查看錯誤詳情
SQL> SHOW ERRORS
ERRORS FOR PROCEDURE BAD_PROC:
LINE/COL ERROR
-------- -------------------------------------------------
3/1      PLS-00428: an INTO clause is expected in this SELECT statement

或查詢字典：

SELECT * FROM user_errors WHERE name = 'BAD_PROC';

第七步：刪除存儲過程

DROP PROCEDURE skeleton;

進階：帶參數的過程

CREATE OR REPLACE PROCEDURE greet (p_name IN VARCHAR2, p_greeting OUT VARCHAR2)
IS
BEGIN
  p_greeting := 'Hello, ' || p_name || '!';
END;
/

-- 呼叫
VARIABLE g VARCHAR2(50);
EXECUTE greet('Alice', :g);
PRINT g;
-- Output: Hello, Alice!

開發檢查清單

• [ ] 使用 CREATE OR REPLACE 避免重複建立報錯。
• [ ] 參數模式明確：IN (預設)、OUT、IN OUT。
• [ ] 例外處理區 EXCEPTION WHEN OTHERS THEN ... 記錄錯誤日誌。
• [ ] PRAGMA AUTONOMOUS_TRANSACTION 僅在獨立交易日誌需求時使用。
• [ ] 定期 DBMS_UTILITY.COMPILE_SCHEMA 重編失效物件。

從 NULL; 開始，一步步加入參數、邏輯、例外、日誌，即能打造健壯、可維護的 Oracle 存儲過程庫。