《MS SQL Server 2000管理员手册》系列——21. 建立及管理预存程序

最新推荐文章于 2024-09-12 13:57:55 发布

天地客人

最新推荐文章于 2024-09-12 13:57:55 发布

阅读量1.4k

点赞数

分类专栏： SQLServer2000概述文章标签： sql server manager go 数据库 output 服务器

本文链接：https://blog.csdn.net/softj/article/details/566405

版权

SQLServer2000概述专栏收录该内容

50 篇文章 0 订阅

订阅专栏

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 陈述式。第二十二章将学习触发程序，触发程序是一种特殊的预存程序，可在特定条件下自动执行。