Oracle 預存程序基本語法與常見陷阱完整指南

一、基本結構

CREATE OR REPLACE PROCEDURE procedure_name (
    param1 IN NUMBER,
    param2 IN NUMBER
) IS
    var1 INTEGER := 0;
    var2 DATE;
BEGIN
    -- 程式邏輯
    NULL;
END procedure_name;
/

二、核心語法要點

1. SELECT INTO

將查詢結果存入變數，**必須回傳恰好一筆**，否則拋出 NO_DATA_FOUND 或 TOO_MANY_ROWS。

BEGIN
    SELECT col1, col2 INTO var1, var2 FROM typestruct WHERE ...;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 處理無資料
        NULL;
END;

2. IF 條件判斷

IF v_test = 1 THEN
    BEGIN
        -- do something
    END;
END IF;

3. WHILE 迴圈

WHILE v_test = 1 LOOP
    BEGIN
        -- 迴圈主體
    END;
END LOOP;

4. 變數指派

v_test := 123;

5. FOR-IN 隱式游標迴圈

IS
    CURSOR cur IS SELECT * FROM xxx;
BEGIN
    FOR cur_rec IN cur LOOP
        v_sum := cur_rec.col1 + cur_rec.col2;
    END LOOP;
END;

6. 參數化游標

CURSOR c_user(c_id NUMBER) IS SELECT name FROM user WHERE typeid = c_id;
OPEN c_user(v_id);
LOOP
    FETCH c_user INTO v_name;
    EXIT WHEN c_user%NOTFOUND;
    -- 處理
END LOOP;
CLOSE c_user;

7. PL/SQL Developer 除錯

1. 連線資料庫 → 建立 Test Window。
2. 輸入呼叫預存程序代碼（如 BEGIN proc_name(1); END;）。
3. F9 開始除錯，Ctrl+N 單步執行。

三、常見陷阱與最佳實務

1. 表格別名不可加 AS

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

Oracle 不支援 AS 關鍵字定義表別名，直接空白隔開即可。

2. SELECT 單一欄位必須緊接 INTO

-- 正確
SELECT af.keynode INTO kn FROM appfoundation af WHERE ...;
-- 錯誤：缺少 INTO
SELECT af.keynode FROM appfoundation af WHERE ...;
-- 編譯錯誤：PLS-00428: an INTO clause is expected in this SELECT statement

3. SELECT INTO 前確認資料存在

避免 NO_DATA_FOUND 例外，可先 SELECT COUNT(*) 檢查。

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

-- 正確
SELECT keynode INTO kn FROM appfoundation WHERE appid = aid AND foundationid = fid;
-- 危險：別名 af 與欄位名衝突，編譯通過但執行期拋 ORA-01422: exact fetch returns more than requested number of rows
SELECT af.keynode INTO kn FROM appfoundation af WHERE af.appid = appid AND af.foundationid = foundationid;

當 WHERE 條件中的 appid、foundationid 解析為表欄位而非參數，導致條件永真，回傳多筆。

5. 聚合函數回傳 NULL 處理

SELECT SUM(vcount) INTO fcount FROM a WHERE bid = 'xxxxxx';
IF fcount IS NULL THEN
    fcount := 0;  -- 即使宣告 fcount NUMBER(8) := 0，查無資料時仍會變 NULL
END IF;

結論：聚合函數查無資料回傳 NULL，需顯式判斷轉 0。

6. Hibernate 呼叫 Oracle 預存程序

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

四、函數與程序差異快速比較