oracle存储过程、函数、触发器应用举例

### Oracle存储过程、函数、触发器应用举例 #### 一、存储过程 **1.1 存储过程的概念** 存储过程是一种预编译的SQL代码集合，它可以在Oracle数据库服务器上执行复杂的逻辑处理任务。存储过程可以接受输入参数并返回输出结果。与普通的SQL语句相比，存储过程能够提高应用程序的性能，简化数据库管理和增强数据的安全性。 **1.2 带参数的存储过程** 存储过程可以根据需要接受输入参数，并且可以有输出参数。在Oracle中，可以通过`IN`关键字标识输入参数，通过`OUT`关键字标识输出参数。 **示例：** 创建一个名为`P_GETAVGSAL`的存储过程，接受一个员工编号作为输入参数，并输出该员工所在部门的平均工资。 ```sql CREATE OR REPLACE PROCEDURE P_GETAVGSAL ( ENO EMP.EMPNO%TYPE, -- 输入参数 AVG_SAL OUT NUMBER -- 输出参数 ) AS BEGIN SELECT AVG(SAL) INTO AVG_SAL FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO = ENO); END; ``` **1.3 调用存储过程** 存储过程可以通过两种方式进行调用： - **位置调用法**：如`P_GETAVGSAL(7782, ASAL)`。 - **参数名称赋值调用法**：如`P_GETAVGSAL(ENO => 7782, AVG_SAL => ASAL)`。 **示例：** ```sql DECLARE ASAL NUMBER; BEGIN P_GETAVGSAL(7782, ASAL); DBMS_OUTPUT.PUT_LINE('7782员工所在部门的平均工资 ' || ASAL); END; ``` #### 二、函数 **2.1 函数的概念** 函数类似于存储过程，但是它必须有一个返回值。函数可以被用来执行计算并返回结果，通常被用作更高级别的SQL查询的一部分。 **2.2 创建函数** 函数的基本语法如下： ```sql CREATE OR REPLACE FUNCTION <函数名称> RETURN <数据类型> AS -- 变量声明 BEGIN -- 执行语句 EXCEPTION -- 异常处理 END; ``` **示例：** 创建一个名为`F_GETAVGSAL`的函数，接受一个员工编号作为输入，并返回该员工所在部门的平均工资。 ```sql CREATE OR REPLACE FUNCTION F_GETAVGSAL ( ENO EMP.EMPNO%TYPE ) RETURN NUMBER AS AVG_SAL NUMBER; BEGIN SELECT AVG(SAL) INTO AVG_SAL FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO = ENO); RETURN AVG_SAL; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, '出错'); END; ``` **2.3 调用函数** 可以通过SQL语句或匿名PL/SQL块来调用函数。 ```sql SELECT F_GETAVGSAL(7782) FROM DUAL; ``` 或者 ```sql DECLARE VSAL NUMBER; BEGIN VSAL := F_GETAVGSAL(7782); DBMS_OUTPUT.PUT_LINE(VSAL); END; ``` #### 三、触发器 **3.1 触发器的概念** 触发器是一种特殊的存储过程，它是在特定的数据库事件（如插入、删除或更新记录）发生时自动执行的代码段。触发器可用于加强数据完整性、实施业务规则等。 **3.2 创建触发器** 触发器的基本语法如下： ```sql CREATE OR REPLACE TRIGGER <触发器名称> [AFTER | BEFORE] [INSERT | DELETE | UPDATE OF <字段列表>] ON <表名> [FOR EACH ROW] [WHEN <条件>] DECLARE BEGIN EXCEPTION END; ``` **示例：** 创建一个触发器，确保学生表`STUDENT`中的入学时间字段`ENROLLTIME`总是小于当前系统时间。 ```sql CREATE TABLE STUD ( STUID NUMBER(4) PRIMARY KEY, -- 学号 STUNAME VARCHAR2(20), -- 姓名 ENROLLTIME DATE -- 入学时间 ); CREATE OR REPLACE TRIGGER TRIGGER_ENROLL_TIME BEFORE INSERT ON STUD FOR EACH ROW WHEN (NEW.ENROLLTIME >= SYSDATE) DECLARE BEGIN IF :NEW.ENROLLTIME >= SYSDATE THEN RAISE_APPLICATION_ERROR(-20001, '入学时间不能晚于当前时间'); END IF; END; ``` 通过以上介绍，我们可以看到Oracle中的存储过程、函数和触发器都是强大的工具，它们能够帮助开发人员更高效地管理数据库，并实现复杂的数据操作和逻辑处理。这些特性对于构建健壮的应用程序至关重要。