Oracle存储过程

PL/SQL存储过程：

存储过程具有如下定义：

• 存储过程是数据库对象之一。
• 是数据服务器内一段使用PL/SQL语言编写的程序单元。
• 具有EXECUTE权限的用户可以显式地调用过程。
• 存储过程保存在数据库服务器上。
• 被显式地调用完成过程定义的计算任务。
• 可以接受各种Oracle定义的参数。
• 用户可以在SQL Developer或者任何可以执行SQL语句的接口处执行PL/SQL过程。
• 一旦过程被创建则在数据字典中记录该数据库对象信息。
• 其数据库对象类型为PROCEDURE。

存储过程具有如下优点：

• 加快程序的执行速度
• 减少网络的数据流量
• 提供了一种安全机制
• 允许程序模块化设计
• 提高编程的灵活性

存储过程编写语法：

CREATE [OR REPLACE] PROCEDURE <存储过程名> [(<参数名> [IN | OUT | IN OUT] <数据类型>,…)] IS|AS
[<变量或常量声明>;]
BEGIN
<子程序...>;
EXCEPTION
<异常处理...>;
END [<存储过程名>];

注意：

• 关键字[OR REPLACE]表示如果存储过程已存在，则对其覆盖，通常用于存储过程的重建
• 参数列表中[IN <表示该参数为输入参数，IN是参数的默认模式> | OUT <表示该参数为返回参数，可以将该参数传递回调用它的过程，OUT模式只能在存储过程体内部赋值> | IN OUT <表示该参数既可以向其过程体中传值，也可以将值传出>]
• 关键字 IS | AS 在存储过程和函数中基本没有区别；但要注意在视图中只能用AS不能用IS，而在游标中只能用IS不能用AS
• <变量或常量声明>区也可声明异常如：exception_name EXCEPTION;
• 与匿名块有三点区别：无DECLARE关键字；在END关键字后可加上过程名作为定义结束标志；存储过程编写完需要调用才能执行

调用存储过程：

创建了存储过程并编译成功后，存储过程拥有者可以将该存储过程的执行权限赋予指定用户。此后，用户就可以执行该存储过程。执行存储过程的PL/SQL语句格式如下：

使用EXECUTE（或EXEC）命令调用存储过程：
EXECUTE <存储过程名[参数]>;
EXEC <存储过程名[参数]>;

使用CALL命令调用存储过程：
CALL <存储过程名[参数]>;

在匿名程序块中直接以如下方式调用：
BEGIN
<存储过程名[参数]>;
END;

当调用有参数列表的存储过程时需要传值，有如下方法：
EXEC <存储过程名> （'值1','值2',...）;（按位置传值）
EXEC <存储过程名> （参数名1=>'值1',参数名2=>'值2',...）;（按参数名称传值）
EXEC <存储过程名> （'值1',参数名2=>'值2',...）;（混合传值，需注意先按位置传值再按参数名称传值）

删除存储过程：

删除存储过程语法格式：
DROP PROCEDURE <存储过程名>;

查看存储过程：

由于存储过程信息保存在Oracle系统数据字典的系统表中，所以查看存储过程只需查询系统表DBA_SOURCE、USER_OBJECTS或USER_SOURCE；

• USER_OBJECTS表可查看对象信息，其常用字段：OBJECT_NAME(对象名)；OBJECT_TYPE(对象类型)；CREATED(创建时间)；STATUS(状态信息)
• USER_SOURCE可查看对象源码信息
• DBA_SOURCE中可以通过对字段OWNER限定为当前用户名来查询当前用户创建的存储过程

存储过程编译问题：

存储过程需要经过编译，在编译时若有出错可使用show error命令查看错误信息，且可以使用ALTER PROCEDURE命令重新编译存储过程。
ALTER PROCEDURE <存储过程名> COMPILE;

存储过程权限问题：

Oracle存储过程有两种不同权限：DR Procedure（定义者权限）和 IR Procedure（调用者权限）。（注：我理解为两种模式，因为是系统固定的权限范围）

• DR Procedure（Oracle默认权限模式）：该权限是以存储过程定义者（拥有者）的特权来执行这个存储过程，执行这个存储过程的用户操作的模式为定义者，操作的对象是定义者在编译时指定的对象。另外定义者权限下，当前用户的权限为角色无效的情况下所拥有的权限。需要注意的是在此权限模式下需要显示授权，原因就是前面提到的角色无效。
• IR Procedure：该权限是指调用这个存储过程的当前用户的的权限。因为存储过程可能会被不同的用户调用（不同的用户可能拥有不同的权限），所以在此权限下用户操作的模式为当前用户，操作的对象是当前模式的对象。另外调用者权限下，当前用户的权限为当前所拥有的、而且包含角色的权限。

补充Oracle 赋予权限语法：

GRANT <权限类型> ON | ANY <对象名> TO <用户名|PUBLIC（所有用户）|GROUP（组）;
<权限类型>常用的权限：SELECT（访问声明的表/视图的所有列/字段）；INSERT（向声明的表中插入所有列/字段）；UPDATE（更新声明的所有列/字段）；DELETE（从声明的表中删除所有行）；RULE（在表/视图上定义规则）；ALL（赋予所有权限）；EXECUTE（为用户赋予执行存储过程的执行权限）

---

文章中若出现错误，请在评论中指出或联系我?
QQ：354008947
Email：[e-mail]