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;

提问之前,请先看提问须知 点击右侧图标发起提问 联系我 或者加入QQ群一起学习 Stars-One安卓学习交流群 TornadoFx学习交流群:1071184701
posted @ 2019-06-03 20:10  Stars-one  阅读(659)  评论(0编辑  收藏  举报