Oracle_PL/SQL(6) 触发器(序列、视图)

深碍√TFBOYSˉ_ 2021-11-22 16:46 435阅读 0赞

序列  
1.创建序列  
create sequence seq\_alog  
start with 1  
increment by 1  
maxvalue 999999999999999999999999999  
minvalue 1  
cache 20;  
说明：  
start with 1 --表示序列从1开始  
increment by 1 --表示序列每次自增1  
maxvalue --序列的最大值  
minvalue --序列的最小值  
cache 20 --缓存区大小

2.nextval和currval属性  
select seq\_alog.nextval from dual;  
select seq\_alog.currval from dual;

3.修改序列  
\--alter sequence seq\_alog start with 2; 起始值不能修改  
alter sequence seq\_alog increment by 2;  
alter sequence seq\_alog maxvalue 9999999;  
\--alter sequence seq\_alog minvalue 100; 修改最小值不能比当前值大  
alter sequence seq\_alog minvalue 3;  
alter sequence seq\_alog nocache;  
  
rename seq\_alog to seq\_alog1;  
4.删除序列  
drop sequence seq\_alog1;  
  
练习：  
修改转账业务日志  
create or replace procedure proc\_trans\_value(p\_acid\_out number,p\_acid\_in number,p\_value number)  
is  
l\_cnt number(8):=0;  
l\_value account.value%type;  
begin  
select count(1) into l\_cnt from account where accountid=p\_acid\_out;  
if l\_cnt=1 then  
select value into l\_value from account where accountid=p\_acid\_out;  
if l\_value>=p\_value then  
update account set value=value-p\_value where accountid=p\_acid\_out;  
insert into ac\_log values (seq\_alog.nextval,p\_acid\_out,'转出',p\_value,sysdate);  
else  
raise\_application\_error(-20001,'\[转出账户金额不足\]');  
end if;  
else  
raise\_application\_error(-20001,'\[转出账户不存在\]');  
end if;

select count(1) into l\_cnt from account where accountid=p\_acid\_in;  
if l\_cnt=1 then  
update account set value=value+p\_value where accountid=p\_acid\_in;  
insert into ac\_log values (seq\_alog.nextval,p\_acid\_in,'转入',p\_value,sysdate);  
else  
raise\_application\_error(-20001,'\[转入账户不存在\]');  
end if;  
end proc\_trans\_value;

5.创建BI触发器  
create or replace trigger tri\_alog\_bi  
before insert on ac\_log for each row  
begin  
select seq\_alog.nextval into :new.log\_id from dual;  
end;  
/  
show err;

练习：  
优化转账业务日志  
create or replace procedure proc\_trans\_value(p\_acid\_out number,p\_acid\_in number,p\_value number)  
is  
l\_cnt number(8):=0;  
l\_value account.value%type;  
begin  
select count(1) into l\_cnt from account where accountid=p\_acid\_out;  
if l\_cnt=1 then  
select value into l\_value from account where accountid=p\_acid\_out;  
if l\_value>=p\_value then  
update account set value=value-p\_value where accountid=p\_acid\_out;  
insert into a\_log (accountid,action,value) values (p\_acid\_out,'转出',p\_value);  
else  
raise\_application\_error(-20001,'\[转出账户金额不足\]');  
end if;  
else  
raise\_application\_error(-20001,'\[转出账户不存在\]');  
end if;

select count(1) into l\_cnt from account where accountid=p\_acid\_in;  
if l\_cnt=1 then  
update account set value=value+p\_value where accountid=p\_acid\_in;  
insert into a\_log (accountid,action,value) values (p\_acid\_in,'转入',p\_value);  
else  
raise\_application\_error(-20001,'\[转入账户不存在\]');  
end if;  
end proc\_trans\_value;  
/  
show err;

触发器  
1.定义  
触发器是特殊的存储程序，由数据库中的事件触发，  
在执行命令时、执行数据库管理系统动作之间运行，  
用PL/SQL编写，可以捕获创建、修改或删除对象的事件，  
可以捕获表或视图中的插入、更新或删除操作，  
可以监控数据库或模式的状态变化及用户动作的变化。

用途：  
控制DML语句的行为；  
控制DDL语句的行为；  
实施参照完整性、复杂业务规则和安全性策略；  
在修改视图中的数据时控制和重定向DML语句；  
通过创建透明日志来审核系统访问和行为的信息。  
2.分类  
数据操作语言(DML)行级触发器  
数据操作语言(DML)语句级触发器  
instead-of(视图)触发器  
复合触发器  
数据定义语言(DDL)触发器  
系统或数据库事件触发器

3.数据操作语言(DML)行级触发器  
执行DML操作时,每作用一行就触发一次的触发器.  
审计数据变化时,可以使用行触发器  
行级触发器：DML事件修改的每一行都会激发触发器执行一次。

3.1 语法  
create \[or replace\] trigger tri\_name  
触发时机 触发事件 on 表名  
\[referencing old as old |new as new\]  
for each row  
\[when condition\]  
declare  
变量  
begin  
触发操作  
end;  
说明：  
当建立dml触发器时,需要指定触发时机(before,after),  
触发事件(insert,update,delete),表名,触发类型,触发条件,操作。  
触发时机：指触发器的执行与dml语句执行的先后顺序,之前(before),之后(after)，  
只能选择一个时机。  
触发事件：指定导致触发器执行的dml操作,也即insert,update,delete操作，  
可以同时指定多个操作并用or连接  
表名：必须指定dml操作所对应的表  
触发类型：分为行级(for each row)和语句级。  
行级(for each row)：执行dml一次操作时,每影响一行就触发一次的触发器。  
语句级：执行一次dml操作时,不管影响了多少行，只触发一次的触发器。  
触发条件when condition：指定执行触发器代码的条件，  
只允许在行触发器上指定触发条件。  
触发操作:指定触发器执行代码.  
  
根据触发器的触发时机和触发事件不同，将组合出14种方式：  
bi\\bu\\bd\\biu\\bid\\bud\\biud  
ai\\au\\ad\\aiu\\aid\\aud\\aiud  
触发器的命名：tri\_表名\_触发时机和触发事件  
  
3.2 BI触发器  
create or replace trigger tri\_student\_bi  
before insert on student for each row  
begin  
select seq\_alog.nextval into :new.a\_id from dual;  
end;  
/  
show err;

3.2-2 BD触发器  
create or replace trigger tri\_dept\_bd  
before delete on dept for each row  
declare  
begin  
\--delete from emp where deptno=:old.deptno;  
update emp set deptno=40 where deptno=:old.deptno;  
end;  
/  
show err;

delete from dept where deptno=10;  
select \* from dept  
select \* from emp

3.3 BU触发器  
建一个触发器，当降低员工工资时，提示“给员工降薪的都不是好公司！”  
update emp set sal=2000 where empno=7788;

create or replace trigger tri\_emp\_bu  
before update of sal on emp  
for each row  
begin  
if :new.sal<:old.sal then  
raise\_application\_error(-20010,'给员工降薪的都不是好公司！');  
end if;  
end;  
/  
show err;

create or replace trigger tri\_emp\_bud  
before update or delete on emp  
for each row  
begin  
if updating and :new.ename<>:old.ename or deleting then  
raise\_application\_error(-20010,'人名不能被修改！');  
end if ;  
end;

3.3 AIUD触发器  
触发事件函数inserting、updating、deleting  
伪记录:new、:old 只能在行级触发器使用  
伪记录与触发事件函数的关系：  
inserting时只能用:new  
updating时可以用:new和:old  
deleting时只能用:old

1.建表 a\_log  
create table a\_log  
(  
a\_id number(16) not null primary key,  
accountid number(16),  
action varchar2(20),  
value number(10,2),  
a\_date date  
);  
2.创建序列  
create sequence seq\_log  
start with 1  
increment by 1;  
3.bi触发器  
create or replace trigger tri\_alog\_bi  
before insert on a\_log for each row  
begin  
select seq\_alog.nextval into :new.a\_id from dual;  
end;  
/  
show err;  
4.清空测试数据  
delete from account;  
delete from a\_log;  
5.AIUD触发器  
create or replace trigger tri\_account\_aiud  
after insert or update or delete on account for each row  
declare  
begin  
if inserting then  
insert into a\_log (accountid,action,value,a\_date)  
values (:new.accountid,'新增账户',:new.value,sysdate);  
elsif updating then  
if :new.value<:old.value then  
insert into a\_log (accountid,action,value,a\_date)  
values (:new.accountid,'转出',:old.value-:new.value,sysdate);  
elsif :new.value>:old.value then  
insert into a\_log (accountid,action,value,a\_date)  
values (:new.accountid,'转入',:new.value-:old.value,sysdate);  
else  
insert into a\_log (accountid,action,value,a\_date)  
values (:new.accountid,:old.name||'更名为'||:new.name,0,sysdate);  
end if;  
elsif deleting then  
insert into a\_log (accountid,action,value,a\_date)  
values (:old.accountid,'删除账户',:old.value,sysdate);  
end if;  
end;  
/  
show err;  
6.转账过程  
create or replace procedure proc\_trans\_value(p\_acid\_out number,p\_acid\_in number,p\_value number)  
is  
l\_cnt number(8):=0;  
l\_value account.value%type;  
begin  
select count(1) into l\_cnt from account where accountid=p\_acid\_out;  
if l\_cnt=1 then  
select value into l\_value from account where accountid=p\_acid\_out;  
if l\_value>=p\_value then  
update account set value=value-p\_value where accountid=p\_acid\_out;  
else  
raise\_application\_error(-20001,'\[转出账户金额不足\]');  
end if;  
else  
raise\_application\_error(-20001,'\[转出账户不存在\]');  
end if;

select count(1) into l\_cnt from account where accountid=p\_acid\_in;  
if l\_cnt=1 then  
update account set value=value+p\_value where accountid=p\_acid\_in;  
else  
raise\_application\_error(-20001,'\[转入账户不存在\]');  
end if;  
end proc\_trans\_value;  
/  
show err;

7.测试  
\--新增账户数据  
insert into account values(111,'acdd',10000);  
insert into account values(112,'acdd1',100);  
\--转账  
exec proc\_trans\_value(111,112,1000);  
exec proc\_trans\_value(111,112,2000);  
exec proc\_trans\_value(112,111,1000);  
\--账户更名  
update account set name='acd' where accountid=111;  
update account set name='acde' where accountid=112;  
\--删除账户  
delete from account where accountid=111;  
delete from account where accountid=112;

练习：  
1.用触发器实现  
删除学生时，同时删除学生的各门课程成绩。  
2.用触发器实现  
新增学生时，同时增加学生4门课的成绩，每门课程成绩为该门课程的平均成绩。  
作业：  
1.给EMP表建一个触发器，监控工资的变化（自己建表记录工资变化）  
建表：  
工资变化日志表（id,ename,变前工资、变后工资、升降说明、时间）  
建sequence  
给日志表建BI触发器  
给EMP建AU触发器  
修改EMP表的工资信息，检查日志表的记录。  
drop trigger tri\_emp\_bu;  
alter trigger tri\_emp\_bu disable;

3.5 AU触发器  
AFTER行触发器可以用来审计表中数据的变化(DML)  
创建一个表（姓名、变前工资、变后工资、变更时间），  
创建AU触发器审计emp表职员的工资变化  
create table audit\_emp\_change(  
name varchar2(10),oldsal number(6,2),newsal number(6,2),time date);  
  
create or replace trigger tri\_emp\_au  
after update of sal on emp  
for each row  
declare  
v\_temp number;  
begin  
select count(\*) into v\_temp from audit\_emp\_change where name=:old.ename;  
if v\_temp=0 then  
insert into audit\_emp\_change values(:old.ename,:old.sal,:new.sal,sysdate);  
else  
update audit\_emp\_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate  
where name=:old.ename;  
end if;  
end;

3.6 带触发条件when condition的触发器  
使用行触发器时,默认情况下会在每个被作用行上执行一次触发器代码,  
为了使得在特定条件下执行行触发器代码,就需要使用WHEN子句对触发器加以限制  
指使用WHEN子句指定一个BOOLEAN表达式,返回为TRUE时触发  
create or replace trigger tri\_emp\_au  
after update of sal on emp  
for each row  
when (old.job='SALESMAN')  
declare  
v\_temp number;  
begin  
select count(\*) into v\_temp from audit\_emp\_change where name=:old.ename;  
if v\_temp=0 then  
insert into audit\_emp\_change values(:old.ename,:old.sal,:new.sal,sysdate);  
else  
update audit\_emp\_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate  
where name=:old.ename;  
end if;  
end;  
/  
show err;

3.7 行级触发器触发顺序  
before行级触发器 -》dml操作(增、删、改) -》after行级触发器  
对基表的操作使用before行级触发器，  
对其它表的操作使用after行级触发器。  
3.8 行级触发器注意事项  
出发器只能包含SELECT,INSERT,UPDATE,DELETE语句,  
不能包含DDL语句(CREATE,ALTER,DROP)和事务控制语句(COMMIT,ROLLBACK,SAVEPOINT)。  
编写DML触发器时,不能从触发器所对应的基表中读取数据.  
建立时不会出现错误,但在执行相应触发操作时会显示错误信息  
（ORA-04091:表xxx发生了变化，触发器不能读它）  
create or replace trigger tri\_emp\_biud  
before insert or update or delete on emp  
for each row  
declare  
v\_cnt number;  
begin  
select count(1) into v\_cnt from emp;  
if v\_cnt<>14 then  
raise\_application\_error(-20010,'记录量不能改变！');  
end if ;  
end;

4.管理触发器  
4.1 显示触发器信息:在数据字典中USER\_TRIGGERS  
select \* from user\_triggers;  
触发器的代码大小不能超过32K.(可调用存储过程解决)

4.2禁止触发器  
使触发器临时失效,处于enable状态,则会触发  
alter trigger TRI\_EMP\_BU disable;

4.3激活触发器  
使触发器重新生效  
alter trigger TRI\_EMP\_BU enable;

4.4禁止或激活表的所有触发器  
alter table emp disable all triggers;  
alter table emp enable all triggers;

4.5重新编译触发器  
使用ALTER TABLE命令修改表结构时,会使得触发器转变为INVALID状态,  
需要重新编译触发器,才能继续生效  
alter trigger tri\_account\_aiud compile;

4.6删除触发器  
drop trigger TRI\_EMP\_BIUD;

5.数据操作语言(DML)语句级触发器  
在insert、update、delete之前或之后触发，  
每条DML语句无论影响多少行数据，只触发一次触发器。  
语句级触发器：无论有多少行受到DML事件的影响，触发器只执行一次。  
当执行DML操作时会自动执行触发器的相应代码.  
使用语句触发器时,不能记录列数据的变化  
5.1 语法  
create \[or replace\] trigger tri\_name  
触发时机 触发事件 on 表名  
\-- \[referencing old as old |new as new\]  
\-- for each row  
\[when condition\]  
declare  
变量  
begin  
触发操作  
end;  
说明：  
DML语句级触发器就是将 行级触发器的for each row关键字删掉即可。

5.2 建立biud语句触发器  
create or replace trigger tri\_emp\_biud  
before insert or update or delete on emp  
begin  
if to\_char(sysdate,'d') in (4) then  
raise\_application\_error(-20001,'不能在休息日改变雇员信息');  
\--dbms\_output.put\_line('tri\_emp\_biud');  
end if;  
end;  
/  
show err;

5.3 在biud语句触发器中使用触发事件函数inserting、updating、deleting  
create or replace trigger tri\_emp\_biud  
before insert or update or delete on emp  
begin  
if to\_char(sysdate,'d') in (3) then  
case  
when inserting then  
raise\_application\_error(-20001,'不能在休息日增加雇员');  
when updating then  
raise\_application\_error(-20002,'不能在休息日更新雇员');  
when deleting then  
raise\_application\_error(-20003,'不能在休息日解雇雇员');  
end case;  
end if;  
end;

5.4 建立AFTER语句触发器  
为了审计DML操作,或者在DML操作之后执行汇总运算.  
(计算INSERT,UPDATE,DELETE的操作次数)  
create table audit\_table(  
name varchar2(20),  
ins number(8),  
upd number(8),  
del number(8),  
endtime date);

create or replace trigger tri\_emp\_aiud  
after insert or update or delete on emp  
declare  
v\_temp int;  
begin  
select count(\*) into v\_temp from audit\_table where name='emp';  
if v\_temp=0 then  
insert into audit\_table values('emp',0,0,0,sysdate);  
end if;  
case  
when inserting then  
update audit\_table set ins=ins+1,endtime=sysdate where name='emp';  
when updating then  
update audit\_table set upd=upd+1,endtime=sysdate where name='emp';  
when deleting then  
update audit\_table set del=del+1,endtime=sysdate where name='emp';  
end case;  
end;  
/  
show err;

5.5 DML触发器的触发顺序  
DML触发器在单行数据上的触发顺序  
对于单行数据而言,无论是语句触发器,还是行触发器,  
触发器代码实际只被执行一次,顺序为:  
before语句,before行,dml操作,after行,after语句  
DML触发器在多行数据上的触发顺序  
对于多行数据而言,语句触发器只被执行一次,而行触发器在每个作用行上都执行一次

6.复合触发器  
6.1定义：  
复合触发器既是语句级又是行级触发器，有4个触发点，  
激发语句之前、激发语句每一行发生变化之前，  
激发语句每一行发生变化之后、激发语句之后。

6.2语法：  
语法：  
create or replace trigger tri\_name  
for insert or update or delete on table\_name  
compound trigger  
\[before statement is  
declaration\_statments;  
begin  
execution\_statments;  
end before statement;\]  
\[before each row is  
declaration\_statments;  
begin  
execution\_statments;  
end before each row;\]  
\[after each row is  
declaration\_statments;  
begin  
execution\_statments;  
end after each row;\]  
\[after statement is  
declaration\_statments;  
begin  
execution\_statments;  
end after statement;\]  
end;

6.3举例  
create table temp(n\_col number,v\_col varchar2(10));

create or replace trigger tri\_temp  
for insert or update or delete on temp  
compound trigger  
before statement is  
begin  
dbms\_output.put\_line('before statement');  
end before statement;  
before each row is  
v\_cnt number:=0;  
begin  
v\_cnt:=v\_cnt+1;  
dbms\_output.put\_line('before each row:'||v\_cnt);  
end before each row;  
after each row is  
v\_cnt number:=0;  
begin  
v\_cnt:=v\_cnt+1;  
dbms\_output.put\_line('after each row:'||v\_cnt);  
end after each row;  
after statement is  
begin  
dbms\_output.put\_line('after statement');  
end after statement;  
end;  
\--测试  
insert into temp values (1,'a');  
insert into temp values (2,'b');  
delete from temp;  
  
7 视图view：是一张虚拟表。  
7.1 语法：  
create or replace view view\_name as 查询语句;  
vw\_name  
7.2 创建视图：  
例1：  
create or replace view view\_emp as  
select empno,ename from emp;  
ORA-01031: 权限不足  
给用户授权：  
grant create any view,drop any view to scott;  
例2：  
create or replace view view\_emp\_dept as  
select empno,ename,dept.deptno,dname from emp,dept  
where emp.deptno=dept.deptno;  
例3：  
create table stu\_oracle(  
id number(16) primary key,  
sname varchar2(20),  
birth date  
);  
create table stu\_java(  
id number(16) primary key,  
sname varchar2(20),  
birth date  
);  
create or replace view view\_stu as  
select id,sname,birth,'oracle' major from stu\_oracle  
union all  
select id,sname,birth,'java' major from stu\_java;

insert into stu\_oracle (id,sname,birth) values (1,'zhangsan',sysdate-8000);  
insert into stu\_java (id,sname,birth) values (2,'lisi',sysdate-7800);

7.2 视图查询  
select \* from view\_emp;  
select \* from view\_emp\_dept where deptno=30;  
select \* from view\_stu

7.3 视图的数据字典：  
select \* from user\_views;  
7.4 编译视图：  
alter view view\_emp\_dept compile;  
7.5 重命名视图：  
rename view\_emp\_dept to view\_emp\_dept1;  
7.6 删除视图：  
drop view view\_emp\_dept1;  
7.7 视图的增删改  
\--简单视图支持增删改  
insert into view\_emp values (1112,'brad');  
update view\_emp set ename='brad1' where empno=1112;  
delete from view\_emp where empno=1112;  
\--简单视图支持增删改  
\--insert into view\_emp\_dept (empno,ename,deptno)values (1112,'brad',20);  
update view\_emp\_dept set ename='scott1' where empno=7788;  
\--update view\_emp\_dept set dname='scott1' where empno=7788;  
\--update view\_emp\_dept set dname='scott1' where deptno=20;  
delete from view\_emp\_dept where empno=1111;  
delete from view\_emp\_dept where deptno=20;  
\--insert into view\_stu (id,sname,birth) values (3,'wangwu',sysdate);  
\--delete from view\_stu where id=1;  
\--update view\_stu set SNAME='liwu' where id=2;  
总结：  
对于简单视图可直接执行INSERT,UPDATE,DELETE操作,  
但对于复杂视图,不允许直接执行DML操作。  
复杂视图包括：  
具有集合操作符:UNION,UNION ALL,INTERSECT,MINUS  
具有分组函数:MIN,MAX,SUM,AVG,COUNT等  
具有GROUP BY ,CONNECT BY ,START WITH等子句  
具有DISTINCT关键字  
具有连接查询。  
  
8.instead-of(视图)触发器  
为了在具有以上情况的复杂视图上执行DML操作，  
必须要基于视图建立INSTEAD-OF触发器；  
在建立了INSTEAD-OF触发器之后,  
就可以基于复杂视图执行INSERT,UPDATE,DELETE语句。

8.1 语法：  
create or replace trigger tri\_name  
instead of insert or update or delete on view\_name  
for each row  
\[declare\]  
declaration\_statments;  
begin  
execution\_statments;  
end;  
说明:  
INSTEAD OF选项只适用于视图  
当基于视图建立触发器时,不能指定BEFORE和AFTER选项  
当建立INSTEAD OF触发器时,必须指定FOR EACH ROW选项

8.2 给view\_emp\_dept视图建立触发器  
create or replace trigger tri\_vwempdept  
instead of insert or update or delete on vw\_emp\_dept  
for each row  
declare  
v\_cnt number;  
begin  
if inserting then  
\--新增部门  
select count(\*) into v\_cnt from dept where deptno=:new.deptno;  
if v\_cnt=0 then  
insert into dept (deptno,dname) values (:new.deptno,:new.dname);  
end if;  
\--新增职员  
select count(\*) into v\_cnt from emp where empno=:new.empno;  
if v\_cnt=0 then  
insert into emp (empno,ename,deptno) values (:new.empno,:new.ename,:new.deptno);  
end if;  
elsif updating then  
\--修改职员  
update emp set empno=:new.empno,ename=:new.ename where empno=:old.empno;  
\--修改部门  
if :new.deptno=:old.deptno then  
update dept set dname=:new.dname where deptno=:old.deptno;  
else  
\--给人员换部门  
select count(\*) into v\_cnt from dept where deptno=:new.deptno;  
if v\_cnt=1 then  
\--部门存在  
update emp set deptno=:new.deptno where empno=:old.empno;  
else  
\--修改部门编号  
select count(\*) into v\_cnt from emp where deptno=:old.deptno;  
if v\_cnt =1 then  
update emp set deptno=null where empno=:old.empno;  
update dept set deptno=:new.deptno where deptno=:old.deptno;  
update emp set deptno=:new.deptno where empno=:old.empno;  
else  
raise\_application\_error(-20001,'部门有多个人，不允许修改部门编号！');  
end if;  
end if;  
end if;  
elsif deleting then  
\--删除职员  
delete from emp where empno=:old.empno;  
\--删除部门  
select count(\*) into v\_cnt from emp where deptno=:old.deptno;  
if v\_cnt=0 then  
delete from dept where deptno=:old.deptno;  
end if;  
end if;  
end;  
/  
show err;

当建立INSTEAD-OF触发器之后,就可以在复杂视图view\_emp\_dept上执行INSERT操作了  
insert into view\_emp\_dept (empno,ename,deptno)values (1112,'brad',20);  
insert into view\_emp\_dept values(1223,'mary',50,'admin');  
insert into view\_emp\_dept values(1224,'bake',50,'admin');  
delete from view\_emp\_dept where empno=1223;  
delete from view\_emp\_dept where empno=1224;  
update view\_emp\_dept set ename='mary-gai' where empno=1223;  
update view\_emp\_dept set ename='bake1',empno=1225 where empno=1224;  
update view\_emp\_dept set deptno=10 where empno=1223;

8.3 课堂练习  
给view\_stu视图触发器，实现view\_stu视图的增、删、改功能

9.数据定义语言(DDL)触发器  
记载系统所发生的DDL事件(CREATE,ALTER,DROP等)

9.1语法  
create or replace trigger trigger\_name  
\{before | after \} ddl\_event on \{database | schema\}  
\[declare\]  
declaration\_statments;  
begin  
execution\_statments;  
end;  
9.2 DDL触发器举例  
conn sys/123 as sysdba;  
create table event\_ddl(  
event varchar2(100),  
username varchar2(40),  
owner varchar2(10),  
objname varchar2(200),  
objtype varchar2(100),  
time date，  
IP varchar2(20)  
);

create or replace trigger tri\_ddl  
after ddl on scott.schema  
begin  
insert into event\_ddl values(ora\_sysevent,ora\_login\_user,  
ora\_dict\_obj\_owner,ora\_dict\_obj\_name,ora\_dict\_obj\_type,sysdate,ora\_client\_ip\_address);  
end;

conn scott/123;  
create table temp(n\_col number,v\_col varchar2(10));  
drop table temp;

conn sys/123 as sysdba;  
select \* from event\_ddl;

9.3常用事件属性函数  
ora\_client\_ip\_address:返回客户端IP地址  
ora\_database\_name:返回当前数据库名  
ora\_des\_encrypted\_password:用于返回DES加密后的用户口令  
ora\_dict\_obj\_name:用于返回DDL操作所对应的数据库对象名  
ora\_dict\_obj\_list(name\_list out ora\_name\_list\_t):返回在事件中被修改的对象名列表  
ora\_dict\_obj\_owner:返回DDL操作所对应的对象的所有者名  
ora\_dict\_obj\_owner\_list(owner\_list out ora\_name\_list\_t):返回在事件中被修改对象的所有者列表  
ora\_dict\_obj\_type:返回DDL操作所对应的数据库对象的类型  
ora\_grantee(user\_list out ora\_name\_list\_t):返回授权事件的授权者  
ora\_instance\_num:返回例程号  
ora\_is\_alter\_column(column\_name in varchar2):用于检测特定列是否被修改  
ora\_is\_creating\_nested\_table:用于检测是否正在建立嵌套表  
ora\_is\_drop\_column(column\_name in varchar2):用于检测特定列是否被删除  
ora\_is\_servererror(error\_number):用于检测是否返回特定oracle错误  
ora\_login\_user:返回登录用户名  
ora\_sysevent:用于返回触发触发器的系统事件名  
  
10.系统或数据库事件触发器  
基于ORACLE系统事件(logon,logoff,startup,shutdown)所建立的触发器,  
用来跟踪用户的登录、退出，数据库的启动、关闭。  
10.1 语法：  
create or replace trigger trigger\_name  
\{before | after \} database\_event on database  
\[declare\]  
declaration\_statments;  
begin  
execution\_statments;  
end;

10.2建立登录和退出触发器  
记载用户登陆和退出事件.  
conn sys/123 as sysdba;  
drop table log\_table;  
create table log\_table(  
username varchar2(20),  
logon\_time date,  
logoff\_time date,  
address varchar2(30));  
  
create or replace trigger tri\_logon  
after logon on database  
begin  
insert into log\_table (username,logon\_time,address)  
values(ora\_login\_user,sysdate,ora\_client\_ip\_address);  
end;  
  
create or replace trigger tri\_logoff  
before logoff on database  
begin  
insert into log\_table (username,logoff\_time,address)  
values(ora\_login\_user,sysdate,ora\_client\_ip\_address);  
end;  
  
10.3建立例程启动和关闭触发器(特权用户)  
记载例程启动和关闭的事件和时间  
conn sys/oracle as sysdba  
create table event\_table(event varchar2(30),time date);  
  
create or replace trigger tr\_startup  
after startup on database  
begin  
insert into event\_table values(ora\_sysevent,sysdate);  
end;

create or replace trigger tr\_shutdown  
before shutdown on database  
begin  
insert into event\_table values(ora\_sysevent,sysdate);  
end;

shutdown  
startup

11.作业  
电商业务练习：  
0.建银行账户表(账户序号、账户名、金额)，并插入买家和卖家的银行账户数据  
1.建商家注册(商家序号、商家名称、商家银行账户)  
2.建用户注册表(买家序号、用户名、用户银行账户)  
3.建商品表(商品序号、商品名称、单价、商家序号)，并给商品表新增几条商品数据  
4.建销售表(销售序号、商品序号、数量、总价、买家序号、时间、活动说明包括出售和退货)  
5.建商品销售视图(销售序号、商品名称、数量、买家序号、出售或退货)  
6.建销售日志表(日志序号、销售序号、商品序号、单价、数量、总价)  
7.建序列（可以是多个序列，每个表一个；也可以是一个序列，所有表公用一个）  
8.建视图触发器实现网上销售的业务逻辑  
9.建银行账户日志表(日志序号、账户序号、转入转出操作、转账金额、时间)，  
10.改写或新建触发器实现网上销售商品的同时进行转账业务处理及记录转账记录。

函数巩固作业：  
1.话说某天一艘海盗船被天上砸下来的一头牛给击中了,  
5个倒霉的家伙只好逃难到一个孤岛,发现岛上孤零零的,  
幸好有有棵椰子树,还有一只猴子!  
大家把椰子全部采摘下来放在一起,但是天已经很晚了,所以就睡觉先.  
晚上某个家伙悄悄的起床,悄悄的将椰子分成5份,结果发现多一个椰子,顺手就给了幸运的猴子,  
然后又悄悄的藏了一份,然后把剩下的椰子混在一起放回原处,最后还是悄悄滴回去睡觉了.  
过了会儿,另一个家伙也悄悄的起床,悄悄的将剩下的椰子分成5份,结果发现多一个椰子,  
顺手就又给了幸运的猴子,然后又悄悄滴藏了一份,把剩下的椰子混在一起放回原处,  
最后还是悄悄滴回去睡觉了.  
又过了一会 ......  
又过了一会 ...  
总之5个家伙都起床过,都做了一样的事情。早上大家都起床,各自心怀鬼胎的分椰子了,  
这个猴子还真不是一般的幸运,  
因为这次把椰子分成5分后居然还是多一个椰子,只好又给它了.问题来了,这堆椰子最少有多少个?

2.说一个屋里有多个桌子，有多个人。  
如果3个人一桌，多2个人。  
如果5个人一桌，多4个人。  
如果7个人一桌，多6个人。  
如果9个人一桌，多8个人。  
如果11个人一桌，正好。  
请问这屋里多少人

转载于:https://www.cnblogs.com/BradMiller/p/9279592.html