Oracle学习笔记之触发器

2022-03-20 16:22:28

触发器

触发器(trigger)是一些过程，与表关系密切，用于保护表中的数据，当一个基表被修改(INSERT、UPDATE或DELETE)时，触发器自动执行，例如通过触发器可实现多个表间数据的一致性和完整性。触发器和应用程序无关。

触发器的类型有三种：

(1)DML触发器。Oracle可以在DML(数据操纵语句)语句进行触发，可以在DML操作前或操作后进行触发，并且可以在每个行或该语句操作上进行触发。

(2)替代触发器。由于在Oracle中不能直接对有两个以上的表建立的视图进行操作，所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方法。

(3)系统触发器。在Oracle8i时，提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的时间中进行触发，如Oracle数据库的关闭或打开等。

创建触发器有以下限制：

(1)代码大小。触发器代码大小必须小于32K。

(2)触发器中有效语句可以包括DML语句，但不能包括DDL语句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。

(3)LONG、LONG RAW和LOB的限制：

① 不能插入数据到LONG或LONG RAW；

② 来自LONG或LONG RAW的数据可以转换成字符型(如char、varchar2)，但是不能超过32K；

③ 使用LONG或LONG RAW不能声明变量；

④ 在LONG或LONG RAW列中不能使用:NEW和:PARENT；

⑤ 在LOB中的:NEW变量不能修改。

每张基表最多可

建立12个触发器，它们是：

(1) BEFORE INSERT；

(2) BEFORE INSERT FOR EACH ROW；

(3) AFTER INSERT；

(4) AFTER INSERT FOR EACH ROW；

(5) BEFORE UPDATE；

(6) BEFORE UPDATE FOR EACH ROW；

(7) AFTER UPDATE；

(8) AFTER UPDATE FOR EACH ROW；

(9) BEFORE DELETE；

(10) BEFORE DELETE FOR EACH ROW；

(11) AFTER DELETE；

(12) AFTER DELETE FOR EACH ROW。

例如：做一个触发器，当删除dept表中部门时，将emp表中该部门人员信息清空；

createorreplacetrigger emp_dept

afterdelete on dept

foreachrow

declare

begin

deletefrom emp where emp.deptno=:old.deptno;

--表示删除或者修改前该记录旧的数据；

end emp_dept;

例如：修改上题，要求将删除的dept和emp表数据备份到指定表 deptOld和empOld表结构和dept、emp结构一致；

createorreplacetrigger emp_dept

afterdelete on dept

foreachrow

declare

cursor cur isselect*from emp where emp.deptno=:old.deptno;

e emp%rowtype;

department dept%rowtype;

begin

deletefrom emp where emp.deptno=:old.deptno;

ifsql%foundthen

open cur;

loop

fetch
cur into e;

insertinto empOld values(e.empno,e.ename,e.job,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);

exitwhen cur%notfound;

endloop;

endif;

insertinto deptOld values(:old.deptno,:old.dname,:old.loc);

end emp_dept;

create or replace trigger deleteDeptTrigger

after delete on dept

for each row

declare

type cur_type is ref cursor;

cur cur_type;

emp_row emp%rowtype;

begin

open cur for

select * from emp where deptno=:old.deptno;

delete from emp where deptno=:old.deptno;

loop

fetch cur into emp_row;

exit when cur%notfound;

insert into empOld values(emp_row.empno,emp_row.ename,emp_row.job,emp_row.mgr,emp_row.hiredate,emp_row.sal,emp_row.comm,emp_row.deptno);

end loop;

close cur;

insert into deptOld values(:old.deptno,:old.dname,:old.loc);

end
deleteDeptTrigger;

305讲解内容：

create or replace trigger deptTrigger

after delete on dept

for each row

declare

dept_deptno number(5);

type cur is ref cursor return emp%rowtype;

empcur cur;

emprow emp%rowtype;

begin

dept_deptno:=:old.deptno;

open empcur for select * from emp where emp.deptno=dept_deptno;

delete from emp where deptno=dept_deptno;

loop

fetch empcur into emprow;

insert into oldemp values(e.nextval,emprow.empno,emprow.ename,emprow.job,emprow.mgr,emprow.hiredate,emprow.sal,emprow.comm,emprow.deptno);

exit when empcur%notfound;

end loop;

end
deptTrigger;

创建替代(Instead_of)触发器：

Instead_of用于对视图的DML触发。由于视图有可能由多个表进行关联(Join)而成，因

而并非所有的关联都是可更新的。但是可以按如下例子来创建触发器。

例如：

在scott数据库中创建视图和触发器，以说明替代触发器。

CREATEORREPLACE VIEW emp_avgSalView

AS SELECT deptno,AVG(sal) AS avgSAL FROM emp

GROUPby deptno;

创建该视图的替代触发器：

CREATETRIGGER empAvgSalDel

INSTEADOFDELETEON
emp_avgSalView FOREACHROW

BEGIN

DELETEFROM emp WHERE deptno=:OLD.deptno;

END
cs_kc_avg_del;

创建系统触发器：

Oracle8i开始提供的系统触发器可以在DDL或数据库系统上被触发。

DDL指的是数据定义语句，如CREATE、ALTER和DROP等。而数据

库系统事件包括数据库服务器的启动或关闭，用户登录与退出等。

--创建当一个用户userA登录时自动记录一些信息的触发器。

CREATETRIGGER loguserAconnects

AFTER LOGON ONSCHEMA

BEGIN

INSERTINTO LOGIN VALUES(‘userA’,’loguserAconnects fired’);

END loguserAconnects;

面试题笔试题查询：

1、通过case when行编列，列边行；

2、删除重复行、留重复行中的一行记录。

3、Nvl和nvl2 区别

4、说出常用的10个数据库函数

5、索引如何使用？ 何时失效？

6、Sql语句级别的优化？

7、函数和存储过程区别？

8、手写存储过程。

9、Oracle分页语句和mysql分页语句？

10、Rowid？ 什么是事务？

例如：查询2000-3000之间的sal和、 4000-6000之间sal和、6000-8000sal和、

Select sum(

Case when
sal between 2000 and 3000

Then sal
else 0 end

) ,sum(

Case when sal between 4000and 6000

Then sal
else 0 end

) from emp

Select count(

Case when sal between 2000 and 3000

Then empno else null end

) ,count(

Case when sal between 4000and 6000

Then empno else null end

) from
emp

综合实例：

模拟银行汇款、取款、以及存款、余额查询操作：

Id、卡号、密码、存款余额

create table
bankMsg( id number(15) primary key,cardno
varchar2(20) unique not null,pwd
varchar2(20) not null,

money number(15,2)

);

Id、卡号、操作标示符（A存款、B取款、C转账）、操作时间、操作金额

create table
bankMsgHistory(id number(5) primary key,cardno
varchar2(20),

flag varchar2(1),markDate
date,money number(15,2)

)

写一个函数验证是否登陆成功；

写一个存储过程、用于操作存款、取款、转账以及余额查询；

（注意：由于触发器获取不到操作标示符因此不写这个触发器了）写一个触发器，当bankMsg表被修改以后，触发操作记录表数据新增；

登陆操作函数：

create or replace function bankLoginByCardno(cardno_param in varchar2,pwd_param in varchar2) return number is

total number(5);

flag number(1):=0;

begin

select count(*) into total from bankMsg where cardno =cardno_param and pwd=pwd_param;

if total >0 then

flag:=1;

end if ;

return flag;

end
bankLoginByCardno;

存储过程：

--该存储过程，根据用户名密码登陆，卡号为用户名，pwd自定义，

--根据操作标示Flag （A存款、B取款、C转账）对银行卡信息数据进行修改，
然后触发记录表记录操作记录

username ：用户名

pwd：密码

controlFlag操作标示符

money金额

targetCardno对方卡号

succssOrError 是否操作成功，1 成功，0 失败

errorMsg 操作失败原因out类型参数

showMsg 查询余额信息的out类型变量

create or replace procedure userBankCardnoMsg(username in varchar2, pwd in varchar2,

controlFlag in varchar2,money_param in number,targetCardno in varchar2,successOrError out number,

errorMsg out varchar2,

showMsg out bankMsg%rowType

) is

flag number(5):=0;

flag2 number(5):=0;

money number(15,2);

begin

successOrError:=0;--成功失败标示符默认为0

flag:=bankloginbycardno(username,pwd);

--
money_param:=nvl(money_param,0);

if flag=1 then--登陆成功

select money into money from bankMsg where cardno=username;

case controlFlag

when 'A' then--存款

update bankMsg set money=money+money_param where cardno=username;--修改金额

insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

successOrError:=1;

errorMsg:='存款成功';

select * into showMsg from bankMsg where cardno=username;

when 'B' then--取款

if money>=money_param then--判断是否有足够余额

update bankMsg set money=money-money_param where cardno=username;--修改金额

insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

successOrError:=1;

errorMsg:='取款成功';

select * into showMsg from bankMsg where cardno=username;

else

successOrError:=0;

errorMsg:='取款失败，余额不足';

end if;

when 'C' then--转账

select count(*) into flag2 from bankMsg where
cardno=targetCardno;--查看目标账号是否存在

if flag2<=0 then

successOrError:=0;

errorMsg:='转账失败，目标卡号不存在';

else

if money>=money_param then--判断是否有足够余额

update bankMsg set money=money-money_param where cardno=username;--修改金额

insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

update bankMsg set money=money+money_param where cardno=targetCardno;--修改目标账号金额

insert into bankMsgHistory values(bankcardno_seq.nextval,targetCardno,'A',sysdate,money_param);

successOrError:=1;

errorMsg:='转账成功';

select * into showMsg from bankMsg where cardno=username;

else

successOrError:=0;

errorMsg:='转账失败，余额不足';

end if;

when 'D' then --查看余额

select * into showMsg from bankMsg where cardno=username;

successOrError:=1;

errorMsg:='查询操作成功';

else

successOrError:=0;

errorMsg:='操作失败';

end case;

else

successOrError:=0;

errorMsg:='登陆失败';

end if;

end userBankCardnoMsg;

测试代码：

-- Created on 2017/10/23 by ADMINISTRATOR

declare

successFlag number(1);

errorMsg varchar2(50);

showMsg
bankMsg%rowType;

begin

-- Test statements here

--测试存款

userBankCardnoMsg('111111','123','A',10,null,successFlag,errorMsg,showMsg);

if successFlag=1
then