Oracle預存程序建立實戰：從編譯錯誤到成功部署的完整除錯流程

實戰環境

• Oracle 9i Enterprise Edition Release 9.2.0.1.0
• 工具：SQL*Plus

完整除錯流程記錄

嘗試 1：帶參數查詢（缺 INTO 子句）

CREATE OR REPLACE PROCEDURE get_news(
  aid IN VARCHAR2, atitle IN VARCHAR2) AS
BEGIN
  SELECT * FROM cf_news;
END;
/

錯誤：PLS-00428: 在此 SELECT 語句中缺少 INTO 子句

嘗試 2：簡化參數（仍缺 INTO）

CREATE OR REPLACE PROCEDURE get_news(
  aid IN VARCHAR2, atitle IN VARCHAR2) AS
BEGIN
  NULL;  -- 空主體
END;
/

錯誤：編譯通過但無實際邏輯

嘗試 3：單參數 + 查詢（仍缺 INTO）

CREATE OR REPLACE PROCEDURE get_news(aid IN VARCHAR2) AS
BEGIN
  SELECT * FROM cf_news;
END;
/

錯誤：PLS-00428

嘗試 4：無參數 + 查詢（仍缺 INTO）

CREATE OR REPLACE PROCEDURE get_news AS
BEGIN
  SELECT * FROM cf_news;
END;
/

錯誤：PLS-00428: 在此 SELECT 語句中缺少 INTO 子句

嘗試 5：加入 INTO 變數（首次編譯成功）

CREATE OR REPLACE PROCEDURE get_news AS
  aa NUMBER;
BEGIN
  SELECT COUNT(*) INTO aa FROM cf_news;
END;
/

結果：過程已建立

嘗試 6：加入輸出（拼字錯誤）

CREATE OR REPLACE PROCEDURE get_news AS
  aa NUMBER;
BEGIN
  SELECT COUNT(*) INTO aa FROM cf_news;
  DBMS_OUTPUB.PUT_LINE('aa=' || aa);  -- 拼寫錯誤
END;
/

錯誤：PLS-00201: 必須說明標識符 'DBMS_OUTPUB.PUT_LINE'

嘗試 7：修正拼寫（成功）

CREATE OR REPLACE PROCEDURE get_news AS
  aa NUMBER;
BEGIN
  SELECT COUNT(*) INTO aa FROM cf_news;
  DBMS_OUTPUT.PUT_LINE('aa=' || aa);  -- 正確：DBMS_OUTPUT
END;
/

結果：過程已建立

執行驗證

SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE get_news;
aa=3
PL/SQL 過程已成功完成。

核心錯誤模式總結

預存程序開發黃金法則

1. SELECT 必須有 INTO：單行查詢用變數，多行用游標或 BULK COLLECT
2. 變數先宣告：DECLARE 區塊（或 AS/IS 後）宣告所有區域變數
3. 異常處理：加入 EXCEPTION 區塊捕獲 NO_DATA_FOUND、TOO_MANY_ROWS 等
4. 輸出除錯：SET SERVEROUTPUT ON + DBMS_OUTPUT.PUT_LINE
5. 編譯檢查：SHOW ERRORS 立即查看詳細錯誤位置

進階範本

CREATE OR REPLACE PROCEDURE proc_name(
  p_param_in  IN  VARCHAR2,
  p_param_out OUT NUMBER
) AS
  v_local_var NUMBER;
BEGIN
  -- 業務邏輯
  SELECT COUNT(*) INTO v_local_var FROM some_table WHERE col = p_param_in;
  
  -- 輸出參數賦值
  p_param_out := v_local_var;
  
  -- 除錯輸出
  DBMS_OUTPUT.PUT_LINE('Processed: ' || v_local_var);
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_param_out := 0;
    DBMS_OUTPUT.PUT_LINE('No data found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/