06过程、函数、触发器和包

6.1 存储过程

6.1.1 创建存储过程

语法格式

（|表示或者，{}表示其中内容不可分割，[]表示其中内容可选）

CREATE [OR REPLACE] PROCEDURE pro_name [parameter1,parameter2,…] IS|AS
	[inner_variable;]
BEGIN
	plsql_sentences;
[EXCEPTION]
	[dowith_sentences;]
END [pro_name];

parameter：存储过程参数，若是输入参数，需要在其后指定 IN 关键字；若是输出参数，需要在其后指定 OUT 关键字；若是既传入又传出的参数，需要在其后指定 IN OUT 关键字。在关键字后面是参数的数据类型，但不能指定该类型的长度。格式：var_name IN|IN OUT|OUT data_type
inner_variable：存储过程内定义的内部变量，不是存储过程被调用时用到的参数。

6.1.2 存储过程的参数

1、IN 模式参数

IN 模式参数可以有默认值，具体语法格式：var_name IN data_type [default default_value]。
当传入参数使用默认值时，只能使用指定名称的传递方式。
向 IN 模式参数传入值有三种方式：
（1）指定名称传递
语法格式：pro_name(parameter 1=>value 1[,parameter 2=>value 2]…)
注意参入的参数值类型与参数类型一致，与参数定义的顺序无关，但与参数个数有关。
（2）按位置传递
按照存储过程参数定义的位置顺序传入参数。可以使用 DESC 命令查看存储过程的定义信息。
（3）混合方式传递
不推荐使用，若在某个位置使用“指定名称传递”方式传入数值后，其后面的参数值也要使用“指定名称传递”，因为“指定名称传递”方式可能已经破坏了参数原始的定义顺序。

2、OUT 模式参数

参数语法格式：var_name OUT data_type [default default_value]。
当调用存储过程时，需要定义变量按顺序位置保存 OUT 参数值。

3、IN OUT 模式参数

参数语法格式：var_name IN OUT data_type [default default_value]。
当调用存储过程时，需要定义变量按顺序位置传入及保存传出的参数值。

6.1.3 重新编译存储过程

ALTER PROCEDURE pro_name compile;

6.1.4 删除存储过程

DROP PROCEDURE pro_name;

6.1.5 总结

（1）定义存储过程参数时，不能指定数据类型的长度。
（2）当传入参数使用默认值时，只能使用指定名称的传递方式。尽量不要使用混合方式传递。
（3）调用的存储过程中有 OUT 或 IN OUT 参数时，需要提前定义相应的变量保存传出的参数值。
（4）例：创建存储过程pro_empsal，实现传入参数是雇员编号，传出参数是雇员的总工资，IN OUT 参数是传入雇员编号传出其上级领导编号（因为这两个编号字段类型一致）。

create or replace procedure pro_empsal(
  in_empno in number,
  out_sal out number,
  io_no in out number
  )as
  var_sal emp.sal%type;
  var_com emp.comm%type;
begin
  select sal into var_sal from emp where empno=in_empno;
  select nvl(comm,0) into var_com from emp where empno=in_empno;
  out_sal:=var_sal+var_com;
  select mgr into io_no from emp where empno=io_no;
end pro_empsal;

然后使用PL/SQL程序调用该存储过程，并把结果输出。

declare
  var_empno emp.empno%type;
  var_io_empno emp.empno%type;
  var_io emp.empno%type;
  var_sal emp.sal%type;
begin
  var_empno:=7499;
  var_io_empno:=7369;
  var_io:=var_io_empno;
  pro_empsal(var_empno,var_sal,var_io);
  --也可以写成：pro_empsal(in_empno=>var_empno,out_sal=>var_sal,io_no=>var_io);
  --不能混用这两种方式！！！
  dbms_output.put_line('雇员编号是'||to_char(var_empno)||'的总工资是：'||to_char(var_sal)||'。'||'雇员编号是'||to_char(var_io_empno)||'的上级领导编号是：'||to_char(var_io)||'。');
end;

此代码运行结果是：雇员编号是7499的总工资是：1900。雇员编号是7369的上级领导编号是：7902。

6.2 函数

函数可以接受零或多个输入参数，必须有返回值。

6.2.1 创建函数

语法格式

（|表示或者，{}表示其中内容不可分割，[]表示其中内容可选）

CREATE [OR REPLACE] FUNCTION fun_name[(parameter1[,parameter2…])] RETURN data_type is|as
	[inner_variable;]
BEGIN
	plsql_sentences;
[EXCEPTION]
	[dowith_sentences;]
END [fun_name];

例：创建函数get_empsal，通过输入雇员编号，返回该雇员的工资。若没有输入的雇员编号，则返回0。

create or replace function get_empsal(var_empno emp.empno%type) return emp.sal%type as
  FunctionResult emp.sal%type;
begin
  select sal into FunctionResult from emp where empno=var_empno;
  return(FunctionResult);--必须要有return语句
exception
  when no_data_found then--使用了no_data_found预定义异常
    dbms_output.put_line('没有该雇员编号。');
    FunctionResult:=0;
    return(FunctionResult);
end get_empsal;

6.2.2 调用函数

由于函数有返回值，在调用时必须使用一个变量来保存函数的返回值。函数和这个变量组成一个赋值表达式。

例：使用get_empsal函数得到雇员编号为7499的工资。

declare
  var_empno emp.empno%type;
  var_sal emp.sal%type;
begin
  var_empno:=7499;
  var_sal:=get_empsal(var_empno);
  if var_sal!=0 then--如果sal为0说明函数返回值为0，说明没有找到该雇员编号
     dbms_output.put_line('雇员编号是'||to_char(var_empno)||'的工资是：'||to_char(var_sal)||'。');
  end if;     
end;

此代码运行结果是：雇员编号是7499的工资是：1600。

若使用一个没有的雇员编号，如7777，运行结果是：没有该雇员编号。

6.2.3 编译函数

ALTER FUNCTION fun_name compile;

6.2.4 删除函数

DROP FUNCTION fun_name;

6.2.5 总结

（1）由于函数有返回值，所以函数创建时函数主体部分（即 BEGIN 部分）必须使用 RETURN 语句返回函数值，并且要求返回值类型要与函数声明时的返回值类型（即data_type）相同。

（2）调用函数时，必须使用一个变量来保存函数的返回值，并且该变量类型与函数返回值类型（即data_type）相同。

（3）函数可以没有输入参数，输入参数定义时可以不写 IN 关键字。

6.3 触发器

6.3.1 触发器概述

触发事件：DML（Data Manipulation Language）语句、DDL（Data Definition Language）语句、数据库系统事件，用户事件。

语法格式

（|表示或者，{}表示其中内容不可分割，[]表示其中内容可选）

CREATE [OR REPLACE] TRIGGER tri_name
	[BEFORE|AFTER|INSTEAD OF] tri_event1 [OR tri_event2…]
	ON table_name|view_name|user_name|db_name
	[FOR EACH ROW][WHEN tri_condition]
BEGIN
	plsql_sentences;
END tri_name;

BEFORE|AFTER|INSTEAD OF：表示触发时机关键字。BEFORE 表示在执行触发事件之前触发，这种方式能够防止某些错误操作发生而便于回滚或者实现某些业务规则；AFTER 表示在 DML 等操作之后发生，这种方式便于记录该操作或某些事后处理信息；INSTEAD OF 表示触发器为替代触发器。

tri_event：触发事件，常用的有 UPDATE、INSERT、DELETE、CREATE、ALTER、DROP 等，可以使用 OR 指定多个事件。

6.3.2 语句级触发器

在语句级触发器中不使用 FOR EACH ROW 子句，无论数据操作影响多少行，触发器都只会执行一次。

例：实现对dept表的各种操作进行监控，首先创建日志表dept_log，存储操作方式和时间。

create table dept_log
(
	event varchar2(10),
    event_time date default sysdate --设置该字段的默认值为系统时间
);

然后创建关于dept表的语句级触发器，将用户对dept表的操作信息保存到dept_log表中。

create or replace trigger tri_dept
	before insert or update or delete
	on dept
declare
	var_event dept_log.event%type;--使用与event字段相同的字段类型，便于赋值
begin
	if inserting then
		var_event:='insert';
	elsif updating then
		var_event:='update';
	elsif deleting then
		var_event:='delete';
	end if;
	insert into dept_log(event) values(var_event);
	--因为dept_log表中的date字段有默认值为系统时间，所以在此不用赋值。
end tri_dept;

以上代码中使用了条件谓词（inserting、updating、deleting）。条件谓词还可以判断特定列是否有变化，例如updating(deptno)，就可以判断是否对deptno列进行了更新。

6.3.3 行级触发器

在行级触发器中必须使用 FOR EACH ROW 子句，DML 操作所影响的每一行数据，触发器都会执行一次。

例：实现对emp工资更新的记录，首先创建日志表empsal_log，存储雇员编号、旧工资、新工资和时间。

create table EMPSAL_LOG
(
	empno NUMBER(4),
    o_sal NUMBER(7,2),
	n_sal NUMBER(7,2),
	event_time DATE default sysdate
);

然后创建关于emp表的行级触发器，将用户对emp表工资的操作信息保存到empsal_log表中。

create or replace trigger tri_empsal
  after update
  on q_emp 
  for each row
begin    
  if:old.sal!=:new.sal then
    insert into empsal_log(empno,o_sal,n_sal) values(:old.empno,:old.sal,:new.sal);
  end if;
end tri_empsal;

以上代码中使用了列标识符，分为原值标识符（:old.col_name）和新值标识符（:new.col_name）。原值标识符通常在 UPDATE 和 DELETE 语句中使用，因为在 INSERT 语句中新插入的行没有原始值；新值标识符通常在INSERT和 UPDATE 语句中使用，因为在 DELETE 语句中被删除的行没有新值。

6.3.4 替换触发器

触发时机关键字为 INSTEAD OF。替换触发器定义在视图上，用户对视图的 DML 操作实际上就变成了执行触发器中的 PL/SQL 语句块，这样就可以通过在触发器中编写适当的代码对构成视图的各个基表进行操作，从而实现对视图的 DML 操作（视图是由多个基表连接组成的逻辑结构，一般不允许用户进行 DML 操作，通过替换触发器就可以实现视图的 DML 操作）。

例：创建视图展现雇员与部门地址的关系，也包含一些基本信息。

create or replace view view_emp_dept as
	select t1.deptno,t2.dname,t1.empno,t1.ename,t2.loc from emp t1
	left join dept t2 on t1.deptno=t2.deptno;

然后创建view_emp_dept视图在 INSERT 事件中的替换触发器，实现向emp表和dept表中插入两行相关联的数据。

create or replace trigger tri_insert_view
	instead of insert
	on 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--使用了隐式游标的%notfound属性
		insert into dept(deptno,dname,loc) values(:new.deptno,:new.dname,:new.loc);
		--如果找不到，就向dept表中插入新数据
	end if;
	insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);
	--因为dept表与emp表是一种“包含”的关系，因此，无论新插入的deptno是否存在都需要新插入emp表。不然插入新行就没有什么意义了（dept表已经有值、emp表也已经有值，那插入还有什么意义？直接更新不就行了)
end tri_insert_view;

替换触发器创建编译后，下面语句就可以正常执行了。定义的是 INSERT 事件的替换触发器，只能执行 INSERT 语句。
如果没有定义替换触发器那下面语句就会报错：ORA-01776:无法通过联接视图修改多个基表。

insert into tri_insert_view(deptno,dname,empno,ename,loc)
	values(30,'SALES',7111,'MIKE','CHICAGO');--只向emp表中插入了数据，因为dept表中有30的这行记录

6.3.5 用户事件触发器

触发事件为DDL、用户登录、退出等，常见事件包括：CREATE、ALTER、DROP、ANALYZE、COMMIT、GRANT、REMOVE、RENAME、TRUNCATE、SUSPEND、LOGON和LOGOFF等。

例：实现对DDL操作的记录，创建日志信息表，存储数据对象、类型、操作行为、操作用户和操作日期等。

create table ddl_oper_log
(
	obj_name varchar2(20),
	obj_type varchar2(20),
    oper_action varchar2(20),
    oper_user varchar2(20),
    oper_date date default sysdate
);

然后创建用户触发器，用于记录某个用户下的DDL操作信息保存到ddl_oper_log表中，这里创建关于scott用户的DDL操作（这里包括 CREATE、ALTER 和 DROP）。

create or replace trigger tri_ddl_oper
	before create or alter or drop
	on scott.schema
begin
	insert into ddl_oper_log values
	(
    	ora_dict_obj_name,
    	ora_dict_obj_type,
        ora_sysevent,
        ora_login_user
    );
end tri_ddl_oper;

以上使用了事件属性，含义如下：

ora_dict_obj_name：获取DDL操作所对应的数据库对象。

ora_dict_obj_type：获取DDL操作所对应的数据库对象的类型。

ora_sysevent：获取触发器的系统事件名。

ora_login_user：获取登录用户名。

在scott模式下进行DDL操作就会引起该触发器的执行。

6.3.6 编译触发器

ALTER TRIGGER tri_name compile;

6.3.7 删除触发器

DROP TRIGGER tri_name;

6.3.8 总结

（1）触发事件，常用的有 UPDATE、INSERT、DELETE、CREATE、ALTER、DROP 等，可以使用 OR 指定多个事件。

（2）条件谓词（inserting、updating、deleting）。条件谓词还可以判断特定列是否有变化，例如updating(deptno)，就可以判断是否对deptno列进行了更新。

（3）列标识符，分为原值标识符（:old.col_name）和新值标识符（:new.col_name）。原值标识符通常在 UPDATE 和 DELETE 语句中使用，因为在 INSERT 语句中新插入的行没有原始值；新值标识符通常在 INSERT 和 UPDATE 语句中使用，因为在 DELETE 语句中被删除的行没有新值。

（4）在某模式下创建的触发器使用：on username.schema。而且 DDL 语句不对应某行，所以不使用 FOR EACH ROW 语句。ora_dict_obj_name、ora_dict_obj_type、ora_sysevent、ora_login_user的含义。

6.4 程序包

程序包通常由规范和包主体组成。

6.4.1 程序包的规范

用于规定在程序包中可以使用哪些变量、类型、游标和子程序。语法格式：

（|表示或者，{}表示其中内容不可分割，[]表示其中内容可选）

CREATE [OR REPLACE] PACKAGE pack_name IS|AS
	[declare_variable;] --规范内声明的变量
	[declare_type;] --规范内声明的类型
	[declare_cursor;] --规范内定义的游标
	[declare_function;] --规范内声明的函数，仅定义参数和返回值类型
	[declare_procedure;] --规范内声明的存储过程，仅定义参数
END [pack_name];

例：创建一个程序包规范pack_empsal，再其中声明一个可以根据雇员编号获取其工资的函数。

create or replace package pack_empsal as
	function fun_empsal(var_empno emp.empno%type) return emp.sal%type;
end pack_empsal;

6.4.2 程序包主体

包含了规范中声明的游标、过程和函数的实现代码，也可以包含在程序包主体中的一些内部变量。语法格式：

（|表示或者，{}表示其中内容不可分割，[]表示其中内容可选）

CREATE [OR REPLACE] PACKAGE BODY pack_name IS|AS
	[innner_variable;]
	[cursor_body;]
	[function_title;]
	BEGIN
		fun_plsql;
	END [fun_name];
	[procedure_title;]
	BEGIN
		pro_plsql;
	END [pro_name];
END [pack_name];

例：创建程序包pack_empsal主体。

create or replace package body pack_empsal as
	function fun_empsal(var_empno emp.empno%type) return emp.sal%type as
		FunctionResult emp.sal%type;
	begin
  		select sal into FunctionResult from emp where empno=var_empno;
  		return(FunctionResult); --必须要有return语句
	exception
  		when no_data_found then --使用了no_data_found预定义异常
    		dbms_output.put_line('没有该雇员编号。');
    		FunctionResult:=0;
    		return(FunctionResult);
	end fun_empsal;
end pack_empsal;

6.4.3 调用程序包

在创建程序包的“规范”和“主体”之后，就可以像普通的存储过程和函数一样实施调用了。

例：调用pack_empsal程序包

declare
  var_empno emp.empno%type;
  var_sal emp.sal%type;
begin
  var_empno:=7499;
  var_sal:=pack_empsal.fun_empsal(var_empno);--对程序包中的fun_empsal函数进行调用
  if var_sal!=0 then
     dbms_output.put_line('雇员编号是'||to_char(var_empno)||'的工资是：'||to_char(var_sal)||'。');
  end if;     
end;

此代码运行结果是：雇员编号是7499的工资是：1600。

6.4.4 编译程序包

ALTER PACKAGE pack_name compile;

6.4.5 删除程序包

DROP PACKAGE pack_name;