ASP 3.0高級(jí)編程（四十一）

發(fā)布時(shí)間：2008-07-09 閱讀數(shù)：次來(lái)源：網(wǎng)樂原科技

9.2.3 存儲(chǔ)過(guò)程
存儲(chǔ)過(guò)程的使用是Command對(duì)象得到應(yīng)用的一個(gè)領(lǐng)域。存儲(chǔ)過(guò)程（有時(shí)也稱存儲(chǔ)查詢）是存儲(chǔ)在數(shù)據(jù)庫(kù)中預(yù)先定義的SQL查詢語(yǔ)句。
為什么應(yīng)該創(chuàng)建和使用存儲(chǔ)過(guò)程而不是在代碼中直接使用SQL字符串呢？主要有以下幾個(gè)理由：
· 存儲(chǔ)過(guò)程被數(shù)據(jù)庫(kù)編譯過(guò)。這樣可以產(chǎn)生一個(gè)“執(zhí)行計(jì)劃”，因此數(shù)據(jù)庫(kù)確切地知道它將做什么，從而加快了過(guò)程的執(zhí)行速度。
· 存儲(chǔ)過(guò)程通常被數(shù)據(jù)庫(kù)高速緩存，這樣使它們運(yùn)行得更快，因?yàn)榇藭r(shí)不需要從磁盤中讀取它們。并非所有的數(shù)據(jù)庫(kù)都支持這種緩存機(jī)制，比如微軟的Access就不支持，而SQL Server卻支持。
· 通過(guò)指定數(shù)據(jù)庫(kù)中的表只能被存儲(chǔ)過(guò)程修改，可以確保數(shù)據(jù)更安全。這意味著具有潛在危險(xiǎn)的SQL操作不會(huì)執(zhí)行。
· 可以避免將ASP代碼和冗長(zhǎng)的SQL語(yǔ)句混在一起，從而使ASP代碼更易于維護(hù)。
· 可以將所有SQL代碼集中存放于服務(wù)器。
· 可以在存儲(chǔ)過(guò)程中使用輸出參數(shù)，允許返回記錄集或其他的值。
一般說(shuō)來(lái)，存儲(chǔ)過(guò)程幾乎總是比相當(dāng)?shù)腟QL語(yǔ)句執(zhí)行速度快。
為了使用存儲(chǔ)過(guò)程，只要將存儲(chǔ)過(guò)程的名字作為命令文本，并設(shè)置相應(yīng)的類型。例如，考慮前面更新書價(jià)的例子。如果在SQL Server上創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，可以編寫代碼：
CREATE PROCEDURE usp_UpdatePrices
AS
UPDATE Titles
SET Price = Price * 1.10
WHERE TYPE='Business'
對(duì)于微軟的Access數(shù)據(jù)庫(kù)，可以使用一個(gè)簡(jiǎn)單的更新查詢語(yǔ)句完成相同的任務(wù)，如圖9-1所示：

圖9-1 使用微軟的Access數(shù)據(jù)庫(kù)完成更新查詢
要在ASP網(wǎng)頁(yè)中運(yùn)行該存儲(chǔ)過(guò)程，只需要使用以下代碼：
Set cmdUpdate = Server.CreateObject("ADODB.Command")

cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = "usp_UpdatePrices"
cmdUpdate.CommandType = adCmdStoredProc

cmdUpdate.Execute , , adExecuteNoRecords
這只是運(yùn)行存儲(chǔ)過(guò)程。沒有記錄集返回，因?yàn)橹皇窃诟聰?shù)據(jù)。需要記住的是，除非確實(shí)需要，不要?jiǎng)?chuàng)建記錄集。
雖然這樣做也可以，但并不是很靈活，因?yàn)閮H僅處理一種類型的書。更好的做法是創(chuàng)建一個(gè)允許我們選擇書類型的過(guò)程，這樣就不必為每類書創(chuàng)建一個(gè)過(guò)程。同樣也可去掉固定的10%更新，這樣使得靈活性更好。那么，如何才能做到這一點(diǎn)呢，很簡(jiǎn)單，使用參數(shù)。
1．參數(shù)
存儲(chǔ)過(guò)程的參數(shù)（或變量）與一般的過(guò)程和函數(shù)的參數(shù)一樣，可以傳到函數(shù)內(nèi)部，然后函數(shù)可以使用它的值。SQL Server（其他數(shù)據(jù)庫(kù)也一樣，包括Access）中的存儲(chǔ)過(guò)程都具有這樣的功能。
為了使存儲(chǔ)過(guò)程能處理多種類型的書，甚至允許用戶指定價(jià)格的增加（或減少），需要增加一些參數(shù)：
CREATE PROCEDURE usp_UpdatePrices
@Type Char(12),
@Percent Money

AS
UPDATE Titles
SET Price = Price * (1 + @Percent / 100)
WHERE Type = @Type
現(xiàn)在，存儲(chǔ)過(guò)程usp_UpdatePrices帶有兩個(gè)參數(shù)：
· 一個(gè)是書的類型（@Type）。
· 一個(gè)是書價(jià)變化的百分比（@Percent）。
與VBScript的函數(shù)一樣，這些參數(shù)都是變量。然而，與VBScript和其他腳本語(yǔ)言不同的是：在這些腳本語(yǔ)言中的變量都是variant類型，而SQL變量具有確定的類型(char、Money等等)。必須遵守SQL變量的命名規(guī)范，即變量必須以符號(hào)@開始。
注意，我們讓百分?jǐn)?shù)作為一個(gè)整數(shù)（如10代表10%），而不是作為一個(gè)分?jǐn)?shù)值傳入此過(guò)程。這只是讓存儲(chǔ)過(guò)程變得更直觀一些。
2． Parameters集合
那么，現(xiàn)在有了帶參數(shù)的存儲(chǔ)過(guò)程，但如何通過(guò)ADO來(lái)調(diào)用它呢？我們已經(jīng)見到了如何用Command對(duì)象調(diào)用不帶參數(shù)的存儲(chǔ)過(guò)程，實(shí)際上，它們之間并沒有什么不同。不同之處在于Parameters集合的使用。
Parameters集合包含存儲(chǔ)過(guò)程中每個(gè)參數(shù)的Parameter對(duì)象。然而，ADO并不會(huì)自動(dòng)地知道這些參數(shù)是什么，因此，必須用CreateParameter方法創(chuàng)建它們，采用下面的形式：
Set Parameter = Command.CreateParameter (Name, [Type], [Direction], [Size], [Value])
參數(shù)及說(shuō)明如表9-3所示：
表9-3 CreateParameter方法的參數(shù)及說(shuō)明
參數(shù)
說(shuō) 明

Name
參數(shù)名。這是Parameters集合中的參數(shù)名，不是存儲(chǔ)過(guò)程中的參數(shù)名。然而，使用相同的名字是一個(gè)好的做法

Type
參數(shù)的數(shù)據(jù)類型。可以是一個(gè)adDataType常數(shù)，詳見附錄

Direction
參數(shù)的方向，指明是參數(shù)向存儲(chǔ)過(guò)程提供信息，還是存儲(chǔ)過(guò)程向ADO返回信息?？梢允窍旅娴闹抵唬?BR>adParamInput，參數(shù)是傳給存儲(chǔ)過(guò)程的輸入?yún)?shù)
adParamOutput，參數(shù)是從存儲(chǔ)過(guò)程檢索出的輸出參數(shù)
adParamInputOutput，參數(shù)可同時(shí)作為輸入和輸出參數(shù)
adParamReturnValue，該參數(shù)包含存儲(chǔ)過(guò)程返回的狀態(tài)

Size
參數(shù)長(zhǎng)度。對(duì)于固定長(zhǎng)度的類型，比如整型，該值可以忽略

Value
參數(shù)的值

一旦創(chuàng)建了參數(shù)就可以將其追加到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
沒有必要顯式地創(chuàng)建一個(gè)對(duì)象去保存參數(shù)，缺省的Variant類型已經(jīng)可以工作得相當(dāng)好。如果不想創(chuàng)建一個(gè)變量，也可以走捷徑，例如下面的代碼：
cmdUpdate.Parameters.Append = _
cmdUpdate.CreateParameter("@Percent", adCurrency, adParamInput, , 10)
這使用CreateParameter方法返回一個(gè)Parameter對(duì)象，并用Append方法接收它。這種方法比使用變量運(yùn)行得快，卻加長(zhǎng)了代碼行，可讀性比較差?？梢愿鶕?jù)自己的愛好選擇其中一種方法。
參數(shù)加到Parameters集合后，就保留在其中，因此，不一定在創(chuàng)建參數(shù)時(shí)就為每個(gè)參數(shù)賦值?？梢栽诿钸\(yùn)行前的任何時(shí)候設(shè)置參數(shù)的值。例如：
cmdUpdate.Parameters.Append = _
cmdUpdate.CreateParameter("@Percent", adCurrency, adParamInput)

cmdUpdate.Parameters("@Percent") = 10
前一章提到了訪問(wèn)集合中的值有好幾種方法，Parameters集合并沒有什么不同。上面的例子使用參數(shù)的名字在集合中檢索參數(shù)，也可以使用索引號(hào)進(jìn)行檢索：
cmdUpdate.Parameters(0) = 10
以上代碼對(duì)參數(shù)集合中第一個(gè)（Parameters集合從0開始編號(hào)）參數(shù)進(jìn)行了賦值。使用索引號(hào)比使用名字索引速度快，但很顯然使用名字使代碼更易讀。
重點(diǎn)注意Parameters集合中參數(shù)的順序必須與存儲(chǔ)過(guò)程中參數(shù)的順序相一致。
運(yùn)行帶參數(shù)的命令
一旦加入?yún)?shù)，就可立即運(yùn)行命令，同時(shí)這些參數(shù)的值傳入存儲(chǔ)過(guò)程?，F(xiàn)在可用一個(gè)友好的網(wǎng)頁(yè)去更新用戶選擇的類型的書價(jià)。例如，假設(shè)有一個(gè)名為UpdatePrices.asp的網(wǎng)頁(yè)，其運(yùn)行時(shí)的界面如圖9-2所示：

圖9-2 UpdatePrices.asp網(wǎng)頁(yè)運(yùn)行時(shí)的界面
通過(guò)數(shù)據(jù)庫(kù)中獲取書類型的列表，可以很輕松地動(dòng)態(tài)創(chuàng)建該頁(yè)面。首先要做的是包含文件Connection.asp，該文件包含了連接字符串（保存在變量strConn中）以及對(duì)ADO常數(shù)的引用，這在前面的章節(jié)已經(jīng)討論過(guò)。

接下來(lái)，可以創(chuàng)建一個(gè)窗體（在這兒不顯示大量文本，僅僅用一個(gè)樣本文件）。該窗體調(diào)用一個(gè)名為StoreProcedure.asp的文件。
<FORM NAME="UpdatePrices" Method="post" ACTION="StoredProcedure.asp">
<TABLE>
<TR>
<TD>Book Type:</TD>
<TD><SELECT NAME="lstTypes"></TD>
現(xiàn)在開始編寫ASP腳本從title表中讀取書的類型。使用一個(gè)SQL字符串只返回唯一的書類型，然后將返回值放到HTML的OPTION標(biāo)記中：
<%
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

%>
顯示書的類型后，接著可以構(gòu)建窗體的其他部分，包括一個(gè)允許用戶輸入書價(jià)變化百分?jǐn)?shù)的文本框。
</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>
現(xiàn)在看一下Run Query按鈕調(diào)用的ASP文件StoredProcedure.asp。首先，聲明變量并從調(diào)用窗體取出書的類型和百分?jǐn)?shù)。
Dim cmdUpdate
Dim lRecs
Dim sType
Dim cPercent

' Get the form values
sType = Request.Form("lstTypes")
cPercent = Request.Form("txtPercent")
現(xiàn)在可以向用戶顯示一些確認(rèn)信息，告訴他們將發(fā)生什么。
' 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>"
現(xiàn)在重新回到代碼內(nèi)部，在此創(chuàng)建Command對(duì)象和參數(shù)。
Set cmdUpdate = Server.CreateObject("ADODB.Command")

With cmdUpdate
.ActiveConnection = strConn
.CommandText = "usp_UpdatePrices"
.CommandType = adCmdStoredProc
利用從前面網(wǎng)頁(yè)的窗體中提取的數(shù)據(jù)值，使用快捷方法創(chuàng)建和增加參數(shù)。
' Add the parameters
.Parameters.Append .CreateParameter ("@Type", adVarWChar, adParamInput, _
12, sType)
.Parameters.Append .CreateParameter ("@Percent", adCurrency, _
adParamInput, , cPercent)
現(xiàn)在，運(yùn)行存儲(chǔ)過(guò)程。
' Execute the command
.Execute lRecs, , adExecuteNoRecords
End With
為了確認(rèn)，可以告訴用戶已經(jīng)更新多少條記錄。
' And finally tell the user what's happened
Response.Write "Procedure complete. " & lRecs & " were updated."

Set cmdUpdate = Nothing
%>
這樣就有了兩個(gè)簡(jiǎn)單界面。前者創(chuàng)建了一個(gè)供選擇的項(xiàng)目列表，后者使用其中某個(gè)項(xiàng)目值更新數(shù)據(jù)。這是許多需要顯示和更新數(shù)據(jù)的ASP頁(yè)面的基礎(chǔ)。
3．傳遞數(shù)組參數(shù)
Parameters參數(shù)集合一般來(lái)說(shuō)比較好用，但有時(shí)稍有麻煩（尤其對(duì)于新手）。好在有一種快捷方法，使用Execute方法的Parameters參數(shù)。例如，調(diào)用存儲(chǔ)過(guò)程usp_UpdatePrices，但不使用Parameters集合。
創(chuàng)建一個(gè)Command對(duì)象，并同前面一樣設(shè)置其屬性。
' Set cmdUpdate = Server.CreateObject("ADODB.Command")

' Set the properties of the command
With cmdUpdate
.ActiveConnection = strConn
.commandText = "usp_UpdatePrices"
.commandType = adCmdStroreProc
但這里正是差異所在。我們僅是通過(guò)Execute方法傳遞參數(shù)給存儲(chǔ)過(guò)程，而不是創(chuàng)建參數(shù)并添加到集合中。
' Execute the command
.Execute lngRecs, Array(strType, curPercent), adExecuteNoRecords
End With
這里使用了Array函數(shù)，將單個(gè)變量轉(zhuǎn)換為數(shù)組，以適于方法調(diào)用。這種方法當(dāng)然也有缺點(diǎn)：
· 只能使用輸入?yún)?shù)。因?yàn)椴荒苤付▍?shù)的類型和傳遞方向，而缺省為輸入?yún)?shù)。
· 如果要多次調(diào)用存儲(chǔ)過(guò)程，這種方法速度就比較慢，因?yàn)锳DO將向數(shù)據(jù)存儲(chǔ)詢問(wèn)參數(shù)的內(nèi)容及數(shù)據(jù)類型。
集合方法和數(shù)組方法之間在速度上的差異非常之小，幾乎可以忽略。所以，如果只有輸入?yún)?shù)，可隨便使用哪一種。實(shí)際上，人們更喜歡使用Parameters集合的方法，盡管它稍為繁瑣，但是使參數(shù)的屬性更加明確。
4．輸出參數(shù)
我們已經(jīng)知道如何獲得受命令影響的記錄數(shù)，如果需要更多信息，卻又不想返回一個(gè)記錄集，怎么辦？也許想從存儲(chǔ)過(guò)程中返回兩個(gè)或三個(gè)值，但又不想費(fèi)心創(chuàng)建一個(gè)記錄集。在這時(shí)，可以定義一個(gè)輸出參數(shù)，其值由存儲(chǔ)過(guò)程提供。
例如，對(duì)于更新書價(jià)的程序，如果想在更新之后找出最高價(jià)格，可將存儲(chǔ)過(guò)程改成：
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
這只是在執(zhí)行更新后運(yùn)行了一個(gè)簡(jiǎn)單的SELECT語(yǔ)句，并將值賦給輸出參數(shù)。
現(xiàn)在可以改寫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
我們只是在集合中加入了另一個(gè)參數(shù)，但這次指定為輸出參數(shù)。注意它并沒有賦值，因?yàn)槠渲祵⒂纱鎯?chǔ)過(guò)程提供，記住這是一個(gè)輸出參數(shù)。
' 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
一旦執(zhí)行這個(gè)過(guò)程，就可從集合中取得該值。
' 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
%>
如果有不止一個(gè)輸出參數(shù)，可用相同的方法訪問(wèn)?？梢允褂脜?shù)名或索引號(hào)取出集合中的值。