《MS SQL Server 2000管理员手册》系列——21. 建立及管理预存程序
21. 建立及管理预存程序
何谓预存程序？
建立预存程序
使用 T-SQL 管理预存程序
本章总结
本章将学习 Microsoft SQL Server 预存程序以及使用方法。首先会介绍预存程序的类型，接着学习建立及管理使用者自订的预存程序与定义参数及变量。建立预存程序的方法有四种，本章会叙述如何使用 Transact-SQL（T-SQL）、SQL Server Enterprise Manager 和建立预存程序精灵，第四种方法为使用 SQL Distributed Management Objects（SQL-DMO），因为该方法与应用程序设计相关，故在此将不作讨论。在操作的过程中可以发现，三种建立预存程序的方法都要利用到 T-SQL 程序代码，本章的 〈使用 CREATE PROCEDURE 陈述式〉 一节中会介绍到 T-SQL 程序代码，请特别留意 T-SQL 程序代码的用法。
何谓预存程序？
预存程序（stored procedure） 是 T-SQL 陈述式的集合，SQL Server 会将该集合中的陈述式编译成一个执行计划单位。当预存程序第一次执行时，经过编译的执行计划即储存在内存的程序快取区中，并且可以重复使用，这样 SQL Server 就不必在每一次执行相同的任务时重复分析语法是否正确。预存程序和其它程序语言的程序相似，可以接受传入的参数、将参数的值传回，或是传回成功或失败状态的讯息。当程序被呼叫时，所有在程序中的陈述式都会被执行。预存程序会执行某项任务所需用到的 T-SQL 陈述式及相关逻辑。由于预存程序可视为一个执行单位，因此可由不同的使用者，重复地执行在同样的任务上，甚至可跨多应用程序作业。预存程序也为作业提供了单一控制点，确保执行商业规则的正确性与一致性。
您的应用程序端可以透过两种方法与 SQL Server 通讯：您可以撰写应用程序以从客户端传送 T-SQL 陈述式到 SQL Server 中，或者自行建立预存程序以在服务器上储存及执行。如果您从客户端应用程序传送 T-SQL 陈述式到服务器，陈述式会透过网络传输，并在每次执行时进行编译。如果使用预存程序，您可以藉由一个陈述式，从应用程序中呼叫预存程序并执行该预存程序。如之前所提到的，SQL Server 会对第一次执行的预存程序进行编译，将此执行计划储存在内存中。当下一次再呼叫相同的执行计划，就不需再重复编译。当一项工作执行时需要多项 T-SQL 陈述式，或某些陈述式经常要执行，使用预存程序就不需要每次执行一项陈述式，就得透过网络从客户端传回结果，从而减少网络的流量和系统的负担。
预存程序也可透过其它方法提高执行效能。举例来说，透过预存程序可减少客户端和服务器端间传送的数据量，客户端所需处理的资料量相对减少，因此利用预存程序在服务器上执行，就是一种提高执行效能的方法。若是在预存程序内部测试条件判断式，可以在预存程序中应用条件陈述式（如在 第二十章 中讨论的 IF 和 WHILE 结构）。这种测试逻辑会透过预存程序在服务器上执行，无须将逻辑写入应用程序里，并且服务器不必立即传回结果到客户端以执行测试条件。您还可以从指令码、批处理，或利用 T-SQL 陈述式的交互式命命列来呼叫预存程序（本章稍后会介绍）。
预存程序还为使用者提供了简单的数据库存取方式。使用者不必知道数据表的结构详细信息就可以存取数据库，换句话说，他们只要执行所需的任务，而不需要直接进入数据表。预存程序即是以此方式确保商业规则。
预存程序可以接收输入参数，使用区域变量，以及传回数据。预存程序透过输出参数、回传SELECT陈述式执行的结果、或全域数据指针（global cursor）传回数据。除了使用全域数据指针，其它的技巧在本章稍后都会有范例。
________________________________________
相关信息
关于全域数据指针的数据，您可在《在线丛书》的 搜寻 标签页中，输入「指针」这个关键词，在 Transact-SQL程序语法参考说明 这个位置，选择 DECLARE CURSOR ，即可找到联机全域数据指针的相关数据。
________________________________________
预存程序共分三种类型： 系统预存程序 、 延伸预存程序 、 简易使用者自订预存程序 。 系统预存程序（system sotred procedure） 由SQL Server所内建，并且有前缀sp_，用于管理 SQL Server 和显示数据库和使用者信息，在 第十三章 中作过介绍； 延伸预存程序（extended sotred procedure） 属于动态链接库（DLLs），SQL Server 可以动态加载与执行，通常是使用 C 或 C++ 语言写成，包含前缀xp_； 简易使用者自订预存程序 由使用者自行建立自订执行使用者指定的任务。
________________________________________
说明
当建立简易使用者自订预存程序时，不应该使用sp_当前缀，因为当SQL Server 遇到有前缀为sp_的预存程序时，会先在 master 数据库中寻找预存程序。举例来说，如果在 MyDB 数据库中，将自订的简易使用者预存程序命名为sp_myproc，SQL Server 会先在 master 数据库中寻找该预存程序（当然是找不到），然后才会去使用者数据库寻找。因此将程序简单的命名为myproc会更有效率。
________________________________________
我们会先简单的介绍延伸预存程序，本章的重点则会放在简易使用者自订预存程序。延伸预存程序因为可利用诸如 C、C++ 等程序语言建立自己的外部例程，扩充了 SQL Server 环境的使用性及延展性，执行时和其它两种类型的预存程序相同，即可以传送参数给延伸预存程序，也可以传回结果集和状态。
如前所述，延伸预存程序属于动态链接库（DLLs），SQL Server 可以动态加载与执行，并直接在 SQL Sever 地址空间中执行。您可以使用 SQL Sever Open Data Services API 撰写程序。
延伸预存程序可以在 SQL Sever 以外环境撰写，当一个延伸预存程序撰写完成，可利用 T-SQL 命令或利用 Enterprise Manager 注册该延伸预存程序。
________________________________________
相关信息
在 SQL Sever《在线丛书》可找到更多延伸预存程序的使用范例。
________________________________________
建立预存程序
本节会介绍建立预存程序的三种方法：T-SQL 的 CREATE PROCEDURE 陈述式、Enterprise Manager 和建立预存程序精灵。
使用 CREATE PROCEDURE 陈述式
CREATE PROCEDURE 陈述式的基本语法如下：
CREATE PROCEDURE [procedure_name]
[{@parameter_name data_type}] [= default][OUTPUT]
[,...,n]
AS t-sql_statement(s)
首先我们建立一个简单的预存程序，此预存程序会在Orders数据表中的每一数据列，选择（并传回）三个数据行中的数据，当ShippedDate数据行中的日期晚于RequiredDate的日期，就会传回数据。请注意，预存程序只能建立在使用中的数据库上，所以必须先利用 USE 陈述式来指定数据库。在建立预存程序前，要先确定是否有重复的名称存在，我们必须用未存在的名称命名预存程序，或是先删除已经存在的名称后再重新命名。用于建立该程序的 T-SQL 程序代码如下：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "LateShipments" AND
type = "P")
DROP PROCEDURE LateShipments
GO
CREATE PROCEDURE LateShipments
AS
SELECT RequiredDate,
ShippedDate,
Shippers.CompanyName
FROM Orders, Shippers
WHERE ShippedDate > RequiredDate AND
Orders.ShipVia = Shippers.ShipperID
GO
执行上述的 T-SQL 程序代码就会建立预存程序，要执行该预存程序，只要呼叫其名称：
LateShipments
GO
LateShipments会传回 37 列资料。
如果呼叫该预存程序的陈述式是一个批次中的陈述式，并且不是该批次操作的第一个陈述式，就必须使用 EXECUTE（可缩写为 EXEC）关键词呼叫，使用范例如下：
SELECT getdate ( )
EXECUTE LateShipments
GO
如果呼叫程序的陈述式为该批次操作的第一个陈述式，或为该批次操作的唯一陈述式，您也可以不使用 EXECUTE 关键词。
使用参数
接下来让我们在这个预存程序中，新增一个输入参数，以便在呼叫预存程序时，同时也传递参数给预存程序。要想在预存程序中指定输入参数，请以 @ 前缀为参数名称的前置字，例如@parameter_name，一个预存程序可指定高达 1024 个参数。在范例中，将建立@ShipperName的参数。当执行预存程序时，输入船公司名称，该查询就只会传回该船公司的数据列，其 T-SQL 陈述式如下：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "LateShipments" AND
type = "P")
DROP PROCEDURE LateShipments
GO
CREATE PROCEDURE LateShipments @shipperName char(40)
AS
SELECT RequiredDate,
ShippedDate,
Shippers.CompanyName
FROM Orders, Shippers
WHERE ShippedDate > RequiredDate AND
Orders.ShipVia = Shippers.ShipperID AND
Shippers.CompanyName = @shipperName
GO
执行此预存程序时，必须先提供输入参数，否则会显示如下的错误讯息：
服务器：讯息 201，层级 16，状态 4，程序 LateShipments，行 0
程序 'LateShipments' 预期使用未提供的参数 '@shipperName'。
想为 Speedy Express 这家船公司传回符合要求的数据列，请执行以下陈述式：
USE Northwind
GO
EXECUTE LateShipments "Speedy Express"
GO
该预存程序将传回 12 个数据列。
您也可以为参数设定默认值，它将适用于当呼叫预存程序时没有参数的情况。举例来说，我们将预存程序的参数默认值设为 United Package，其 T-SQL 预存程序程序代码变更如下（只变更 CREATE PROCEDURE 行）：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "LateShipments" AND
type = "P")
DROP PROCEDURE LateShipments
GO
CREATE PROCEDURE LateShipments @shipperName char(40) = "United
Package"
AS
SELECT RequiredDate,
ShippedDate,
Shippers.CompanyName
FROM Orders, Shippers
WHERE ShippedDate > RequiredDate AND
Orders.ShipVia = Shippers.ShipperID AND
Shippers.CompanyName = @shipperName
GO
如果在执行LateShipments时没有提供参数，预存程序将使用United Package为@ShipperName的默认值（并传回 16 个数据列）。即使预设参数已经确定，仍然可以提供输入参数，此参数会覆写默认值。
如果您想在预存程序中传回值给呼叫程序，请在参数名称后使用 OUTPUT 关键词。要将值储存在变量中以供呼叫预存程序的应用程序使用，也可以在呼叫该预存程序时，使用 OUTPUT 关键词。让我们实际操作一个范例，首先建立新的预存程序以为某个产品选择单价，@prod_id为 ProductID 的输入参数，@unit_price输出参数将传回单价，名称为@price的区域变量将在呼叫程序中被宣告，并用于储存传回值。下面是用于建立GetUnitPrice预存程序的程序代码：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "GetUnitPrice" AND
type = "P")
DROP PROCEDURE GetUnitPrice
GO
CREATE PROCEDURE GetUnitPrice @prod_id int, @unit_price money OUT-
PUT
AS
SELECT @unit_price = UnitPrice
FROM Products
WHERE ProductID = @prod_id
GO
您必须在呼叫程序中宣告变量，才可在预存过程调用中使用该变量。举例来说，在下面的程序代码中我们先宣告@price变量并将其数据型别设为money（它必须符合OUTPUT参数数据型别），然后执行此预存程序：
DECLARE @price money
EXECUTE GetUnitPrice 77, @unit_price = @price OUTPUT
PRINT CONVERT(varchar(6), @price)
GO
PRINT 陈述式为@price传回值13.00。请注意我们用 CONVERT 陈述式将@price由原来的money数据型别，转换为varchar数据型别，才可以将该值当成字符串、字符数据型别，或以不直接的方式转换成字符来打印（这些是 PRINT 陈述式的打印要求）。请注意在预存程序和呼叫程序中为 OUTPUT 使用不同名称的变量，以使您能够更方便的找到范例中变量的位置，并强调名称可以不同。
您也可以在执行预存程序时，使用变量指定输入值，让预存程序可以接收来自呼叫程序的数值，然后修改该值或利用该值以执行某种作业，再将新的值传回呼叫程序。操作方法为在执行预存程序前，先为呼叫程序中的某变量分配一个值（或执行查询以在变量中插入值），再将该变量传送到预存程序中。现在就来看看如何在预存程序中使用区域变量。
在预存程序中使用区域变量
DECLARE 关键词用于建立区域变量，在建立区域变量时，就要指定区域变量名称及数据型别，而名称必须以 @ 前缀为前置字。一但变量宣告，其值会先被设为 NULL。
区域变量可在批次操作、指令码（或呼叫程序）或预存程序中宣告。预存程序中的变量通常用来储存条件陈述式所测试传回的数据值，或是储存预存程序 RETURN 陈述式所传回的数据值。变数也常被用来当作计数器。变量范围从变量的宣告处开始，宣告该变量的预存程序结束后，该变量就不再有效。
现在就来看一个包含区域变量的预存程序。该预存程序使用 WHILE 循环结构插入五个数据列到数据表中。首先建立一个范例数据表，命名为mytable，然后建立一个预存程序，命名为InsertRows。在程序中将使用@loop_counter和@start_val这两个区域变量，我们会一起宣告这两个变量，并且以逗号将两变量区隔。以下为使用 T-SQL 程序代码建立数据表和预存程序的方法：
USE MyDB
GO
CREATE TABLE mytable
(
column1 int,
column2 char(10)
)
GO
CREATE PROCEDURE InsertRows @start_value int
AS
DECLARE @loop_counter int, @start_val int
SET @start_val = @start_value - 1
SET @loop_counter = 0
WHILE (@loop_counter < 5)
BEGIN
INSERT INTO mytable VALUES (@start_val + 1, "new row")
PRINT (@start_val)
SET @start_val = @start_val + 1
SET @loop_counter = @loop_counter + 1
END
GO
现在执行起始值为 1 的预存程序，如下所示：
EXECUTE InsertRows 1
GO
执行后会打印五个@start_val值：0、1、2、3和4。使用下面的陈述式从mytable中选择所有的数据列：
SELECT *
FROM mytable
GO
在执行 SELECT 陈述式后，会出现如下的输出：
column1 column2
-------- ----------
1 new row
2 new row
3 new row
4 new row
5 new row
当结束预存程序后，@loop_counter和@start_val就无法再被存取。先使用下面的 T-SQL 陈述式尝试打印：
PRINT (@loop_counter)
PRINT (@start_val)
GO
就会得到这样的错误讯息：
服务器：讯息 137，层级 15，状态 2，行 1
必须宣告变量 '@loop_counter'。
服务器：讯息 137，层级 15，状态 1，行 2
必须宣告变量 '@start_val'。
执行批次陈述式操作时，此变量范围的规则也适用。一旦宣告关键词 GO（表示批次陈述式结束），批次陈述式所宣告的区域变量将不能再被使用，区域变量的范围仅限该批次陈述式使用。以下的预存过程调用能让您了解这个规则：
USE Northwind
GO
DECLARE @price money
EXECUTE GetUnitPrice 77, @unit_price = @price OUTPUT
PRINT CONVERT(varchar(6), @price）
GO
PRINT CONVERT(varchar(6), @price)
GO
第一个 PRINT 陈述式打印了批次操作中的区域变量@price；第二个 PRINT 陈述式企图在批次操作结束后，打印此区域变量，这时就会回传如下的错误讯息：
13.00
服务器：讯息 137，层级 15，状态 2，行 1
必须宣告变量 '@price'。
第一个打印陈述式成功的执行（打印出值 13.00）。
您可能想在预存程序中使用不只一条 T-SQL 陈述式，如 BEGIN TRANSACTION、COMMIT 和 ROLLBACK 这些陈述式，操作的方法是要先将这些陈述式群组成为一个单位的交易，使用的细节请参阅 第十九章 。
使用 RETURN
在任何时刻使用 RETURN 关键词都可以无条件退出预存程序以回到呼叫程序，也可用于退出批次操作等等。当 RETURN 执行时，预存程序执行到该点即停止执行，并回到呼叫程序中的下一个陈述式，RETURN 也可传回整数值。
首先，让我们看一个使用 RETURN 从预存程序中退出的范例。您将修改之前的GetUnitPrice程序来检查是否提供输入值，如果没有，打印此信息给使用者并回到呼叫程序。要达成此目的，请将输入参数默认值定义为 NULL，然后检查程序中的值是否为 NULL，这表示没有输入任何值。以下是这个程序的程序代码：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "GetUnitPrice" AND
type = "P")
DROP PROCEDURE GetUnitPrice
GO
CREATE PROCEDURE GetUnitPrice @prod_id int = NULL
AS
IF @prod_id IS NULL
BEGIN
PRINT "Please enter a product ID number"
RETURN
END
ELSE
BEGIN
SELECT UnitPrice
FROM Products
WHERE ProductID = @prod_id
END
GO
现在请不要输入 ProductID 值来执行GetUnitPrice并看看结果。由于呼叫程序陈述式并非该批次操作中的第一个陈述式，因次必须使用 EXECUTE 陈述式执行预存程序。程序代码如下：
PRINT "Before procedure"
EXECUTE GetUnitPrice
PRINT "After procedure returns from stored procedure"
GO
输出结果显示如下：
Before procedure
Please enter a product ID number
After procedure returns from stored procedure
由第二个 PRINT 陈述式可看出，当预存程序执行 RETURN 时，批次操作在 PRINT 陈述式处开始继续执行。
现在使用 RETURN 来传回值到呼叫程序中。传回的值必须是一个整数，常数或变量皆可。变量必须透过宣告才可在呼叫程序中使用。举例来说，当输入参数为单价少于 $100 的产品，传回值 1，否则传回 99。
CREATE PROCEDURE CheckUnitPrice @prod_id int
AS
IF (SELECT UnitPrice
FROM Products
WHERE ProductID = @prod_id) < 100
RETURN 1
ELSE
RETURN 99
GO
为了呼叫预存程序并能使用传回值，在呼叫程序中宣告变量并将该变量设为预存程序的传回值，（输入参数使用 ProductID 值 66），程序代码如下：
DECLARE @return_val int
EXECUTE @return_val = CheckUnitPrice 66
IF (@return_val = 1) PRINT 'Unit price is less than $100'
GO
由于指定的产品单价为 $17，因此传回值为 1，并打印 Unit price is less than $100。当变量用于保存传回值时，记住其数据型别必须被宣告为整数，这是 RETURN 陈述式的要求。
使用 SELECT 传回值
在预存程序中可以使用 SELECT 陈述式传回数据，您可以从 SELECT 查询或传回变量值中传回结果集。
现在来看看几个范例。首先建立一个预存程序，命名为PrintUnitPrice，该预存程序会传回输入参数所指定的产品单价（透过 ProductID），程序代码如下：
CREATE PROCEDURE PrintUnitPrice @prod_id int
AS
SELECT ProductID,
UnitPrice
FROM Products
WHERE ProductID = @prod_id
GO
将 66 作为输入参数值呼叫该程序：
PrintUnitPrice 66
GO
结果看起来像这样：
ProductID UnitPrice
---------- ------------
66 17.0000
(影响 1 个数据列)
要使用 SELECT 陈述式传回变量值，请在陈述式后面接着输入变量名称。在下面的范例中，我们会重建CheckUnitPrice预存程序，传回一变量值，并指定输出标题：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "CheckUnitPrice" AND
type = "P")
DROP PROCEDURE CheckUnitPrice
GO
CREATE PROCEDURE CheckUnitPrice @prod_id INT
AS
DECLARE @var1 int
IF (SELECT UnitPrice
FROM Products
WHERE ProductID = @prod_id) > 100
SET @var1 = 1
ELSE
SET @var1 = 99
SELECT "Variable 1" = @var1
PRINT "Can add more TSQL statements here"
GO
将 66 作为输入参数值呼叫该程序：
CheckUnitPrice 66
GO
执行该预存程序的输出结果如下：
Variable 1
-------------
99
(影响 1 个数据列)
Can add more T-SQL statements here
我们打印 Can add more T-SQL statements here 以强调使用 SELECT 传回值与使用 RETURN 传回值之间的差别。当呼叫 RETURN 时，预存程序跟着结束；当呼叫 SELECT 时，预存程序则在 SELECT 传回结果集后，继续执行。
在前面的范例中，如果没有指定输出标题，只使用SELECT @varl，输出结果将没有标题，如下所示：
(没有数据行名称)
---------------
99
(影响 1 个数据列)
使用 Enterprise Manager
现在让我们看看如何使用 Enterprise Manager 建立预存程序。要使用 Enterprise Manager 建立预存程序，仍然必须知道如何编写 T-SQL 陈述式，Enterprise Manager 只提供图形接口以供建立程序。现在就依照下面步骤重建InsertRows预存程序。
1. 在 Enterprise Manager 展开左侧窗口中 MyDB 数据库，选择 预存程序 数据夹以删除预存程序，所有的预存程序都显示在右侧窗格，在InsertRows预存程序（本章稍早已建立，所以应该已经存在）上右点鼠标，然后从快捷菜单中选择 删除 （也可以从快捷菜单重新命名或复制该预存程序），会出现如图 21-1 所示的 卸除对象 对话框，按 全部卸除 以删除预存程序。
图21-1「卸除对象」对话框
2. 在 预存程序 上右点鼠标，从快捷菜单中选择 新增预存程序 ，出现的窗口如图 21-2 所示。
图21-2「预存程序属性」窗口
3. 在 一般 卷标页的 文字 方块中，使用预存程序的名称来取代 [OWNER].[PROCEDURE NAME]，在本例中，即为InsertRows，然后为预存程序输入 T-SQL。图 21-3 显示了使用 T-SQL 程序代码新增InsertRows后的预存程序属性窗口。
图21-3用 T-SQL 程序代码增加新的预存程序
4. 按一下 检查语法 按钮，让 SQL Server 指出预存程序中的 T-SQL 语法错误，修正找到的语法错误，并反复按 语法检查 直到语法检查成功（如图 21-4 所示），按一下 确定 。
图21-4显示语法检查成功窗口
5. 在 预存程序属性 对话框中按 确定 以建立您的预存程序，建立完后，返回 Enterprise Manager，在左侧窗格中选择 预存程序 数据夹，便可在右侧窗格中看到新建立的预存程序，如图21-5所示。
图21-5在 Enterprise Manage 新建的的预存程序
6. 要为新的预存程序指派使用者的执行权限，请在右边窗格的预存程序名称上右点鼠标，在快捷菜单中选择 属性 ，出现 预存程序属性 窗口后，按一下 权限 呼叫 对象属性 窗口（如图 21-6 所示），在 EXEC 复选框中选择是否允许该使用者或数据库角色执行此预存程序。本范例中，我们将InsertRows预存程序的使用权限开放给三个使用者。
图21-6「对象属性」窗口的「权限」卷标页
7. 按一下 套用／确定 以回到 预存程序属性 窗口，按一下 确定 结束。
您可以编辑已经存在的预存程序，方法为：在程序名称上按右钮，从快捷菜单中选择 属性 ，在 预存程序属性 窗口中编辑程序（同图 21-3 的对话框），使用 检查语法 按钮检查语法，确定正确无误后，按一下 套用 ，然后按一下 确定 。
您还可以利用 Enterprise Manager 管理已经存在的预存程序权限，方法为：在 Enterprise Manager 中的预存程序名称上右点鼠标，从快捷菜单中选择 所有工作 ，然后选择 管理使用权限 。在 所有工作 中，还可以选择对该预存程序 建立新的发行集 （将在本书 第二十六章 中讨论）、产生 SQL 指令码以及显示相依性。假设选择了 产生 SQL 指令码 ，SQL Server 会自动建立一个指令码档案（由您指定指令码文件名称），该指令码会包括预存程序的定义。当您想重新建立一个预存程序，就可以使用该指令文件。
使用建立预存程序精灵
第三种方法是使用 建立预存程序精灵 ，该精灵提供了建立预存程序所需撰写的 T-SQL 程序代码基本结构，可以帮助您插入、删除或更新数据列至数据表，但无法执行从数据表中寻找数据列的程序。
精灵允许在一个数据库中建立多个预存程序，而不需退出精灵和重新启动。但是，若要在另一个数据库中建立预存程序，就必须再次执行精灵。执行精灵时，请依照下面的步骤：
1. 在 Enterprise Manager，从工具列中选择 工具／精灵 以呼叫 选择精灵 对话框，在 数据库 中选择 建立预存程序精灵 ，如图 21-7 所示。
图21-7「选择精灵」对话框
2. 选择 确定 叫出 建立预存程序精灵 的画面，如图 21-8 所示。
图21-8「建立预存程序精灵」欢迎画面
3. 选择 下一步 显示 选取数据库 画面，在 数据库名称 选择欲建立预存程序的数据库。
4. 选择 下一步 进入 选取预存程序 画面（如图 21-9 所示），在这个窗口中，可以看到在这个数据库所建立的所有数据表名称，并有三个包含复选框的数据行，分别代表使用精灵所能建立的三种预存程序：插入、删除及更新数据，请选择合适的复选框。
图21-9「选择预存程序」画面
这个范例显示了本书所使用的两个数据表，其中Bicycle_Inventory数据表被指派了两个预存程序：一个插入程序和一个更新程序。如后续的步骤所示，在实际建立这些程序之前，您可以加以修改。
________________________________________
说明
当然，一个预存程序可以执行多种类型的数据修改，但是 建立预存程序精灵 视每一个修改类型为独立的预存程序。您可以将精灵所建立的任何预存程序，用 T-SQL 程序代码变更。
________________________________________
5. 按 下一步 显示 完成建立预存程序精灵 ，如图 21-10 所示，这个画面列出所建的预存程序精灵名称及描述。
图21-10「完成建立预存程序精灵」画面
6. 欲重新命名或编辑预存程序，在 完成建立预存程序精灵 画面中按一下 编辑 ，进入 编辑预存程序属性 窗口，如图 21-11 所示，这个画面列出此预存程序会影响的数据行，在 选取 数据行中勾选的数据行名称将会被预存程序所使用。
图21-11「编辑预存程序属性」窗口
这个范例显示中在Bicycle_Inventory数据表中，有六个数据行会受到插入预存程序所影响，此插入预存程序已命名为insert_Bicycle_Inventory_1，每个数据行在 选取 栏里都有勾选复选框，代表六个数据行的值在预存程序执行时都需要输入，且六个数据行都会被预存程序插入值至该数据表中。
7. 要重新命名预存程序，请清除 名称 编辑方块中的名称，并用新的名称替代。
8. 要编辑预存程序，请按一下 编辑 SQL 来显示 编辑预存程序 SQL 对话框，如图 21-12 所示，在这个对话框中，可以检视预存程序的 T-SQL 程序代码，这里的 T-SQL 非常的基本。在这个范例中，当呼叫预存程序时，这里所列出的五个参数将会插入成为新资料列的值。要编辑该程序代码，只要在文字方块中输入修改设定。完成编辑后，按一下 剖析 检查语法错误，修正错误，然后按一下 确定 返回 完成建立预存程序精灵 画面。
图21-12「编辑预存程序 SQL」对话框
9. 确定所有预存程序的程序代码无误后，按一下 完成 建立预存程序。建立完成后，别忘了为每个预存程序设定权限。（请参阅本章稍早用于介绍设定权限的 〈使用 Enterprise Manager 建立预存程序〉 一节。）
就某些方面来说，精灵并不见得非常有用。所以如果知道如何编写 T-SQL，就可以使用指令码或 Enterprise Manager 来建立预存程序。
使用 T-SQL 管理预存程序
现在来看看如何使用 T-SQL 命令来更改、删除和检视预存程序内容。
ALTER PROCEDURE 陈述式
ALTER PROCEDURE T-SQL 陈述式用于改变先前由 CREATE PROCEDURE 所建立的预存程序。使用 ALTER PROCEDURE 改变预存程序，不会变更原来预存程序所设的权限，也不会影响任何相依的预存程序或触发程序（相依程序或触发程序是呼叫预存程序的程序）。
ALTER PROCEDURE 陈述式的语法和 CREATE PROCEDURE 类似：
ALTER PROC[EDURE] procedure_name
[{@parameter_name data_type}] [=default] [OUTPUT]
[,...,n]
AStsql_statement(s)
在 ALTER PROCEDURE 陈述式中，您必须重写整个预存程序，以达成需要的变更。举例来说，让我们重建前面例子的预存程序GetUnitPrice，并将预存程序更改为检查大于 $100 的单价，如下所示：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "GetUnitPrice" AND
type = "P")
DROP PROCEDURE GetUnitPrice
GO
CREATE PROCEDURE GetUnitPrice @prod_id int,
@unit_price money OUTPUT
AS
SELECT @unit_price = UnitPrice
FROM Products
WHERE ProductID = @prod_id
GO
ALTER PROCEDURE GetUnitPrice @prod_id int,
@unit_price money OUTPUT
AS
SELECT @unit_price = UnitPrice
FROM Products
WHERE ProductID = @prod_id AND
UnitPrice > 100
GO
现在使用下列的陈述式授权使用者 DickB 该预存程序的执行权限。
GRANT EXECUTE ON GetUnitPrice TO DickB
GO
如前所述，当变更预存程序时，不会变更原预存程序的权限。现在我们更改该预存程序，选择以UnitPrice大于 $200 的数据列，代替大于 $100 的数据列，程序如下：
ALTER PROCEDURE GetUnitPrice @prod_id int,
@unit_price money OUTPUT
AS
SELECT @unit_price = UnitPrice
FROM Products
WHERE ProductID = @prod_id AND
UnitPrice > 200
GO
执行 ALTER PROCEDURE 陈述式后，使用者 DickB 仍保留对该预存程序的执行权限。
DROP PROCEDURE 陈述式
DROP PROCEDURE T-SQL陈述式简单的说，就是用以删除已经存在的预存程序；预存程序在删除后不能被复原，如果要重新建立已经删除的预存程序，就要使用 CREATE PROCEDURE 陈述式重建。删除预存程序将遗失所有权限设定，重新建立之后必须重新授权。下面是使用 DROP PROCEDURE 来删除GetUnitPrice预存程序的范例：
USE Northwind
GO
DROP PROCEDURE GetUnitPrice
GO
________________________________________
说明
要删除预存程序，必须使用该预存程序所属的数据库。请记住当您使用数据库时，使用 USE 陈述式并且输入数据库名称即可。
________________________________________
sp_helptext预存程序
sp_helptext系统预存程序让您检视预存过程定义，以及建立预存程序的陈述式（也可用于打印触发程序、检视表、规则或默认值的定义）。当您想要快速使用 ISQL、OSQL 或 SQL Query Analyzer 呼叫出程序的定义时，sp_helptext就很有用。您也可以将输出指定到一个档案内，当需要编辑或重建预存程序时，就可叫出档案直接修改。要使用sp_helptext，必须利用使用者自订预存程序（或其它对象名称）的名称作为参数。举例来说，要检视之前建立InsertRows预存程序的陈述式，请使用以下命令：
USE MyDB
GO
sp_helptext InsertRows
GO
输出如下：
Text
------------------------------------------------
CREATE PROCEDURE InsertRows @start_value int
AS
DECLARE @loop_counter int,
@start_val int
SET @start_val = @start_value - 1
SET @loop_counter = 0
WHILE (@loop_counter < 5)
BEGIN
INSERT INTO mytable VALUES (@start_val + 1, 'new row')
PRINT (@start_val)
SET @start_val = @start_val + 1
SET @loop_counter = @loop_counter + 1
END
本章总结
在本章中，您学到了系统预存程序与使用者自订预存程序的使用方式，以及如何使用 T-SQL 程序代码、Enterprise Manager 和建立预存程序精灵，建立使用者自订的预存程序；此外，您还学到如何使用参数和变量，以及如何执行预存程序，并看到了用于更改、删除和检视预存过程定义的 T-SQL 陈述式。 第二十二章 将学习触发程序，触发程序是一种特殊的预存程序，可在特定条件下自动执行。
