在Oracle數(shù)據(jù)庫中,觸發(fā)器(Trigger)和存儲過程(Stored Procedure)可以一起使用,以實現(xiàn)更復雜的業(yè)務(wù)邏輯。觸發(fā)器是一種自動執(zhí)行的代碼塊,當對表進行特定操作(如INSERT、UPDATE或DELETE)時,觸發(fā)器會自動觸發(fā)并執(zhí)行相應(yīng)的操作。存儲過程是一組預先編寫好的SQL語句,可以通過調(diào)用來執(zhí)行這些語句。
以下是一個簡單的示例,展示了如何在Oracle中使用觸發(fā)器和存儲過程:
employees
的表:CREATE TABLE employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);
departments
的表:CREATE TABLE departments (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
);
CREATE OR REPLACE PROCEDURE insert_department (
p_id IN departments.id%TYPE,
p_name IN departments.name%TYPE
) IS
BEGIN
INSERT INTO departments (id, name) VALUES (p_id, p_name);
COMMIT;
END insert_department;
/
employees
表插入新記錄時,調(diào)用insert_department
存儲過程:CREATE OR REPLACE TRIGGER emp_insert_trigger
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
v_dept_id departments.id%TYPE := :new.department_id;
v_dept_name departments.name%TYPE := 'New Department';
BEGIN
IF NOT EXISTS (SELECT 1 FROM departments WHERE id = v_dept_id) THEN
insert_department(v_dept_id, v_dept_name);
END IF;
END emp_insert_trigger;
/
在這個示例中,當向employees
表插入新記錄時,觸發(fā)器emp_insert_trigger
會檢查departments
表中是否存在相應(yīng)的部門。如果不存在,則調(diào)用insert_department
存儲過程插入新的部門。這樣,我們可以確保在插入員工記錄時,相應(yīng)的部門已經(jīng)存在。