过程、函数、程序包、触发器

1 过程

（1）定义语法

create [or replace ] procedure 过程名

[(参数1 [in | out | in out] 参数类型，

参数2 [in | out | in out] 参数类型，

……)]

is | as

begin

执行部分

exception

异常处理部分

end [过程名];

 过程中没有declare部分，声明部分在begin之前。

 

（2）调用语法

用户既可以从匿名的PL/SQL程序块中调用，也可以在SQL*Plus中使用exec函数 +过程名来调用。

执行过程中还设计到权限问题，过程的权限是execute，使用下列语句进行授权：

grant execute on 过程名 to 用户名；

 

在SQL*PLUS中调用

EXEC  procedure_name(parameter_list)

EXECUTE show_emp(10)

 

在PL/SQL块中调用

BEGIN

      procedure_name(parameter_list);

END；

 

（3）使用参数

① 参数模式

in：输入参数，由过程读取

out：输出参数，由过程写入，该参数适合用于过程向调用者返回多个信息。

in out：过程用来读取和写入它们的值。

 

② 参数传递

在Oracle中，可以使用三种方式传递参数，即使用名称表示法，使用位置表示法，使用混合表示法。

A 使用名称表示法：

过程名（参数名 => 值，……）

参数的使用次序和声明次序可以不一样。

 

B 使用位置表示法：

过程名（参数值1，参数值2，……）

基于参数在过程中定义的次序进行传递。

 

C 使用混合表示法：

过程名（参数名 => 值，参数值，……）

 

 

③ out参数

必须在执行过程前，根据过程将返回的参数个数，先定义相应的变量及正确的变量类型来接收返回值。

 

比如说定义了两个变量A和B，那么A、B用来接收返回值的用法是：

exec 过程名（:A, :B）;

 

为了查看A和B的值，可以使用print命令

print A；

print B；

 

（4）过程的管理

    ① 修改存储过程

CREATE OR REPLACE PROCEDURE

② 重新编译存储过程

ALTER PROCEDURE procedure_name COMPILE；

③ 删除存储过程

DROP PROCEDURE procedure_name名；

④ 查看过程源代码

select text from user_source where name=procedure_name ;

 

2 函数

（1）概述

函数和过程很类似，是数据库中存储的命名PL/SQL程序块。

创建函数与创建过程的规则一样。

函数与过程的安全方式和参数传递方式也一样。

函数的主要特征是它必须返回一个值。

 

（2）定义语法

create [or replace ] function 函数名

[(参数1 [in | out | in out] 参数类型，

参数2 [in | out | in out] 参数类型，

……)]

return 数据类型

is | as

begin

执行部分

exception

异常处理部分

end [函数名];

 

 

（3）返回值

在定义函数的函数体的任何地方，用户都可以使用return 数据;子句。

注意这里的数据的数据类型要和参数后的return数据类型一致。

 

为了获取返回值，可以调用赋予一个变量，

变量 ：= 函数名[(参数……)]；

 

还可以将函数用作其他过程以及其他函数的in参数。

 

（4）函数的管理

    ① 修改函数

       CREATE OR REPLACE FUNCTION function_name

    ② 重新编译存储过程

       ALTER FUNCTION function_name COMPILE；

    ③ 删除存储过程

       DROP FUNCTION function_name ；

    ④ 查看过程源代码

       select text from user_source where name= function_name;

 

3 程序包

（1）概述

    ① 包是包含一个或多个子程序单元（过程、函数等）的容器

② 包是全局的

③ 包类型

数据库内置包

用户创建的包

④ 包由包规范和包体两部分组成，在数据库中独立存储

 

（2）包的相关

① 包规范声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。 

 

② 包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，但是它们是私有元素，只能由同一包体中其他过程和函数使用。

 

③ 包体中函数和过程的原型必须与包规范中的声明完全一致；

④ 只有在包规范已经创建的条件下，才可以创建包体；

 

⑤ 如果包规范中不包含任何函数或过程，则可以不创建包体。

 

（3）语法

 

create [or replace] package 程序包名

is | as

procedure 过程名（过程参数列表）；

function 函数名（函数参数列表）；

……

end 程序包名；

 

以上只是程序包的定义，里面的过程和函数等的实现要在以下代码实现：

 

create [or replace] package body 程序包名

is | as

之前定义的函数、过程的具体实现部分

end 程序包名；

 

（4）包的调用

包外：通过package.element形式调用；exec 程序包名.过程名（参数列表）；

包内：直接通过元素名进行调用。

在包体中定义而没有在包头中声明的元素是私有的，只能在包体中引用。

 

（5）包的管理

包的修改

CREATE OR REPLACE PACKAGE 包名

 

重新编译包

ALTER PACKAGE package_name COMPILE;

ALTER PACKAGE package_name COMPILE  SPECIFICATION;

ALTER PACKAGE package_name COMPILE BODY;

 

删除包

DROP PACKAGE package_name ;

DROP PACKAGE BODY package_name ;

 

查看包源代码

select text from user_source

where name=‘EMP_PACKAGE’

and type=‘PACKAGE’;

 

4 触发器

（1）概述

触发器是一种特殊的过程，但是用户不能直接调用触发器。

触发器是当特定事件出现时自动执行的代码块。

触发器没有参数。

 

触发器的类型：

① 语句触发器

② 行触发器

③ instead of 触发器

④ 系统事件触发器

⑤ 用户事件触发器

 

作用对象：表、视图、数据库、模式

触发事件：DML、DDL、数据库系统事件

触发时间：BEFORE、AFTER

触发级别：语句级、行级

触发条件：WHEN条件

触发操作：SQL语句、PL/SQL块

 

（2）定义语法

create [or replace] trigger 触发器名

触发事件

on 表名

[referencing old as old_value | new as new_value]

for each row

when (条件)

begin

SQL处理语句

end；

 

触发事件有：

① before insert             表示对表进行插入之前就触发

② before insert for each row  表示对表进行插入每一行之前就触发

③ after insert               表示对表进行插入之后就触发

④ after insert for each row    表示对表进行插入每一行之后就触发

类似的有 update、delete操作。

 

❤判断当前执行的触发器到底是由哪个DML操作激发的。

谓词
 行为
 
INSERTING
 如果触发语句是INSERT，则为TRUE；否则为FALSE
 
UPDATING
 如果触发语句是UPDATE，则为TRUE；否则为FALSE
 
DELETING
 如果触发语句是DELETE，则为TRUE；否则为FALSE

 

 

标识符

      :OLD   、:NEW

引用方式：

      :old.field和:new.field （执行部分）

      old.field 和new.field   (WHEN条件中)

 

（3）行触发器

用户不仅可以建立为表上各个insert或update或delete语句激活的触发器，而且可以定义受到影响的各行激活的触发器。这些触发器称为行触发器。

 

行触发器要在触发器定义的触发语句中包含for each row子句，还可以包含referencing子句。

行触发器是指执行DML操作时，每操作一记录，触发器就执行一次，一个DML操作涉及到多少个记录，触发器就执行多少次。

 

可以获取语句执行之前的值和语句执行之后的值。

前映像的默认相关名称是：old，后映像的默认相关名称是：new

在insert触发器中，只可以使用：new

在delete触发器中，只可以使用：old

 

for each row 表示行触发器。

 

（4）instead of触发器

instead of触发器是Oracle系统用来替换所使用的实际语句而执行的触发器。是行级触发器。

如果在视图上执行insert或update或delete操作的用户，且该视图上有instead of触发器，那么Oracle系统就会将用户的操作转移到instead of触发器指定的位置上进行操作。

 

instead of触发器总是等于after行触发器，instead of 触发器会为每个受影响的行激活一次，用户不能修改它们的：new值。

 

create [or replace] trigger 触发器名

instead of insert | update | delete

on视图名

begin

   ……

end；

 

（5）用户事件触发器

常用的用户事件：

create、alter、drop、grant、revoke、rename、truncate等。这几个可以用before也可以用after。

只能用before触发器：logoff；只能用after触发器：suspend。

 

（6）系统事件触发器

① 系统事件：

事件
 允许计时
 描述
 
STARTUP
 AFTER
 当实例开始时激发
 
SHUTDOWN
 BEFORE
 当实例关闭时激发
 
SERVERERROR
 AFTER
 只要错误发生就激发
 
LOGON
 AFTER
 在一个用户成功连接数据库时触发
 
LOGOFF
 BEFORE
 在用户注销时开始激发
 
CREATE
 BEFORE,AFTER
 创建一个模式对象之前或之后激发
 
DROP
 BEFORE,AFTER
 在删除一个模式对象之前或之后激发
 
ALTER
 BEFROE,AFTER
 在更改一个模式对象之前或之后激发

 

② 语法

CREATE [OR REPLACE] TRIGGER trigger_name

BEFORE|AFTER ddl_event_list|database_event_list

ON DATABASE|SCHEMA

[WHEN trigger_condition]

DECLARE

     /*Declarative section is here */

BEGIN

    /*Executable section is here*/

EXCEPTION

    /*Exception section is here*/

END [trigger_name];

 

 

（7）触发器操作

禁用：alter trigger 触发器名 disable；

启用：alter trigger 触发器名 enable；

禁用某个表相关的所有触发器：alter table 表名 disable | enable all triggers；

重新编译触发器：alter trigger 触发器名 compile；

删除：drop trigger 触发器名；

查看：视图user_triggers；

另外，Oracle不允许在触发器中进行commit和rollback操作。