Oracle学习笔记--day06

触发器
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中运行，必须加(),其他地方不需要