Oracle 預存程序開發實戰：從報錯到成功的完整除錯筆記

場景：建立 get_news 程序查詢 cf_news 表

連線環境：Oracle 9i Enterprise Edition 9.2.0.1.0

錯誤演進與修正過程

最終正確代碼

CREATE OR REPLACE PROCEDURE get_news AS
  aa NUMBER;
BEGIN
  SELECT COUNT(*) INTO aa FROM cf_news;
  dbms_output.put_line('aa=' || aa);
END;
/

執行與驗證

SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE get_news;
aa=3
PL/SQL procedure successfully completed.

核心知識點總結

1. SELECT INTO：PL/SQL 靜態查詢必須單行單列，結果存入標量變數或 %ROWTYPE 記錄。
2. 變數宣告區：AS/IS 之後、BEGIN 之前，型別建議用 %TYPE/%ROWTYPE 鎖定資料字典。
3. DBMS_OUTPUT：需 SET SERVEROUTPUT ON 才會顯示；生產環境慎用，改用日誌表或 UTL_FILE。
4. SHOW ERRORS：編譯失敗後必下指令，精準定位行/欄與錯誤碼。
5. / 執行斜線：SQL*Plus 中單獨一行 / 代表執行緩衝區 PL/SQL 區塊，不可省略。

進階建議：參數化與例外處理

CREATE OR REPLACE PROCEDURE get_news_cnt(
  p_table  IN VARCHAR2 DEFAULT 'cf_news',
  p_count  OUT NUMBER
) AS
  v_sql VARCHAR2(200);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || p_table;
  EXECUTE IMMEDIATE v_sql INTO p_count;
EXCEPTION
  WHEN OTHERS THEN
    p_count := -1;
    dbms_output.put_line('Error: ' || SQLERRM);
    RAISE;
END;

• 使用 OUT 參數回傳結果，支援動態表名。
• EXECUTE IMMEDIATE 執行動態 SQL。
• EXCEPTION 區塊記錄錯誤並重拋，保證呼叫端可捕捉。

結語

預存程序開發核心在於「語法正確性」→「變數綁定」→「輸出驗證」三步驟。養成 SHOW ERRORS、SET SERVEROUTPUT ON、%TYPE 習慣，能避開 90% 的新手坑。