Oracle学习笔记（五）

八、存储过程

​ 存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集。经过编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程的带有参数）来执行它。存储过程是数据库的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

语法：

create [or replace] procedure 过程名[(参数名 in/out 数据类型)]
as
begin
	PLSQL子程序体；
end;
或
create [or replace] procedure 过程名[(参数名 in/out 数据类型)]
is
begin
	PLSQL子程序体;
end 过程名；

参数只指定类型，不指定长度。
过程参数的三种模式：
in 传入参数（默认）
out 传出参数，主要用于返回程序运行结果
in out 传入传出参数

举例：

创建一个输出helloworld的存储过程
create or replace procedure helloworld is
begin
	dbms_output.put_line('helloworld');
end helloworld;

调用存储过程
begin
	helloworld
end;

给指定的员工涨工资，并打印涨前和涨后的工资（需要使用带有参数的存储过程）
create or replace procedure addsal(eno in number) is
	pemp myemp%rowtype;
begin
	select * into pemp from myemp where empno = eno;
	update myemp set sal = sal + 100 where empno = eno;
	dbms_outout.put_line('涨工资前'||pemp.sal||'涨工资后'||(pemp.sal + 100));
end addsal;

调用
begin
	addsal(eno => 7902);
end;

九、存储函数

​ 存储函数又称为自定义函数，可以接收一个或多个参数，返回一个结果。

结构：

create or replace function 函数名(Name in type,Name in type,...) return 数据类型 is 结果变量 数据类型；
begin
	逻辑部分
	return(结果变量)；
	[exception
    	异常处理部分]
end 函数名；

存储过程和存储函数的区别：

1. 存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
2. 存储函数可以在select语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
3. 存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。
   举例：

使用存储函数来查询指定运功的年薪
create or replace function empincome(eno in emp.empno%type) return number is
	psal emp.sal%type;
	pcomm emp.comm%type;
begin
	select t.sal into psal from emp t where t.empno = eno;
	return psal * 12 +nvl(pcomm,0);
end;

使用存储过程来替换上面的例子
create or replace procedure rmpincomep(eno in emp.empno%type,income out number) is
	psal emp.sal%type;
	pcomm emp.comm%type;
begin
	select t.sal,t.comm into psal,pcomm from emp t where t.empno = eno;
	income := psal*12 + nvl(pcomm,0);
end empincomep;

调用：
declare
	income number;
begin
	empincomep(7369,income);
	dbms_output.put_line(income);
end;

十、触发器

​ 数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句（inset，update，delete）在指定的表上发出时，oracle自动的执行触发器中定义的语句序列。

触发器可用于：

1. 数据确认
2. 实施复杂的安全性检查
3. 做审计，跟踪表上所做的数据操作等
4. 数据的备份和同步。

触发器的类型：

语句级触发器：在指定的操作之前或之后执行依次，不管这条语句影响了多少行。

行级触发器（for each row）：触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量，识别值的状态。

语法：

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

举例：

插入员工后打印一句话“一个新员工插入成功”
create or replace trigger testTrigger
	after insert on student
declare
	--local variables here
begin
	dbms_output.put_line('一个员工插入成功');
end testTrigger;

不能在休息时间插入员工
create or replace trigger validInsertStudent
	before insert on student
declare
	weekend varchr2(10);
begin
	select to_char(sysdate,'day') into weekend from dual;
	if weekend in ('星期一') then
	raise_application_error(-20001,'不能在非法时间插入员工');
	end ifl
end calidInsertStudent;
当执行插入时会报错

在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
insert	所有字段都是空（null）	将要插入的数据
update	更新一点该行的值	更新后的数据
delete	删除以前改行的值	所有字段都是空（null）

举例：

判断员工涨工资之后的工资的值一定要大于涨工资之前的工资
create or replace trigger addsal
	before update of sal on myemp
	for each row
begin
	if :old.sal >= :new.sal then
	raise_application_error(-20002,'涨前的工资不能大于涨后的工资');
	end if;
end;

调用
update myemp t set t.sal = t.sal - 1;