sql存儲進程的應用和引見。本站提示廣大學習愛好者:(sql存儲進程的應用和引見)文章只能為提供參考,不一定能成為您想要的結果。以下是sql存儲進程的應用和引見正文
sql server存儲進程語法
界說老是很籠統。存儲進程其實就是能完成必定操作的一組SQL語句,只不外這組語句是放在數據庫中的(這裡我們只談SQL Server)。假如我們經由過程創立存儲進程和在ASP中挪用存儲進程,便可以免將SQL語句同ASP代碼混淆在一路。如許做的利益至多有三個:
第1、年夜年夜進步效力。存儲進程自己的履行速度異常快,並且,挪用存儲進程可以年夜年夜削減同數據庫的交互次數。
第2、進步平安性。假設將SQL語句混雜在ASP代碼中,一旦代碼掉密,同時也就意味著庫構造掉密。
第3、有益於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]
04017<PIXTEL_MMI_EBOOK_2005>17 </PIXTEL_MMI_EBOOK_2005>