Oracle 存储过程、存储函数、触发器

来源：互联网发布：成都富士康java 编辑：程序博客网时间：2024/06/06 00:00

存储过程概念

什么是存储过程【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

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)

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)

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);

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；依然正常工作

当闪回表后，触发器会在吗？

触发器还在，依然正常工作

彻底删除表，触发器会在吗？

原来的触发器也被删除了

新的同样名字的表，是不会继承原来的触发器的

阅读全文

1 0