Oracle 存储过程、存储函数、触发器
存储过程概念
什么是存储过程【procedure】?
事先运用Oracle语法,写好一段具有业务功能的程序片段,长期保存在Oracle服务器中,供客户端(sqlplus)和程序语言远程访问,类似于java中的函数
为什么要用存储过程?
(1)PLSQL每次执行都要整体运行一遍,才有结果
(2)PLSQL不能将其封装起来,长期保存在oracle服务器中
(3)PLSQL不能被其它应用程序调用,例如:Java
存储过程与PLSQL是什么关系?
存储过程是PLSQL的一个方面的应用,而PLSQL是存储过程的基础
即存储过程需要用到PLSQL
创建无参存储过程hello,无返回值
语法:create or replace procedure 过程名 as PLSQL程序
create or replace procedure hello
as
begin
dbms_output.put_line('这是我的第一个存储过程');
end;
/
删除存储过程hello
语法:drop procedure 过程名
drop procedure hello;
调用存储过程方式一,exec 存储过程名
exec hello;
调用存储过程方式二,PLSQL程序
begin
hello;
end;
/
调用存储过程方式三,Java程序
提示:用JDBC讲过的一个对象:CallableStatement
创建有参存储过程raiseSalary(编号),为7369号员工涨10%的工资,演示in的用法,默认in,大小写不敏感
--定义过程
create or replace procedure raiseSalary(pempno in number)
as
begin
update emp set sal = sal*1.2 where empno = pempno;
end;
/
--调用过程
exec raiseSalary(7369);
创建有参存储过程findEmpNameAndSalAndJob(编号),查询7788号员工的的姓名,职位,月薪,返回多个值,演示out的用法
--定义过程
create or replace procedure
findEmpNameAndSalAndJob(pempno in number,pename out varchar2,pjob out varchar2,psal out number)
as
begin
select ename,job,sal into pename,pjob,psal from emp where empno = 7788;
end;
/
--调用过程
declare
pename emp.ename%type;
pjob emp.job%type;
psal emp.sal%type;
begin
findEmpNameAndSalAndJob(7788,pename,pjob,psal);
dbms_output.put_line('7788号员工' || pename || '-------' || pjob|| '-----'|| psal);
end;
/
什么情况下用exec调用,什么情况下用PLSQL调用存储过程?
exec 调用过程无返回值
plsql适合于过程有返回值,不管多少个
用存储过程,写一个计算个人所得税的功能
--定义过程
create or replace procedure get_rax(sal in number,rax out number);
as
bal number;
begin
bal := sal -3500;
if bal <=1500 then
rax := bal * 0.03 - 0;
elsif bal<=4500 then
rax := bal * 0.1 - 105;
elsif bal<=9000 then
rax := bal * 0.1 - 105;
elsif bal<=35000 then
rax := bal * 0.1 - 105;
elsif bal<=55000 then
rax := bal * 0.1 - 105;
elsif bal<=80000 then
rax := bal * 0.1 - 105;
else
rax := bal * 0.45 - 13505;
end if;
end;
/
--调用过程
declare
--需交的税
rax number;
begin
get_rax(&sal,rax);
dbms_output.put_line('你需要交的税是' || rax);
end;
/
存储函数
创建无参存储函数getName,有返回值,语法:create or replace function 函数名 return 返回类型 as PLSQL程序段
删除存储函数getName,语法:drop function 函数名
调用存储函数方式一,PLSQL程序
调用存储函数方式二,Java程序
过程函数适合场景
声明:适合不是强行要你使用,只是优先考虑
什么情况下【适合使用】存储过程?什么情况下【适合使用】存储函数?
【适合使用】存储过程: 无返回值或有多个返回值,适合用过程
【适合使用】存储函数:有且只有一个返回值,适合用函数
什么情况【适合使用】过程函数,什么情况【适合使用】SQL?
【适合使用】过程函数:
》需要长期保存在数据库中
》需要被多个用户重复调用
》业务逻辑相同,只是参数不一样
》批操作大量数据,例如:批量插入很多数据
【适合使用】SQL:
》凡是上述反面,都可使用SQL
》对表,视图,序列,索引,等这些还是要用SQL
触发器
什么是触发器【Trigger】?
不同的DML(select/update/delete/insert)操作,触发器能够进行一定的拦截,符合条件的操作,方可操作基表,反之,不可操作基表,类似于JavaWeb的Filter,Struts2中的Interceptor
为什么要用触发器?
如果没有触发器,那么DML所有的操作,均可无限制的操作基表,这样不符合业务需求
创建语句级触发器insertEmpTrigger,当对表【emp】进行增加【insert】操作前【before】,显示"hello world"
create or replace trigger insertEmpTrigger
before
insert
on emp
begin dbms_output.put_line('hello word');
end;
/
删除触发器insertEmpTrigger,语法:drop trigger 触发器名
drop trigger insertEmpTrigger;
使用insert语句插入一条记录,引起insertEmpTrigger触发器工作
insert into emp(empno,ename,sal) values(1111,'李大懒','10000');
使用insert语句插入N条记录,引起insertEmpTrigger触发器工作
insert into emp select * from xxx_emp;
创建语句级触发器deleteEmpTrigger,当对表【emp】进行删除【delete】操作后【after】,显示"world hello"
create or replace trigger insertEmpTrigger
after
delete
on emp
begin dbms_output.put_line('呜呜,好伤心呀');
end;
/
使用delete语句删除一条记录,引起deleteEmpTrigger触发器工作
delete from emp where empno = 7788;
使用delete语句删除N条记录,引起deleteEmpTrigger触发器工作
delete from emp where 1 = 1;
星期一到星期五,且9-20点能向数据库emp表插入数据,否则使用函数抛出异常,
语法:raise_application_error('-20000','例外原因')
create or replace trigger securityTrigger
before
insert
on emp
declare
pday varchar2(10);
phour number(2);
begin
--获取星期
select to_char(sysdate,'day') into pday from dual;
--获取时间
select to_char(sysdate,'hh24') into phour from dual;
--业务
if pday in ('星期六','星期日') or phour not between 7 and 23 then
--抛例外
raise_application_error('-20000','非工作时间,不能向emp表中插入数据')
end if;
end;
/
创建行级触发器checkSalaryTrigger,涨后工资这一列,确保大于涨前工资,语法:for each row/:new.sal/:old.sal
create or replace trigger checkSalaryTrigger
after
update of sal
on emp
for each row
declare
begin
--如果涨后工资<涨前工资,则抛例外
if :new.sal <= :old.sal then
--抛例外
raise_application_error('-20200','工资不能越长越少');
end if;
end;
/
删除触发器,表还在吗?
表还在
将表丢到回收站后,触发器还在吗?
drop table emp;依然正常工作
当闪回表后,触发器会在吗?
触发器还在,依然正常工作
彻底删除表,触发器会在吗?
原来的触发器也被删除了
新的同样名字的表,是不会继承原来的触发器的