oracle学习笔记（二十） 子程序——函数与触发器

子程序——函数

语法

之前select语句中使用的函数，都是SQL内置函数，我们可以通过自定义函数更满足我们的需要。 自定义函数的语法和存储过程差不多。

复制create [or replace] $funtion_name$[(参数..)] return $data_type$ is/as begin return result; [exception]--异常处理 end $funtion_name$; /

注意点：

函数只能接收参数模式只能是in,默认不写即可函数参数和返回结果的类型只能是SQL的标准类型，PL/SQL特有类型不可使用（如boolean,string..)

使用自定义函数

SQL函数都是在select语句中使用，这也是函数与存储过程过程的区别

复制--编写一个函数获得指定部门的平均工资。 create or replace function avg_sal(p_deptno employee.sal%type) return employee.sal%type is v_avg_sal employee.sal%type; begin select avg(nvl(sal,0) into v_avg_sal from employee where deptno=p_deptno; return v_avg_sal; end avg_sal; / --select语句使用 select dname,avg_sal(deptno) from department;

触发器

触发器，当满足某种事件时候系统会自动执行（隐式执行）

语法

复制create or replace trigger 触发器名 触发时间(BEFORE|AFTER) 触发事件(INSERT OR UPDATE OR DELETE) [OF 列名] ON 触发对象(表、视图等) 触发频率[for each row] --没有for each row的话默认为语句级触发器 when (触发条件) declare --声明变量部分 begin --执行部分 end 触发器名;

说明

触发事件：

DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP 语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）

触发频率：

语句级 语句结束，触发器结束，只执行一次行级 每行又满足触发器条件，都会执行一次触发器

触发条件： NEW和OLD都是参考对象

NEW 代表新的数据对象（record）

OLD 代表原来的数据对象（record)

insert操作只有NEW, 代表着要插入的新记录 delete操作只会有OLD,代表着要删除的该条记录 update操作NEW 和 OLD 都有

PS：PLSQL使用的时候需要使用:，when里面则不需要使用

条件量( boolean型 )： 可以在when语句或者是PLSQL中使用

inserting: 代表做的是insert操作updating: 代表做的是update操作deleting: 代表做的是delete操作

例子

复制--示例 1：当向employee插入数据时自动填充(生成)主键值，（使用序列） create or replace trigger trg_auto_generate_pk before insert on employee for each row --行级触发器,每一行满足条件触发 when(NEW.empno is null) --当插入的数据主键为空，自动生成，NEW表示当前INSERT的数据，不需要使用: declare empno number; begin --之前创建的序列 select emp_seq.nextval into empno from dual; if inserting then :NEW.empno := empno;--使用:引用 end if; end trg_auto_generate_pk; / /* 示例 2：当删除数据时，自动备份 a. 创建备份表 b. 创建触发器 */ --这里也可以使用动态SQL创建表 --创建备份表，只复制结构，不复制数据 create table employee_dump as select * from employee where 1=2; --创建触发器 create or replace trigger trg_backup_employee after delete on employee for each row when(OLD.empno is not null) declare begin insert into employee_dump values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno); commit; exception when others then null; --还可以把异常的信息插入到日志表 end trg_backup_employee; / --禁用触发器：disable alter trigger trg_backup_employee disable; --启用触发器：enable alter trigger trg_backup_employee enable; --禁用某个表上所有的触发器：disable all triggers; alter table employee disable all triggers; --删除触发器 drop trigger trg_backup_employee;

转载于:https://www.cnblogs.com/chaoyang123/p/11549614.html

相关资源：JAVA上百实例源码以及开源项目