第19课 使用存储过程

第19课 使用存储过程

19.1 存储过程

存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

 

19.2 为什么要使用存储过程

使用存储过程有三个主要的好处，即简单、安全、高性能。

 

19.3 执行存储过程

存储过程的 SQL 语句： EXECUTE。EXECUTE 接受存储过程名和需要传递给它的任何参数。请看下面的例子：

 

输入▼

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 variable for order number

DECLARE @order_num INTEGER

-- Get current highest order number

SELECT @order_num=MAX(order_num)

FROM Orders

-- Determine next order number

SELECT @order_num=@order_num+1

-- Insert new order

INSERT INTO Orders(order_num, order_date, cust_id)

VALUES(@order_num, GETDATE(), @cust_id)

-- Return order number

RETURN @order_num;

分析▼

此存储过程在 Orders 表中创建一个新订单。它只有一个参数，即下订单顾客的 ID。订单号和订单日期这两列在存储过程中自动生成。代码首先声明一个局部变量来存储订单号。接着，检索当前最大订单号（使用MAX()函数）并增加 1（使用 SELECT 语句）。然后用 INSERT 语句插入由新生成的订单号、当前系统日期（用 GETDATE()函数检索）和传递的顾客 ID 组成的订单。最后，用 RETURN @order_num 返回订单号（处理订

单物品需要它）。请注意，此代码加了注释，在编写存储过程时应该多加注释。

 

下面是相同 SQL Server 代码的一个很不同的版本：

输入▼

CREATE PROCEDURE NewOrder @cust_id CHAR(10)

AS

-- Insert new order

INSERT INTO Orders(cust_id)

VALUES(@cust_id)

-- Return order number

SELECT order_num = @@IDENTITY;

分析▼

此存储过程也在 Orders 表中创建一个新订单。这次由 DBMS生成订单号。

大多数 DBMS都支持这种功能；SQL Server中称这些自动增量的列为标识字段（identity field），而其他 DBMS称之为自动编号（auto number）或序列（sequence）。传递给此过程的参数也是一个，即下订单的顾客 ID。订单号和订单日期没有给出，DBMS对日期使用默认值（GETDATE()函数），订单号自动生成。怎样才能得到这个自动生成的 ID？在 SQL Server上可在全局变量@@IDENTITY 中得到，它返回到调用程序（这里使用 SELECT 语句）。

可以看到，借助存储过程，可以有多种方法完成相同的工作。不过，所选择的方法受所用 DBMS 特性的制约。