当前位置: 首页 > news >正文

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
BEGINresult := num1 + num2;DBMS_OUTPUT.PUT_LINE('两数之和为: ' || result);
END;
调用方式:
DECLAREv_result NUMBER;
BEGINadd_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
ISv_salary NUMBER;
BEGINSELECT salary INTO v_salaryFROM employeesWHERE employee_id = emp_id;RETURN v_salary;
EXCEPTIONWHEN NO_DATA_FOUND THENRETURN NULL;
END;
调用方式:
DECLAREv_salary NUMBER;
BEGINv_salary := get_salary(100);IF v_salary IS NOT NULL THENDBMS_OUTPUT.PUT_LINE('员工工资为: ' || v_salary);ELSEDBMS_OUTPUT.PUT_LINE('未找到该员工');END IF;
END;

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

SET SERVEROUTPUT ON; -- 必须先开启输出BEGINDBMS_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
BEGININSERT 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
BEGININSERT 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)

部分描述
Package Specification声明变量、常量、过程、函数接口
Package Body实现声明的逻辑

🔹 2. 创建包规范(Spec)

CREATE OR REPLACE PACKAGE emp_pkg ISPROCEDURE 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 ISPROCEDURE update_salary(emp_id IN NUMBER, new_salary IN NUMBER) ISBEGINUPDATE employeesSET salary = new_salaryWHERE employee_id = emp_id;END;FUNCTION get_fullname(emp_id IN NUMBER) RETURN VARCHAR2 ISv_name VARCHAR2(100);BEGINSELECT first_name || ' ' || last_name INTO v_nameFROM employeesWHERE employee_id = emp_id;RETURN v_name;END;END emp_pkg;

🔹 4. 调用包中的方法

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

✅ 总结

以下内容为主要点:

模块技能点
存储过程支持输入输出参数的过程定义与调用
函数定义使用 RETURN 返回值,处理异常
触发器机制DML 触发器、INSTEAD OF 触发器
包的使用封装多个过程和函数,提升模块化能力
实战应用审计日志、视图增删改、业务封装等场景

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


📚 参考资料

  • Oracle PL/SQL 官方文档
http://www.lryc.cn/news/582468.html

相关文章:

  • 【OceanBase诊断调优】—— 执行计划显示分区 PARTITIONS[P0SP9] 如何查询是哪个分区?
  • 数据结构与算法:博弈类问题
  • 服务器经常出现蓝屏是什么原因导致的?如何排查和修复?
  • node.js中yarn、npm、cnpm详解
  • npm : 无法加载文件 D:\Node\npm.ps1,因为在此系统上禁止运行脚本。
  • 【QT】-隐式转换 explicit用法
  • React18+TypeScript状态管理最佳实践
  • 说说SpringBoot常用的注解?
  • 【Nginx】Nginx代理WebSocket
  • Ollama+OpenWebUI 0.42+0.3.35 最新版一键安装教程,解决手动更新失败问题
  • kafka如何让消息均匀的写入到每个partition
  • OpenWebUI(5)源码学习-后端socket通信模块
  • App Trace功能实战:一键拉起应用实践
  • 【保姆级图文详解】RAG 实战(Spring AI + 本地知识库)旅游知识库问答
  • 微软上线 Deep Research 预览版:o3+必应赋能研究自动化
  • OGRE 3D----6. 背景图片渲染实现详解
  • 【Unity3D】微信小游戏适配安全区域或胶囊控件(圆圈按钮)水平高度一致方案
  • element el-table渲染二维对象数组
  • 什么是 3D 文件?
  • 开源 python 应用 开发(三)python语法介绍
  • 【电脑】主要组成部分
  • 装备制造数字孪生底座平台
  • 【网络】Linux 内核优化实战 - net.ipv4.tcp_syn_retries
  • 利用AI技术快速提升图片编辑效率的方法
  • 数据结构 --- 栈
  • django-ckeditor配置html5video实现视频上传与播放
  • word中的单位详解
  • 算法化资本——智能投顾技术重构金融生态的深度解析
  • 使用阿里云/腾讯云安装完成mysql使用不了
  • 分库分表之实战-sharding-JDBC广播表