SQL必需掌握的100个重要知识点：使用存储过程

19.1 存储过程
迄今为止，我们使用的大多数 SQL语句都是针对一个或多个表的单条语
句。并非所有操作都这么简单，经常会有一些复杂的操作需要多条语句
才能完成，例如以下的情形。
  为了处理订单，必须核对以保证库存中有相应的物品。
  如果物品有库存，需要预定，不再出售给别的人，并且减少物品数据
以反映正确的库存量。
  库存中没有的物品需要订购，这需要与供应商进行某种交互。
  关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知
相应的顾客。
这显然不是一个完整的例子，它甚至超出了本书中所用样例表的范围，
但足以表达我们的意思了。执行这个处理需要针对许多表的多条 SQL语
句。此外，需要执行的具体 SQL语句及其次序也不是固定的，它们可能
会根据物品是否在库存中而变化。

那么，怎样编写代码呢？可以单独编写每条 SQL语句，并根据结果有条
件地执行其他语句。在每次需要这个处理时（以及每个需要它的应用中），
都必须做这些工作。
可以创建存储过程。简单来说，存储过程就是为以后使用而保存的一条
或多条 SQL语句。可将其视为批文件，虽然它们的作用不仅限于批处理。
说明：不适用于 SQLite
SQLite不支持存储过程。
说明：还有更多内容
存储过程很复杂，全面介绍它需要很大篇幅。市面上有专门讲存储过
程的书。本课不打算讲解存储过程的所有内容，只给出简单介绍，让
读者对它们的功能有所了解。因此，这里给出的例子只提供 Oracle和
SQL Server的语法。
19.2 为什么要使用存储过程
我们知道了什么是存储过程，那么为什么要使用它们呢？理由很多，下
面列出一些主要的。
  通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面
例子所述）。
  由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如
果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都
是相同的。
  上一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就
越大。防止错误保证了数据的一致性。

  简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变
化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道
这些变化。
  上一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减
少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
  因为存储过程通常以编译过的形式存储，所以 DBMS处理命令所需的
工作量少，提高了性能。
  存在一些只能用在单个请求中的 SQL元素和特性，存储过程可以使用
它们来编写功能更强更灵活的代码。
换句话说，使用存储过程有三个主要的好处，即简单、安全、高性能。
显然，它们都很重要。不过，在将 SQL代码转换为存储过程前，也必须
知道它的一些缺陷。
  不同 DBMS中的存储过程语法有所不同。事实上，编写真正的可移植
存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数
据如何传递）可以相对保持可移植。因此，如果需要移植到别的DBMS，
至少客户端应用代码不需要变动。
  一般来说，编写存储过程比编写基本 SQL语句复杂，需要更高的技能，
更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为
安全措施（主要受上一条缺陷的影响）。
尽管有这些缺陷，存储过程还是非常有用的，并且应该使用。事实上，
多数 DBMS都带有用于管理数据库和表的各种存储过程。更多信息请参
阅具体的 DBMS文档。

说明：不会写存储过程？你依然可以使用
大多数 DBMS将编写存储过程所需的安全和访问权限与执行存储过
程所需的安全和访问权限区分开来。这是好事情，即使你不能（或
不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存
储过程。
19.3 执行存储过程
存储过程的执行远比编写要频繁得多，因此我们先介绍存储过程的执行。
执行存储过程的 SQL语句很简单，即 EXECUTE 。 EXECUTE 接受存储过程
名和需要传递给它的任何参数。请看下面的例子（你无法运行这个例子，
因为 AddNewProduct 这个存储过程还不存在）：
输入▼
EXECUTE AddNewProduct('JTS01',
'Stuffed Eiffel Tower',
6.49,
'Plush stuffed toy with
➥the text La Tour Eiffel in red white and blue');
分析▼
这里执行一个名为 AddNewProduct 的存储过程，将一个新产品添加到
Products 表中。 AddNewProduct 有四个参数，分别是：供应商 ID
（ Vendors 表的主键）、产品名、价格和描述。这 4 个参数匹配存储过程
中 4个预期变量（定义为存储过程自身的组成部分）。此存储过程将新行
添加到 Products 表，并将传入的属性赋给相应的列。
我们注意到，在 Products 表中还有另一个需要值的列 prod_id 列，它
是这个表的主键。为什么这个值不作为属性传递给存储过程？要保证恰

当地生成此 ID，最好是使生成此 ID 的过程自动化（而不是依赖于最终
用户的输入）。这也是这个例子使用存储过程的原因。以下是存储过程所
完成的工作：
  验证传递的数据，保证所有 4个参数都有值；
  生成用作主键的唯一 ID；
  将新产品插入 Products 表，在合适的列中存储生成的主键和传递的
数据。
这就是存储过程执行的基本形式。对于具体的 DBMS，可能包括以下的
执行选择。
  参数可选，具有不提供参数时的默认值。
  不按次序给出参数，以“参数=值”的方式给出参数值。
  输出参数，允许存储过程在正执行的应用程序中更新所用的参数。
  用 SELECT 语句检索数据。
  返回代码，允许存储过程返回一个值到正在执行的应用程序。
19.4 创建存储过程
正如所述，存储过程的编写很重要。为了获得感性认识，我们来看一个简
单的存储过程例子，它对邮件发送清单中具有邮件地址的顾客进行计数。
下面是该过程的 Oracle版本：
输入▼
CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)

IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;
分析▼
这个存储过程有一个名为 ListCount 的参数。此参数从存储过程返回一
个值而不是传递一个值给存储过程。关键字 OUT 用来指示这种行为。
Oracle支持 IN （传递值给存储过程）、 OUT （从存储过程返回值，如这里）、
INOUT （既传递值给存储过程也从存储过程传回值）类型的参数。存储
过程的代码括在 BEGIN 和 END 语句中，这里执行一条简单的 SELECT 语
句，它检索具有邮件地址的顾客。然后用检索出的行数设置 ListCount
（要传递的输出参数）。
调用 Oracle例子可以像下面这样：
输入▼
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;
分析▼
这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储
过程，再使用 SELECT 语句显示返回的值。
下面是该过程的 SQL Server 版本。

输入▼
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
分析▼
此存储过程没有参数。调用程序检索 SQL Server的返回代码提供的值。
其中用 DECLARE 语句声明了一个名为 @cnt 的局部变量（SQL Server中所
有局部变量名都以 @ 起头）；然后在 SELECT 语句中使用这个变量，让它
包含 COUNT() 函数返回的值；最后，用 RETURN @cnt 语句将计数返回给
调用程序。
调用 SQL Server例子可以像下面这样：
输入▼
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;
分析▼
这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储
过程，再使用 SELECT 语句显示返回的值。
下面是另一个例子，这次在 Orders 表中插入一个新订单。此程序仅适
用于 SQL Server，但它说明了存储过程的某些用途和技术：

输入▼
CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 为订单号声明一个变量
DECLARE @order_num INTEGER
-- 获取当前最大订单号
SELECT @order_num=MAX(order_num)
FROM Orders
-- 决定下一个订单号
SELECT @order_num=@order_num+1
-- 插入新订单
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id)
-- 返回订单号
RETURN @order_num;
分析▼
此存储过程在 Orders 表中创建一个新订单。它只有一个参数，即下订
单顾客的 ID。订单号和订单日期这两列在存储过程中自动生成。代码首
先声明一个局部变量来存储订单号。接着，检索当前最大订单号（使用
MAX() 函数）并增加 1（使用 SELECT 语句）。然后用 INSERT 语句插入由
新生成的订单号、当前系统日期（用 GETDATE() 函数检索）和传递的顾
客 ID组成的订单。最后，用 RETURN @order_num 返回订单号（处理订
单物品需要它）。请注意，此代码加了注释，在编写存储过程时应该多加
注释。
说明：注释代码
应该注释所有代码，存储过程也不例外。增加注释不影响性能，因此
不存在缺陷（除了增加编写时间外）。注释代码的好处很多，包括使别
人（以及你自己）更容易地理解和更安全地修改代码。

对代码进行注释的标准方式是在之前放置 -- （两个连字符）。有的
DBMS还支持其他的注释语法，不过所有 DBMS都支持 -- ，因此在注
释代码时最好都使用这种语法。
下面是相同 SQL Server代码的一个很不同的版本：
输入▼
CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 插入新订单
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
-- 返回订单号
SELECT order_num = @@IDENTITY;
分析▼
此存储过程也在 Orders 表中创建一个新订单。这次由 DBMS 生成订单
号。大多数 DBMS都支持这种功能；SQL Server中称这些自动增量的列
为标识字段（identity field），而其他 DBMS称之为自动编号（auto number）
或序列（sequence）。传递给此过程的参数也是一个，即下订单的顾客 ID。
订单号和订单日期没有给出，DBMS 对日期使用默认值（ GETDATE() 函
数），订单号自动生成。怎样才能得到这个自动生成的 ID？在 SQL Server
上可在全局变量 @@IDENTITY 中得到，它返回到调用程序（这里使用
SELECT 语句）。
可以看到，借助存储过程，可以有多种方法完成相同的工作。不过，所
选择的方法受所用 DBMS特性的制约。