資料庫中預存程序的自動化產生

原文出處：MSDN Magazine April 2003(Advanced T-SQL)

原代碼下載：StoredProcedures.exe (108KB)

本文假定你熟悉 T-SQL。
　

摘要

　　設計時的自動化加快了編碼，並保證所有過程都用相同的命名規範和結構來產生。為了在大型 SQL 項目中盡量提高編碼的效率，作者編寫了一系列的設計時 （design-time）預存程序，用它們來產生運行時（run-time）預存程序，並一直在工程中使用。最近，作者更新了其預存程序以便使用 SQL Server 2000 的特性， 其中包括使用者定義函數。本文所及內容涵蓋建立並執行這些動態 T-SQL 指令碼以使普通的資料庫預存程序編碼自動化。
　

　　以前，我們開發了一個相當大的n層的客戶/伺服器項目。在最初計劃期間，我們決定用一系列存取資料庫中大量表的方法。有四個基本的預存程序用來實現每 個表的 select、insert、uodate 和delete 操作。雖然所需的預存程序在設計上類似，每張表的唯一索引列的結構控制每個獨立過程的重要細節會不同，這 無疑會導致預存程序的編寫非常單調煩瑣。很顯然預存程序的編寫過程必須實現自動化。
　　 在任何給定的項目中，從實驗著手，建立一套核心的設計時預存程序，用它編寫資料庫表基本的運行時預存程序集。通過建立並使用這些設計時過程，我們不僅節省了大量時間，同時還為我們的客戶節省了金錢，此外還留下了一些我們至今仍在使用的好代碼。
　　這些設計時預存程序已經被更新，以便利用 SQL Server 2000 的一些新特性，尤其是使用者自訂函數（UDFs）特性。因此代碼變得非常模組化，並且我們還有 額外的函數來完成其它任務。
　　另一個設計時自動化的好處是保證所產生的數以百計的預存程序都有一致的結構和標準的命名規範。在我們的例子中，所有產生的運行時預存程序的名字被格式化為：prApp_TableName_Task，這裡Task 可以是 Select、Update 或者 Delete。用於 Customers 和 Orders 表的預存程序如下所示：

　　prApp_Customers_Delete
　　prApp_Customers_Insert
　　prApp_Customers_Select
　　prApp_Customers_Update
　　prApp_Orders_Delete
　　prApp_Orders_Insert
　　prApp_Orders_Select
　　prApp_Orders_Update

　　正如你看到的，這個規範添加大量的組織到資料庫中，使任何預存程序都容易定位並使每個過程的名字都是自描述的。開發人員可以快速發現它並建立代碼。最重要的是這個 項目未來的團隊成員將會發現這些代碼和預存程序很容易理解和上手。當然，如果你已經使用了一個不同的命名規範，那麼只需簡單地改變幾行代碼，你的命名規範便可被替換使用。
　　這四個設計時預存程序不是一成不變的，而是可以將它作為模版在其它項目中使用。將它們安裝到項目資料庫中，如果需要，可以修改它們適應特定應用程式的需要。例如，在 我們的幾個應用程式中，我們增加代碼在單獨的資料庫中維護每次記錄被修改的稽核記錄。 　　 　　　　　　　　 　　　　　　

一個簡單例子  　　　　　　　　 　　　　　　

    開始前，先看一個使用資料庫 Northwind 中 Order_Details 表的簡單例子（該表的表名 是被修改過的，用底線取代了空白字元）。儘管空格和其它字元在對象名字中被允許使用，但我們推薦使用常規分割符來命名物件，以防止在使用這些自動化預存程序時可能出現的問題，請參見 SQL Server 線上書籍中“Using Identifiers”部分來獲得更多資訊。
　　第一個任務是運行這個設計時預存程序，以建立修改 Order_Details 表資料的運行時預存程序：

　　EXEC pr__SYS_MakeUpdateRecordProc ''Order_Details''

　　運行這個設計時預存程序將產生如 Figure 1 所示的 T-SQL 指令碼。當這個 T-SQL 指令碼運行時，它為 Order_Details 表建立一個新的 update 預存程序。所有的列被說明為新預存程序的參數，但是注意當其 它非主鍵欄位是 update 命令的 SET 語句的一部分時，主鍵列（OrderID 和 ProductID）是如何在 WHERE 字句中出現的。設計時預存程序檢查 Order_Details 表格儲存體在 SQL Server 系統資料表中的中繼資料(metadata)，並用這個資訊來建立適當的輸出指令碼，運行後將建立最後的運行時預存程序。
　　運行後僅僅產生輸出，並且不產生新的運行時預存程序。然而，做個簡單的修改，設計時預存程序實際上能運行作為最後輸出結果的 T-SQL 指令碼。為此，我們只運行設計時預存程序，將值1作為 一個標誌位，用第二個可選擇參數傳遞，並且再次運行：

　　EXEC pr__SYS_MakeUpdateRecordProc ''Order_Details'', 1

　　這此不僅顯示以前那樣的輸出結果，而且運行這個輸出結果，由此建立運行時預存程序。

　　現在我們來看一下這個建立特定應用程式運行時預存程序的設計時預存程序的代碼。  　　　　　　　　 　　　　　　

SQL Server 系統資料表和視圖 　　　　 　　　　　　　　 　　　　　　

　　為了建立設計時預存程序，我們必須知道如何從 SQL Server 的系統資料表和資訊視圖中獲得表的定義。首先，我們必須找到這些列，並找出哪些是主鍵，每列支援哪些資料類型，以及列是否允許為空白。 

　　Figure 2 查看系統資料表 

　　 瞭解通過修改註冊伺服器的屬性，SQL Server 企業管理器能使你查看系統資料表是非常有用的，如 Figure 2 所示。如果你在企業管理器中按右鍵伺服器名字，並選擇“Edit SQL Server Registration properties”，將彈出一個對話方塊。在對話方塊的下面，你可以看到一個標有“Show system databases and system objects”的複選框。選中這個選項便開啟了系統物件檢視，也可以選擇關閉來使表的視圖看起來更簡單和更易讀。

解析表列 

　　Syscolumns 表提供了許多必須的中繼資料資訊，例如列名、ID、長度和是否允許空值。它還被用來串連 sysindexes 表來確定表的主鍵。同樣可以通過 INFORMATION_SCHEMA.COLUMNS 視圖擷取列的預設值。
　　既然所有的預存程序都使用相同的中繼資料資訊，那麼出於模組化和可維護性考慮，將其封裝在獨立的代碼塊中是件非常好的事情。SQL Server 的早期版本沒有UDF（使用者定義函數 ），使得模組化看起來很困難。但是 SQL Server 2000 具備了 UDF 特性，我們決定進一步採用該代碼並將四個設計時預存程序中的公用特性進行模組化。 建立五個新的 UDFs 來處理系統資料表和資訊大綱視圖，封裝所有取得的中繼資料。
　　毫無疑問，為了建立新的運行時預存程序，我們需要知道下面的關於表的中繼資料列資訊：  　　　　　　

• 列名
• 列的ID號
• 列的資料類型
• 列最大長度（包括字元和位元據）
• 列的精度，或者值的位元（decimal和numeric資料）
• 列的數值範圍，或者小數點後的位元（decimal和numeric資料）
• 列是否允許為null
• 列是否是主鍵的一部分
• 列是否是 Identity 列
• 列的預設值

　　這些資訊的大多數來自 syscolumns 表，只有兩個除外。預設值實際上來自INFORMATION_SCHEMA.COLUMNS 視圖。資料類型名（datatype name）從 systypes 表中吸取，並且通過一個更複雜的 syscolumns，sysindexes 和 sysindexkeys 表聯合來確定一個列是否是主鍵的一部分。它是如此的複雜， 以至於我們將該功能封裝到其自己的 UDF 中。
　　讓我們看一下 Figure 3 中的主要功能，它揭示了更多的中繼資料資訊。這個 UDF 不是太複雜。正如你看到的，大多數中繼資料資訊——除了一些簡單的列重新命名——被返回時未做任何修改，包括列名、列 ID、長度、精度、範圍、是否允許為空白和資料類型名字 。接下來需要對這些資訊做一點額外的工作。對於主鍵中繼資料，我們已建立另外一個 UDF 來確定表中一列是否是某個表主鍵的一部分。我們將馬上檢查這些額外的 UDFs 函數。 
　　讓我們看一下 alternate 類型和 identity 狀態。Syscolumns 狀態欄位的第8位（128）指示該列是否是一個 identity 列。 （這對於瞭解何時建立 Insert 和 Update 指令碼非常重要）。我們的簡單公式對這個值實施一個邏輯與 (&)，並將該結果封裝在 Sign 函數中。如果該位被設定，則意味該列是 identity 列。c.status & 128 將傳回值 128 。否則，將傳回值 0 。Sign 函數當為正值時返回1，負數時返回 -1，0 值時返回 0。因此，如果列被評估為 identity 列，將傳回值 1，否則返回0。
　　alternate 類型被用來表示該資料類型在定義時是否要求額外的資訊（長度、精度或範圍）。我們將 character 和 binary 資料類型作為值是 1 的 alternate 類型，decimals 和 numerics 作為2，其 它資料類型作為 0。這個值在預存程序用來確定長度、精度和範圍是否需要加入到參數定義中。

尋找主鍵列

　　正如你看到的，尋找列的資訊並不是很難。尋找一個欄位是否是主鍵的一部分稍微要費點力。有一個 欄位列表是能擷取的。但要找到這些欄位得在 syscolumns、sysindexes 和 sysindexkeys 表聯合中並與我們請求的列進行比較（在 @sColumnName 參數中被傳遞到 UDF）。因此，尋找主鍵的任務在單獨的使用者定義函數中較容易完成，因為我們可以將這個工作封裝到某個單一的函數調用中。 

讓我們考察一下這個函數，看看所發生的真相：

CREATE FUNCTION dbo.fnIsColumnPrimaryKey
    (@sTableName varchar(128), @sColumnName varchar(128))
RETURNS bit
AS
BEGIN
    DECLARE    @nTableID int,
               @nIndexID int,
               @i int

    SET @nTableID = OBJECT_ID(@sTableName)

　　這個函數包含兩個參數，表名和列名，如果指定列是表中的主鍵的一部分，函數將返回一個 bit 標誌。我們接著聲明一些預存程序中要用到的變數，並且賦予初始值。現在來到有趣的部分：尋找主鍵資訊。我們開始為表的主鍵索引找到索引ID，如下面的代碼所示：

SELECT @nIndexID = indid
FROM      sysindexes
WHERE  id = @nTableID
 AND      indid BETWEEN 1 And 254 
 AND      (status & 2048) = 2048
ORDER BY indid

IF (@nIndexID  Is Null)
    RETURN 0

現在將這個表的主鍵索引的索引 ID 賦予變數 @nIndexID。狀態列的第12位（2048）指示是否是主鍵索引。如果不是主鍵，則沒有記錄被返回，並將 @nIndexID 設定為空白值。如果退出函數時@nIndexID 包含一個空值，返回0值。換句話說，如果沒有主鍵索引，那麼列就不是主鍵的一部分。現在我們再次在主鍵索引列的列表中檢查要求的列 (@sColumnName)。

    IF @ColumnName IN
        (SELECT sc.[name]
          FROM       sysindexkeys sik
          INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid =
                     sc.colid
          WHERE      sik.id = @nTableID
          AND        sik.indid = @nIndexID        )
     BEGIN
        RETURN 1
     END

    RETURN 0
END

　　用前面獲得的 IndexID，我們從 syscolumns 和 sysindexkeys 的聯合中獲得列名。這些表通過列 ID 和物件識別碼 進行聯合。通過 WHERE 從句設定檢索條件，因此我們可以只選擇要求的表的索引列（sik.id = @nTableID），並且僅僅選擇主鍵索引（sik.indid = @nIndexID）。如果 @sColumnName 在返回的列的列表中，傳回值1，否則，傳回值0，標識匹配沒有找到。 

列的預設值 

　　當某條記錄被插入到表中，如果不給某一指定列提供值，同時該列有一預設值的話，則該預設值將作為該列的值。由於新產生的表插入預存程序有一個參數是用於所有可能被插入 的列，同時變數必須包括一個值，即使是一個空值，該表的預設值不會被使用。本質上，通過明確為每一列提供值（即使是 NULL），我們將改寫列的預設值。為了中和我們所建立的預存程序這個特性，我們 必須在插入資料時提供預設值。在本文後面我們將看到如何在自動化預存程序中使用預設值。但是現在，讓我們首先來考察如何獲得那些預設值。
　　我們將使用的 UDF 只是簡單地引用了 INFORMATION_SCHEMA.COLUMNS 視圖，它提供某列的預設值。與使用 sysconstraints 系統資料表 相比，用這個視圖來擷取預設值更容易。下一個 UDF 通過將預設值的尋找邏輯封裝到一個簡單的函數調用中來簡化這個過程。

CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128), 
                                    @sColumnName varchar(128))
RETURNS varchar(4000)
AS
BEGIN
    DECLARE @sDefaultValue varchar(4000)

    SELECT  @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
      FROM      INFORMATION_SCHEMA.COLUMNS
      WHERE     TABLE_NAME = @sTableName
      AND       COLUMN_NAME = @sColumnName

    RETURN  @sDefaultValue

END

　　列的預設值儲存在一對圓括弧中，但我們不需要。所以如你所看到的，我們將 COLUMN_DEFAULT  欄位傳遞給另外一個函數 fnCleanDefaultValue，它將園括弧 剝離掉，然後返回實際的預設值。
　　例如，如果一個叫 nQty 的列有一個預設值 1，COLUMN_DEFAULT 值將當然包括（1）。如果預設值是“Enter Text Here”,我們就得到（“Enter Text Here”）。這裡是這個 UDF 的原始碼：

CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
    RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END

　　現在我們得到了所有建立自動化預存程序所需的中繼資料資訊。

動態執行T-SQL

　　動態 T-SQL 的執行是我們的預存程序的精華所在，它允許你寫一個通用的 T-SQL 指令碼，按次序產生 T-SQL 指令碼。正是 T-SQL 的 EXECUTE 命令允許通用的 T-SQL 指令碼具體執行專門的輸出，並建立將被應用程式使用的運行時預存程序。
　　EXECUTE 或者 EXEC 實際上有兩種功能：它可以執行一個存在的預存程序和動態執行一個儲存在一個字串中的 SQL 命令。正是後一個功能，我們將使用取得的中繼資料的聯合來自動建立這些預存程序。一個 該過程的簡化視圖將用需要建立該預存程序的預存程序代碼（使用中繼資料）填充一個大的 varchar 變數，然後一次性動態執行這個 varchar 變數的內容，建立新的預存程序。
讓我們著手測試一個動態 T-SQL 的簡單例子：

CREATE PROC prGetAuthor
    @au_id char(11)
AS
DECLARE @sExec varchar(8000)
SET @sExec = ''SELECT * FROM authors WHERE au_id = '''''' + @au_id + ''''''''

EXEC (@sExec)

　　在這個例子中，我們傳入 author 的 ID 並將它串連到一個從 author 表擷取某個 author 的 SELECT 語句。 

我們象下面這樣調用這個預存程序：

　　EXEC prGetAuthor ''123-45-6789''

prGetAuthor 預存程序將建立一個如下的 SQL 陳述式：

　　SELECT * FROM authors WHERE au_id = ''123-45-6789''

　　這個語句將在 EXEC 中執行並返回 ID 為 123-45-6789 的 author。正如你看到的，設計時預存程序將在很高的水平上使用這個特徵。 
　　但是我們應注意到這不是動態 T-SQL 推薦的用法。任何時候動態 T-SQL 代碼對外部世界是可獲得的，這樣就存在 SQL 攻擊的可能性。我們僅僅使用動態 T-SQ L來實現管理和 任務目的，決不會在除系統人員和管理員之外的任何人可存取的任何預存程序中暴露這個功能。

建立預存程序 

　　建立這些設計時預存程序的第一步是相當的標準。 定義預存程序，聲明變數，變數初始化。在建立其它預存程序之前，快速探索這些代碼是否有可疑之處。我們建立兩個特別的字串變數，一個儲存 TAB 字元，另一個儲存斷行符號分行符號。這些 都可用 UDFs 來建立，但是我們決定不這樣做，以便作為練習留給讀者來完成。它們被用來輔助代碼輸出的格式化。讓我們看一下這個過程的開始，如 Figure 4 所示。
　　再次，這裡並沒有什麼 T-SQL 新發現。我們首先檢查表是否有主鍵。這將防止我們的代碼建立具有潛在危險的運行時預存程序。接著設定一些變數和預設值。預存程序首先 為新過程建立 DROP 語句，避免預存程序已經存在，再建立一些注釋，建立實際的預存程序定義（見 Figure 1 的前面幾行）。你可以修改這些代碼來建立還不存在的運行時預存程序（如果存在則什麼都不做）。這個新特性將由第三個選項參數 @bIfExistsDoNothing 設定。我們將作為一個簡單的練習留給讀者。 
　　下一個程式碼片段開始建立動態 T-SQL 的過程。為了新預存程序的定義（見 Figure 5 ）， 添加刪掉某個已存在的預存程序及定義新的預存程序的代碼。注意如何使用第二個參數（可選） @bExecute 來確定我們是否要實際運行代碼。在我們的自動化預存程序的定義中，這個參數是可選的，預設值為0，意味著並不實際執行代碼。 
　　下一步我們將使用一個有趣的特性。我們使用 fnTableColumnInfo 使用者定義函數作為遊標的中繼資料，fnTableColumnInfo 是一個返回 table 值的函數。這個函數用來取代複雜的 T-SQL，在四個自動化預存程序中，僅僅在遊標的聲明中引用這個 UDF。聲明遊標後，我們接著開啟它並擷取第一條記錄，放在一個包含中繼資料資訊的變數中，我們就可以使用它來建立我們的新的預存程序（見 Figure 6 ）。 
　　當然，我們使用 WHILE 語句來建立一個迴圈，一直到取得所有的值（@@FETCH_STATUS = 0）。現在我們準備來解析列的資訊，並為新預存程序建立關鍵語句段。 
　　在下一個代碼例子中，我們使用遊標進行迴圈，並使用列的中繼資料資訊建立代碼。你將注意到有三個變數被修改：@sKeyFields, @sSetClause 和 @sWhereClause。第一個用來為預存程序建立參數列表（包括在 Figure 1 中的 CREATE PRDC 段）。第二個用來設定 Figure 1 中 UPDATE 命令的 SET 語句。最後一個變數用來設定 Figure 1 最後的 WHERE 子句。現在我們來驗證代碼的第一部分（見 Figure 7 ）。 
　　Figure 7 包括了為新的預存程序建立參數列表的代碼。第一個IF語句檢查是否準備好了將資料加入到變數中。如果已準備好，我們加入一個逗號和一個斷行符號/換行。我們必 須正確地結束參數列表中的每一個參數。如果沒有檢查，我們將會以一個或更多逗號結束。在下一列前加入逗號，就可以防止了這個錯誤。 
　　下一步，我們加入一個字元和中繼資料資訊的串聯,包括一個TAB字元，一個@字元，列的名字，一個空格和列類型的名字。接著我們看是否需要資料類型的其它資訊，檢查是否需要精度、範圍、長度等資訊。如果需要，我們另外加入這些在 圓括弧中的值（如 T-SQL 文法的需要）。 
　　最後，如果該列不是一個 identity 列，並且該列允許空值或是一個時間戳記（不允許被更新因為它是被直接自動更新的），接著我們在參數定義中加入"= NULL"。例如，資料庫 pubs 中 discounts 表的列是這樣的：

　　discounttype varchar(40),
　　stor_id char(4) = NULL,
　　lowqty smallint = NULL,
　　highqty smallint = NULL,
　　discount decimal(4, 2)

　　注意 discounts 表沒有主鍵，將不允許自動產生代碼。這些預存程序依賴於主鍵來確定資料如何被更新。如果沒有主鍵，這個自動化預存程序應當修改，在新的預存程序的 WHERE 語句中使用所有的列，或者尋找一個唯一索引列 給 WHERE 子句使用。換句話說，如果可能，所有表應當有主鍵，這是資料庫設計的基本原則。 
　　下一步，看一下為新預存程序的 UPDATE 命令建立的 SET 語句的代碼（見 Figure 8）。注意我們如何處理不是主鍵的列。再次，如果你想更新所有的列，包括主鍵中的列，你可以簡單地刪除IF語句。注意這個IF可以是選項特性，由另外一個參數設定。在最後部分，如果需要 的話，我們為變數加入一個逗號。在這個例子中，如果沒有資料（意味著我們還沒有加入任何列），我們就在變數中設定SET語句來關閉。
　　下一步，我們加入一個TAB字元，需要資料更新的列名，和一個等號（＝）。在 Order_Details 表中，應由如下代碼結束：

　　　SET　UnitPrice = @UnitPrice,
　　　　　 Quantity = @Quantity,
　　　　　 Discount = @Discount

　　下一步，我們建立為新預存程序建立的 WHERE 語句。你將注意到程式碼片段有一個 ELSE 語句。這是主鍵檢查的例外狀態，表示這列是主鍵的一部分,僅僅運行了這段代碼（見 Figure 9）。 
　　再次，或者由 WHERE 子句開始這個變數，或者加入一個 AND 子句，這決定於它是否是 WHERE 字句的第一項。接著，我們加入一個TAB字元，列名，字串"=@"和列名。Order_Details 例子的結果如下面所示：

　　WHERE OrderID = @OrderID
　　　　 AND ProductID = @ProductID

　　在結束 WHILE 迴圈前，我們需要從遊標中擷取下一行，並再次將中繼資料值放在變數中。一旦迴圈結束，我們關閉和回收遊標。現在我們就可以輸出任意建立的新運行時預存程序的資訊（見 Figure 10）
　　最後，設計時預存程序將輸出產生新運行時預存程序的 T-SQL，首先加入一個斷行符號分行符號給SET語句（純屬于格式輸出目的）。下一步，我們加入關鍵字段（預存程序參數）和關鍵字AS（預存程序定義要求）。 接著，UPDATE 和將被更新的表的名字被加入。最後，我們加入SET語句變數和 WHERE 語句變數，結束預存程序的定義。注意，@sProcText 變數包括為新的運行時預存程序的 T-SQL，可以選擇執行。如果被執行，運行時預存程序將被加入到資料庫中。

結論

　　上述僅僅是我們開發的四個自動化預存程序的一個。當然，每個預存程序將根據需要變化。例如，建立運行時 delete 預存程序的設計時預存程序僅僅使用了每個表的主鍵。所有使用者定義函數和預存程序可以通過本文前面的連結進行下載。
　　這些預存程序可以加入許多其它的特性，一些是我們已經提到的，如使用相同的對象名字，對象存在的驗證，稽核索引的建立，以及預存程序存在時改變聲明 （維護預存程序和混合 XML 則更有效）。你也可以在一個表中建立另外的設定來對代碼產生提供協助。換句話說，這些預存程序可以作為其 它自動代碼產生任務的起點。在其它的例子中，這些代碼將協助你節省大量的時間和精力，甚至能協助你探索其它感興趣的 T-SQL 技術。

相關文章 

.NET Reflection: Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET Metadata and Reflection 

SQL and XML: Use XML to Invoke and Return Stored Procedures Over the Web 
Serving the Web: Stored Procedure Wizard in Visual Basic Boosts Productivity 

背景資料
　　　　　　　　
《Inside Microsoft SQL Server 2000》，作者： Kalen Delaney (Microsoft Press, 2000) 
《The Guru''s Guide to SQL Server Stored Procedures, XML, and HTML》，作者：Ken Henderson (Addison-Wesley Professional, 2001)

作者介紹

　　Peter W. DeBetta：是 一位 Wintellect 培訓人員。他使用 Visual Basic、ASP、.NET Framework 和 SQL Server 解決和開發企業級軟體。他還和別人合著了一些書籍，包括《including SQL Server 7.0 Programming Unleashed 》(SAMS, 1999)。

　　J. Byer Hill：有自己的公司 Advanced Software Logic。Byer 有11年使用微軟技術如 Visual 　　　　　　Basic、Visual C++、COM+、ADO、ASP、DHTML 和 SQL Serve 編寫和設計資料庫的經驗。他主要的工作重心是應用系統的構造、設計和高效代碼重用。

譯者簡介
　　　　　　　　
肖進：南京中萃食品有限公司資訊部，軟體工程師。