oracle数据库(六)

存储过程和函数以及触发器

PL/SQL程序块都是匿名块，当需要再次调用这些程序块时，只能再次编写程序块的内容，然后又oracle重新编译执行，为了提高系统的应用性能，oracle提供了一系列“命名程序块”，包括存储过程，函数，触发器和包(包没有涉及)。本章将介绍这些命名程序块。

1.创建存储过程

create [ or replace ] procedure 存储过程名称（参数名 in 类型, 参数名  out 类型, 参数名 in out 类型）
is|as
 变量声明部分;
begin
 逻辑部分
[exception
 异常处理部分]
end;

2.三种参数的使用

2.1 参数in指定输入参数，有存储过程的调用者为其赋值。

2.2参数out指定输出参数，由存储过程中的语句为其赋值，并返回给用户。

create procedure select_emp
(emp_num in number,emp_name out varchar2) as
begin
  select ename into emp_name
  from emp where empno=emp_num;
end select_emp;

2.3 IN OUT参数，既可以传进来也可以传出去 

create procedure exchange_value
(value in out number, value2 in out number)
as
temp1 number;
temp2 number;
begin
temp1 = value1;
temp2 = value2;
value1=temp2;
value2=temp1
end xchange_value;

3.函数

函数与存储过程很相似，他同样可以接受用户的传递值，也可以向用户返回值，不同之处在于函数必须有返回值。

语法如下：

create [ or replace ] function 函数名称（参数名称 参数类型, 参数名称 参数类型, ...）
return 结果变量数据类型
id
 变量声明部分;
begin
 逻辑部分;
 return 结果变量;
[exception
 异常处理部分]
end;

例子：

create function get_name(emp_num number)
return varchar2 as
emp_name emp.ename%TYPE
begin
select ename into emp_name from emp where empno=emp_num;
return emp_name;
end get_name;

4.触发器

触发器是一种特殊的存储过程，它在发生某种数据库事件事由oracle系统自动触发，触发器通常用来加强完整性约束和业务规则等，对于表来说，触发器可以实现比CHECK约束更复杂的约束。

触发器主要类型由DDL触发器、系统触发器、instead of触发器和DDL触发器

4.1创建触发器

create [or replace] trigger 触发器名
 before | after
 [delete | insert | update [of 列名...]]
 ON 表名
 [for each row] [when(条件)]
declare
 ……
begin
 PLSQL 块
end;

4.2DML触发器

DML触发器主要包括insert、delete、updata操作，任何触发器都可以按触发时间分为after或者before触发器。

接下来举个例子：

创建一个学生表：

create table student(
sid number(4),
sname varchar2(10),
sage number(4)
);

我们插入几条记录

insert into student values (1001,'xiaoqing',24);
insert into student values (1002,'xiaojing',25);
insert into student values (1003,'xiaoxi',26);

接下来创建一个表用来存储对student的修改

creat table record(

content varchar2(50),

rtime timestamp

);

接下来我们创建一个触发器，要求更改student表后，在record表中记录修改操作，并保存修改前的行数据，创建触发器的语句如下：

create trigger update_student_trigger
after update
on student
for each row
begin
       insert into record values
       ('执行了update操作，执行该操作前的数据为：sid=' || :OLD.sid || '，sname=' || :OLD.sname || '，sage='|| :OLD.sage,SYSDATE);
end update_student_trigger;

4.3instead of触发器

用来执行一个替代操作来代替触发事件的操作，而触发事件本身最终不会执行。

不过instead of不能针对表，只能针对视图进行操作，我们知道如果视图的列如果进行了数学或者函数运算，就不能对该列进行DML操作，这时可以使用instead of触发器。

create view student_view
as
select sid,sname,sage+1 new_age
from student
with check option;

如果我们直接对视图进行插入数据操作会报错，我们可以使用instead of触发器来解决这个问题。

create trigger insteadod_student_view
instead of insert
on student_view
for each row
begin
  instead into student(sid,sname,sage)
  values( :new.sid,:new.sname,:new.new_age);
end insteadof_studnet_view;

4.3系统触发器

系统触发器是指数据库系统事件触发的触发器，比如登录、关闭数据库。

4.4DDL触发器

DDL触发器由DDL语句触发触发事件包括alter、create、drop等。创建DDL触发器需要用户有DBA权限。