Oracle 存儲過程實戰進階：執行方式、修改技巧、DBMS_OUTPUT 除錯、OUT 參數與 REF CURSOR 回傳結果集

承接入門篇，本文深入實戰技巧：多種執行方式、動態修改、DBMS_OUTPUT 除錯、OUT 參數回傳標量、REF CURSOR 回傳結果集、套件封裝，直指企業級開發需求。

執行存儲過程的三種方式

-- 1. EXECUTE 命令 (SQL*Plus 專用)
EXECUTE skeleton;

-- 2. 匿名 PL/SQL 區塊 (通用，可在程式中嵌入)
BEGIN
  skeleton;
END;
/

-- 3. 呼叫語法 (JDBC/ODP.NET/其他驅動標準)
CALL skeleton();

修改存儲過程：CREATE OR REPLACE 熱替換

直接重跑 CREATE OR REPLACE PROCEDURE ... 即可**原子性替換**，無需先 DROP。正在執行的舊版本會跑完，新呼叫自動用新版本。

DBMS_OUTPUT 除錯必開：SET SERVEROUTPUT ON

SQL> SET SERVEROUTPUT ON SIZE 1000000  -- 緩衝區 1MB，防截斷
SQL> EXECUTE debug_proc;
Debug: var_x = 42
PL/SQL procedure successfully completed.

生產環境建議改寫日誌表或 UTL_FILE 寫檔，避免緩衝區溢位影響效能。

OUT 參數回傳標量值

CREATE OR REPLACE PROCEDURE get_dept_name (
  p_deptno IN  NUMBER,
  p_dname  OUT VARCHAR2
) IS
BEGIN
  SELECT dname INTO p_dname FROM dept WHERE deptno = p_deptno;
EXCEPTION
  WHEN NO_DATA_FOUND THEN p_dname := 'NOT FOUND';
END;
/

-- 呼叫
VARIABLE v_dname VARCHAR2(30);
EXECUTE get_dept_name(10, :v_dname);
PRINT v_dname;

REF CURSOR 回傳結果集 (核心技能)

1. 定義弱型游標型別 (Package Spec)

CREATE OR REPLACE PACKAGE pkg_emp IS
  TYPE ref_cursor IS REF CURSOR;  -- 弱型，可對應任意查詢
  PROCEDURE get_emps (p_deptno IN NUMBER, p_rc OUT ref_cursor);
END pkg_emp;
/

2. 實作開啟游標 (Package Body)

CREATE OR REPLACE PACKAGE BODY pkg_emp IS
  PROCEDURE get_emps (p_deptno IN NUMBER, p_rc OUT ref_cursor) IS
  BEGIN
    OPEN p_rc FOR
      SELECT empno, ename, sal
      FROM emp
      WHERE deptno = p_deptno
      ORDER BY sal DESC;
  END;
END pkg_emp;
/

3. 呼叫並遍歷 (SQL*Plus / Java / Python)

-- SQL*Plus
VARIABLE rc REFCURSOR;
EXECUTE pkg_emp.get_emps(10, :rc);
PRINT rc;

-- Java (JDBC)
CallableStatement cs = conn.prepareCall("{call pkg_emp.get_emps(?, ?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);
while (rs.next()) { ... }

套件封裝最佳實務

• Spec/Body 分離：介面與實作分離，修改 Body 不需重編譯呼叫端。
• 公開最小化：僅在 Spec 公開必要程序/函數/型別。
• 常數集中：c_max_retry CONSTANT NUMBER := 3;
• 例外處理標準化：自訂 e_biz_error EXCEPTION; PRAGMA EXCEPTION_INIT(e_biz_error, -20001);

效能提示

• 大量 DML 使用 FORALL + BULK COLLECT 減少 Context Switch。
• 避免在迴圈中 COMMIT，批次處理最後統一提交。
• 查詢結果集直接 OPEN ... FOR SELECT ...，勿先 BULK COLLECT INTO 再開游標 (雙重耗資源)。

除錯進階：DBMS_DEBUG / IDE 整合

SQL*Plus 僅能 SHOW ERRORS。建議使用：

• PL/SQL Developer：內建 Debugger，支援斷點、變數觀察、Call Stack。
• SQL Developer (免費)：Debug 模式，功能完整。
• VS Code + Oracle Developer Tools：現代化編輯+除錯體驗。

掌握 REF CURSOR、套件封裝、批次處理與現代化除錯工具，即可從「會寫存儲過程」進階為「寫出高效能、可維護的企業級 PL/SQL 系統」。