|
9.2.3 存儲過程
存儲過程的使用是Command對象得到應用的一個領域。存儲過程（有時也稱存儲查詢）是存儲在數據庫中預先定義的SQL查詢語句。
為什么應該創建和使用存儲過程而不是在代碼中直接使用SQL字符串呢？主要有以下幾個理由：
· 存儲過程被數據庫編譯過。這樣可以產生一個“執行計劃”，因此數據庫確切地知道它將做什么，從而加快了過程的執行速度。
· 存儲過程通常被數據庫高速緩存，這樣使它們運行得更快，因為此時不需要從磁盤中讀取它們。并非所有的數據庫都支持這種緩存機制，比如微軟的Access就不支持，而SQL Server卻支持。
· 通過指定數據庫中的表只能被存儲過程修改，可以確保數據更安全。這意味著具有潛在危險的SQL操作不會執行。
· 可以避免將ASP代碼和冗長的SQL語句混在一起，從而使ASP代碼更易于維護。
· 可以將所有SQL代碼集中存放于服務器。
· 可以在存儲過程中使用輸出參數，允許返回記錄集或其他的值。
一般說來，存儲過程幾乎總是比相當的SQL語句執行速度快。
為了使用存儲過程，只要將存儲過程的名字作為命令文本，并設置相應的類型。例如，考慮前面更新書價的例子。如果在SQL Server上創建一個存儲過程，可以編寫代碼：
CREATE PROCEDURE usp_UpdatePrices
AS
UPDATE Titles
SET Price = Price * 1.10
WHERE TYPE='Business'
對于微軟的Access數據庫，可以使用一個簡單的更新查詢語句完成相同的任務，如圖9-1所示：
圖9-1 使用微軟的Access數據庫完成更新查詢
要在ASP網頁中運行該存儲過程，只需要使用以下代碼：
Set cmdUpdate = Server.CreateObject("ADODB.Command")
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = "usp_UpdatePrices"
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.Execute , , adExecuteNoRecords
這只是運行存儲過程。沒有記錄集返回，因為只是在更新數據。需要記住的是，除非確實需要，不要創建記錄集。
雖然這樣做也可以，但并不是很靈活，因為僅僅處理一種類型的書。更好的做法是創建一個允許我們選擇書類型的過程，這樣就不必為每類書創建一個過程。同樣也可去掉固定的10%更新，這樣使得靈活性更好。那么，如何才能做到這一點呢，很簡單，使用參數。
1． 參數
存儲過程的參數（或變量）與一般的過程和函數的參數一樣，可以傳到函數內部，然后函數可以使用它的值。SQL Server（其他數據庫也一樣，包括Access）中的存儲過程都具有這樣的功能。
為了使存儲過程能處理多種類型的書，甚至允許用戶指定價格的增加（或減少），需要增加一些參數：
CREATE PROCEDURE usp_UpdatePrices
@Type Char(12),
@Percent Money
AS
UPDATE Titles
SET Price = Price * (1 + @Percent / 100)
WHERE Type = @Type
現在，存儲過程usp_UpdatePrices帶有兩個參數：
· 一個是書的類型（@Type）。
· 一個是書價變化的百分比（@Percent）。
與VBScript的函數一樣，這些參數都是變量。然而，與VBScript和其他腳本語言不同的是：在這些腳本語言中的變量都是variant類型，而SQL變量具有確定的類型(char、Money等等)。必須遵守SQL變量的命名規范，即變量必須以符號@開始。
注意，我們讓百分數作為一個整數（如10代表10%），而不是作為一個分數值傳入此過程。這只是讓存儲過程變得更直觀一些。
2． Parameters集合
那么，現在有了帶參數的存儲過程，但如何通過ADO來調用它呢？我們已經見到了如何用Command對象調用不帶參數的存儲過程，實際上，它們之間并沒有什么不同。不同之處在于Parameters集合的使用。
Parameters集合包含存儲過程中每個參數的Parameter對象。然而，ADO并不會自動地知道這些參數是什么，因此，必須用CreateParameter方法創建它們，采用下面的形式：
Set Parameter = Command.CreateParameter (Name, [Type], [Direction], [Size], [Value])
參數及說明如表9-3所示：
表9-3 CreateParameter方法的參數及說明
參 數
說 明
Name
參數名。這是Parameters集合中的參數名，不是存儲過程中的參數名。然而，使用相同的名字是一個好的做法
Type
參數的數據類型。可以是一個adDataType常數，詳見附錄
Direction
參數的方向，指明是參數向存儲過程提供信息，還是存儲過程向ADO返回信息。可以是下面的值之一：
adParamInput，參數是傳給存儲過程的輸入參數
adParamOutput，參數是從存儲過程檢索出的輸出參數
adParamInputOutput，參數可同時作為輸入和輸出參數
adParamReturnValue，該參數包含存儲過程返回的狀態
Size
參數長度。對于固定長度的類型，比如整型，該值可以忽略
Value
參數的值
一旦創建了參數就可以將其追加到Parameters集合中，例如：
Set parValue = cmdUpdate.CreateParameter("@Type", adVarWChar, adParamInput, _
12, "Business")
cmdUpdate.Parameters.Append parValue
Set parValue = cmdUpdate.CreateParameter("@Percent", adCurrency, _
adParamInput, , 10)
cmdUpdate.Parameters.Append parValue
沒有必要顯式地創建一個對象去保存參數，缺省的Variant類型已經可以工作得相當好。如果不想創建一個變量，也可以走捷徑，例如下面的代碼：
cmdUpdate.Parameters.Append = _
cmdUpdate.CreateParameter("@Percent", adCurrency, adParamInput, , 10)
這使用CreateParameter方法返回一個Parameter對象，并用Append方法接收它。這種方法比使用變量運行得快，卻加長了代碼行，可讀性比較差。可以根據自己的愛好選擇其中一種方法。
參數加到Parameters集合后，就保留在其中，因此，不一定在創建參數時就為每個參數賦值。可以在命令運行前的任何時候設置參數的值。例如：
cmdUpdate.Parameters.Append = _
cmdUpdate.CreateParameter("@Percent", adCurrency, adParamInput)
cmdUpdate.Parameters("@Percent") = 10
前一章提到了訪問集合中的值有好幾種方法，Parameters集合并沒有什么不同。上面的例子使用參數的名字在集合中檢索參數，也可以使用索引號進行檢索：
cmdUpdate.Parameters(0) = 10
以上代碼對參數集合中第一個（Parameters集合從0開始編號）參數進行了賦值。使用索引號比使用名字索引速度快，但很顯然使用名字使代碼更易讀。
重點注意Parameters集合中參數的順序必須與存儲過程中參數的順序相一致。
運行帶參數的命令
一旦加入參數，就可立即運行命令，同時這些參數的值傳入存儲過程。現在可用一個友好的網頁去更新用戶選擇的類型的書價。例如，假設有一個名為UpdatePrices.asp的網頁，其運行時的界面如圖9-2所示：
圖9-2 UpdatePrices.asp網頁運行時的界面
通過數據庫中獲取書類型的列表，可以很輕松地動態創建該頁面。首先要做的是包含文件Connection.asp，該文件包含了連接字符串（保存在變量strConn中）以及對ADO常數的引用，這在前面的章節已經討論過。
<!-- #INCLUDE FILE="../Include/Connection.asp" -->
接下來，可以創建一個窗體（在這兒不顯示大量文本，僅僅用一個樣本文件）。該窗體調用一個名為StoreProcedure.asp的文件。
<FORM NAME="UpdatePrices" Method="post" ACTION="StoredProcedure.asp">
<TABLE>
<TR>
<TD>Book Type:</TD>
<TD><SELECT NAME="lstTypes"></TD>
現在開始編寫ASP腳本從title表中讀取書的類型。使用一個SQL字符串只返回唯一的書類型，然后將返回值放到HTML的OPTION標記中：
<%
Dim recTypes
Dim sDQ
sDQ = Chr(34) ' double quote character
Set recTypes = Server.CreateObject("ADODB.Recordset")
recTypes.Open "usp_BookTypes", strConn
While Not recTypes.EOF
Response.Write "<OPTION VALUE=" & sDQ & recTypes("type") & sDQ & _
">" & recTypes("type")
recTypes.MoveNext
Wend
recTypes.Close
Set recTypes = Nothing
%>
顯示書的類型后，接著可以構建窗體的其他部分，包括一個允許用戶輸入書價變化百分數的文本框。
</SELECT>
</TD>
</TR>
<TR>
<TD>Percent Value</TD>
<TD><INPUT NAME="txtPercent" TYPE="TEXT"></TD>
</TR>
</TABLE>
<P>
<INPUT TYPE="submit" VALUE="Run Query">
</FORM>
現在看一下Run Query按鈕調用的ASP文件StoredProcedure.asp。首先，聲明變量并從調用窗體取出書的類型和百分數。
Dim cmdUpdate
Dim lRecs
Dim sType
Dim cPercent
' Get the form values
sType = Request.Form("lstTypes")
cPercent = Request.Form("txtPercent")
現在可以向用戶顯示一些確認信息，告訴他們將發生什么。
' Tell the user what's being done
Response.Write "Updating all books"
If sType <> "all" Then
Response.Write " of type <B>" & sType & "</B>"
End If
Response.Write " by " & cPercent & "%<P>"
現在重新回到代碼內部，在此創建Command對象和參數。
Set cmdUpdate = Server.CreateObject("ADODB.Command")
With cmdUpdate
.ActiveConnection = strConn
.CommandText = "usp_UpdatePrices"
.CommandType = adCmdStoredProc
利用從前面網頁的窗體中提取的數據值，使用快捷方法創建和增加參數。
' Add the parameters
.Parameters.Append .CreateParameter ("@Type", adVarWChar, adParamInput, _
12, sType)
.Parameters.Append .CreateParameter ("@Percent", adCurrency, _
adParamInput, , cPercent)
現在，運行存儲過程。
' Execute the command
.Execute lRecs, , adExecuteNoRecords
End With
為了確認，可以告訴用戶已經更新多少條記錄。
' And finally tell the user what's happened
Response.Write "Procedure complete. " & lRecs & " were updated."
Set cmdUpdate = Nothing
%>
這樣就有了兩個簡單界面。前者創建了一個供選擇的項目列表，后者使用其中某個項目值更新數據。這是許多需要顯示和更新數據的ASP頁面的基礎。
3． 傳遞數組參數
Parameters參數集合一般來說比較好用，但有時稍有麻煩（尤其對于新手）。好在有一種快捷方法，使用Execute方法的Parameters參數。例如，調用存儲過程usp_UpdatePrices，但不使用Parameters集合。
創建一個Command對象，并同前面一樣設置其屬性。
' Set cmdUpdate = Server.CreateObject("ADODB.Command")
' Set the properties of the command
With cmdUpdate
.ActiveConnection = strConn
.commandText = "usp_UpdatePrices"
.commandType = adCmdStroreProc
但這里正是差異所在。我們僅是通過Execute方法傳遞參數給存儲過程，而不是創建參數并添加到集合中。
' Execute the command
.Execute lngRecs, Array(strType, curPercent), adExecuteNoRecords
End With
這里使用了Array函數，將單個變量轉換為數組，以適于方法調用。這種方法當然也有缺點：
· 只能使用輸入參數。因為不能指定參數的類型和傳遞方向，而缺省為輸入參數。
· 如果要多次調用存儲過程，這種方法速度就比較慢，因為ADO將向數據存儲詢問參數的內容及數據類型。
集合方法和數組方法之間在速度上的差異非常之小，幾乎可以忽略。所以，如果只有輸入參數，可隨便使用哪一種。實際上，人們更喜歡使用Parameters集合的方法，盡管它稍為繁瑣，但是使參數的屬性更加明確。
4． 輸出參數
我們已經知道如何獲得受命令影響的記錄數，如果需要更多信息，卻又不想返回一個記錄集，怎么辦？也許想從存儲過程中返回兩個或三個值，但又不想費心創建一個記錄集。在這時，可以定義一個輸出參數，其值由存儲過程提供。
例如，對于更新書價的程序，如果想在更新之后找出最高價格，可將存儲過程改成：
CREATE PROCEDURE usp_UpdatePricesMax
@Type Char(12),
@Percent Money,
@Max Money OUTPUT
AS
BEGIN
UPDATE Titles
SET Price = Price * (1 + @Percent / 100)
WHERE Type = @Type
SELECT @Max = MAX(Price)
FROM Titles
END
這只是在執行更新后運行了一個簡單的SELECT語句，并將值賦給輸出參數。
現在可以改寫StroreProcedure.asp的代碼從而獲取變量@MAX的值。
<%
Dim cmdUpdate
Dim lngRecs
Dim strType
Dim curPercent
Dim curMax
' Get the form values
strType = Request.Form("lstTypes")
curPercent = Request.Form("txtPercent")
' Tell the user what's being done
Response.Write "Updating all books" & " of type <B>" & strType & "</B>" & _
" by " & curPercent & "%<P>"
Set cmdUpdate = Server.CreateObject("ADODB.Command")
' Set the properties of the command
With cmdUpdate
.ActiveConnection = strConn
.CommandText = "usp_UpdatePricesMax"
.CommandType = adCmdStoredProc
我們只是在集合中加入了另一個參數，但這次指定為輸出參數。注意它并沒有賦值，因為其值將由存儲過程提供，記住這是一個輸出參數。
' Add the parameters
.Parameters.Append .CreateParameter("@Type", adVarWChar, adParamInput, _
12, strType)
.Parameters.Append .CreateParameter("@Percent", adCurrency, _
adParamInput, , curPercent)
.Parameters.Append.CreateParameter("@Max", adCurrency, adParamOutput)
' Execute the command
.Execute lngRecs, , adExecuteNoRecords
一旦執行這個過程，就可從集合中取得該值。
' Extract the output parameter, which the stored
' procedure has supplied to the parameters collection
curMax = .Parameters("@Max")
End With
' And finally tell the user what's happened
Response.Write "Procedure complete. " & lngRecs & _
" records were updated.<P>"
Response.Write "The highest price book is now " & _
FormatCurrency(curMax)
Set cmdUpdate = Nothing
%>
如果有不止一個輸出參數，可用相同的方法訪問。可以使用參數名或索引號取出集合中的值。
|
