Oracle存儲過程快速入門：創建語法、變量賦值、條件控制

存儲過程標準模板

CREATE OR REPLACE PROCEDURE proc_name (
    param_in  IN  datatype,
    param_out OUT datatype
) IS
    -- 聲明區：局部變量、游標、異常
    var_name datatype := default_value;
BEGIN
    -- 執行區：業務邏輯
    NULL;
EXCEPTION
    -- 異常處理區
    WHEN OTHERS THEN
        RAISE;
END proc_name;

三大核心語法詳解

1. 變量聲明與賦值

-- 聲明
v_count    NUMBER(10) := 0;
v_name     VARCHAR2(50) DEFAULT 'Unknown';
v_date     DATE := SYSDATE;
v_rec      emp%ROWTYPE;          -- 記錄類型
v_arr      DBMS_SQL.VARCHAR2_TABLE; -- 關聯數組

-- 賦值 (關鍵: 使用 := 而非 =)
v_count := v_count + 1;
v_name  := UPPER('oracle');
SELECT max(sal) INTO v_max_sal FROM emp;  -- SELECT INTO賦值

2. 條件控制 IF/ELSIF/ELSE

IF condition1 THEN
    statements;
ELSIF condition2 THEN
    statements;
ELSE
    statements;
END IF;

-- 實戰示例：薪資分級
IF v_sal < 2000 THEN
    v_grade := 'A';
ELSIF v_sal < 4000 THEN
    v_grade := 'B';
ELSE
    v_grade := 'C';
END IF;

3. 循環控制

-- 數值循環
FOR i IN 1..10 LOOP
    INSERT INTO log VALUES (i, SYSDATE);
END LOOP;

-- 游標循環 (隱式OPEN/FETCH/CLOSE)
FOR rec IN (SELECT * FROM emp WHERE deptno = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.ename);
END LOOP;

-- WHILE循環
WHILE v_flag LOOP
    -- 條件變更邏輯
    v_flag := check_condition;
END LOOP;

完整實戰示例：員工薪資調整

CREATE OR REPLACE PROCEDURE adjust_salary (
    p_deptno   IN  NUMBER,
    p_pct      IN  NUMBER DEFAULT 0.1,
    p_updated  OUT NUMBER
) IS
    v_count NUMBER := 0;
BEGIN
    -- 參數驗證
    IF p_pct <= 0 OR p_pct > 1 THEN
        RAISE_APPLICATION_ERROR(-20001, '調整比例必須在0-1之間');
    END IF;
    
    -- 批量更新
    UPDATE emp
    SET sal = sal * (1 + p_pct)
    WHERE deptno = p_deptno;
    
    v_count := SQL%ROWCOUNT;
    p_updated := v_count;
    
    -- 提交由調用方控制，此處不提交
    DBMS_OUTPUT.PUT_LINE('部門' || p_deptno || '調整' || v_count || '名員工薪資');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);
        RAISE;
END adjust_salary;

調用驗證

-- SQL*Plus調用
VARIABLE v_updated NUMBER;
EXECUTE adjust_salary(10, 0.15, :v_updated);
PRINT v_updated;

-- 匿名塊調用
DECLARE
    v_num NUMBER;
BEGIN
    adjust_salary(20, 0.1, v_num);
    DBMS_OUTPUT.PUT_LINE('更新記錄數: ' || v_num);
END;

新手避坑指南

• := vs =：PL/SQL賦值用:=，SQL中SET col = val用=
• SELECT INTO：必須返回恰好一行，否則拋出NO_DATA_FOUND或TOO_MANY_ROWS
• 變量作用域：內層塊可訪問外層變量，同名時內層遮蔽外層
• 異常處理：盡量捕獲具體異常，WHEN OTHERS最後兜底且通常RAISE拋出
• 提交控制：存儲過程默認不自動提交，由調用事務邊界決定