自定义函数，触发器和程序包

函数

　　函数一般用于计算和返回一个值，可以将经常需要使用到的计算或功能写成一个函数，语法如下：

create [or replace] function fun_name[(parameter1[,parameter2]…) return data_type is
  [inner_variable]
begin
  plsql_ sentence;
[exception]
  [dowith _ sentences;]
end [fun_name];

　　案例：计算emp表中指定某个部门的平均工资

create or replace function get_avg_pay(num_deptno number) return number is--创建一个函数，该函数实现计算某个部门的平均工资，传入部门编号参数
  num_avg_pay number;--定义临时变量，保存某个部门的平均工资
begin
  select avg(sal) into num_avg_pay from emp where deptno=num_deptno;--获取某个部门的平均工资
  return(round(num_avg_pay,2));--返回平均工资
exception
  when no_data_found then --若此部门编号不存在
    dbms_output.put_line('该部门编号不存在');
    return(0); --返回平均工资为0
end;

　　调用函数：由于函数有返回值，所以在调用函数时，必须使用一个变量来保存函数的返回值，这样函数和这个变量就组成了一个赋值表达式。可以直接使用select语句返回

　　删除函数，其语法格式如下：
　　　　drop function fun_name; --fucn_name表示要删除的函数名称。

触发器

　　触发器可以看作特殊的存储过程，它定义了一些与数据库相关事件（如INSERT、UPDATE、DELETE等）发生时应执行的功能代码块，通常用于管理复杂的完整性约束，或监控对表的修改，或通知其它程序，甚至可以实现对数据的审计功能。

触发器组成: 

        触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

       触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

       触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

       触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

       触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

       触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

　　语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；

　　行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

创建触发器语法

create [or replace] trigger tri_name
  [before | after | instead of] tri_event
  on table_name | view_name | user_name | db_name
   [for each row [when tri_condition]
begin
plsql_sentences;
end tri_name;

　　案例：使用触发器针对dept表进行监控，首先创建一个日志表，用于存储对dept表的各种操作信息

create table dept_log(
  operate_tag varchar2(10),        --定义字段，存储操作种类信息
  operate_time date                --定义字段，存储操作日期
);

-- 创建一个关于dept表的语句级触发器，将用户对dept表的操作信息保存到dept_log表中
create or replace trigger tri_dept
  before insert or update or delete
  on dept --创建触发器，当dept表发生插入，修改，删除操作时引起该触发器执行
declare
  var_tag varchar2(10);--声明一个变量，存储对dept表执行的操作类型
begin
  if inserting then --当触发事件是INSERT时
    var_tag := '插入';--标识插入操作
  elsif updating then --当触发事件是UPDATE时
    var_tag := '修改';--标识修改操作
  elsif deleting then--当触发事件是DELETE时
    var_tag := '删除';--标识删除操作
  end if;
  insert into dept_log
  values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
end tri_dept;

　　条件谓词（由关键字if或elsif和谓词inserting、updating、deleting组成）

　　行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。必须在语法中使用for each row。典型应用----给数据表生成主键值。

　　首先创建一个序列。

CREATE SEQUENCE seq_course_id;

-- 创建一个行级触发器，该触发器在数据表tb_course插入数据时被触发，并且在该触发器的主体中实现课程表的id列的值
CREATE OR REPLACE TRIGGER TRI_INSERT_COURSE
    BEFORE INSERT
    ON TB_COURSE
    FOR EACH ROW
BEGIN
    SELECT SEQ_COURSE_ID.NEXTVAL INTO :new.cos_id FROM DUAL;
END;

　　列标识符

 　　:NEW 新值标识符（操作完成后列的值）

        :OLD  原值标识符（操作完成前列的值）

　　例： 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

　　替换触发器——即instead of触发器，它的“触发时机”关键字是instead of，而不是before或after。与其它类型触发器不同是，替换触发器定义在视图（一种数据库对象，在后面章节中会讲解到）上的，而不是定义在表上。由于视图是由多个基表连接组成的逻辑结构，所以一般不允许用户进行DML操作（如insert、update、delete等操作），这样当用户为视图编写“替换触发器”后，用户对视图的DML操作实际上就变成了执行触发器中的PL/SQL语句块，这样就可以通过在“替换触发器”中编写适当的代码对构成视图的各个基表进行操作。

create view view_emp_dept
  as select empno,ename,dept.deptno,dname,job,hiredate
     from emp,dept
     where emp.deptno = dept.deptno;


create or replace trigger tri_insert_view
  instead of insert
  on view_emp_dept--创建一个关于view_emp_dept视图的替换触发器
  for each row--是行级视图
declare
  row_dept dept%rowtype;
begin
  select * into row_dept from dept where deptno = :new.deptno;--检索指定部门编号的记录行
  if sql%notfound then--未检索到该部门编号的记录
     insert into dept(deptno,dname)
     values(:new.deptno,:new.dname);--向dept表中插入数据
  end if;
  insert into emp(empno,ename,deptno,job,hiredate)
  values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);--向emp表中插入数据
end tri_insert_view;
/

　　用户事件触发器是因进行DDL操作或用户登录、退出等操作而引起运行的一种触发器，引起该类型触发器运行的常见用户事件包括：CREATE、ALTER、DROP、ANALYZE、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、SUSPEND、LOGON和LOGOFF等。

create table ddl_oper_log
(
  db_obj_name varchar2(20),--数据对象名称
  db_obj_type varchar2(20),--对象类型
  oper_action varchar2(20),--具体dll行为
  oper_user varchar2(20),--操作用户
  oper_date date--操作日期
);

create or replace trigger tri_ddl_oper
  before create or alter or drop
  on scott.schema --在scott模式下，在创建、修改、删除数据对象之前将引发该触发器运行
begin
  insert into ddl_oper_log values(
    ora_dict_obj_name,--操作的数据对象名称
    ora_dict_obj_type,--对象类型
    ora_sysevent,--系统事件名称
    ora_login_user,--登录用户
    sysdate);
end;

程序包

　　程序包由PL/SQL元素组成（变量，类型，匿名pl/sql,存储过程，函数）组成，程序包可以被整体加载到内存，这加快了程序包中任何一个组成部分的访问速度。程序包通常由规范和主体组成。

　　该“规范”用于规定在程序包中可以使用哪些变量、类型、游标和子程序（指各种命名的PL/SQL块），需要注意的是：程序包一定要在“包主体”之前被创建，其语法格式如下：

create [or replace ] package pack_name is
[declare_variable];
[declare_type];
[declare_cursor];
[declare_function];
[declare_ procedure];
end [pack_name];

　　与创建“规范”不同的是，创建“程序包主体”使用CREATE PACKAGE BODY语句，而不是CREATE PACKAGE，这一点需要读者注意，创建程序包主体的代码如下：

create [or replace] package body pack_name is
[inner_variable]
[cursor_body]
[function_title]
{begin
fun_plsql;
[exception]
[dowith _ sentences;]
end [fun_name]}
[procedure_title]
{begin
pro_plsql;
[exception]
[dowith _ sentences;]
end [pro_name]}
…
end [pack_name];