Oracle学习笔记--day06

￡神魔★判官ぃ 2022-06-17 14:14 276阅读 0赞

触发器  
trigger  
数据库对象  
命名块，有名字，会被编译并存储在数据库中  
当在表、视图、数据库中发生某些事件时，Oracle会自动执行的一段程序  
只有在触发器对应的事件发生时，触发器才会执行，触发器是自动执行的一段代码，用户不能调用  
  
触发器的4要素:  
      1、触发源  
用于指定在操作什么时会引发触发器执行  
表、视图、数据库  
      2、触发的事件  
用于指定在触发源上执行哪些操作会引发触发器执行  
insert 、update 、delete、logon、logonoff  
      3、触发的时间  
用于指定是在操作执行之前、之后，执行触发器  
before  after  
      4、触发的频率  
用于指定在事件发生时，触发器执行几次  
语句级 一条SQL语句，无论操作了几行，触发器只执行一次  
行级 一条SQL语句，每操作1行，触发器就执行1次  
  
  
1、DML触发器  
  
  
创建语法:  
create trigger 触发器名字  
\{before|after\}  
\{insert|update|delete\} on 表名  
\[for each row\]  
\[when (条件)\]  
\[declare  
/\*  
\*定义部分  
\*/  
\]  
begin  
/\*  
\*触发体  
\*/  
\[exception     
  
\]  
end;  
  
for each row  
用于指定是行级触发器  
  
when (条件)  
触发限制  
只有when中指定的条件为true时，才会执行触发器  
访问旧行、新行 new old 不需要加冒号  
  
可以为一个触发器指定多个触发事件，多个事件之间通过or分隔  
  
  
在触发器的底层管理了事务，内部事务？还是不太准确  
所以自己写的触发器代码中，不允许写commit、rollback语句  
对于DML、instead of触发器  
与引发该触发器的DML操作，用的是同一个事务 执行DML操作时，打开的连接关联的事务  
在DML触发器的执行过程中  
如果发生了错误，会rollback，并会阻止后续操作的执行(before触发器，后面还要执行DML操作)  
如果执行成功，不会commit  
对于系统触发器  
执行时，可能会执行DML操作  
发生异常，回滚  
成功，提交  
  
例：  
单表操作的before、after表级触发器，使用游标取表数据，验证在DML操作执行前后执行触发器  
行级触发器的话，表 HWZ.T\_EMP 发生了变化, 触发器/函数不能读它  
下面例子：验证使用的是同一个事务，出错之后会回滚   
create table t\_emp\_copy as select \* from t\_emp where 1 = 2;  
  
\--将数据插入t\_emp表中之前，先将数据保存到t\_emp\_copy表中的触发器  
create or replace trigger tr1  
before   
insert on t\_emp  
for each row  
begin  
 dbms\_output.put\_line('将要向t\_emp表中插入数据...');  
 insert into t\_emp\_copy values(:new.id,:new.name,:new.age);  
 --raise zero\_divide;  
end;  
/  
\--将数据插入到t\_emp\_copy表之后，执行的触发器  
create or replace trigger tr2  
before   
insert on t\_emp  
for each row  
begin  
dbms\_output.put\_line('往t\_emp\_copy表中插入了数据...');  
for emp\_copy\_record in emp\_copy\_cursor  
loop  
dbms\_output.put\_line(emp\_copy\_record.name);  
end loop;  
end;  
/  
  
例：  
create trigger tr1   
after   
insert or update on t\_emp  
for each row  
declare  
 v\_test varchar2(60) := 'zhangsan';  
begin  
 dbms\_output.put\_line(v\_test);  
 dbms\_output.put\_line('在t\_emp表中插入了数据');  
end;  
  
判断执行的SQL语句的类型  
inserting  
deleting  
updating  
  
行级触发器中访问旧行与新行的数据  
:old 旧行  
:new 新行  
旧行与新行可能完全一样  
记录类型  
访问旧行、新行中列的值：  
记录类型的变量.成员名  此处记录类型的成员名与表的列名同名  
:new.列名  
:old.列名  
  
insert没有旧值  
delete没有新值  
只能用于行级触发器  
（ORA-04082: NEW 或 OLD 引用不允许在表级触发器中）  
对于语句级触发器，在执行一条sql语句时，只执行一次  
这条sql语句可能会操作多行，而old、new是一行的数据   
在操作多行时，用new、old无法存放  
  
after触发器中不允许修改:new新行的值 因为新行的值已经插入、修改过了，修改了也没意义  
  
例：  
1：  
create trigger tr1   
after   
insert or update or delete on t\_emp  
for each row  
declare  
 v\_test varchar2(60) := 'zhangsan';  
begin  
 if inserting then  
dbms\_output.put\_line('插入了数据...');  
dbms\_output.put\_line(:new.name);  
dbms\_output.put\_line(:new.age);  
 end if;  
 if updating then  
dbms\_output.put\_line('修改了数据...');  
dbms\_output.put\_line(:old.name);  
dbms\_output.put\_line(:old.age);  
dbms\_output.put\_line('----------->');  
dbms\_output.put\_line(:new.name);  
dbms\_output.put\_line(:new.age);  
 end if;  
 if deleting then  
insert into t\_emp\_bak values (:old.id, :old.name, :old.age, sysdate);  
 end if;  
end;  
2：  
create trigger tr2   
before  
insert on t\_emp  
for each row  
when (new.age < 18)  
declare  
 age\_to\_little\_exception exception;  
 pragma exception\_init(age\_to\_little\_exception,-20001);  
begin  
 raise age\_to\_little\_exception;  
exception  
 when age\_to\_little\_exception then  
dbms\_output.put\_line('不能小于18岁');  
end;  
3：  
create trigger tr3  
before  
insert on t\_emp  
for each row  
when (new.age < 18)  
declare  
begin  
 raise\_application\_error(-20003,'不能小于18岁');  
end;  
4：主键自动生成  
insert into t\_test values(seq.nextval, '',....);  
insert into test (name) values('abc');-->  
  
create sequence test\_seq;  
  
begin  
:old.id  
:new.id := 1;  
:new.id := test\_seq.nextval;  
dbms\_output.put\_line(test\_seq.nextval); \--此处不允许表, 视图或序列引用，只能用在sql语句中  
end;  
  
create or replace trigger pk\_auto\_increment  
before insert on test  
for each row  
begin  
dbms\_output.put\_line('赋值前id:' ||:new.id);  
select test\_seq.nextval into :new.id from dual;  
dbms\_output.put\_line('赋值后id:' ||:new.id);  
end;  
  
  
2、instead of触发器  
替代触发器   
用触发器的执行替代对视图要执行的操作  
  
有的视图，在创建时，指定的是with check option，即允许通过视图对对应的表执行DML操作  
但视图中的列可能是一个或多个表的列经过了一系列的处理后，才得到的列，就会造成视图与表中的列对应不上(虚拟列)  
也就无法通过视图去操作对应的表，从而产生错误  
  
create trigger 触发器名   
instead of   
insert|update|delete on 视图名  
\[for each row\]  
\[declare  
/\*定义部分\*/  
\]  
begin  
触发体;  
end;  
  
注意：  
只能在视图上创建替代触发器  
视图必须是with check option  
不能指定before或after  
只能是行级触发器，即使不指定for each row，也是行级触发器，可以使用:new :old  
不允许使用when子句  
  
视图或表中的列是不允许重名的  
  
  
3、系统触发器  
create trigger 触发器名 before|after   
系统事件 on database  
declare  
begin  
exception  
end;  
  
  
logon:登录  
只能是after触发器  
如果触发器执行过程中，抛出了异常，则会阻止用户的登录  
如果用户拥有administer database trigger 管理数据库触发器的系统权限，登录时也会触发登录触发器的执行，  
但如果触发器执行过程中抛出了异常，也不会阻止该用户的登录  
dba角色、alll privileges系统权限中 包含了administer database trigger  
例：  
v\_result := 1 / 0;  
raise zero\_divide;  
raise\_application\_error(-20000,'msg');  
  
logoff：退出  
只能是before触发器  
如果触发器执行过程中，抛出了异常，不会阻止用户的退出登录  
在系统触发器中可以使用的函数：  
ora\_login\_user() 返回当前正在登录的用户名  
ora\_client\_ip\_address() 返回当前客户端的ip地址  
本机登录时，返回的ip地址可能为空  
  
例：  
将用户的登录、退出登录信息保存进用户日志表中  
\--记录用户登录信息的表  
create table t\_user\_log(  
 id number primary key,  
 username varchar2(60),  
 ip varchar2(60),  
 logon\_time date,  
 logoff\_time date  
);  
\--负责为t\_user\_log表生成主键的序列  
create sequence s\_user\_log;  
\--往t\_user\_log表中插入数据之前，修改新行主键值的触发器  
create or replace trigger tr2  
before  
insert  
on t\_user\_log  
for each row  
begin  
 select s\_user\_log.nextval into :new.id from dual;  
end;  
/  
\--用户登录之后，将用户信息保存到t\_user\_log表中的触发器  
create or replace trigger tr3  
after logon  
on database  
begin  
 insert into t\_user\_log(username,ip,logon\_time) values(ora\_login\_user(),ora\_client\_ip\_address(),sysdate);  
 raise zero\_divide;  
end;  
/  
select \* from t\_user\_log;  
禁用启用触发器  
禁用：alter trigger 触发器名 disable;  
启用: alter trigger 触发器名 enable;  
删除触发器：drop trigger 触发器名;  
  
子程序  
指的是存储过程和函数  
数据库对象  
有名字，程序员可以重复调用，且会被编译并存储在数据库中的一段程序  
\*命名块，有名字，会被编译并存储在数据库中的一段程序  
命名块  
程序员可以调用  
可以接收参数  
  
子程序的参数的三种模式：  
in   传入参数，只能用于接收值，不能修改，该模式是默认模式  
如果进行了赋值，编译时报错：不能用作赋值目标  
out 传出参数，只能用于把值传出去，不能用于接收值(接收不到)  
调用时参数个数或类型错误  
必须传入变量，否则：表达式不能用作赋值目标  
in out, 上面两种的结合  
必须传入变量，否则：表达式不能用作赋值目标  
存储过程  
按照一定的语法，编写的一段程序  
不能有返回值  
  
创建：  
create \[or replace\] procedure procedure\_name \[(param1 \[in|out|in out\] oraType, ...)\]   
is|as  
定义部分  
begin  
执行部分  
exception  
异常处理部分  
end;  
  
创建时不执行，调用时才执行  
如果不需要参数，定义时用于指定参数列表的圆括号可以省略  
定义参数的类型时，必须不能指定长度，实际长度由实际传入的值或变量决定  
in|out|in out  
参数的模式  
\*可以使用%type、%rowtype指定参数的类型  
在过程中，使用的就是参数类型xxx\_record\_type，然后按顺序传给外部记录的成员  
  
例：  
create or replace procedure delete\_emp\_by\_id (v\_id number) is  
 emp\_record t\_emp%rowtype;  
begin  
 delete from t\_emp where id = v\_id returning id,name,age into emp\_record;  
 dbms\_output.put\_line('删除的数据为:' || emp\_record.name);  
end;  
  
create or replace procedure delete\_emp\_by\_id(v\_id out number,v\_name out varchar2,v\_age out number)  is  
 emp\_record t\_emp%rowtype;  
begin  
 delete from t\_emp where id = 4 returning id,name,age into emp\_record;  
 v\_id := emp\_record.id;  
 v\_name := emp\_record.name;  
 v\_age := emp\_record.age;  
end;  
  
create or replace procedure delete\_emp\_by\_id(v\_id in out number,v\_name in out varchar2,v\_age in out number)  is  
 emp\_record t\_emp%rowtype;  
begin  
 delete from t\_emp where id = v\_id returning id,name,age into emp\_record;  
 v\_id := emp\_record.id;  
 v\_name := emp\_record.name;  
 v\_age := emp\_record.age;  
end;  
  
调用：  
  
SQL\*Plus  
execute 过程名(参数值列表)  
sqldeveloper中也可以用  
  
还可以在PL/SQL程序中(匿名块、触发器、存储过程、函数中)调用  
begin  
存储过程名(参数值列表);  
end;  
存储过程中执行DML、DQL语句直接执行  
存储过程中执行DDL语句  
PL/SQL块中不能直接执行DDL语句  
先把DDL语句存放在一个字符串变量中，然后通过execute immediate语句执行字符串内容，字符串内容必须符合SQL语法  
用户从角色中获取的权限，在PL/SQL块、函数、存储过程中都将丢失  
练习：  
5、编写存储过程实现两数相除，返回结果，如果除数为0，抛出自定义异常-20003,  
‘除数不能为0’  
函数  
必须要有返回值  
  
创建：  
create \[or replace\] function function\_name\[(参数列表)\]   
return oraType is|as  
定义部分  
begin  
执行部分,一定有某个地方用了return，用于返回值  
exception  
异常处理部分  
此处也可以返回值  
end;  
  
可以使用%type、%rowtype指定参数的类型  
在过程中，使用的就是参数类型xxx\_record\_type，然后按顺序传给外部记录的成员  
create or replace function f\_divide(a number, b number)  
return number is  
v\_divider number;  
my\_zero\_excp exception;  
pragma exception\_init(my\_zero\_excp, -20000);  
begin  
if b = 0 then  
raise my\_zero\_excp;  
end if;  
v\_divider := a / b;  
return v\_divider;  
exception  
when my\_zero\_excp then   
v\_divider := a / 1;  
return v\_divider;  
end;  
SQL\*Plus  
SQL\*Plus变量:var varName oraType;  
call 函数名 into :varName;  
print varName;  
SQL语句、所有的PL/SQL程序块中,注意：不能作为独立的PL/SQL语句，参照表达式用法( 表达式不是语句，可以其他作为语句一部分)  
begin  
f\_divide(1,100); -- error  
end;  
  
  
包  
组织管理子程序  
避免子程序重名  
加强模块化  
包   
包中定义的是规范，只包含了函数或存储过程的说明部分  
地位同接口  
创建包：  
create \[or replace\] package package\_name is|as  
\--函数定义  
function 函数名\[(参数列表)\] return oraType;  
\--存储过程定义  
procedure 存储过程名\[(参数列表)\];  
end;  
包体  
包体中是函数或存储过程的实现  
地位同实现类  
创建包体：  
create \[or replace\] package body package\_name is|as  
\--函数实现  
function 函数名\[(参数列表)\] return oraType is   
函数实现内容  
\--存储过程实现  
procedure 存储过程名\[(参数列表)\] is|as  
存储过程的内容  
end \[package\_name\];  
  
create or replace package mypackage is  
function f1 return varchar2;  
procedure p1;  
end;  
ORA-06508: PL/SQL: 无法找到正在调用 : "HWZ.PKG" 的程序单元  
PLS-00221: 'FUN' 不是过程或尚未定义，直接调函数，不作为语句  
create or replace package body mypackage is  
错误(1,14): PLS-00201: 必须声明标识符 'PKG3'  
错误(1,14): PLS-00304: 如果没有说明, 则无法编译 'PKG3' 主体  
function f1 return varchar2 is  
类型声明也必须一致  
错误(2,12): PLS-00323: 子程序或游标 'FUN' 已在程序包说明中声明, 必须在程序包体中对其进行定义。  
begin  
return 'aaaa';  
end f1;  
错误(2,12): PLS-00323: 子程序或游标 'FUN' 已在程序包说明中声明, 必须在程序包体中对其进行定义。  
procedure p1 is  
begin  
dbms\_output.put\_line('我是包过程！！！');  
end p1;  
end mypackage;  
  
使用包中的存储过程或函数：包名.子程序名  
调用存储过程时，如果不需要传参，调用该过程的时候，可以加()，也可以不加  
调用函数时，如果不需要传参，在SQL\*Plus中运行，必须加(),其他地方不需要