Microsoft SQL Server 2000 超級管理手冊(二一)

最新推荐文章于 2024-07-30 11:20:44 发布

gzwl

最新推荐文章于 2024-07-30 11:20:44 发布

阅读量1.2k

点赞数

分类专栏： Database 文章标签： sql server microsoft manager go output sql

本文链接：https://blog.csdn.net/gzwl/article/details/123817

版权

Database 专栏收录该内容

39 篇文章 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預存程序。

- 在 Enterprise Manager 展開左側視窗中 MyDB 資料庫，選擇 預存程序 資料夾以刪除預存程序，所有的預存程序都顯示在右側窗格，在InsertRows預存程序（本章稍早已建立，所以應該已經存在）上右點滑鼠，然後從快顯功能表中選擇刪除（也可以從快顯功能表重新命名或複製該預存程序），會出現如圖 21-1 所示的 卸除物件 對話方塊，按 全部卸除 以刪除預存程序。
  　
  
  圖21-1　「卸除物件」對話方塊
- 在 預存程序 上右點滑鼠，從快顯功能表中選擇 新增預存程序 ，出現的視窗如圖 21-2 所示。
  　
  
  圖21-2　「預存程序屬性」視窗
- 在一般標籤頁的文字方塊中，使用預存程序的名稱來取代 [OWNER].[PROCEDURE NAME]，在本例中，即為InsertRows，然後為預存程序輸入 T-SQL。圖 21-3 顯示了使用 T-SQL 程式碼新增InsertRows後的預存程序屬性視窗。
  　
  
  圖21-3　用 T-SQL 程式碼增加新的預存程序
- 按一下 檢查語法 按鈕，讓 SQL Server 指出預存程序中的 T-SQL 語法錯誤，修正找到的語法錯誤，並反覆按 語法檢查 直到語法檢查成功（如圖 21-4 所示），按一下確定。
  　
  
  圖21-4　顯示語法檢查成功視窗
- 在 預存程序屬性 對話方塊中按確定以建立您的預存程序，建立完後，返回 Enterprise Manager，在左側窗格中選擇 預存程序 資料夾，便可在右側窗格中看到新建立的預存程序，如圖21-5所示。
  　
  
  圖21-5　在 Enterprise Manage 新建的的預存程序
- 要為新的預存程序指派使用者的執行權限，請在右邊窗格的預存程序名稱上右點滑鼠，在快顯功能表中選擇屬性，出現 預存程序屬性 視窗後，按一下權限呼叫 物件屬性 視窗（如圖 21-6 所示），在 EXEC 核取方塊中選擇是否允許該使用者或資料庫角色執行此預存程序。本範例中，我們將InsertRows預存程序的使用權限開放給三個使用者。
  　
  
  圖21-6　「物件屬性」視窗的「權限」標籤頁
- 按一下 套用／確定 以回到 預存程序屬性 視窗，按一下確定結束。

您可以編輯已經存在的預存程序，方法為：在程序名稱上按右鈕，從快顯功能表中選擇屬性，在 預存程序屬性 視窗中編輯程序（同圖 21-3 的對話方塊），使用 檢查語法 按鈕檢查語法，確定正確無誤後，按一下套用，然後按一下確定。

您還可以利用 Enterprise Manager 管理已經存在的預存程序權限，方法為：在 Enterprise Manager 中的預存程序名稱上右點滑鼠，從快顯功能表中選擇 所有工作 ，然後選擇 管理使用權限 。在 所有工作 中，還可以選擇對該預存程序 建立新的發行集 （將在本書第二十六章中討論）、產生 SQL 指令碼以及顯示相依性。假設選擇了 產生 SQL 指令碼 ，SQL Server 會自動建立一個指令碼檔案（由您指定指令碼檔案名稱），該指令碼會包括預存程序的定義。當您想重新建立一個預存程序，就可以使用該指令檔。

使用建立預存程序精靈
　

第三種方法是使用 建立預存程序精靈 ，該精靈提供了建立預存程序所需撰寫的 T-SQL 程式碼基本結構，可以幫助您插入、刪除或更新資料列至資料表，但無法執行從資料表中尋找資料列的程序。

精靈允許在一個資料庫中建立多個預存程序，而不需退出精靈和重新啟動。但是，若要在另一個資料庫中建立預存程序，就必須再次執行精靈。執行精靈時，請依照下面的步驟：

- 在 Enterprise Manager，從工具列中選擇 工具／精靈 以呼叫 選擇精靈 對話方塊，在 資料庫 中選擇 建立預存程序精靈 ，如圖 21-7 所示。
  　
  
  圖21-7　「選擇精靈」對話方塊
- 選擇確定叫出 建立預存程序精靈 的畫面，如圖 21-8 所示。
  　
  
  圖21-8　「建立預存程序精靈」歡迎畫面
- 選擇 下一步 顯示 選取資料庫 畫面，在 資料庫名稱 選擇欲建立預存程序的資料庫。
- 選擇 下一步 進入 選取預存程序 畫面（如圖 21-9 所示），在這個視窗中，可以看到在這個資料庫所建立的所有資料表名稱，並有三個包含核取方塊的資料行，分別代表使用精靈所能建立的三種預存程序：插入、刪除及更新資料，請選擇合適的核取方塊。
  　
  
  圖21-9　「選擇預存程序」畫面
  
  這個範例顯示了本書所使用的兩個資料表，其中Bicycle_Inventory資料表被指派了兩個預存程序：一個插入程序和一個更新程序。如後續的步驟所示，在實際建立這些程序之前，您可以加以修改。
  
  說明
  
  當然，一個預存程序可以執行多種類型的資料修改，但是 建立預存程序精靈 視每一個修改類型為獨立的預存程序。您可以將精靈所建立的任何預存程序，用 T-SQL 程式碼變更。
- 按 下一步 顯示 完成建立預存程序精靈 ，如圖 21-10 所示，這個畫面列出所建的預存程序精靈名稱及描述。
  　
  
  圖21-10　「完成建立預存程序精靈」畫面
- 欲重新命名或編輯預存程序，在 完成建立預存程序精靈 畫面中按一下編輯，進入 編輯預存程序屬性 視窗，如圖 21-11 所示，這個畫面列出此預存程序會影響的資料行，在選取資料行中勾選的資料行名稱將會被預存程式所使用。
  　
  
  圖21-11　「編輯預存程序屬性」視窗
  
  這個範例顯示中在Bicycle_Inventory資料表中，有六個資料行會受到插入預存程序所影響，此插入預存程序已命名為insert_Bicycle_Inventory_1，每個資料行在選取欄裡都有勾選核取方塊，代表六個資料行的值在預存程序執行時都需要輸入，且六個資料行都會被預存程序插入值至該資料表中。
- 要重新命名預存程序，請清除名稱編輯方塊中的名稱，並用新的名稱替代。
- 要編輯預存程序，請按一下 編輯 SQL 來顯示 編輯預存程序 SQL 對話方塊，如圖 21-12 所示，在這個對話方塊中，可以檢視預存程序的 T-SQL 程式碼，這裡的 T-SQL 非常的基本。在這個範例中，當呼叫預存程序時，這裡所列出的五個參數將會插入成為新資料列的值。要編輯該程式碼，只要在文字方塊中輸入修改設定。完成編輯後，按一下剖析檢查語法錯誤，修正錯誤，然後按一下確定返回 完成建立預存程序精靈 畫面。
  　
  
  圖21-12　「編輯預存程序 SQL」對話方塊
- 確定所有預存程序的程式碼無誤後，按一下完成建立預存程序。建立完成後，別忘了為每個預存程序設定權限。（請參閱本章稍早用於介紹設定權限的〈使用 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 陳述式。第二十二章將學習觸發程序，觸發程序是一種特殊的預存程序，可在特定條件下自動執行。