oracle存储过程和存储函数&触发器
oracle存储过程和存储函数
指存储在数据库中供所有用户程序调用的子程序叫存储过程，存储函数
存储过程和存储函数的相同点：完成特定功能的程序
存储过程和存储函数的区别：是否用return语句返回值
=========================创建和使用存储过程=============================
用create procedure命令建立存储过程和存储函数
语法：
create [or replace] procedure 过程名(参数列表)
as
PLSQL子程序体;
实例：带参数的存储函数
create or replace procedure RaiseSalry(eno in number)
as
psal emp.sal%type;
begin
select sal into psal from emp where EMPNO=eno;
update emp set sal = sal +100 where EMPNO=eno;
DBMS_OUTPUT.PUT_LINE('涨工资前的薪水'||psal||'涨工资后的薪水'||(psal+100));
end;
/
===========================创建存储函数的语法============================
create [or replace] function 函数名(参数列表)
return 函数值类型
as
plsql子程序体;
实例：查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as
--定义变量保存员工薪水和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
--得到员工的月薪和奖金
select sal,comm into psal,pcomm from emp where empno=eno;
--直接返回年收入
return psal*12+pcomm;
end;
/
===========================in和out参数=============================
过程和函数可以通过out指定一个或多个输出参数，我们可以利用out参数，在过程和函数中实现多个返回值
原则：如果只有一个返回值，就用存储函数，否则，就用存储过程
实例：out参数，查询员工姓名，月薪和职位
create or replace procedure queryempinform(eno in number,
pname out varchar2,
psal out number,
pjob out varchar2)
as
begin
--得到该员工的姓名，月薪和职位
select ename,sal,ejob into pname,psal,pjob from emp where empno=eno;
end;
/
==========================在out参数中使用光标=========================
案例：查询某个部门中所有员工的所有信息
--包头:声明
create or replace package mypackage as
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;
--包体:实现包头声明的所有方法
create or replace package body mypackage as
procedure queryEmpList(dno in number,empList out empcursor)
as
begin
open empList for select * from emp where deptno=dno;
end queryEmpList;
end mypackage;
oracle触发器
========================什么是触发器（trigger）==========================
①数据库触发器是一个与表相关联的，存储的pl/sql程序
②没当一个特定的数据操作语句（insert，update，delete）在指定的表上发出时，oracle自动的执行触发器中定义的语句序列
如何创建触发器
create trigger saynewemp
after insert
on emp
declare
begin
dbms_output.put_line('成功插入新员工');
end;
/
============================触发器应用场景==============================
1.复杂的安全性检查
2.数据确认
3.实现审计功能
4.完成数据的备份和同步
==============================触发器的语法==============================
创建触发器的语法
create [or replace] trigger 触发器名
{before|after}
{delete|insert|update[of 列名]}
on 表名
[for each row [when(条件)]]
plsql块
==============================触发器的类型==============================
语句级触发器：在指定的操作语句之前或之后执行一次，不管这条语句影响了多少行
行级触发器：触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量，识别值得状态
=================================案例=================================
触发器应用场景一：实施复杂的安全性检查
禁止在非工作时间插入新员工
1，周末：to_char(sysdate,'day')in('星期六','星期日')
2，上班前，下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 18
create or replace trigger securityemp
before insert
on emp
begin
if to_char(sysdate,'day')in('星期六','星期日') or
to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
--禁止insert新员工
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end;
/
触发器应用场景二：数据的确认
涨工资不能越涨越少
create or replace trigger checksalary
before update
on emp
for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水,涨后的薪水：'||:new.sal||'涨前的薪水'||:old.sal);
end if;
end;
/
触发器应用场景三：数据库审计
创建基于值的触发器
给员工涨工资，当涨后的薪水超过6000，审计该员工的信息
创建表用于保存审计信息
create table audit_info
(
information varchar2(200)
);
create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
--当涨后的薪水大于5000，插入审计信息
if :new.sal>5000 then
insert into audit_info values(:new.empno||' '||new.ename||'
'||:new.sal);
end if;
end;
/
触发器应用场景四：数据的备份和同步
利用触发器实现数据的同步部分（分布式数据库）
当给员工涨工资后自动备份到备份表中
create or replace trigger sync_salary
after update
on emp
for each row
begin
--当主表更新后，自动更新备份表
update emp_back set sal=:new.sal where empno=:new.empno;
end;
/
