Oracle PL/SQL 存儲過程入門完全指南：語法、游標、除錯實戰

核心語法實戰

1. SELECT INTO - 查詢單筆資料賦值變數

BEGIN
  SELECT col1, col2 INTO var1, var2 
  FROM typestruct 
  WHERE condition;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 處理無資料情況
    var1 := -1;
  WHEN TOO_MANY_ROWS THEN
    -- 處理多筆資料錯誤
    RAISE_APPLICATION_ERROR(-20001, '查詢回傳多筆');
END;

注意：必須且只能回傳 1 筆，否則拋出例外。

2. IF 條件判斷

IF v_test = 1 THEN
  -- do something
ELSIF v_test = 2 THEN
  -- do something else
ELSE
  -- default
END IF;

3. WHILE 迴圈

WHILE v_test = 1 LOOP
  -- 迴圈主體
  v_test := v_test + 1;  -- 避免無限迴圈
  EXIT WHEN v_test > 100;  -- 或使用 EXIT WHEN
END LOOP;

4. 變數賦值

v_test := 123;           -- 純量賦值
v_date := SYSDATE;       -- 日期賦值
v_var := NVL(v_other, 0); -- 運算式賦值

5. 隱含游標 FOR 迴圈 (最常用)

DECLARE
  v_sum NUMBER := 0;
BEGIN
  FOR rec IN (SELECT col1, col2 FROM table_name WHERE condition) LOOP
    v_sum := v_sum + rec.col1 + rec.col2;
    DBMS_OUTPUT.PUT_LINE('處理: ' || rec.col1);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('總計: ' || v_sum);
END;

優點：自動 OPEN/FETCH/CLOSE，程式碼簡潔。

6. 顯式游標 WITH 參數

DECLARE
  CURSOR c_user(p_type_id NUMBER) IS 
    SELECT name FROM users WHERE type_id = p_type_id;
  v_name users.name%TYPE;
BEGIN
  OPEN c_user(10);
  LOOP
    FETCH c_user INTO v_name;
    EXIT WHEN c_user%NOTFOUND;
    -- 處理 v_name
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;
  CLOSE c_user;
END;

游標屬性：%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN

7. REF CURSOR 動態游標

TYPE ref_cur IS REF CURSOR;
c_ref ref_cur;
BEGIN
  OPEN c_ref FOR 'SELECT * FROM ' || table_name;  -- 動態 SQL
  -- 或 OPEN c_ref FOR SELECT * FROM fixed_table;
  FETCH c_ref INTO ...;
  CLOSE c_ref;
END;

PL/SQL Developer 除錯技巧

1. 連線資料庫 → 工具列 Test Window (或 Ctrl+T)
2. 輸入呼叫存儲過程代碼：BEGIN my_proc(1, 'test'); END;
3. F9 開始除錯 (Debug)
4. Ctrl+N 單步執行 (Step Over)
5. F7 進入函數 (Step Into)
6. F8 跳出函數 (Step Out)
7. 監看視窗：右鍵變數 → Add Watch
8. 呼叫堆疊：View → Call Stack

進階主題速查

效能與最佳實踐

• 盡量使用 BULK COLLECT + FORALL 批次處理
• 避免在迴圈中執行 SQL (Row-by-row = Slow-by-slow)
• 使用 %TYPE、%ROWTYPE 錨定型別，減少維護
• 適度加註解，複雜邏輯拆分為私有子程序
• 統一錯誤處理套件，記錄錯誤堆疊與業務上下文