PL/SQL包&触发器

包：存放存储过程和函数的容器

组成结构：包头 、包体

包头建立如下

CREATE OR REPLACE PACKAGE emp_pack 
IS
  PROCEDURE new_emp
    (v_ename   emp.ename%TYPE,
     v_job     emp.job%TYPE     DEFAULT 'SALESMAN',
     v_mgr     emp.mgr%TYPE     DEFAULT 7839, 
     v_sal     emp.sal%TYPE     DEFAULT 1000,
     v_comm    emp.comm%TYPE    DEFAULT 0,
     v_deptno  emp.deptno%TYPE  DEFAULT 30);
END emp_pack;
/

包体建立如下

CREATE OR REPLACE PACKAGE BODY emp_pack 
IS
  FUNCTION valid_deptno --私有函数，只能在这个包中被调用，而不能被其他程序调用
    (v_deptno IN dept.deptno%TYPE)
    RETURN BOOLEAN
  IS	
    v_dummy  VARCHAR2(1);
  BEGIN
    SELECT 'x'
    INTO   v_dummy
    FROM   dept
    WHERE  deptno = v_deptno;
    RETURN (TRUE);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      RETURN(FALSE);
  END valid_deptno;

  PROCEDURE new_emp
    (v_ename   emp.ename%TYPE,
     v_job     emp.job%TYPE     DEFAULT 'SALESMAN',
     v_mgr     emp.mgr%TYPE     DEFAULT 7839,
     v_sal     emp.sal%TYPE     DEFAULT 1000,
     v_comm    emp.comm%TYPE    DEFAULT 0,
     v_deptno  emp.deptno%TYPE  DEFAULT 30)
  IS
  BEGIN
    IF valid_deptno(v_deptno) THEN
      INSERT INTO emp
      VALUES (seq_empno.NEXTVAL, v_ename, v_job, v_mgr, 
              TRUNC (SYSDATE, 'DD'), v_sal, v_comm, v_deptno);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Invalid department number. Try again.');
    END IF;
  END new_emp;
END emp_pack;
/

包的重载：多个具有相同名称的子程序。

用户在调用同名组件时使用不同参 数传递数据。

例如，当取得雇员工资或解雇雇员时，可能希望既可以输入雇员号，也可以输 入雇员名

1、建立包规范 使用重载特性时，同名的过程和函数必须具有不同类型的输入参数。但需要注意，同名函数返回值的数据类型必 须完全相同。 

2、建立包体 当建立包体时，必须要给不同的重载过程和重载函数提供不同的实现代码。 

3、调用重载过程和重载函数 建立了包规范和包体之后，就可以调用包的公用组件了。在调用重载过称和重载函数时，pl/sql执行器会自动根据输入参数值的数据类型确定需要调用的过程和函数。

创建包头
CREATE OR REPLACE PACKAGE over_load is
  FUNCTION print_it(v_arg date)
    RETURN VARCHAR2;
  FUNCTION print_it(v_arg VARCHAR2)
    RETURN VARCHAR2;
END over_load;

创建包体
CREATE OR REPLACE PACKAGE BODY over_load is
  FUNCTION print_it(v_arg date)
    RETURN VARCHAR2
  IS
  BEGIN
    RETURN to_char(v_arg,'yyyy-mm-dd');
  END print_it;
         
  FUNCTION print_it(v_arg VARCHAR2)
    RETURN VARCHAR2
  IS
  BEGIN
    RETURN to_char(v_arg,'L99,999.00');
  END print_it;
END over_load;
/

调用包
VARIABLE g_datevalue varchar2(40);
execute :g_datevalue := over_load.PRINT_IT(sysdate); 
print g_datevalue

VARIABLE g_datevalue varchar2(40);
execute :g_datevalue := over_load.PRINT_IT('123'); 
print g_datevalue

案例一

create or replace package query_sal is
 function q_sal (v_empno emp.empno%type) return number;
 function q_sal (v_ename emp.ename%type) return number;
 end;
 /

reate or replace package body query_sal is
   function q_sal (v_empno emp.empno%type) return number
   is
   v_sal number;
   begin
   select sal into v_sal from emp where empno=v_empno;
   return v_sal;
   end;
   function q_sal (v_ename emp.ename%type) return number
   is
   v_sal number;
   begin
   select sal into v_sal from emp where ename=v_ename;
   return v_sal;
   end;
   end;
   /

依赖关系查看

select name,type,referenced_name,referenced_type
    from (SELECT *FROM user_dependencies
    WHERE NAME<>REFERENCED_NAME AND REFERENCED_OWNER='SCOTT')
   start with name='PK2' connect by prior referenced_name=name

触发器：DML触发器 & 系统触发器

语法结构

CREATE OR REPLACE TRIGGER trigger_name
	{BEFORE|AFTER|INSTEAD OF} EVENTs
	ON TABLE_NAME
	[for each row] 代表是行级触发器 没有就是表级触发器
	declare
		...
	begin
		...
	end;

检测的谓词 可以直接用于 if 条件
updating 如果是update操作 此值为true 否则false 
inserting 如果是insert操作 此值为true 否则false 
deleting 如果是delete操作 此值为true 否则false

查看触发器的数据字典 

user_source 

user_triggers 

user_trigger_cols

create or replace trigger tr_no_dml before update or delete or insert on t1
begin
        if updating
        then
                raise_application_error(-20001,'不允许做UPDATE操作!');
        elsif inserting
        then
                raise_application_error(-20002,'不允许做insert操作!');
        else
                raise_application_error(-20003,'不允许做delete操作!');
        end if;
end;

create or replace trigger tri_t2_after_row
after update or insert or delete
on t2 for each row
begin
      if updating then
      	insert into aud_tmp values(sysdate,'update',user);
      elsif inserting then
	insert into aud_tmp values(sysdate,'insert',user);
      else
	insert into aud_tmp values(sysdate,'delete',user);
      end if;
end;
/

表级触发器和行级触发器的区别 
 行级建立的时带有 for each row 关键字 
 表级只触发一次 行级每一行都会触发一次

 create or replace trigger tri_t3_col before update on t3
    for each row
    begin
    dbms_output.put_line('you are updating this row of table t3!!!');
   end;

 create or replace trigger tri_t3_tab before update on t3
    begin
    dbms_output.put_line('you are updating table t3!!!');
   end;
   /

update t3 set sal=sal+1;
you are updating table t3!!!         /*表级触发器只触发一次*/
you are updating this row of table t3!!!   /*行级触发器每一行触发一次，执行多少次由行数决定*/
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!
you are updating this row of table t3!!!

已更新12行。

触发器触发顺序:

before statement trigger(on table)
	->before row trigger (on table for each row)
		->after row trigger (on table for each row)
			->after statement trigger (on table)

create or replace trigger tri_t3_col_after after update on t3
    for each row
    begin
    dbms_output.put_line('you have updated this row of table t3!!!');
  end;
 /

create or replace trigger tri_t3_tab_after after update on t3
   begin
    dbms_output.put_line('you have updated table t3!!!');
   end;
 /

update t3 set sal=sal+1 where empno=7369;
you are updating table t3!!!
you are updating this row of table t3!!!
you have updated this row of table t3!!!
you have updated table t3!!!
已更新 1 行。

验证触发器的状态
select trigger_name,status from user_triggers;
select trigger_name,trigger_type,triggering_event,table_name,status from user_triggers;

禁用某个触发器
ALTER TRIGGER e_update3 disable;
禁用某个表上的所有触发器
alter table e disable all triggers;
删除触发器
DROP TRIGGER e_update3;

行级触发器的协同更新
行级触发器增加了更新动作的旧值和新值的存储 表级不可以,/*行级触发器 才有 :old.COL 和 :new.COL 的定义*/
当对触发器所依附的表进行dml操作时，触发行级别触发器，随之行级别触发器会自动将操作前后的值存入
record类型的变量当中去，这些变量随触发器作用而产生，随触发器执行完毕而消失，
操作前值存入‘：old’中，操作后的值存入‘：new’中，触发器执行完后，
变量也就消失了，我们可以在触发器执行过程中对这些变量进行操作。每触发一次，就存储一次变量值。
行级别触发器的这种特性可以存在于after类型的触发器中，也可以存在于before类型的触发器中。

create or replace trigger tri_t4
    after update or delete or insert
    on t4 for each row
    begin
  	   if updating then
  	       dbms_output.put_line('old_value: '||:old.sal||' '||'new_value: '||:new.sal);
  	   elsif inserting then
  	       dbms_output.put_line('old_value: '||:old.sal||' '||'new_value: '||:new.sal);
  	   else
 	       dbms_output.put_line('old_value: '||:old.sal||' '||'new_value: '||:new.sal);
 	   end if;
  end;

自治事务 DML（delete/update/insert）触发器中不能使用DDL（CREATE，DROP，ALTER）语句， 也不能使用事务控制语句（ROLLBACK, COMMIT,SAVEPOINT）。 特别注意的是，在触发器的主体中引用的函数（function）/过程（procedure）中也不能有 事务控制语句 会报错误:ORA-04092: cannot COMMIT in a trigger trigger不能含有事务处理的语句!需要使用自制事务！ 自治事务可以将触发器内的DML独立成事务 自治事务在declare中声明 PRAGMA autonomous_transaction; 切记:如果触发器中引用的过程也带有DDL DCL也要定义成自制事务 还有，如果定义了自治事务，那么触发器中必须有事物操作的动作比如commit，否则同样会报错。

create or replace trigger tri_t2 after update or delete or insert
on t2 
for each row
declare
pragma autonomous_transaction;
begin
if updating then
insert into aud_tmp values(sysdate,'update',user);
elsif deleting then
insert into aud_tmp values(sysdate,'delete',user);
else
insert into aud_tmp values(sysdate,'insert',user);
end if;
commit;
end;

替代触发器 instead of  
做触发器在视图上 把单条的DML分解成多条DML到多个表 就可以插入了 但带有聚集函数的还是不能操作

create or replace trigger tr_DML_v1 instead of insert on v1
begin
        insert into d(DEPTNO,DNAME,LOC) values(:new.DEPTNO,:new.DNAME,:new.LOC);
        insert into e(EMPNO,ENAME) values(:new.EMPNO,:new.ENAME);
end;

系统触发器

使用这些触发器，可以对数据库发生的一些重要事件进行审计 可触发的事件包括: 数据库启动/关闭触发器 DDL触发器 schema 最终用户登陆/注销触发器 系统错误触发器

触发器事件 触发时间 触发条件 Logon After 用户登录成功后 Logoff Before 用户退出登录前 Startup After 数据库启动后 Shutdown Before 数据库关闭前 Servererror After 系统发生故障后

create or replace TRIGGER logon_audit AFTER LOGON ON DATABASE
BEGIN
	if user not in ('SYS','SYSMAN')
	then
 	  insert into log_audit(login_date,username,user_ip) values(sysdate,user,ora_client_ip_address);
	end if;
END;
/
create or replace TRIGGER logoff_audit BEFORE LOGOFF ON DATABASE
BEGIN
	if user not in ('SYS','SYSMAN')
	THEN
 	  insert into log_audit (logoff_date,username,user_ip) values(sysdate,user,ora_client_ip_address);
	end if;
END;
/

错误跟踪的触发器

CREATE OR REPLACE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
BEGIN
 IF (IS_SERVERERROR (1017)) THEN
  insert into log_audit (login_date,USERNAME,USER_IP,error_code) values(sysdate,ora_login_user,ora_client_ip_address,'ORA-1017');
 ELSIF (IS_SERVERERROR (2449)) THEN
  insert into log_audit (login_date,USERNAME,USER_IP,error_code) values(sysdate,ora_login_user,ora_client_ip_address,'ORA-2449');
 END IF;
END;

DDL触发器

CREATE OR REPLACE TRIGGER DDL_FB before  ALTER OR DROP  OR RENAME 
on schema  
BEGIN
  Raise_application_error(-20030,'此系统已经运行，不允许对表进行DDL维护');
end;