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

Oracle PL/SQL 進階：游標與觸發器完整實戰指南

編輯：Oracle 教學

Oracle PL/SQL 進階：游標與觸發器完整實戰指南

游標與觸發器是 PL/SQL 程式設計的兩大支柱。游標掌控結果集逐行處理邏輯，觸發器實現事件驅動的自動化業務規則。本文系統整理隱式/顯式游標、參數化游標、FOR UPDATE 鎖定、各類觸發器語法與實戰範例，助你構建健壯的資料庫程式邏輯。

一、游標核心知識體系

1. 隱式游標屬性

DML 執行後自動開啟，常用屬性：

SQL%FOUND：影響至少一行回傳 TRUE
SQL%NOTFOUND：未影響任何行回傳 TRUE
SQL%ROWCOUNT：影響行數

BEGIN
  UPDATE employees2 SET first_name = 'scott' WHERE employee_id = 2;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('資料已更新');
  ELSE
    DBMS_OUTPUT.PUT_LINE('資料未找到');
  END IF;
END;

2. 顯式游標完整週期

DECLARE → OPEN → FETCH → CLOSE

DECLARE
  c_salary employees2.salary%TYPE;
  CURSOR emp2_cursor IS SELECT salary FROM employees2 WHERE salary > 300000;
BEGIN
  OPEN emp2_cursor;
  LOOP
    FETCH emp2_cursor INTO c_salary;
    EXIT WHEN emp2_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('c_salary = ' || c_salary);
  END LOOP;
  CLOSE emp2_cursor;
END;

3. %ROWTYPE 記錄型別

自動對應表結構，適合 SELECT *：

DECLARE
  CURSOR employee2_cursor IS SELECT * FROM employees2 WHERE division_id = 'SAL';
  myrecord employees2%ROWTYPE;
BEGIN
  OPEN employee2_cursor;
  FETCH employee2_cursor INTO myrecord;
  WHILE employee2_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('id=' || myrecord.employee_id);
    FETCH employee2_cursor INTO myrecord;
  END LOOP;
  CLOSE employee2_cursor;
END;

4. 參數化游標

提升重用性，防止 SQL 注入：

DECLARE
  myrecord employees2%ROWTYPE;
  CURSOR emp_cursor(p_division_id VARCHAR2) IS
    SELECT * FROM employees2 WHERE division_id = p_division_id;
BEGIN
  OPEN emp_cursor('SAL');
  FETCH emp_cursor INTO myrecord;
  WHILE emp_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('id=' || myrecord.employee_id);
    FETCH emp_cursor INTO myrecord;
  END LOOP;
  CLOSE emp_cursor;
END;

5. FOR UPDATE 鎖定與 WHERE CURRENT OF

鎖定查詢行，後續更新無需重新定位：

DECLARE
  v_fname VARCHAR2(20);
  CURSOR c_emp IS SELECT first_name FROM employees2 WHERE employee_id = 1 FOR UPDATE OF first_name;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_fname;
    EXIT WHEN c_emp%NOTFOUND;
    UPDATE employees2 SET first_name = 'jeff' WHERE CURRENT OF c_emp;
  END LOOP;
  CLOSE c_emp;
  COMMIT;
END;

二、觸發器全景圖

1. 觸發器類型

類型	觸發時機	典型用途
行級	每受影響一行觸發一次	資料驗證、衍生欄位計算、審計細節
語句級	每個 DML 語句觸發一次	安全控制、統計維護、跨表同步
INSTEAD OF	取代 View 的 DML	複雜視圖更新
模式/資料庫級	DDL、登入/登出、啟動/關閉	審計、自動化維運

2. 建立語法骨架

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE [OF col] | DELETE}
ON table_or_view
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
[WHEN (condition)]
DECLARE ... BEGIN ... EXCEPTION ... END;

3. 實戰範例集

行級：薪資自動調整

CREATE OR REPLACE TRIGGER tri_employees2
BEFORE INSERT OR UPDATE OF first_name ON employees2
REFERENCING NEW AS newdata OLD AS olddata
FOR EACH ROW
WHEN (newdata.first_name = 'scott')
BEGIN
  :newdata.salary := 20000;
  DBMS_OUTPUT.PUT_LINE('new salary:' || :newdata.salary);
END;

行級：外鍵級聯刪除/更新（手工實現）

-- 刪除 dept 時同步刪除 emp 對應行
CREATE OR REPLACE TRIGGER del_deptid
AFTER DELETE ON dept
FOR EACH ROW
BEGIN
  DELETE FROM emp WHERE deptid = :OLD.deptid;
END;

-- 新增 dept 時同步新增 emp
CREATE OR REPLACE TRIGGER insert_dept
AFTER INSERT ON dept
FOR EACH ROW
BEGIN
  INSERT INTO emp(empid, empname, deptid) VALUES (emp_seq.NEXTVAL, 'auto', :NEW.deptid);
END;

-- 更新 dept 時同步更新 emp
CREATE OR REPLACE TRIGGER update_dept
AFTER UPDATE ON dept
FOR EACH ROW
BEGIN
  UPDATE emp SET deptid = :NEW.deptid WHERE deptid = :OLD.deptid;
END;

行級：序列自動填充主鍵

CREATE SEQUENCE rowtablesequence;
CREATE OR REPLACE TRIGGER set_sequence
BEFORE INSERT ON rowtable
FOR EACH ROW
DECLARE v_seq NUMBER;
BEGIN
  SELECT rowtablesequence.NEXTVAL INTO v_seq FROM dual;
  :NEW.id := v_seq;
END;

語句級：DML 審計日誌

CREATE TABLE mylog(curr_user VARCHAR2(100), curr_date DATE, opera VARCHAR2(10));
CREATE OR REPLACE TRIGGER tri_mylog
AFTER INSERT OR DELETE OR UPDATE ON employees2
BEGIN
  IF INSERTING THEN INSERT INTO mylog VALUES (USER, SYSDATE, 'INSERT');
  ELSIF DELETING THEN INSERT INTO mylog VALUES (USER, SYSDATE, 'DELETE');
  ELSE INSERT INTO mylog VALUES (USER, SYSDATE, 'UPDATE');
  END IF;
END;

模式級：DDL 物件刪除審計

CREATE TABLE dropped_obj(obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE);
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
  INSERT INTO dropped_obj VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;

4. 觸發器管理命令

-- 啟用/禁用單一觸發器
ALTER TRIGGER tri_employees2 ENABLE;
ALTER TRIGGER tri_employees2 DISABLE;

-- 啟用/禁用表上所有觸發器
ALTER TABLE employees2 ENABLE ALL TRIGGERS;
ALTER TABLE employees2 DISABLE ALL TRIGGERS;

-- 刪除觸發器
DROP TRIGGER tri_employees2;

-- 查看觸發器資訊
DESC user_triggers;
SELECT trigger_name, trigger_type, triggering_event, status FROM user_triggers;