4.包+触发器+异常处理机构

三.包

把一系列的存储过程或函数打包放在一起,包含两部分:包头 package 和包体 package body

在一个大型项目中，可能有很多模块，而每个模块又有自己的过程、函数等,

而这些过程、函数默认是放在一起的，这些非常不方便查询和维护，甚至会发生误删除的事件

所以通过使用包就可以分类管理过程和函数

1.语法:

包头

create [or replace] package 包名 is

function 函数名称(参数1 数据类型,参数2 数据类型...) return 返回值数据类型;

procedure 过程名称(参数1 数据类型,参数2 out 数据类型,参数3 in out 数据类型...);

end;

包体

create [or replace] package body 包名 is

function 函数名称(参数1 数据类型,参数2 数据类型...) return 返回值数据类型 is

--定义变量

begin

--业务逻辑处理

return 返回值;

end;

procedure 过程名称(参数1 数据类型,参数2 out 数据类型,参数3 in out 数据类型...) is

--定义变量

begin

--具体的业务逻辑处理

参数2 := 返回值;

end;

end;

2.学习案例

create package pk1 is
      function f1(n1 number ,n2 number) return number;
      procedure p1(n1 number, n2 number);
    end;

    create package body pk1 is
      function f1(n1 number ,n2 number) return number is
      begin
        return n1*n2;
      end;
      
      procedure p1(n1 number, n2 number) is
        v_res number;
      begin
        v_res := n1 + n2;
        dbms_output.put_line(v_res);
      end; 
    end;

    select pk1.f1(2,3) from dual;
    call pk1.p1(3,4);

3.练习

create or replace package pk1 is
      function f1(n1 number, n2 number) return number;
      procedure p1(n1 in number, n2 in number);
    end pk1;

    create or replace package body pk1 is
      function f1(n1 number, n2 number) return number is
      begin
        return n2 * n1;
      end f1;

      procedure p1(n1 in number, n2 in number) is
        v_s number;
      begin
        v_s := n1 - n2; 
        dbms_output.put_line(v_s);
      end p1;
    end pk1;

四.触发器

满足制定的一些规则后，会自动去执行的一个函数.

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，

而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。

ORACLE事件指的是对数据库的表进行的 INSERT、UPDATE 及 DELETE 操作或对视图进行类似的操作。

1.语法:

1.1创建语法:

create [or replace] trigger 触发器名称 after insert or update or delete on 表名 [for each row]

begin

--触发器被触发了需要执行的内容

end;

注意:

[update][insert][delete] :可以写一个到多个,执行相关的操作会触发触发器

[for each row] :不写的话对表操作一次触发一次(语句级触发器),无论一条dml语句影响多少行数据只触发一次;

写了后对每行数据进行操作都会触发触发器 (行级触发器),dml语句影响了多少行数据就会被触发多少次.

2.禁用启用和删除

--禁用启用某一个触发器

alter trigger t1 disable;

alter trigger t1 enable;

--禁用启用某一张表相关的所有触发器

alter table emptest disable all triggers;

alter table emptest enable all triggers;

--删除触发器

drop trigger t1;

注意:

inserting: 插入

updating: 更新

deleting: 删除

:new 和 :old 只能在行级触发器内使用

:new --更新之后的一整行数据

:old --更新之前的一整行数据

3.学习案例

3.1如果对emptest表进行了更新操作控制台输出提示内容

 create or replace trigger t1 after update on emptest for each row
   begin
     dbms_output.put_line('t1触发器工作了!');
   end;

3.2如果对emptest表进行了删除更新插入操作控制台输出提示内容

create  or replace  trigger t2 after update or delete or insert on emptest
   begin
     dbms_output.put_line('t2触发器工作了!');
   end;

3.3如果对emptest表进行了删除更新插入操作控制台输出提示内容

create  or replace  trigger t1 after update or delete or insert on emptest for each row
   begin
     if updating then
       dbms_output.put_line('t2触发器工作了,数据更新了,更新前:'||:old.sal||',更新后:'||:new.sal);
     elsif inserting then
       dbms_output.put_line('t2触发器工作了,数据增加了,增加前:'||:old.sal||',增加后:'||:new.sal);
     elsif deleting then
       dbms_output.put_line('t2触发器工作了,数据减少了,增少前:'||:old.sal||',增少后:'||:new.sal);
     end if; 
   end;

3.4给emptest表创建一个触发器,检查更新之后的工资,如果更新后的工资比更新前的工资低,阻止用户相应操作

create or replace trigger t1 after update on emptest for each row
  begin
    if :new.sal < :old.sal then
      raise_application_error(-20001,'不能降低工资!');
    end if;
  end;

4.练习

4.1建立一触发器,当员工表 emptest被删除一条信息时,把删除的记录写到员工信息删除记录表内

 create or replace trigger t1 after delete on emptest for each row
    begin
      if deleting then
        insert into empdemo values (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno); 
      end if;
    end;

4.2创建一个触发器, 如果在周末操作数据库,则抛出异常阻止操作

create or replace trigger t1 after delete or update or insert on emptest for each row
    begin
      if to_char(sysdate,'day') in ('星期四','星期六','星期日') then
        raise_application_error(-20001,'周末无法操作');     
      end if;
    end;

五.异常

1.常见异常

（1）NO_DATA_FOUND:当没有调用分组函数（例如：SUM或者COUNT等）时，SELECT INTO 语句没有返回任何数据行，此时就会抛出这个异常

例如，假设对EM表执行SELECT INTO语句，其中查询语句中EMPNO等于110,如果EMP表中没有EMPNO等于110的记录，则会抛出这个异常

（2）TOO_MANY_ROWS:SELECT INTO 语句返回多个数据行的时候，就会抛出这个异常,根据定义，SELECT INTO只能返回一个数据行

（3）ZERO_DIVIDE :当除数为0的时候会抛出这个异常

（4）VALUE_ERROR :数据类型转换或者大小匹配错误的时候，就会抛出这个异常

（5）OTHERS :不知道程序出么异常的情况下，使用这个。但是，在很多情况下中，你会发现很多程序使用这个异常处理程序，

不会给你和用户带来有价值的信息，你也许不知道发生了什么错误

2.捕获异常并处理

declare

v_num number(1);

begin

--select sal into v_num from emp where empno = 7788;

--select 5/0 into v_num from dual;

--select sal into v_num from emp where deptno = 10;

select sal into v_num from emp where empno = 1122;

exception

when VALUE_ERROR then

dbms_output.put_line('数据类型转换或者大小匹配错误');

when ZERO_DIVIDE then

dbms_output.put_line('除数不能为0');

when others then

dbms_output.put_line('不知道咋回事,就出错了');

dbms_output.put_line(sqlcode);

dbms_output.put_line(sqlerrm);

end;

注意:

sqlcode:程序错误序号,其中 0--成功；-1--失败；100--没有检索到数据

sqlerrm:程序错误信息,内的错误代码可以帮助快速定位错误原因

others要写在最后,如果上面定义的所有异常都没有匹配到就执行others写的异常处理逻辑

3.学习案例

用异常处理的方式,将程序块发生的错误写入错误日志表内

create table err_log(err_code varchar2(20),err_msg varchar2(200));
    select * from err_log;
    
  declare
    v_num number(1);
    v_code varchar2(20);
    v_msg varchar2(200);
  begin
    select sal into v_num from emp where empno = 7788;
    select 5/0 into v_num from dual;
    select sal into v_num from emp where deptno = 10;
    select sal into v_num from emp where empno = 1122;
  exception
    when others then
      v_code := sqlcode;
      v_msg := sqlerrm;
      insert into err_log values(v_code,v_msg);
  end;

---

本来想日更的，昨天发烧的厉害，今天下午再发