PL/SQL之存储过程

PL/SQL之存储过程

一 创建并调用存储过程

1 建立存储过程
在 ORACLE SERVER 上建立存储过程 可以被多个应用程序调用 可以向存储过程传递参数 也可以向存储过程传回参数
(1) 语法：

CREATE [ OR REPLACE ] PROCEDURE Procedure_name
[ (argment [ {IN | IN OU T }] Type,
	argment [ { IN | OUT | IN OUT } ] Type
[ AUTHID DEFINER | CURRENT_USER ]
{IS | ASv}
<类型 变量的说明>
BEGIN
	<执行部分>
EXCEPTION
	<可选的异常错误处理程序>
END

（2）案例： 删除指定员工的记录

插入员工记录

2 调用存储过程
ORACLE 使用 EXECUTE 语句来实现对存储过程的调用：

EXEC[UTE] Procedure_name( parameter1, parameter2…);

案例
查询指定员工记录

调用过程

计算指定部门的工资总和，并统计其中的职工数量

调用过程

二 删除存储过程

可以使用DROP PROCEDURE 命令对不需要的过程进行删除，语法如下：

DROP PROCEDURE [user.]Procudure_name;

三 AUTHID

在创建存储过程 时 , 可使用 AUTHID CURRENT_USER 或 AUTHID DEFINER 选项 以表明在执行该过程时Oracle 使用的权限 .
1 如果使用 AUTHID CURRENT_USER 选项创建一个过程 , 则 Oracle 用调用该 过程的用户权限执行该过程 . 为了成功执行该过程 调用者 必须具有访问该存储过程体中引用的所有数据库对象所必须的权限
2 如果用默认的 AUTHID DEFINER 选项创建过程 , 则 Oracle 使用过程所有者的特权执行该过程，为了成功执行该过程 , 过程的所有者 必须具有访问该存储过程体中引用的所有数据库对象所必须的权限 . 想要简化应用程序用户的特权管理 , 在创建存储过程时 , 一般选择 AUTHID DEFINER选项 这样就不必授权给需要调用的此过程的所有用户了

四 开发存储过程步骤

开发存储过程、函数、包及触发器的步骤如下：
（1）使用文字编辑处理软件编辑存储过程源码
使用文字编辑处理软件编辑存储过程源码， 需将源码存为文本格式。
（2）在 SQLPLUS 或用调试工具将存储过程程序进行解释
在 SQLPLUS 或用调试工具将存储过程程序进行解释；
在 SQL> 下调试，可用 START 或 GET 等 ORACLE 命令来启动解释。如：SQL>START c: stat1.sql
（3）调试源码直到正确
我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。
在 SQLPLUS 下来调式主要用的方法是：
① 使用 SHOW ERROR 命令来提示源码的错误位置
② 使用 user_errors 数据字典来查看各存储过程的错误位置。
（4）授权执行权给相关的用户或角色
如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在 SQLPLUS 下可以用 GRANT 命令来进行存储过程的运行授
权。
GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION
（5）与过程相关数据字典
USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS
相关的权限：
CREATE ANY PROCEDURE
DROP ANY PROCEDURE
在SQLPLUS 中，可以用 DESCRIBE 命令查看过程的 名字及其参数表
DESCRIBE Procedure_name;