Oracle 與 SQL Server 預存程序調試與錯誤處理實戰對比

調試工具與方法

Oracle：PL/SQL Developer (推薦)

1. 右鍵程序 → Add Debug Information（編譯帶調試符號）。
2. 右鍵 → Test 開啟測試視窗。
3. 輸入參數區塊填入測試值（無需引號，輸出參數留空）。
4. 按 F9 或 Start Debugger，支援 Step Into/Over/Out、斷點、變數觀察、呼叫堆疊、即時修改變數。

SQL Server：無原生圖形調試器（SSMS 18+ 已移除），常用替代方案

• 列印/插入中間表：
  PRINT 'var=' + CAST(@var AS VARCHAR(20))
  或 INSERT INTO debug_log SELECT @var, GETDATE()。
• 動態 SQL 調試：
  EXEC ('UPDATE ... WHERE ' + @dynamic_where)
  注意變數拼接語法、單引號轉義。
• 第三方工具：dbForge SQL Tools、Toad for SQL Server、ApexSQL Debug（付費）。
• Extended Events / Profiler：捕獲 RPC:Completed、SQL:BatchCompleted 事件分析參數與執行計畫。

錯誤處理機制對比

實戰錯誤處理範本

Oracle 標準模板

CREATE OR REPLACE PROCEDURE proc_sample (p_id IN NUMBER, p_result OUT VARCHAR2) AS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM t1 WHERE id = p_id;
  IF v_cnt = 0 THEN
    p_result := 'NOT_FOUND';
    RETURN;
  END IF;

  UPDATE t1 SET status = 'PROCESSED' WHERE id = p_id;
  p_result := 'SUCCESS';
  COMMIT;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_result := 'NO_DATA';
    ROLLBACK;
  WHEN OTHERS THEN
    p_result := 'ERR:' || SQLCODE || ':' || SQLERRM;
    ROLLBACK;
    -- 記錄至審計表
    INSERT INTO error_log (proc_name, error_code, error_msg, log_time)
    VALUES ('proc_sample', SQLCODE, SQLERRM, SYSTIMESTAMP);
    -- 可選：RAISE 向上拋出
END proc_sample;
/

SQL Server 標準模板

CREATE PROCEDURE proc_sample
  @p_id INT,
  @p_result VARCHAR(100) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRANSACTION;

    IF NOT EXISTS (SELECT 1 FROM t1 WHERE id = @p_id)
    BEGIN
      SET @p_result = 'NOT_FOUND';
      ROLLBACK TRANSACTION;
      RETURN;
    END

    UPDATE t1 SET status = 'PROCESSED' WHERE id = @p_id;
    SET @p_result = 'SUCCESS';
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    SET @p_result = 'ERR:' + CAST(ERROR_NUMBER() AS VARCHAR) + ':' + ERROR_MESSAGE();
    INSERT INTO error_log (proc_name, error_code, error_msg, log_time)
    VALUES ('proc_sample', ERROR_NUMBER(), ERROR_MESSAGE(), SYSDATETIME());
    -- 可選：THROW 向上拋出
  END CATCH
END
GO

動態 SQL 安全與調試要點

• Oracle：EXECUTE IMMEDIATE sql USING bind_vars; 綁定變數防注入、共享計畫。
• SQL Server：EXEC sp_executesql @sql, N'@p1 int, @p2 varchar(20)', @p1, @p2; 參數化執行。
• 調試動態 SQL：先 PRINT 或 DBMS_OUTPUT 完整 SQL 文本，複製至工具驗證語法與計畫。

最佳實踐總結

1. 統一錯誤處理模板，強制所有生產程序遵循。
2. 業務錯誤回傳代碼/訊息，系統錯誤記錄完整堆疊並向上拋出。
3. 調試環境與生產環境嚴格隔離，調試痕跡（PRINT/DEBUG LOG）上線前清理。
4. 建立單元測試框架（utPLSQL / tSQLt），自動化回歸測試。
5. 程式碼評審清單：例外處理完整性、事務邊界正確性、動態 SQL 參數化、敏感資料不記錄。

調試是開發期工作，錯誤處理是生產期生命線。投資於標準化模板、自動化測試與可觀測性建設，才能在系統規模增長時保持可控。