ASP開發中存儲過程應用全接觸
現在,我基本上通過調用存儲過程訪問SQL Server,以下的文字都是實踐的總結,希望對大家能有幫助。
存儲過程就是作為可執行對象存放在數據庫中的一個或多個SQL命令。
定義總是很抽象。存儲過程其實就是能完成一定操作的一組SQL語句,只不過這組語句是放在數據庫中的(這裡我們只談SQL Server)。如果我們通過創建存儲過程以及在ASP中調用存儲過程,就可以避免將SQL語句同ASP代碼混雜在一起。這樣做的好處至少有三個:
第一、大大提高效率。存儲過程本身的執行速度非常快,而且,調用存儲過程可以大大減少同數據庫的交互次數。
第二、提高安全性。假如將SQL語句混合在ASP代碼中,一旦代碼失密,同時也就意味著庫結構失密。
第三、有利於SQL語句的重用。
在ASP中,一般通過command對象調用存儲過程,根據不同情況,本文也介紹其他調用方法。為了方便說明,根據存儲過程的輸入輸出,作以下簡單分類:
1. 只返回單一記錄集的存儲過程
假設有以下存儲過程(本文的目的不在於講述T-SQL語法,所以存儲過程只給出代碼,不作說明):
/*SP1*/ CREATE PROCEDURE dbo.getUserList as set nocount on begin select * from dbo.[userinfo] end go |
以上存儲過程取得userinfo表中的所有記錄,返回一個記錄集。通過command對象調用該存儲過程的ASP代碼如下:
'**通過Command對象調用存儲過程** DIM MyComm,MyRst Set MyComm = Server.CreateObject("ADODB.Command") MyComm.ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 MyComm.CommandText = "getUserList" '指定存儲過程名 MyComm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true '要求將SQL命令先行編譯 Set MyRst = MyComm.Execute Set MyComm = Nothing |
存儲過程取得的記錄集賦給MyRst,接下來,可以對MyRst進行操作。
在以上代碼中,CommandType屬性表明請求的類型,取值及說明如下:
-1 表明CommandText參數的類型無法確定
1 表明CommandText是一般的命令類型
2 表明CommandText參數是一個存在的表名稱
4 表明CommandText參數是一個存儲過程的名稱
還可以通過Connection對象或Recordset對象調用存儲過程,方法分別如下:
'**通過Connection對象調用存儲過程** DIM MyConn,MyRst Set MyConn = Server.CreateObject("ADODB.Connection") MyConn.open MyConStr 'MyConStr是數據庫連接字串 Set MyRst = MyConn.Execute("getUserList",0,4) '最後一個參斷含義同CommandType Set MyConn = Nothing '**通過Recordset對象調用存儲過程** DIM MyRst Set MyRst = Server.CreateObject("ADODB.Recordset") MyRst.open "getUserList",MyConStr,0,1,4 'MyConStr是數據庫連接字串,最後一個參斷含義與CommandType相同 |
2. 沒有輸入輸出的存儲過程
請看以下存儲過程:
/*SP2*/ CREATE PROCEDURE dbo.delUserAll as set nocount on begin delete from dbo.[userinfo] end go |
該存儲過程刪去userinfo表中的所有記錄,沒有任何輸入及輸出,調用方法與上面講過的基本相同,只是不用取得記錄集:
'**通過Command對象調用存儲過程** DIM MyComm Set MyComm = Server.CreateObject("ADODB.Command") MyComm.ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 MyComm.CommandText = "delUserAll" '指定存儲過程名 MyComm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true '要求將SQL命令先行編譯 MyComm.Execute '此處不必再取得記錄集 Set MyComm = Nothing |
當然也可通過Connection對象或Recordset對象調用此類存儲過程,不過建立Recordset對像是為了取得記錄集,在沒有返回記錄集的情況下,還是利用Command對象吧。
3. 有返回值的存儲過程
在進行類似SP2的操作時,應充分利用SQL Server強大的事務處理功能,以維護數據的一致性。並且,我們可能需要存儲過程返回執行情況,為此,將SP2修改如下:
/*SP3*/ CREATE PROCEDURE dbo.delUserAll as set nocount on begin BEGIN TRANSACTION delete from dbo.[userinfo] IF @@error=0 begin COMMIT TRANSACTION return 1 end ELSE begin ROLLBACK TRANSACTION return 0 end return end go |
以上存儲過程,在delete順利執行時,返回1,否則返回0,並進行回滾操作。為了在ASP中取得返回值,需要利用Parameters集合來聲明參數:
'**調用帶有返回值的存儲過程並取得返回值** DIM MyComm,MyPara Set MyComm = Server.CreateObject("ADODB.Command") MyComm.ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 MyComm.CommandText = "delUserAll" '指定存儲過程名 MyComm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true '要求將SQL命令先行編譯 '聲明返回值 Set Mypara = MyComm.CreateParameter("RETURN",2,4) MyComm.Parameters.Append MyPara MyComm.Execute '取得返回值 DIM retValue retValue = MyComm(0) '或retValue = MyComm.Parameters(0) Set MyComm = Nothing |
在MyComm.CreateParameter("RETURN",2,4)中,各參數的含義如下:
第一個參數("RETURE")為參數名。參數名可以任意設定,但一般應與存儲過程中聲明的參數名相同。此處是返回值,我習慣上設為"RETURE";
第二個參數(2),表明該參數的數據類型,具體的類型代碼請參閱ADO參考,以下給出常用的類型代碼:
adBigInt: 20 ; adBinary : 128 ; adBoolean: 11 ; adChar: 129 ; adDBTimeStamp: 135 ; adEmpty: 0 ; adInteger: 3 ; adSmallInt: 2 ; adTinyInt: 16 ; adVarChar: 200 ; |
對於返回值,只能取整形,且-1到-99為保留值;
第三個參數(4),表明參數的性質,此處4表明這是一個返回值。此參數取值的說明如下:
0 : 類型無法確定; 1: 輸入參數;2: 輸入參數;3:輸入或輸出參數;4: 返回值
以上給出的ASP代碼,應該說是完整的代碼,也即最複雜的代碼,其實
Set Mypara = MyComm.CreateParameter("RETURN",2,4) MyComm.Parameters.Append MyPara |
可以簡化為
MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4) |
甚至還可以繼續簡化,稍後會做說明。
對於帶參數的存儲過程,只能使用Command對象調用(也有資料說可通過Connection對象或Recordset對象調用,但我沒有試成過)。
4. 有輸入參數和輸出參數的存儲過程
返回值其實是一種特殊的輸出參數。在大多數情況下,我們用到的是同時有輸入及輸出參數的存儲過程,比如我們想取得用戶資訊表中,某ID用戶的用戶名,這時候,有一個輸入參數----用戶ID,和一個輸出參數----用戶名。實現這一功能的存儲過程如下:
/*SP4*/ CREATE PROCEDURE dbo.getUserName @UserID int, @UserName varchar(40) output as set nocount on begin if @UserID is null return select @UserName=username from dbo.[userinfo] where userid=@UserID return end go |
調用該存儲過程的ASP代碼如下:
'**調用帶有輸入輸出參數的存儲過程** DIM MyComm,UserID,UserName UserID = 1 Set MyComm = Server.CreateObject("ADODB.Command") MyComm.ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 MyComm.CommandText = "getUserName" '指定存儲過程名 MyComm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true '要求將SQL命令先行編譯 '聲明參數 MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID) MyComm.Parameters.append MyComm.CreateParameter("@UserName",200,2,40) MyComm.Execute '取得出參 UserName = MyComm(1) Set MyComm = Nothing |
在以上代碼中,可以看到,與聲明返回值不同,聲明輸入參數時需要5個參數,聲明輸出參數時需要4個參數。聲明輸入參數時5個參數分別為:參數名、參數數據類型、參數類型、數據長度、參數值。聲明輸出參數時,沒有最後一個參數:參數值。
需要特別注意的是:在聲明參數時,順序一定要與存儲過程中定義的順序相同,而且各參數的數據類型、長度也要與存儲過程中定義的相同。
如果存儲過程有多個參數,ASP代碼會顯得繁瑣,可以使用with命令簡化代碼:
'**調用帶有輸入輸出參數的存儲過程(簡化代碼)** DIM MyComm,UserID,UserName UserID = 1 Set MyComm = Server.CreateObject("ADODB.Command") with MyComm .ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 .CommandText = "getUserName" '指定存儲過程名 .CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將SQL命令先行編譯 .Parameters.append .CreateParameter("@UserID",3,1,4,UserID) .Parameters.append .CreateParameter("@UserName",200,2,40) .Execute end with UserName = MyComm(1) Set MyComm = Nothing |
假如我們要取得ID為1到10,10位用戶的用戶名,是不是要創建10次Command對象呢?不是的。如果需要多次調用同一存儲過程,只需改變輸入參數,就會得到不同的輸出:
'**多次調用同一存儲過程** DIM MyComm,UserID,UserName UserName = "" Set MyComm = Server.CreateObject("ADODB.Command") for UserID = 1 to 10 with MyComm .ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 .CommandText = "getUserName" '指定存儲過程名 .CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將SQL命令先行編譯 if UserID = 1 then .Parameters.append .CreateParameter("@UserID",3,1,4,UserID) .Parameters.append .CreateParameter("@UserName",200,2,40) .Execute else '重新給入參賦值(此時參數值不發生變化的入參以及出參不必重新聲明) .Parameters("@UserID") = UserID .Execute end if end with UserName = UserName + MyComm(1) + "," '也許你喜歡用數組存儲 next Set MyComm = Nothing |
通過以上代碼可以看出:重復調用同一存儲過程時,只需為值發生改變的輸入參數重新賦值即可,這一方法在有多個輸入輸出參數,且每次調用時只有一個輸入參數的值發生變化時,可以大大減少代碼量。
5. 同時具有返回值、輸入參數、輸出參數的存儲過程
前面說過,在調用存儲過程時,聲明參數的順序要與存儲過程中定義的順序相同。還有一點要特別注意:如果存儲過程同時具有返回值以及輸入、輸出參數,返回值要最先聲明。
為了演示這種情況下的調用方法,我們改善一下上面的例子。還是取得ID為1的用戶的用戶名,但是有可能該用戶不存在(該用戶已刪除,而userid是自增長的字段)。存儲過程根據用戶存在與否,返回不同的值。此時,存儲過程和ASP代碼如下:
/*SP5*/ CREATE PROCEDURE dbo.getUserName --為了加深對"順序"的印象,將以下兩參數的定義順序顛倒一下 @UserName varchar(40) output, @UserID int as set nocount on begin if @UserID is null return select @UserName=username from dbo.[userinfo] where userid=@UserID if @@rowcount>0 return 1 else return 0 return end go '**調用同時具有返回值、輸入參數、輸出參數的存儲過程** DIM MyComm,UserID,UserName UserID = 1 Set MyComm = Server.CreateObject("ADODB.Command") with MyComm .ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 .CommandText = "getUserName" '指定存儲過程名 .CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將SQL命令先行編譯 '返回值要最先被聲明 .Parameters.Append .CreateParameter("RETURN",2,4) '以下兩參數的聲明順序也做相應顛倒 .Parameters.append .CreateParameter("@UserName",200,2,40) .Parameters.append .CreateParameter("@UserID",3,1,4,UserID) .Execute end with if MyComm(0) = 1 then UserName = MyComm(1) else UserName = "該用戶不存在" end if Set MyComm = Nothing |
6. 同時返回參數和記錄集的存儲過程
有時候,我們需要存儲過程同時返回參數和記錄集,比如在利用存儲過程分頁時,要同時返回記錄集以及數據總量等參數。以下給出一個進行分頁處理的存儲過程:
/*SP6*/ CREATE PROCEDURE dbo.getUserList @iPageCount int OUTPUT, --總頁數 @iPage int, --當前頁號 @iPageSize int --每頁記錄數 as set nocount on begin --創建臨時表 create table #t (ID int IDENTITY, --自增字段 userid int, username varchar(40)) --向臨時表中寫入數據 insert into #t select userid,username from dbo.[UserInfo] order by userid --取得記錄總數 declare @iRecordCount int set @iRecordCount = @@rowcount --確定總頁數 IF @iRecordCount%@iPageSize=0 SET @iPageCount=CEILING(@iRecordCount/@iPageSize) ELSE SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1 --若請求的頁號大於總頁數,則顯示最後一頁 IF @iPage > @iPageCount SELECT @iPage = @iPageCount --確定當前頁的始末記錄 DECLARE @iStart int --start record DECLARE @iEnd int --end record SELECT @iStart = (@iPage - 1) * @iPageSize SELECT @iEnd = @iStart + @iPageSize + 1 --取當前頁記錄 select * from #t where ID>@iStart and ID<@iEnd --刪除臨時表 DROP TABLE #t --返回記錄總數 return @iRecordCount end go |
在上面的存儲過程中,輸入當前頁號及每頁記錄數,返回當前頁的記錄集,總頁數及記錄總數。為了更具典型性,將記錄總數以返回值的形式返回。以下是調用該存儲過程的ASP代碼(具體的分頁操作略去):
'**調用分頁存儲過程** DIM pagenow,pagesize,pagecount,recordcount DIM MyComm,MyRst pagenow = Request("pn") '自定義函數用於驗證自然數 if CheckNar(pagenow) = false then pagenow = 1 pagesize = 20 Set MyComm = Server.CreateObject("ADODB.Command") with MyComm .ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 .CommandText = "getUserList" '指定存儲過程名 .CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將SQL命令先行編譯 '返回值(記錄總量) .Parameters.Append .CreateParameter("RETURN",2,4) '出參(總頁數) .Parameters.Append .CreateParameter("@iPageCount",3,2) '入參(當前頁號) .Parameters.append .CreateParameter("@iPage",3,1,4,pagenow) '入參(每頁記錄數) .Parameters.append .CreateParameter("@iPageSize",3,1,4,pagesize) Set MyRst = .Execute end with if MyRst.state = 0 then '未取到數據,MyRst關閉 recordcount = -1 else MyRst.close '注意:若要取得參數值,需先關閉記錄集對象 recordcount = MyComm(0) pagecount = MyComm(1) if cint(pagenow)>=cint(pagecount) then pagenow=pagecount end if Set MyComm = Nothing '以下顯示記錄 if recordcount = 0 then Response.Write "無記錄" elseif recordcount > 0 then MyRst.open do until MyRst.EOF ...... loop '以下顯示分頁資訊 ...... else 'recordcount=-1 Response.Write "參數錯誤" end if |
7. 返回多個記錄集的存儲過程
本文最先介紹的是返回記錄集的存儲過程。有時候,需要一個存儲過程返回多個記錄集,在ASP中,如何同時取得這些記錄集呢?為了說明這一問題,在userinfo表中增加兩個字段:usertel及usermail,並設定只有登錄用戶可以查看這兩項內容。
/*SP7*/ CREATE PROCEDURE dbo.getUserInfo @userid int, @checklogin bit as set nocount on begin if @userid is null or @checklogin is null return select username from dbo.[usrinfo] where userid=@userid --若為登錄用戶,取usertel及usermail if @checklogin=1 select usertel,usermail from dbo.[userinfo] where userid=@userid return end go |
以下是ASP代碼:
'**調用返回多個記錄集的存儲過程** DIM checklg,UserID,UserName,UserTel,UserMail DIM MyComm,MyRst UserID = 1 'checklogin()為自定義函數,判斷訪問者是否登錄 checklg = checklogin() Set MyComm = Server.CreateObject("ADODB.Command") with MyComm .ActiveConnection = MyConStr 'MyConStr是數據庫連接字串 .CommandText = "getUserInfo" '指定存儲過程名 .CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將SQL命令先行編譯 .Parameters.append .CreateParameter("@userid",3,1,4,UserID) .Parameters.append .CreateParameter("@checklogin",11,1,1,checklg) Set MyRst = .Execute end with Set MyComm = Nothing '從第一個記錄集中取值 UserName = MyRst(0) '從第二個記錄集中取值 if not MyRst is Nothing then Set MyRst = MyRst.NextRecordset() UserTel = MyRst(0) UserMail = MyRst(1) end if Set MyRst = Nothing |
以上代碼中,利用Recordset對象的NextRecordset方法,取得了存儲過程返回的多個記錄集。
至此,針對ASP調用存儲過程的各種情況,本文已做了較為全面的說明。最後說一下在一個ASP程式中,調用多個存儲過程的不同方法。
在一個ASP程式中,調用多個存儲過程至少有以下三種方法是可行的:
1. 創建多個Command對象
DIM MyComm Set MyComm = Server.CreateObject("ADODB.Command") '調用存儲過程一 ...... Set MyComm = Nothing Set MyComm = Server.CreateObject("ADODB.Command") '調用存儲過程二 ...... Set MyComm = Nothing ...... |
2. 只創建一個Command對象,結束一次調用時,清除其參數
DIM MyComm Set MyComm = Server.CreateObject("ADODB.Command") '調用存儲過程一 ..... '清除參數(假設有三個參數) MyComm.Parameters.delete 2 MyComm.Parameters.delete 1 MyComm.Parameters.delete 0 '調用存儲過程二並清除參數 ...... Set MyComm = Nothing |
此時要注意:清除參數的順序與參數聲明的順序相反,原因嘛,我也不知道。
3. 利用Parameters數據集合的Refresh方法重置Parameter對象
DIM MyComm Set MyComm = Server.CreateObject("ADODB.Command") '調用存儲過程一 ..... '重置Parameters數據集合中包含的所有Parameter對象 MyComm.Parameters.Refresh '調用存儲過程二 ..... Set MyComm = Nothing |
一般認為,重復創建對像是效率較低的一種方法,但是經測試(測試工具為Microsoft Application Center Test),結果出人意料:
方法2 >= 方法1 >> 方法3
方法2的運行速度大於等於方法1(最多可高4%左右),這兩種方法的運行速度遠大於方法3(最多竟高達130%),所以建議在參數多時,採用方法1,在參數較少時,採用方法2。
申明
非源创博文中的内容均收集自网上,若有侵权之处,请及时联络,我会在第一时间内删除.再次说声抱歉!!!
博文欢迎转载,但请给出原文连接。