大型数据库Oracle笔记整理第七章
第七章存储过程、触发器和程序包
- 存储过程(重点)
- 函数(重点)
- 触发器
- 程序包
在很多时候,都需要保存pl/sql程序块以便于可以重新使用。这也意味着程序块需要一个名称,这样才可以引用它。
Oracle提供了四种类型的可存储的程序:过程、函数、触发器和程序包。
7.1存储过程
存储过程是一种命名的pl/sql程序块,它可以接受零个或多个参数作为输入、输出,或者既作输入又作输出的参数。
过程被存储在数据库中,并且过程没有返回值。
存储过程不能又sql语句直接使用,只能通过execut命令或者pl/sql程序块内部调用
由于存储过程是已经编译好的代码,所以在调用的时候不必再次进行编译,从而提高了程序的运行效率。
7.1.1创建存储过程
create (or replace)procedure procedure_name is
[local declarations]
begin
…
end [procedure_name]
注意:
- 存储过程与匿名程序块类似,包括三个部分:声明部分、执行部分和异常处理部分。
- 不能使用declare关键字表示声明部分,is关键字后声明的变量为过程体内的局部变量。
- 不能指定参数类型的长度和精度。
- 重新定义存储过程(同名),必须使用or replace选项,使新版本覆盖旧版本。
- 可以在pl/sql程序块中调用存储过程,也可以直接在sql*plus中使用execute语句调用。
7.1.2参数
Oracle有三种参数模式:IN、OUT和IN OUT
- IN参数
在调用存储过程时,有三种向其传递参数的方法:名称表示法、位置表示法和混合表示法。(一般使用位置表示法)
位置表示法:当参数比较多时,通过名称表示法调用过程会非常长,此时可以采用位置表示法。采用位置表示法传递参数时,用户提供的参数值顺序必须与过程定义中的参数顺序一致。
- OUT参数
OUT类型的参数由存储过程传入值,然后由调用者接收参数值。
在使用sql*plus调用具有OUT参数的过程时,需要使用variable命令绑定参数值。
放绑定参数图
为了查看执行结果,可以使用print命令显示变量值,也可以通过select语句检索绑定的变量值。
放select变量值图
- IN OUT参数
IN OUT类型的参数同时具有IN参数和OUT参数的特性,在调用过程时既可以向该类型的参数传入值,也可以从该参数接收值;而在过程的执行过程中,既可以读取又可以写入该类型参数。
7.1.3默认值
存储过程的参数也可以有默认值,这样当调用该过程时,如果没有向参数传入值,则该参数将使用定义的默认值。
注意:只能给IN参数设置默认值
7.1.4 过程中的事物处理
当在sql*plus中进行操作时,用户可以使用commit语句将在事物中的所有操作“保存”到数据库中。如果用户需要撤销所有的操作,则可以使用ROLLBACK语句回退到事物中未提交的操作,使数据库返回到事物处理开始前的状态。在pl/sql过程中,不仅可以包括插入和更新这类的DML操作,还可以包括事物处理语句COMMIT和ROLLBACK。
Oracle支持事物的嵌套,即在事物中进行事物处理。在嵌套的事务过程中,子事物可以独立于父事务处理进行提交和回滚。
7.2函数
函数与过程非常相似,它也是一种存储在数据库中的命名程序块,并且函数也可以接受零个或多个输入参数。
函数与过程之间的主要区别在于,函数必须有返回值,并且可以作为一个表达式的一部分,函数不能作为一个完整的语句使用。函数返回值的数据类型在创建函数时定义,定义函数的基本语法如下:
create[ or replace]function function_name
(parameter[,parameter])
return data_type is
[local declarations]
begin
…
end function_name
创建函数图
7.3触发器
触发器是关系数据库系统提供的一项技术,触发器类似于过程和函数,它们都包括声明部分,执行逻辑处理部分和异常处理部分,并且都被存储在数据库中。
7.3.1触发器的概述
触发器是与一个表或数据库时间联系在一起的,当特定事件出现时将自动执行触发器的代码块。
触发器与过程的区别在于:
- 过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的
- 在创建触发器时需要指定触发器的执行时间和触发事件
创建触发器的语法规则如下
create [or replace] trigger trigger_name
[before|after|instead of ] trigger_event
on table_name
[for each row [when trigger_condition]]
begin
trigger_body
end[ trigger_name];
Oracle对触发器的功能进行了扩展,不仅对表和视图的DML操作会引发起触发器的运行,而且对Oracle系统的操作也会引起触发器的运行。
根据触发器的触发事件和执行情况,可以将Oracle所支持的触发器分为如下几种类型:
- 行级触发器
- 语句级触发器
- INSTEAD OF触发器:视图上
- 系统事件触发器:系统事件触发(如启动与关闭)
- 用户事件触发器:与DDL或用户的登入/注销等事件相关
7.3.2 语句级触发器
如果在创建触发器时未使用FOR EACH ROW子句,则创建的触发器为语句级触发器。语句级触发器在被触发后只执行一次,而不管这一操作会影响到数据库中多少行记录。
为了确定触发事件的类型,可以使用条件谓词。条件谓词是由一个关键字IF和谓词INSERTING、UPDATING和DELETING组成。如果值为真,那么就是相应类型的语句触发了触发器
此外,还可以在UPDATE触发器中使用条件谓词,判断特定列是否被更新。
begin
if inserting then
--insert 语句触发
elseif updating then
--update 语句触发
elseif deleting then
--delete 语句触发
end if;
end;
if updating(job) then
–do something
end if;
7.3.3 行级触发器
在创建触发器时,如果使用了for each row选项,则创建的该触发器为行级触发器。对于行级触发器而言,当一个DML语句操作影响到数据库中多行数据时,行级触发器会针对每一行执行一次。
7.4程序包(相关对象集合)
程序包的组成:
- 规范:包的说明/声明包对象 create or …package 包名称 is
- 主体:包实现/对象实现
程序包调用:包名.对象名{过程:包名.过程名 函数:变量名:=包名.函数名}