子程序——函数
语法
之前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;