oracle学习笔记(二十) 子程序——函数与触发器
2023-02-18 16:39:06 时间
子程序——函数
语法
之前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;
相关文章
- HTML5项目笔记10:使用HTML5 IndexDB设计离线数据库
- HTML5项目笔记9:HTML5 Canvas 的图表报表开发
- HTML5项目笔记8:使用HTML5 的跨域通信机制进行数据同步
- HTML5项目笔记7:使用HTML5 WebStorage API构建与.NET对应的会话机制
- HTML5项目笔记6:使用HTML5 FileSystem API设计离线文件存储
- HTML5项目笔记5:使用HTML5 WebDataBase设计离线数据库
- HTML5项目笔记4:使用Audio API设计绚丽的HTML5音乐播放器
- HTML5项目笔记2:离线系统表单设计
- HTML5项目笔记1:项目准备和工具使用
- Web前端设计模式--购物车拖拽的实现...
- LinQ构建分层架构
- Web前端设计模式--构建Ajax智能搜索...
- Web前端设计模式--制作漂亮的弹出层...
- Web 前端设计模式--Dom重构...
- Web前端设计模式--jQuery验证插件...
- PBN主区代表95%时间概率的范围,这个理解对么?
- 第九节 RNP APCH保护区的绘制
- 第八节 起始进近基线转弯保护区的绘制
- 第七节 VOR/DME进近程序保护区的绘制
- 第六节 FAF与GP不工作保护区的绘制