3.6　存储过程与存储程序

3.6　存储过程与存储程序
- 3.6.1　存储过程

在前面的部分我们介绍了PL/SQL块的基本编写方法。在SQLplus中编写PL/SQL程序，并在SQLplus中执行它，PL/SQL块的代码就存放在SQLplus的缓冲区中。如果在SQLplus中执行了其他的SQL语句或PL/SQL块，缓冲区中就会存放新的代码，原来的PL/SQL块就会被从缓冲区中清除出去。这种没有名称只是临时存放在缓冲区中的PL/SQL块叫做匿名块。

匿名块就是没有名字的PL/SQL块，它仅存放在缓冲区中，只能在当前SQL*plus环境中执行。如果希望PL/SQL块能随时被调用执行，并且能被数据库用户共享，就需要创建存储程序。存储程序是有名字的PL/SQL块，用户可以根据它的名字进行多次调用。存储程序在创建时经过了编译与优化，被存放在数据库中，任何用户只要有适当的权限，就可以调用它。而且在调用时无需再进行编译，因此能以很快的速度执行。

与匿名块相比，存储程序是作为数据库对象存储在数据库中的，因此，首先要在数据库中创建存储程序。

存储程序的调用可以在SQL语句中、应用程序中、SQL*plus中以及其他PL/SQL块中进行。在第一次被调用时，存储程序的代码被装载到系统全局区的共享池中，以后再次调用时直接从共享池中取出代码即可执行。

存储程序与前面介绍的子程序的区别在于子程序是完成某个特定功能的程序段，它本身并不能单独执行，只能作为一个模块，在一个PL/SQL块内部被调用执行。而存储程序是一个可单独执行的程序，它可以包含多个子程序，可以在SQL语句中、应用程序中、SQL*plus中以及其他PL/SQL块中被调用执行。

存储程序的形式包括：存储过程、存储函数、触发器和程序包等。

3.6.1　存储过程

如果用户要在自己的模式中创建存储过程，需要具有CREATE PROCEDURE系统权限，如果要在其他用户的模式中创建存储过程，则需要具有CREATE ANY PROCEDURE系统权限。创建存储过程的语法为：

CREATE OR REPLACE PROCEDURE 过程名（参数1，参数2……）

AUTHID CURRENT_USER|DEFINER

声明部分

BEGIN

可执行部分

EXCEPTION

异常处理部分

END；

其中OR REPLACE选项的作用是当同名的存储过程存在时，首先将其删除，再创建新的存储过程。当然，条件是当前用户具有删除原存储过程的权限。存储过程在创建过程中已经进行了编译和优化。如果需要对存储过程进行修改，不能直接修改它的源代码，只能执行CREATE命令重新创建。存储过程、存储程序、程序包都是这样的情况。

存储过程可以带有参数，这样在调用存储过程时就需要指定相应的实际参数。如果没有参数，过程名后面的圆括号和参数列表就可以省略了。每个参数的定义格式为：

参数名　参数传递模式　数据类型：=默认值

参数各定义中各部分的用法与子程序中的参数完全相同，详细信息请参阅本章“子程序设计”部分。

AUTHID选项用来规定存储过程执行时的权限。这个选项有两个可选值，即CURRENT_USER和DEFINER，二者只能选择其中一个。过程的执行者和创建者可能不是同一个用户，如果使用CURRENT_USER创建存储过程，那么在调用时，该过程以当前登录用户的身份执行。为此，过程的创建者必须授予当前用户执行该过程的权限。如果以DEFINER创建存储过程，那么在调用时，该过程将以创建者身份执行，这是创建存储过程时默认的选项。

在存储过程中可以定义变量、类型、子程序、游标等元素，定义的方法与在匿名块中完全相同，这里不再详细描述。存储过程的声明部分开始于关键字AS，结束于关键字BEGIN，而且不需要使用关键字DECLARE。

存储过程的可执行部分是它的主要部分，它可以包含SQL语句和流控制语句，是存储过程功能的集中体现。异常处理部分用来处理存储过程在执行过程中可能出现的错误。例如，下面的代码用来创建存储过程total_income，它的功能是计算某部门员工的总收入。这个过程有一个参数，代表部门编号，并指定了默认值。这样，在调用时，如果提供了参数，则计算指定部门的数据，否则将计算所有员工的数据。

CREATE OR REPLACE PROCEDURE total_income（d_no IN integer：=0）

AUTHID DEFINER

total number；

BEGIN

if d_no=0 then—表示所有部门

SELECT sum（sal+nvl（comm，0））INTO total FROM emp；

else—仅表示指定的部门

SELECT sum（sal+nvl（comm，0））INTO total

FROM emp

WHERE deptno=d_no；

END if；

dbms_output.put_line（'总收入：'||total）；

END；

存储过程创建以后，就可以随时调用执行了。在SQL*plus中调用存储过程的命令是EXECUTE，命令的使用格式为：

EXECUTE过程名（实际参数）

例如，要计算部门10的员工总收入和应缴的税，则可以以下形式调用刚才创建的存储过程total_income：

SQL＞EXECUTE total_income（10）

如果要在一个PL/SQL块中调用存储过程，则不需要EXECUTE命令，只要通过过程名和实际参数就可以调用，调用的格式为：

过程名（实际参数）；

每个用户都可以执行自己创建的存储过程，如果要执行其他用户的存储过程，则需要具有对该存储过程的EXECUTE权限。为此，存储过程的所有者要将EXECUTE权限授予这个用户。授予EXECUTE权限的语句格式为：

GRANT EXECUTE ON过程名TO用户

例如，存储过程total_income的所有者要将它的执行权限授予用户lxj，则可以执行下面的SQL语句：

SQL＞GRANT EXECUTE ON total_income TO lxj

如果要删除一个存储过程，可以执行DROP命令，这个命令的格式为：

DROP PROCEDURE 过程名

3.6 存储过程与存储程序