存储过程、存储函数及触发器简介

1、首先介绍什么是PL/SQL（Procedure Language/SQL）？

PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

2、存储过程和存储函数：

指存储在数据库中供所有用户程序调用的子程序（PL/SQL语言程序）叫存储过程、存储函数。两者最大的区别：存储函数可以通过return语句返回函数的值，而存储过程不可以。但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

如果只有一个返回值，用存储函数；否则用存储过程。

存储过程实例：（带参）

/*
  给指定的员工涨100，并且打印涨前和涨后的薪水
*/
create or replace procedure rasiSalary(p_id in number) --in(传入一个id值)
as
  --定义变量保存涨前工资
  p_salary s_emp.salary%type;
begin 
  --得到涨前薪水
  select salary into p_salary from s_emp where id=p_id;
  --涨100  不在需要commit 在调用存储过程的时候再commit;
  update s_emp set salary=salary+100 where id=p_id;
  
  dbms_output.put_line('涨前工资：'||p_salary||'涨后工资：'||(p_salary+100));
  end ;

通过以下两种方式可编译该存储过程：

--调用存储过程
1.exec rasiSalary(23); --传入id为23的员工号

2. begin
    rasiSalary(23);
    commit;
 end;
    /

执行该存储过程打印23号员工涨前和涨后的工资。

存储函数实例：

--查询某个员工的年收入
create or replace Function queryIncom(p_id in number)
return number
as
  --定义变量保存月薪和奖金
  p_salary s_emp.salary%type;
  P_pct s_emp.commission_pct%type;
begin
  --查询
  select salary,commission_pct into p_salary,p_pct from s_emp where id=p_id;
  
  --返回年收入
  return p_salary*12+nvl(p_pct,0);
end;

执行该存储函数：

begin
:result :=queryIncom(id =>23);
dbms_out.put_line(:result);
end;
/

3、触发器：

数据库触发器是一个与表相关联的、存储PL/SQL程序。每当一个特定的数据操作语句如

增删改查语句在指定的表上发出时，Oracle自动执行触发器中定义的语句序列。

语句级触发器：在指定的操作语句执行之前或者之后执行，不管影响了多少行（针对表操作）

/*
触发器应用一: 禁止在非工作时间插入新员工

1. 周末: to_char(sysdate,'day') in ('星期六','星期日')
2. 上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 17 
*/
create or replace trigger securityemp
before insert
on s_emp
begin
  if to_char(sysdate,'day') in ('星期六','星期日') or
     to_number(to_char(sysdate,'hh24')) not between 9 and 17  then
     --禁止insert操作
     raise_application_error(-20001,'禁止在非工作时间插入新员工');
  end if;

end;
/

编译该触发器后在其指定时间内做插入操作将有异常。

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

 /*
触发器应用二：数据的确认
涨后的薪水不能少于涨前的薪水
*/
create or replace trigger checksalary
before update
on s_emp
for each row --行级触发器
begin
  --if 涨后的薪水 < 涨前的薪水 then
  if :new.salary < :old.salary then
    raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水.涨前:'||:old.sal||'   涨后:'||:new.sal);
  end if;
end;
/

编译该行级触发器后，若对员工工资进行减操作将会有异常。