Oracle 預存程序與函數實戰範例集：游標、IF/CASE、迴圈、異常處理、套件

收錄 Oracle PL/SQL 核心語法實戰範例：隱式/顯式游標、IF/ELSIF/CASE 條件、FOR/LOOP/WHILE 迴圈、自訂異常、套件規格與主體，適合開發者快速複製修改套用。

1. 隱式游標 FOR 迴圈 + IF/ELSIF

CREATE OR REPLACE PROCEDURE peace_if IS
BEGIN
    FOR rec IN (SELECT * FROM grade) LOOP
        IF rec.course_name = 'OS' THEN
            DBMS_OUTPUT.PUT_LINE('Stu_name = ' || rec.stu_name);
        ELSIF rec.course_name = 'DB' THEN
            DBMS_OUTPUT.PUT_LINE('DB');
        ELSE
            DBMS_OUTPUT.PUT_LINE('feng la feng la');
        END IF;
    END LOOP;
END;
/

2. CASE 表達式（簡單/搜尋式）

-- 簡單 CASE：比較單一運算式
CREATE OR REPLACE PROCEDURE peace_case1 IS
BEGIN
    FOR rec IN (SELECT * FROM test_case) LOOP
        CASE rec.vol
            WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('haha1');
            WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('haha2');
            WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('haha3');
            WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('haha4');
            ELSE DBMS_OUTPUT.PUT_LINE('qita');
        END CASE;
    END LOOP;
END;
/

-- 搜尋式 CASE：評估多個布林條件
CREATE OR REPLACE PROCEDURE peace_case2 IS
BEGIN
    FOR rec IN (SELECT * FROM test_case) LOOP
        CASE
            WHEN rec.vol = 1 THEN DBMS_OUTPUT.PUT_LINE('haha1');
            WHEN rec.vol = 2 THEN DBMS_OUTPUT.PUT_LINE('haha2');
            WHEN rec.vol = 3 THEN DBMS_OUTPUT.PUT_LINE('haha3');
            WHEN rec.vol = 4 THEN DBMS_OUTPUT.PUT_LINE('haha4');
            ELSE DBMS_OUTPUT.PUT_LINE('qita');
        END CASE;
    END LOOP;
END;
/

3. 巢狀 FOR 迴圈（九九乘法表）

CREATE OR REPLACE PROCEDURE peace_for IS
    v_sum NUMBER := 0;
    v_line VARCHAR2(500);
BEGIN
    FOR i IN 1 .. 9 LOOP
        v_line := '';
        FOR j IN 1 .. i LOOP
            v_sum := i * j;
            v_line := v_line || i || ' * ' || j || ' = ' || v_sum || '  ';
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(v_line);
    END LOOP;
END;
/

4. 基本 LOOP + EXIT WHEN

CREATE OR REPLACE PROCEDURE peace_loop IS
    v_sum NUMBER := 0;
    v_cnt NUMBER := 0;
BEGIN
    LOOP
        EXIT WHEN v_cnt >= 10;
        v_sum := v_sum + v_cnt;
        v_cnt := v_cnt + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_sum);  -- 45
END;
/

5. 顯式游標 OPEN/FETCH/CLOSE

CREATE OR REPLACE PROCEDURE loop_cur IS
    v_stu_name     VARCHAR2(100);
    v_course_name  VARCHAR2(100);
    CURSOR c_grade IS SELECT stu_name, course_name FROM grade;
BEGIN
    OPEN c_grade;
    LOOP
        FETCH c_grade INTO v_stu_name, v_course_name;
        EXIT WHEN c_grade%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_stu_name || ' ' || v_course_name);
    END LOOP;
    CLOSE c_grade;
END;
/

6. 系統預定義異常處理

CREATE OR REPLACE PROCEDURE peace_exp(p_name IN VARCHAR2) IS
    v_course VARCHAR2(100);
BEGIN
    SELECT course_name INTO v_course FROM grade WHERE stu_name = p_name;
    DBMS_OUTPUT.PUT_LINE(v_course);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('try: no data');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('more: too many rows');
END;
/

7. 自訂異常 + 交易控制

CREATE OR REPLACE PROCEDURE peace_insert(p_course IN VARCHAR2) IS
    e_custom EXCEPTION;
BEGIN
    IF p_course = 'OK' THEN
        INSERT INTO course(course_name) VALUES (p_course);
    ELSIF p_course = 'NG' THEN
        INSERT INTO course(course_name) VALUES (p_course);
        RAISE e_custom;  -- 主動拋出自訂異常
    ELSE
        DBMS_OUTPUT.PUT_LINE('c_n: ' || p_course);
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_custom THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('ERRO: custom exception');
END;
/

8. 套件規格與主體（封裝介面與實作）

-- 規格：公開介面
CREATE OR REPLACE PACKAGE peace_pkg AS
    FUNCTION test1(p_in VARCHAR2) RETURN NUMBER;
    PROCEDURE test2(p_in VARCHAR2);
END peace_pkg;
/

-- 主體：實作細節
CREATE OR REPLACE PACKAGE BODY peace_pkg AS
    FUNCTION test1(p_in VARCHAR2) RETURN NUMBER IS
        v_temp NUMBER := 0;
    BEGIN
        RETURN v_temp;
    END;

    PROCEDURE test2(p_in VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(p_in);
    END;
END peace_pkg;
/

呼叫測試

BEGIN
    peace_if;
    peace_case1;
    peace_for;
    peace_loop;
    loop_cur;
    peace_exp('張三');
    peace_insert('OK');
    peace_pkg.test2('Hello Package');
    DBMS_OUTPUT.PUT_LINE('Func result: ' || peace_pkg.test1('x'));
END;
/

PL/SQL 核心語法速查表

PL/SQL 是 Oracle 資料庫邏輯的執行引擎。**游標、異常、套件** 三大支柱掌握得好，複雜業務邏輯下沉資料庫層才能又快又穩。

作者：peace.zhao