Oracle 存储过程、函数与触发器

关键词：Oracle 存储过程、函数、触发器、包、PL/SQL 编程

✅ 摘要

在企业级 Oracle 数据库开发中，存储过程（Procedure）、函数（Function）和触发器（Trigger） 是构建复杂业务逻辑、提高系统性能、实现数据一致性的核心组件。

本文将围绕 Oracle 中的 PL/SQL 程序结构 展开讲解：

• 创建与调用存储过程（支持 IN/OUT 参数）
• 创建与使用函数（RETURN 返回值）
• 使用 DBMS_OUTPUT 调试输出
• 触发器类型与应用场景（DML 触发器、INSTEAD OF 触发器）
• 包（Package）的创建与封装（组织多个过程和函数）

每部分都配有 完整的 PL/SQL 示例代码，适合初学者学习掌握，也适合中级开发者深入理解。

一、存储过程与函数

🔹 1. 创建存储过程（支持 IN/OUT 参数）

示例：计算两个数之和并返回结果

CREATE OR REPLACE PROCEDURE add_numbers (
    num1 IN NUMBER,
    num2 IN NUMBER,
    result OUT NUMBER
)
IS
BEGIN
    result := num1 + num2;
    DBMS_OUTPUT.PUT_LINE('两数之和为: ' || result);
END;

调用方式：

DECLARE
    v_result NUMBER;
BEGIN
    add_numbers(5, 3, v_result);
    DBMS_OUTPUT.PUT_LINE('调用后的结果是: ' || v_result);
END;

🔹 2. 创建函数（RETURN 返回值）

示例：根据员工ID查询其工资

CREATE OR REPLACE FUNCTION get_salary (emp_id IN NUMBER)
RETURN NUMBER
IS
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = emp_id;

    RETURN v_salary;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

调用方式：

DECLARE
    v_salary NUMBER;
BEGIN
    v_salary := get_salary(100);
    IF v_salary IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('员工工资为: ' || v_salary);
    ELSE
        DBMS_OUTPUT.PUT_LINE('未找到该员工');
    END IF;
END;

🔹 3. 使用 DBMS_OUTPUT 输出调试信息

SET SERVEROUTPUT ON; -- 必须先开启输出

BEGIN
    DBMS_OUTPUT.PUT_LINE('这是调试信息');
END;

二、触发器（Trigger）

🔹 1. DML 触发器：在表操作前后自动执行

示例：记录员工表更新日志

CREATE TABLE emp_log (
    log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    operation   VARCHAR2(10),
    emp_id      NUMBER,
    old_salary  NUMBER,
    new_salary  NUMBER,
    change_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_emp_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO emp_log (operation, emp_id, old_salary, new_salary)
    VALUES ('UPDATE', :OLD.employee_id, :OLD.salary, :NEW.salary);
END;

测试语句：

UPDATE employees SET salary = 8000 WHERE employee_id = 100;
SELECT * FROM emp_log;

🔹 2. INSTEAD OF 触发器：用于视图上的 DML 操作

示例：为只读视图添加插入功能

CREATE OR REPLACE VIEW emp_info_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees;

CREATE OR REPLACE TRIGGER trg_insert_emp_info
INSTEAD OF INSERT ON emp_info_view
BEGIN
    INSERT INTO employees(employee_id, first_name, last_name, department_id)
    VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.department_id);
END;

测试插入：

INSERT INTO emp_info_view(employee_id, first_name, last_name, department_id)
VALUES (1001, 'Tom', 'Smith', 60);

三、包（Package）

🔹 1. 包的组成：规范（Specification） + 主体（Body）

🔹 2. 创建包规范（Spec）

CREATE OR REPLACE PACKAGE emp_pkg IS
    PROCEDURE update_salary(emp_id IN NUMBER, new_salary IN NUMBER);
    FUNCTION get_fullname(emp_id IN NUMBER) RETURN VARCHAR2;
END emp_pkg;

🔹 3. 创建包主体（Body）

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

    PROCEDURE update_salary(emp_id IN NUMBER, new_salary IN NUMBER) IS
    BEGIN
        UPDATE employees
        SET salary = new_salary
        WHERE employee_id = emp_id;
    END;

    FUNCTION get_fullname(emp_id IN NUMBER) RETURN VARCHAR2 IS
        v_name VARCHAR2(100);
    BEGIN
        SELECT first_name || ' ' || last_name INTO v_name
        FROM employees
        WHERE employee_id = emp_id;

        RETURN v_name;
    END;

END emp_pkg;

🔹 4. 调用包中的方法

BEGIN
    emp_pkg.update_salary(100, 9000);
    DBMS_OUTPUT.PUT_LINE('姓名为: ' || emp_pkg.get_fullname(100));
END;

✅ 总结

以下内容为主要点：

这些技能是你成为 Oracle 数据库高级开发者的必备基础。建议你将文中示例复制到本地环境中运行练习，加深理解。

📚 参考资料

• Oracle PL/SQL 官方文档