您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle PL/SQL 綜合練習題：序列、游標、存儲過程、函數與動態 SQL

編輯：Oracle 教學

Oracle PL/SQL 綜合練習題：序列、游標、存儲過程、函數與動態 SQL

本文整理一套經典的 Oracle PL/SQL 綜合練習題，涵蓋序列建立、批次插入、顯式游標查詢、存儲過程帶參數回傳結果集、函數條件更新、動態 SQL 執行 DDL 等核心技能。附完整參考答案與解析，適合面試複習與教學實訓。

表結構定義

CREATE TABLE employee (
    id     NUMBER(10) NOT NULL,           -- 員工工號
    salary NUMBER(10,2) DEFAULT 0 NOT NULL, -- 薪水
    name   VARCHAR2(24) NOT NULL            -- 姓名
);

-- 開啟輸出緩衝
SET SERVEROUTPUT ON;

題目 1：建立序列 seq_employee

要求：從 1 開始，遞增 1，無最大值，不循環，緩存 20。

CREATE SEQUENCE seq_employee
    MINVALUE 1
    MAXVALUE 9999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20
    NOCYCLE;

題目 2：PL/SQL 區塊插入 100 筆測試資料

要求：使用序列產生 ID，薪水與姓名自定。

DECLARE
    i NUMBER;
BEGIN
    FOR i IN 1 .. 100 LOOP
        INSERT INTO employee (id, salary, name)
        VALUES (seq_employee.NEXTVAL, 1950 + i, '匿名' || TO_CHAR(i));
    END LOOP;
    COMMIT;
END;
/

解析：FOR i IN 1..100 隱式宣告計數器，NEXTVAL 自動遞增，批次提交在迴圈外 (100 筆無壓力)。大量資料請分批提交。

題目 3：顯式游標查詢前 10 筆 (依 ID 升序)

要求：定義顯式游標，按 ID 排序，列印前 10 筆。

DECLARE
    CURSOR c_emp IS
        SELECT id, salary, name
        FROM (SELECT * FROM employee ORDER BY id)
        WHERE ROWNUM <= 10;
    r_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO r_emp;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(r_emp.id || ',' || r_emp.salary || ',' || r_emp.name);
    END LOOP;
    CLOSE c_emp;
END;
/

解析：子查詢先排序再 ROWNUM 過濾是 Oracle 分頁經典寫法 (12c+ 可用 FETCH FIRST 10 ROWS ONLY)。使用 %ROWTYPE 自動適應游標結構。

題目 4：建立存儲過程 p_employee (薪水範圍查詢回傳結果集)

要求：輸入最小/最大薪水，回傳符合條件員工 (ID、薪水、姓名) 依薪水升序。

CREATE OR REPLACE PROCEDURE p_employee (
    p_min_sal IN NUMBER,
    p_max_sal IN NUMBER,
    p_result  OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN p_result FOR
        SELECT id, salary, name
        FROM employee
        WHERE salary BETWEEN p_min_sal AND p_max_sal
        ORDER BY salary;
END p_employee;
/

呼叫測試：

VAR rc REFCURSOR;
EXEC p_employee(1951, 1956, :rc);
PRINT rc;

題目 5：建立函數 f_employee (條件調薪回傳狀態碼)

要求：薪水 < 2000 且姓「王」的員工加薪 5%，成功回傳 0，無符合資料回傳 1。

CREATE OR REPLACE FUNCTION f_employee RETURN NUMBER IS
BEGIN
    UPDATE employee
    SET salary = salary * 1.05
    WHERE salary < 2000
      AND name LIKE '王%';

    IF SQL%ROWCOUNT = 0 THEN
        RETURN 1;  -- 無資料更新
    ELSE
        COMMIT;
        RETURN 0;  -- 更新成功
    END IF;
END f_employee;
/

解析：SQL%ROWCOUNT 記錄最近一次 DML 影響行數。函數中直接 COMMIT 屬自主事務風格，實務上交易控制通常交由呼叫端。

題目 6：匿名區塊呼叫函數並列印結果

DECLARE
    v_ret NUMBER;
BEGIN
    v_ret := f_employee();
    DBMS_OUTPUT.PUT_LINE('函數回傳碼: ' || v_ret);
END;
/

題目 7：建立存儲過程 p_create_emp (動態 SQL 刪除表)

要求：判斷 employee 表是否存在，存在則刪除。

CREATE OR REPLACE PROCEDURE p_create_emp IS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM user_tables
    WHERE table_name = 'EMPLOYEE';

    IF v_cnt > 0 THEN
        EXECUTE IMMEDIATE 'DROP TABLE employee';
        DBMS_OUTPUT.PUT_LINE('表 employee 已刪除');
    ELSE
        DBMS_OUTPUT.PUT_LINE('表 employee 不存在');
    END IF;
END p_create_emp;
/

解析：DDL (DROP TABLE) 不能直接在 PL/SQL 靜態 SQL 中執行，必須使用 EXECUTE IMMEDIATE 動態 SQL。查詢 USER_TABLES 判斷存在性。

題目 8：呼叫存儲過程 p_create_emp

EXEC p_create_emp;
-- 或
BEGIN
    p_create_emp;
END;
/

延伸練習與進階考點

分頁查詢：12c+ OFFSET ... ROWS FETCH NEXT ... ROWS ONLY vs 傳統 ROWNUM 子查詢效能對比。
游標變數強型別：定義 TYPE t_cur IS REF CURSOR RETURN employee%ROWTYPE; 提升編譯期檢查。
函數確定性：CREATE FUNCTION ... DETERMINISTIC 允許結果快取，適用於純計算函數。
自主事務：PRAGMA AUTONOMOUS_TRANSACTION; 在函數/觸發器中獨立提交/回滾，不影響主事務。
批次處理：BULK COLLECT ... LIMIT + FORALL 替換逐行 FETCH/UPDATE，效能提升 10x+。
動態 SQL 防注入：EXECUTE IMMEDIATE 'DROP TABLE ' || v_tbl_name 需驗證 v_tbl_name 合法性，避免 SQL 注入。