Oracle：4、存储过程、函数、触发器，程序包

目录

一、存储过程

1.1、参数

1.2、不能被SQL语句直接使用

1.3、效率

1.4、DML操作和事务性

二、函数

三、触发器

3.1、条件谓词inserting,updating,deleting

3.2、行级触发器for each row

3.3触发器instead of

四、程序包

4.1程序包规范

4.2程序包主体

4.3重载

---

 

一、存储过程

create [or replace] procedure procedure_name[(parameter[,parameter,...])] is
[local declarations]
begin
 execute statements
[exception
 exception handlers]
end [procedure_name];

1.1、参数

存储过程是一种命名的pl/sql程序块，它可以接收零个或多个输入(in),输出(out)或者既作输入又作输出(in out)的参数。

parameter示例：

create procedure procedure_name(
param_name1 in number,
param_name2 out varchar,
param_name3 in out varchar
param_name4 in number default 99
param_name5 in varchar default '默认值') is
...

1.2、不能被SQL语句直接使用

存储过程不能被SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用。

1.3、效率

存储过程是一编译好的代码，调用时不必再编译i，从而提高了效率。

例：
定义存储过程：

create or replace procedure swap(
 num1_param in out number,
 num2_param in out number) is
 var_temp number;
begin
 var_temp:=num1_param;
 num1_param:=num2_param;
 num2_param:=var_temp;
end swap;

调用存储过程：

declare
 var_max number:=3;
 var_mix number:=5;
begin
 if var_max < var_mix then
  swap(var_max,var_mix); -- 调用存储过程
 end if;
 dbms_output.put_line(var_max || '>' || var_mix); -- 此时的var_max=5,var_mix=3.
end;

1.4、DML操作和事务性

存储过程中支持DML（data manipulation language）操作（增删改），且支持事务的提交和回滚commit，rollback。

子存储提交的事务无法被父存储事务回滚，即子存储对应自己的一个独立子事务。

二、函数

create [or replace] function function_name (parameter[,parameter])
 return data_type is
[local declarations]
begin
 execute statements
[exception
exception handlers]
end [function_name]

函数与程序过程类型，区别在于：

1. 第一，在函数头部必须使用return子句指定返回的数据类型。
2. 第二，在函数体内，任意处使用return语句返回结果值。
3. 第三，函数作为表达式的一部分，而不能作为完整的语句使用（存储过程可以）。

三、触发器

create [or replace] trigger trigger_name
 [before||after|instead of]
 trigger_event
 on table_name
 [for each row[when trigger_condition]]
begin
 ...
end trigger_name;

before|after|instead of：用于指定触发器的出发时间。before指事件之前执行，after指事件之后执行，instead of指定触发器为替代触发器（替代其要进行的操作，即不执行其原本的操作，而去执行方法体的操作）。

• trigger_event：指定引起触发器运行的触发事件（insert，update，delete等）
• table_name：与触发器相关的表名
• for each row：指定为行级触发器，即每影响一行触发一次，若不指定则为语句级触发器，整个过程仅执行一次。
• trigger_conditioin指定触发器应该满足的条件。

例：

create or replace trigger trigger_name
before insert or update or delete
on table_name
begin
if inserting then
 ...
elseif updating(column) then
 ...
elseif deleting then
 ...
end if;
end trigger_name;

3.1、条件谓词inserting,updating,deleting

上例中的inserting,updating,deleting是条件谓词，用于判定事件是哪种类型的，以做出相应的处理。updating可以指定是否是特定列的更新（也可以不指定），对于特定列的更新做相应的处理。
 

3.2、行级触发器for each row

如前所述，行级触发器每影响一行出发一次。行级触发器提供行标识符:old,:new来获取所影响的行的相关列
（:old.column,:new.column）。

例：

create or replace trigger trigger_name
before insert or update
on foo
for each row
begin
 if inserting then
  select seq_foo.nextval
  into :new.sid
  from dual;
 else
  ...
 end if;
end;

其中：

• seq_foo是创建的sequence序列，将序列值赋值给当前行的sid。
• insert中仅有:new(插入后才有值)
• update中有:old,:new。:old代表修改前，:new代表修改后
• delete中仅有:old(删除前才有值)
   

3.3触发器instead of

instead of常用于对视图的操作，因视图可能是多表联合查询的结果，对其进行的增加和修改是无法执行的。通过该触发器，可以将对多表的操作拆分成各个单表的操作，以实现视图的插入与修改。

create or replace trigger view_name_trigger
instead of
insert on view_name
for each row
begin
 insert into table_name1(column_list)
 values(:new.xxx1,:new.xxx2,:new:xxx3...);

 update table_name2 set ...
 where column=:new.xx;
end view_name_trigger;

四、程序包

程序包是指组合在一起的相关对象的集合，当程序包中任何函数或存储过程被调用时，程序包就被加载到内存中，从而提高其子程序的访问速度。

程序包由两个部分组成：1、规范 2、包主体

4.1程序包规范

create [or replace] package package_name is
 [public_variable_declarations...]
 [public_type_declarations...]
 [public_exception_declarations...]
 [public_cursor_declarations...]
 [function_declarations...]
 [procedure_declarations...]
end [package_name];

规范仅申明过程、函数、变量等，不编写其内部实现过程等信息。

例：

create or replace package package_name is
 procedure procedure_name(param1 number,param2 varchar2);--过程
 function  function_name(param1 number) return varchar2;--函数
end package_name;

4.2程序包主体

仅申明了程序包规范，调用是会出错的。还需要程序包主体中编写相关实现代码。

create [or replace] package body package_name is
 [procedure_spec...]
 [function_spec...]
 ...
end [package_name]

例：

create or replace package body package_name is
 procedure procedure_name(param1 number,param2 varchar2) is
 begin
  ...
 end procedure_name;

 function function_name(param1 number) return varchar2 is
 ...
 return x;
 end function_name;
end package_name;

4.3重载

存储过程或函数可以通过不同的参数个数、参数类型、参数顺序进行重载（数据类型属一个家族的算一种类型，即number和integer不能进行重载。）

典型例子：to_char()函数:

function to_char(left number,right varchar2) return varchar2;
function to_char(left date  ,right varchar2) return varchar2;