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操作。