Oracle 預存程序開發避坑指南：六大高頻錯誤與最佳實踐

六大高頻錯誤與解決方案

1. 表別名不可用 AS

-- 正確
SELECT a.appname FROM appinfo a;

-- 錯誤
SELECT a.appname FROM appinfo AS a;

原因：Oracle 不支援 AS 關鍵字用於表別名，僅用於欄位別名。避免與 PL/SQL 宣告區 AS/IS 混淆。

2. SELECT 必須有 INTO 子句

-- 正確
SELECT af.keynode INTO kn FROM appfoundation af WHERE af.appid = aid;

-- 錯誤
SELECT af.keynode FROM appfoundation af WHERE af.appid = aid;
-- PLS-00428: an INTO clause is expected in this SELECT statement

規則：PL/SQL 中單行查詢強制要求 INTO 接收結果；多行查詢需用游標或 BULK COLLECT。

3. SELECT...INTO 需確保單行結果

-- 風險：無資料拋 NO_DATA_FOUND，多行拋 TOO_MANY_ROWS
SELECT SUM(vcount) INTO fcount FROM a WHERE bid = 'xxxxxx';

-- 安全模式
DECLARE
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM a WHERE bid = 'xxxxxx';
  IF v_cnt > 0 THEN
    SELECT SUM(vcount) INTO fcount FROM a WHERE bid = 'xxxxxx';
  ELSE
    fcount := 0;
  END IF;
END;

4. 別名不可與欄位名同名

-- 正確：表別名區分欄位
SELECT af.keynode INTO kn FROM appfoundation af 
WHERE af.appid = aid AND af.foundationid = fid;

-- 錯誤：別名與欄位同名導致歧義
SELECT af.keynode INTO kn FROM appfoundation af 
WHERE af.appid = appid AND af.foundationid = foundationid;
-- ORA-01422: exact fetch returns more than requested number of rows

原因：appid = appid 永真，等同無 WHERE 條件，返回多行。

5. 聚合函數無資料返回 NULL

-- 問題：無匹配行時 SUM 返回 NULL，非 0
SELECT SUM(vcount) INTO fcount FROM a WHERE bid = 'xxxxxx';
-- 即使 fcount NUMBER := 0 預設，仍被覆蓋為 NULL

-- 修正
IF fcount IS NULL THEN fcount := 0; END IF;

6. Hibernate 呼叫預存程序範本

this.pnumberManager.getHibernateTemplate().execute(
  new HibernateCallback() {
    public Object doInHibernate(Session session) 
      throws HibernateException, SQLException {
      CallableStatement cs = session.connection()
        .prepareCall("{call modify_appnumber_remain(?)}");
      cs.setString(1, foundationid);
      cs.execute();
      return null;
    }
  });

關鍵點：使用 CallableStatement、{call proc_name(?)} 語法、正確綁定參數。

預存程序開發最佳實踐清單

進階範本：健壯的單行查詢封裝

CREATE OR REPLACE FUNCTION get_sum_vcount(p_bid VARCHAR2) RETURN NUMBER AS
  v_result NUMBER;
BEGIN
  SELECT NVL(SUM(vcount), 0) INTO v_result  -- NVL 直接處理 NULL
  FROM a WHERE bid = p_bid;
  RETURN v_result;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RETURN 0;  -- 或 RAISE 視業務而定
END;
/