在Asp中使用存儲過程數(shù)值類型值

1、在 Asp 中使用存儲過程數(shù)值類型值為了提高 Asp 程序的效率, 有時需要在 Asp 中使用使用 Sql Server的存儲技術, 下面簡單作一個介紹。 存儲過程的建立這里只簡單介紹如何在 Sql Server的企業(yè)管理器中如何建立存儲過程:(1 打開企業(yè)管理器 Enterprise manager(2 選擇服務器組 (SQL Server Group、服務器、數(shù)據庫 (Database以及相就的數(shù)據庫,鼠標右擊對應數(shù) 據庫下的 Stored Procdures項,在彈出的菜單中選擇 New Stored Procedure,在 Stored Procedures Pr operties 中

2、輸入建立存儲過程的語句。下面是一個例子:CREATE PROCEDURE proctest mycola Char(10,mycolb Char(10,mycolc text ASInsert into chatdata (mycola,mycolb,mycolc values(mycola,mycolb,mycolc在 Sql Server的文檔中它的語法為:CREATE PROCEDURE procedure_name ;number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION|

3、RECOMPILE, ENCRYPTION FOR REPLICATION ASsql_statement .n如果你對 Sql 語法不熟悉,可以使用 Check Syntax來檢查語法。在上例中,表示建立存儲過程名為 m ycola ,帶3個參數(shù)的存儲過過程,其中第一個參數(shù) mycola 數(shù)據類型為 char ,寬度 10;第2個參數(shù)數(shù)據類 型為 char ,寬度為 10,第3個參數(shù)數(shù)據類型為 text ,在這里使用的是 Sql Server的數(shù)據類型。存儲過程建立后,下面就是如何在 Asp 程序中調用該存儲過程:在 Asp 中調用存儲過程 為了提高 Asp 程序的效率,有時需要在 Asp

4、中使用使用 Sql Server的存儲技術,下面簡單作一個 , 在上面的增加參數(shù)的 語句 p.Append cm.CreateParameter("mycolc",201,1,250中,格式為:p.Append cm.CreateParameter("參數(shù)名稱 ", 類型 , 方向 , 大小 參許參數(shù)值的類型的意義如下:名稱值 整數(shù)值 功能adDBTimeStamp 135 日期時間數(shù)據類型adDecimal 14 十進制整數(shù)值adDouble 5 雙精度小數(shù)值adError 10 系統(tǒng)錯誤信息AdGUID 72 全域性唯一識別字 (Globally u

5、nique identifier adDispath 9 COM/OLE自動對象 (Automation Object adInteger 3 4字節(jié)有符號整數(shù)adIUnknown 13 COM/OLE對象adLongVarBinary 205 大型2字節(jié)值adLongVarChar 201 大型字符串值adLongVarWChar 203 大型未編碼字符串adNumeric 131 十進制整數(shù)值adSingle 4 單精度浮點小數(shù)adSmallInt 2 2字節(jié)有符號整數(shù)adTinyInt 16 1字節(jié)有符號整數(shù)adUnsignedBigInt 21 8字節(jié)無符號整數(shù)adUnsignedIn

6、t 19 4字節(jié)無符號整數(shù)adUnsignedSmallInt 18 2字節(jié)無符號整數(shù)adUnsignedTinyInt 17 1字節(jié)無符號整數(shù)adUserDefined 132 用戶自定義數(shù)據類型adVariant 12 OLE對象adVarBinary 204 雙字節(jié)字符變量值adVarChar 200 字符變量值advarchar 202 未編碼字符串變量值adWchar 130 未編碼字符串方向值的意義如下:名稱值 整數(shù)值 功能adParamInput 1 允許數(shù)據輸入至該參數(shù)當中adParamOutput 2 允許數(shù)據輸出至該參數(shù)當中adParamInputOutput 3 允許數(shù)據

7、輸入、輸出至該參數(shù)當中adparamReturnValue 4 允許從一子程序中返回數(shù)據至該參數(shù)當中更多詳細資源請參考 Sql Server的文檔和 IIS 的文檔資源。本欄文章均來自于互聯(lián)網,版權歸原作者和各發(fā)布網站所有,本站收集這些文章僅供學習參考之用。任何 人都不能將這些文章用于商業(yè)或者其他目的。 ( C ASP 與存儲過程 (Stored Procedures的文章不少,但是我懷疑作者們是否真正 實踐過。 我在初學時查閱過大量相關資料, 發(fā)現(xiàn)其中提供的很多方法實際操作起 來并不是那么回事。對于簡單的應用,這些資料也許是有幫助的,但僅限于此, 因為它們根本就是千篇一律, 互相抄襲, 稍微

8、復雜點的應用, 就全都語焉不詳了。 現(xiàn)在,我基本上通過調用存儲過程訪問 SQL Server,以下的文字雖不敢保 證絕對正確,但都是實踐的總結,希望對大家能有幫助。存儲過程就是作為可執(zhí)行對象存放在數(shù)據庫中的一個或多個 SQL 命令。 定義總是很抽象。 存儲過程其實就是能完成一定操作的一組 SQL 語句, 只不 過這組語句是放在數(shù)據庫中的 (這里我們只談 SQL Server。如果我們通過創(chuàng)建 存儲過程以及在 ASP 中調用存儲過程, 就可以避免將 SQL 語句同 ASP 代碼混雜在 一起。這樣做的好處至少有三個:第一、 大大提高效率。 存儲過程本身的執(zhí)行速度非?？? 而且, 調用存儲過 程可以

9、大大減少同數(shù)據庫的交互次數(shù)。第二、 提高安全性。 假如將 SQL 語句混合在 ASP 代碼中, 一旦代碼失密, 同 時也就意味著庫結構失密。第三、有利于 SQL 語句的重用。在 ASP 中, 一般通過 command 對象調用存儲過程, 根據不同情況, 本文也介 紹其它調用方法。為了方便說明,根據存儲過程的輸入輸出,作以下簡單分類: 1. 只返回單一記錄集的存儲過程假設有以下存儲過程 (本文的目的不在于講述 T-SQL 語法,所以存儲過程只 給出代碼,不作說明 :/*SP1*/CREATE PROCEDURE dbo.getUserListasset nocount onbeginselect

10、 * from dbo.userinfoendgo以上存儲過程取得 userinfo 表中的所有記錄,返回一個記錄集。通過 command 對象調用該存儲過程的 ASP 代碼如下 :'*通過 Command 對象調用存儲過程 *DIM MyComm,MyRstSet MyComm = Server.CreateObject("ADODB.Command"MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據庫連接 字串MyComm.CommandText = "getUserList" '指

11、定存儲過程名MyComm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true '要求將 SQL 命令先行編 譯Set MyRst = MyComm.ExecuteSet MyComm = Nothing存儲過程取得的記錄集賦給 MyRst ,接下來,可以對 MyRst 進行操作。 在以上代碼中, CommandType 屬性表明請求的類型,取值及說明如下: -1 表明 CommandText 參數(shù)的類型無法確定1 表明 CommandText 是一般的命令類型2 表明 CommandText 參數(shù)是一個存在的表名稱4 表明 Co

12、mmandText 參數(shù)是一個存儲過程的名稱還可以通過 Connection 對象或 Recordset 對象調用存儲過程,方法分別如 下:'*通過 Connection 對象調用存儲過程 *DIM MyConn,MyRstSet MyConn = Server.CreateObject("ADODB.Connection"MyConn.open MyConStr 'MyConStr是數(shù)據庫 連接字串Set MyRst = MyConn.Execute("getUserList",0,4 '最后一個參斷含義 同 CommandTy

13、peSet MyConn = Nothing'*通過 Recordset 對象調用存儲過程 *DIM MyRstSet MyRst = Server.CreateObject("ADODB.Recordset"MyRst.open "getUserList",MyConStr,0,1,4'MyConStr是數(shù)據庫連接字串 , 最后一個參斷含義與 CommandType 相同 2. 沒有輸入輸出的存儲過程請看以下存儲過程:/*SP2*/CREATE PROCEDURE dbo.delUserAllasset nocount onbegind

14、elete from dbo.userinfoendgo該存儲過程刪去 userinfo 表中的所有記錄,沒有任何輸入及輸出,調用方 法與上面講過的基本相同,只是不用取得記錄集:'*通過 Command 對象調用存儲過程 *DIM MyCommSet MyComm = Server.CreateObject("ADODB.Command"MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據庫連接 字串MyComm.CommandText = "delUserAll" '指定存儲過程名MyC

15、omm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true '要求將 SQL 命令先行編 譯MyComm.Execute '此處不必再取得記錄集 Set MyComm = Nothing當然也可通過 Connection 對象或 Recordset 對象調用此類存儲過程,不過 建立 Recordset 對象是為了取得記錄集, 在沒有返回記錄集的情況下, 還是利用 Command 對象吧。3. 有返回值的存儲過程在進行類似 SP2的操作時,應充分利用 SQL Server強大的事務處理功能, 以維護數(shù)據的一致性。并且,我們

16、可能需要存儲過程返回執(zhí)行情況,為此,將 SP2修改如下:/*SP3*/CREATE PROCEDURE dbo.delUserAllasset nocount onbeginBEGIN TRANSACTIONdelete from dbo.userinfoIF error=0beginCOMMIT TRANSACTIONreturn 1endELSEbeginROLLBACK TRANSACTIONreturn 0endreturnendgo以上存儲過程, 在 delete 順利執(zhí)行時, 返回 1, 否則返回 0, 并進行回滾操 作。為了在 ASP 中取得返回值,需要利用 Parameters

17、 集合來聲明參數(shù):'*調用帶有返回值的存儲過程并取得返回值 *DIM MyComm,MyParaSet MyComm = Server.CreateObject("ADODB.Command"MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據庫連接 字串MyComm.CommandText = "delUserAll" '指定存儲過程名MyComm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true '要求將 SQL 命令

18、先行編 譯'聲明返回值Set Mypara = MyComm.CreateParameter("RETURN",2,4MyComm.Execute'取得返回值DIM retValueretValue = MyComm(0 '或 retValue = MyComm.Parameters(0Set MyComm = Nothing在 MyComm.CreateParameter("RETURN",2,4中,各參數(shù)的含義如下:第一個參數(shù) ("RETURE"為參數(shù)名。 參數(shù)名可以任意設定, 但一般應與存儲過 程中聲明的

19、參數(shù)名相同。此處是返回值,我習慣上設為 "RETURE" ;第二個參數(shù) (2, 表明該參數(shù)的數(shù)據類型, 具體的類型代碼請參閱 ADO 參考, 以下給出常用的類型代碼:adBigInt: 20 ;adBinary : 128 ;adBoolean: 11 ;adChar: 129 ;adDBTimeStamp: 135 ;adEmpty: 0 ;adInteger: 3 ;adSmallInt: 2 ;adTinyInt: 16 ;adVarChar: 200 ;對于返回值,只能取整形,且 -1到 -99為保留值;第三個參數(shù) (4,表明參數(shù)的性質,此處 4表明這是一個返回值。

20、此參數(shù)取 值的說明如下:0 : 類型無法確定; 1: 輸入參數(shù); 2: 輸入參數(shù); 3:輸入或輸出參數(shù); 4: 返回值以上給出的 ASP 代碼,應該說是完整的代碼,也即最復雜的代碼,其實 Set Mypara = MyComm.CreateParameter("RETURN",2,4可以簡化為甚至還可以繼續(xù)簡化,稍后會做說明。對于帶參數(shù)的存儲過程,只能使用 Command 對象調用 (也有資料說可通過 Connection 對象或 Recordset 對象調用,但我沒有試成過 。4. 有輸入參數(shù)和輸出參數(shù)的存儲過程返回值其實是一種特殊的輸出參數(shù)。 在大多數(shù)情況下, 我們用到

21、的是同時有 輸入及輸出參數(shù)的存儲過程,比如我們想取得用戶信息表中,某 ID 用戶的用戶 名,這時候,有一個輸入參數(shù) -用戶 ID ,和一個輸出參數(shù) -用戶名。實現(xiàn) 這一功能的存儲過程如下:/*SP4*/CREATE PROCEDURE dbo.getUserNameUserID int,UserName varchar(40 outputasset nocount onbeginif UserID is null returnselect UserName=usernamefrom dbo.userinfowhere userid=UserIDreturnendgo調用該存儲過程的 ASP 代

22、碼如下:'*調用帶有輸入輸出參數(shù)的存儲過程 *DIM MyComm,UserID,UserNameUserID = 1Set MyComm = Server.CreateObject("ADODB.Command"MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據庫連接 字串MyComm.CommandText = "getUserName" '指定存儲過程名MyComm.CommandType = 4 '表明這是一個存儲過程 MyComm.Prepared = true 

23、9;要求將 SQL 命令先行編 譯'聲明參數(shù)MyComm.CreateParameter("UserID",3,1,4,UserIDMyComm.CreateParameter("UserName",200,2,40MyComm.Execute'取得出參UserName = MyComm(1Set MyComm = Nothing在以上代碼中, 可以看到, 與聲明返回值不同, 聲明輸入參數(shù)時需要 5個參 數(shù),聲明輸出參數(shù)時需要 4個參數(shù)。聲明輸入參數(shù)時 5個參數(shù)分別為:參數(shù)名、 參數(shù)數(shù)據類型、參數(shù)類型、數(shù)據長度、參數(shù)值。聲明輸出參數(shù)時,沒

24、有最后一個 參數(shù):參數(shù)值。需要特別注意的是:在聲明參數(shù)時, 順序一定要與存儲過程中定義的順序相 同,而且各參數(shù)的數(shù)據類型、長度也要與存儲過程中定義的相同。如果存儲過程有多個參數(shù), ASP 代碼會顯得繁瑣,可以使用 with 命令簡化 代碼:'*調用帶有輸入輸出參數(shù)的存儲過程 (簡化代碼 *DIM MyComm,UserID,UserNameUserID = 1Set MyComm = Server.CreateObject("ADODB.Command"with MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據庫

25、連接字 串.CommandText = "getUserName" '指定存儲過程名.CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將 SQL 命令先行編譯 .Parameters.append .CreateParameter("UserID",3,1,4,UserID .Parameters.append .CreateParameter("UserName",200,2,40 .Executeend withUserName = MyComm(1Set M

26、yComm = Nothing假如我們要取得 ID 為 1到 10, 10位用戶的用戶名,是不是要創(chuàng)建 10次 Command 對象呢?不是的。 如果需要多次調用同一存儲過程, 只需改變輸入參數(shù), 就會得到不同的輸出:'*多次調用同一存儲過程 *DIM MyComm,UserID,UserNameUserName = ""Set MyComm = Server.CreateObject("ADODB.Command"for UserID = 1 to 10with MyComm.ActiveConnection = MyConStr 'M

27、yConStr是數(shù)據庫連接字串.CommandText = "getUserName" '指定存儲過程名.CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將 SQL 命令先行編 譯if UserID = 1 then.Parameters.append .CreateParameter("UserID",3,1,4,User ID.Parameters.append .CreateParameter("UserName",200,2,40 .Executeelse

28、'重新給入參賦值 (此時參數(shù)值不發(fā)生變化的入參以及出參不必重 新聲明 .Parameters("UserID" = UserID.Executeend ifend withUserName = UserName + MyComm(1 + "," '也許你喜歡用數(shù)組存儲 nextSet MyComm = Nothing通過以上代碼可以看出:重復調用同一存儲過程時, 只需為值發(fā)生改變的輸 入參數(shù)重新賦值即可, 這一方法在有多個輸入輸出參數(shù), 且每次調用時只有一個 輸入參數(shù)的值發(fā)生變化時,可以大大減少代碼量。5. 同時具有返回值、輸入參數(shù)、輸出

29、參數(shù)的存儲過程前面說過, 在調用存儲過程時, 聲明參數(shù)的順序要與存儲過程中定義的順序 相同。 還有一點要特別注意:如果存儲過程同時具有返回值以及輸入、 輸出參數(shù), 返回值要最先聲明。為了演示這種情況下的調用方法,我們改善一下上面的例子。還是取得 ID 為 1的用戶的用戶名, 但是有可能該用戶不存在 (該用戶已刪除, 而 userid 是自 增長的字段 。存儲過程根據用戶存在與否,返回不同的值。此時,存儲過程和 ASP 代碼如下:/*SP5*/CREATE PROCEDURE dbo.getUserName-為了加深對 " 順序 " 的印象,將以下兩參數(shù)的定義順序顛倒一下Us

30、erName varchar(40 output,UserID intasset nocount onbeginif UserID is null returnselect UserName=usernamefrom dbo.userinfowhere userid=UserIDif rowcount>0return 1elsereturn 0returnendgo'*調用同時具有返回值、輸入參數(shù)、輸出參數(shù)的存儲過程 *DIM MyComm,UserID,UserNameUserID = 1Set MyComm = Server.CreateObject("ADODB.

31、Command"with MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據庫連接字 串.CommandText = "getUserName" '指定存儲過程名.CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將 SQL 命令先行編譯 '返回值要最先被聲明.Parameters.Append .CreateParameter("RETURN",2,4'以下兩參數(shù)的聲明順序也做相應顛倒.Parameters

32、.append .CreateParameter("UserName",200,2,40 .Parameters.append .CreateParameter("UserID",3,1,4,UserID .Executeend withif MyComm(0 = 1 thenUserName = MyComm(1elseUserName = "該用戶不存在 "end ifSet MyComm = Nothing6. 同時返回參數(shù)和記錄集的存儲過程有時候, 我們需要存儲過程同時返回參數(shù)和記錄集, 比如在利用存儲過程分 頁時, 要同時返

33、回記錄集以及數(shù)據總量等參數(shù)。 以下給出一個進行分頁處理的存 儲過程:/*SP6*/CREATE PROCEDURE dbo.getUserListiPageCount int OUTPUT, -總頁數(shù)iPage int, -當前頁號iPageSize int -每頁記錄數(shù)asset nocount onbegin-創(chuàng)建臨時表create table #t (ID int IDENTITY, -自增字段userid int,username varchar(40-向臨時表中寫入數(shù)據insert into #tselect userid,username from dbo.UserInfoorde

34、r by userid-取得記錄總數(shù)declare iRecordCount intset iRecordCount = rowcount-確定總頁數(shù)IF iRecordCount%iPageSize=0SET iPageCount=CEILING(iRecordCount/iPageSize ELSESET iPageCount=CEILING(iRecordCount/iPageSize+1-若請求的頁號大于總頁數(shù),則顯示最后一頁IF iPage > iPageCountSELECT iPage = iPageCount-確定當前頁的始末記錄DECLARE iStart int -s

35、tart recordDECLARE iEnd int -end recordSELECT iStart = (iPage - 1 * iPageSizeSELECT iEnd = iStart + iPageSize + 1-取當前頁記錄select * from #t where ID>iStart and ID<iEnd-刪除臨時表DROP TABLE #t-返回記錄總數(shù)return iRecordCountendgo在上面的存儲過程中,輸入當前頁號及每頁記錄數(shù),返回當前頁的記錄集, 總頁數(shù)及記錄總數(shù)。 為了更具典型性, 將記錄總數(shù)以返回值的形式返回。 以下是 調用該存儲過程

36、的 ASP 代碼 (具體的分頁操作略去 :'*調用分頁存儲過程 *DIM pagenow,pagesize,pagecount,recordcountDIM MyComm,MyRstpagenow = Request("pn"'自定義函數(shù)用于驗證自然數(shù)if CheckNar(pagenow = false then pagenow = 1pagesize = 20Set MyComm = Server.CreateObject("ADODB.Command"with MyComm.ActiveConnection = MyConStr &

37、#39;MyConStr是數(shù)據庫連接字 串.CommandText = "getUserList" '指定存儲過程名.CommandType = 4 '表明這是一個存儲過程 .Prepared = true '要求將 SQL 命令先行編譯 '返回值 (記錄總量 .Parameters.Append .CreateParameter("RETURN",2,4'出參 (總頁數(shù) .Parameters.Append .CreateParameter("iPageCount",3,2'入參 (當

38、前頁號 .Parameters.append .CreateParameter("iPage",3,1,4,pagenow '入參 (每頁記錄數(shù) .Parameters.append .CreateParameter("iPageSize",3,1,4,pagesiz eSet MyRst = .Executeend withif MyRst.state = 0 then '未取到數(shù)據, MyRst 關閉recordcount = -1elseMyRst.close '注意:若要取得參數(shù)值,需先關閉記錄集對象recordcount

39、 = MyComm(0pagecount = MyComm(1if cint(pagenow>=cint(pagecount then pagenow=pagecountend ifSet MyComm = Nothing'以下顯示記錄if recordcount = 0 thenResponse.Write "無記錄 "elseif recordcount > 0 thenMyRst.opendo until MyRst.EOF.loop'以下顯示分頁信息.else 'recordcount=-1Response.Write "

40、;參數(shù)錯誤 "end if對于以上代碼, 只有一點需要說明:同時返回記錄集和參數(shù)時, 若要取得參 數(shù),需先將記錄集關閉,使用記錄集時再將其打開。7. 返回多個記錄集的存儲過程本文最先介紹的是返回記錄集的存儲過程。 有時候, 需要一個存儲過程返回 多個記錄集,在 ASP 中,如何同時取得這些記錄集呢?為了說明這一問題,在 userinfo 表中增加兩個字段:usertel 及 usermail ,并設定只有登錄用戶可以 查看這兩項內容。/*SP7*/CREATE PROCEDURE dbo.getUserInfouserid int,checklogin bitasset nocount onbeginif us