PLSQL 触发器，包，定时任务

--触发器    trigger
DML触发器
语法:
create or replace trigger 触发器名 
before|after       --before指事前触发器,after指事后触发器
dml操作 on 表
[for each row]     --默认是语句级,写了是行级
[declare 声明]
begin
end;

语句级触发:当某事件发生时,该触发器执行一次
行级触发:  当某事件发生时,影响一行触发一次

--emp1有emp,20部门的数据,emp2是空表,表结构同emp
--对emp1表创建触发器,当被插入数据时,向emp2的empno列插入一个1
create or replace trigger t_1
before
insert on emp1
for each row
begin
  insert into emp2(empno) values(1);
end;

insert into emp1 select * from emp

--对emp1创建触发器,当他被删除数据时
--向emp2的ename列插入delete,hiredate列插入删除时间
create or replace trigger t_2
before
delete on emp1
for each row
begin
  insert into emp2(ename,hiredate) values ('delete',sysdate);
end;

delete from emp1 

select *
from emp2

--有他们就只能用行级触发器
           insert      delete        update
:old.列    null        有            有
:new.列    有          null          有

--把emp2的hiredate列的类型改为timestamp
alter table emp2 modify hiredate timestamp;

--给emp1创建触发器,当emp1的ename被更新时
--向emp2的ename列插入更新前和更新后的名字
--job列插入更新前和更新后
--hiredate插入更新时的系统时间戳

create or replace trigger t_3 
before
update of ename on emp1       --给列加触发器的语句:dml语句 of 列名 on 表名
for each row
begin
  insert into emp2(ename,job,hiredate) values (:old.ename,'更新前',systimestamp);
  insert into emp2(ename,job,hiredate) values (:new.ename,'更新后',systimestamp);
end;    

update emp1 set ename=lower(ename);

--触发器和异常的联动

--创建触发器,12点到21点之间不能向emp1插入数据
create or replace trigger t_1
before
insert on emp1
begin
  if to_char(sysdate,'hh24') between 12 and 21
    then raise_application_error(-20001,'不能在12点到21点之间插入数据');
  end if;
end;

影响DML速度的因素
约束,触发器,索引

四种触发器分别有不同的作用
一、dml触发器：当发出update、insert、delete命令就可以触发已定义好的dml触发器，是最简单和常用的一种触发器
语法：
create or replace trigger trigger_name
after|before insert|update|delete
on table_name
for each row
  
二、instead-of触发器：当向一个由多个表联接成的视图作dml操作时，一般情况下是不允许的，这时候就可以用instead-of触发器来解决这种问题（在触发器写代码分别对各表作相应dml操作），语法是这样的：
create or replace trigger trigger_name
instead of insert|update|delete
on view_name
for each row
  
三、ddl触发器：当发出create、alter、drop、truncate命令时会触发已定义好的ddl触发器，这种触发器可以用来监控某个用户或整个数据库的所有对象的结构变化
语法：
create or replace trigger trigger_name
before|after create|alter|drop|truncate
on schema|database
例：
--禁止用create、alter、drop、truncate命令操作apps用户的对象
create or replace trigger apps_no_ddl
before create or alter or drop or truncate
on apps
begin
raise_application_error(-20001,'不允许用DDL操作APPS用户的对象');
end;

四、db事件触发器：当startup、shutdown、logon、logoff数据库时就会触发db事件触发器，这种触发器可以用来监控数据库什么时候关闭/打，或者用户的logon/logoff数据库情况
语法：
create or replace trigger trigger_name
before|after startup|shutdown|logon|logoff
on database
例：
--记录数据库关闭的时间（shutdown类型要用关键字before，startup用after）
create or replace trigger db_shutdown
before shutdown
on database
begin
insert into test_tbl(log_event) values('db shutdown at '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
commit;
end;
--记录用户logon时间（logoff类型要用关键字before，logon用after）
create or replace trigger user_logon_db
after logon
on database
begin
insert into test_tbl(username,logon_time) values(user,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
commit;
end;
注意：要创建ddl和db事件这两种触发器必须要有dba的权限才行

触发器有如下作用：
1）可维护数据库的安全性、一致性和完整性。
2）可在写入数据表前，强制检验或转换数据。
3）当触发器发生错误时，异常的结果会被撤销。
4）部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器，还可以针对视图定义替代触发器（INSTEADOF）。
优点：
①　触发器可通过数据库中的相关表实现级联更改。
②　从约束的角度而言，触发器可以定义比CHECK更为复杂的约束。
③　触发器也可以评估数据修改前后的表的状态，并根据其差异采取对策。
④　一个表中的多个同类触发器（INSERT、UPDATE或DELETE）允许采取多个不同的对策以响应同一个修改语句。
缺点：
①　滥用会造成数据库及应用程序的维护困难。在数据库操作中，可以通过关系、触发器、存储过程、应用程序等来实现数据操作，同时，规则、约束、缺省值也是保证数据完整性的重要保障。如果对触发器过分地依赖，那么势必会影响数据库的结构，同时增加了维护的复杂性。
②　一个表上的触发器越多，对于表的DML操作性能影响越大
③　如果触发频率高，占用内存，降低数据访问速度
④　相对不灵活，一旦触发马上执行，不能排除特殊情况
⑤　一定程度上打乱代码结构，相关的代码都需要特别注释，否则造成阅读和维护上的困难
⑥　过度使用也会造成数据库的维护困难

--包    package    
大型项目中,有可能有多个模块,而每个模块又有很多自己编译的存过函数等,
如果都放在一起就会混乱,不方便管理和维护

┌包定义  package       包头
└包主体  package body  包体

创建包头的语法
create or replace package 包头名
is|as
function 函数名1(参数1 参数类型..) return 参数类型;
function 函数名2(参数1 参数类型..) return 参数类型;
..
procedure 存过名1(参数1 参数类型..);
..
type ..;
cursor ..;
..
end 包头名;

创建包体的语法
create or replace package body 包体名 
is|as
声明变量;      --全局变量
function 函数名1(参数...) return...
is|as
要执行的部分;
[exception];
end;

function 函数名2(参数...) return...
is|as
要执行的部分;
[exception];
end;
...

procedure 存过名1(参数...)
is|as
begin
  要执行的部分;
[exception];
end;
...

end 包体名;

包头和包体要分开执行!

包定义和包主体要分开编译,并且作为两个分开的对象
存在数据字典中(user_source),声明的时候包头和包体
的变量类型一致,先执行包头,再执行包体

创建一个包,里面包含fun1 pro1 pro2
--fun1 函数 输入一个员工编号,返回它的部门名称
--pro1 存过 输入一个员工编号,输出他所在部门平均工资
--pro2 存过 输入一个名字,打印他的名字和经理的名字

create or replace package pck_1
is
  function fun1(v_empno number) return varchar2;
  procedure pro1(v_empno number,v_a_sal out number);
  procedure pro2(v_ename varchar2);
end pck_1;

create or replace package body pck_1
is

  function fun1(v_empno number) return varchar2
  is
  v_dname varchar2(20);
  begin
    select dname into v_dname
    from dept
    where deptno=(
    select deptno
    from emp
    where empno=v_empno);
    return v_dname;
  end;
  
  procedure pro1(v_empno number,v_a_sal out number)
  is
  begin
    select avg(sal) into v_a_sal
    from emp
    where deptno=(
    select deptno
    from emp
    where empno=v_empno);
    
    dbms_output.put_line(v_a_sal);
  end;
  
  procedure pro2(v_ename varchar2) 
  is
  v1 varchar2(20);
  v2 varchar2(20);
  begin
    select a.ename,e.ename into v1,v2
    from emp e  --经理
    join emp a  --员工
    on e.empno=a.mgr
    where a.ename=v_ename;
    dbms_output.put_line(v1||' '||v2);
  end;  
end pck_1;

--使用pck_1包中的fun1函数

select pck_1.fun1(7654)
from dual

call pck_1.pro2('SMITH')

--创建一个包 包含3个函数1个存过
--f1 函数 输入一个员工编号 number 返回员工工资
--f1 函数 输入一个员工姓名 varchar2 返回部门名称
--f1 函数 输入一个日期 date 返回这个日期之前入职的人数
--p1 存过 把dept表10部门的部门所在地插入到emp1的job列
create or replace package pck_2
is
function f1(v_empno number) return number;
function f1(v_ename varchar2) return varchar2;
function f1(v_hiredate date) return number;
procedure p1;
end pck_2;

create or replace package body pck_2
is
  function f1(v_empno number) return number
  is
  v_sal number;
  begin
    select sal into v_sal
    from emp
    where empno=v_empno;
    
    return v_sal;
  end;

  function f1(v_ename varchar2) return varchar2
  is
  v_dname varchar2(20);
  begin
    select dname into v_dname
    from dept
    where deptno=
    (select deptno
    from emp
    where ename=v_ename);
    
    return v_dname;
  end; 

  function f1(v_hiredate date) return number
  is
  v_count number;
  begin
    select count(1) into v_count
    from emp
    where hiredate<v_hiredate;
    
    return v_count;
  end;
 
  procedure p1
  is
  begin
    insert into emp1(job) select loc from dept where deptno=10;
  end;
end pck_2;

--包的重载
包的子程序的名字相同,但是通过传入不同的参数类型,
参数个数,得到不同的结果.

--定时任务job
job是oracle的定时任务,又叫定时器,定时作业,作业
定时地自动执行一些脚本,或作数据备份,或作数据提炼,
或作数据库性能的优化,或作重建索引等等的工作,需要
用到job

--创建job的包
dbms_job

--创建job的语法
declare
v_job_id number;      --job号
begin
  dbms_job.submit(job      =>v_job_id,--job号
                  what     =>'pro_name/DML;',--定时执行的脚本
                  next_date=>sysdate+1,--第一次执行的世界
                  interval =>'SYSDATE+1/24/60'--间隔时间
                  );
  --commit;
end;

--创建定时任务,每分钟执行一次pck_2.p1
declare
v_job_id number;
begin
  dbms_job.submit(job       =>v_job_id,
                  what      =>'pck_2.p1;',
                  next_date =>sysdate+1/24/60,
                  interval  =>'sysdate+1/24/60'
                 );
  commit;
end;

select *
from emp1

--查看job
select * from user_jobs;
--删除job
call dbms_job.remove(24);     --23指的是job号
commit;
--停止job
begin
  dbms_job.broken(24,true);
  commit;
end;
--立即执行
call dbms_job.run(24);

drop table emp1;

查看一下job临界值 
select value from v$parameter where name like '%job_queue_processes%'
结果如果是0那么执行下面语句,如果不是 就不用修改 

alter system set job_queue_processes =100;

--当job执行失败后它会重试
1、每次重试时间都是递增的，第一次1分钟，2分钟，4分钟，8分钟 ... 依此类推。
2、当超过1440分钟，也就是24小时的时候，固定的重试时间为1天。
3、超过16次重试后，job就会被标记为broken，next_date为4000-1-1，也就是不再进行job重试。

16次重试的时间大概是7天半。
--为了避免出现重复调用16次的情况，可以用以下的方法
1.创建一张空表，用来接收数据 create table t_k(id number(1));
2.创建一个存过里面包含真实的存储过程。