軟件首頁(yè) |  文章首頁(yè)
最新更新 軟件分類 設(shè)為首頁(yè) 加入收藏 聯(lián)系我們
當(dāng)前位置:首頁(yè)文章首頁(yè) IT學(xué)院 VB

VB實(shí)現(xiàn)SQL Server 2000存儲(chǔ)過(guò)程調(diào)用

作者:東坡下載  來(lái)源:uzzf  發(fā)布時(shí)間:2010-10-14 13:54:54  點(diǎn)擊:

  關(guān)鍵字:數(shù)據(jù)庫(kù);SQL Server2000;存儲(chǔ)過(guò)程;應(yīng)用程序;VB
  存儲(chǔ)過(guò)程
  存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的一組預(yù)編譯的Transact-SQL語(yǔ)句,是一種封裝重復(fù)任務(wù)操作的方法,支持用戶提供的變量,具有強(qiáng)大的編程功能。它類似于DOS系統(tǒng)中的BAT文件。在BAT文件中,可以包含一組經(jīng)常執(zhí)行的命令,這組命令通過(guò)BAT文件的執(zhí)行而被執(zhí)行。同樣的道理,可以把要完成某項(xiàng)任務(wù)的許多Transact-SQL語(yǔ)句寫(xiě)在一起,組織成存儲(chǔ)過(guò)程的形式,通過(guò)執(zhí)行該存儲(chǔ)過(guò)程就可以完成這項(xiàng)任務(wù)。存儲(chǔ)過(guò)程與BAT文件又有差別,即存儲(chǔ)過(guò)程已經(jīng)進(jìn)行了預(yù)編譯。
  1、創(chuàng)建存儲(chǔ)過(guò)程的方法
  在Transact-SQL語(yǔ)言中,創(chuàng)建存儲(chǔ)過(guò)程可以使用CREATE PROCEDURE語(yǔ)句,其語(yǔ)法形式如下:

CREATE PROC[EDURE] procedure_name[;number]
[{@parameter data_type}[VARYING][=default][OUTPUT]
]],…n]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement[…n]
  在上面的CREATE PROCEDURE語(yǔ)句中,方括號(hào)"[ ]"中的內(nèi)容是可選的,花括號(hào)"{}"中的內(nèi)容是必須出現(xiàn)的,不能省略,[,…n]表示前面的參數(shù)樣式,可以重復(fù)出現(xiàn)。豎線"|"表示兩邊的選項(xiàng)可以任選一個(gè)。
  下面分析該語(yǔ)句中各種選項(xiàng)的含義。
  CREATE PROCEDURE是關(guān)鍵字,也可以寫(xiě)成CREATE PROC。
  procedure_name是該存儲(chǔ)過(guò)程的名稱,名稱可以是任何符合命名規(guī)則的標(biāo)示符。名稱后的[;number]參數(shù)表示可以定義一系列的存儲(chǔ)過(guò)程名稱,這些存儲(chǔ)過(guò)程的數(shù)量由number指定。
  參數(shù)名稱可以使用@parameter data_type來(lái)指定。在Transact-SQL語(yǔ)言中,用戶定義的參數(shù)名稱前面加"@"符號(hào),這些數(shù)據(jù)類型是Transact-SQL語(yǔ)言允許的各種數(shù)據(jù)類型,包括系統(tǒng)提供的數(shù)據(jù)類型和用戶定義的數(shù)據(jù)類型。
  當(dāng)參數(shù)類型為cursor時(shí),必須使用關(guān)鍵字VARYING和OUTPUT。VARYING表示結(jié)果集可以是一個(gè)輸出參數(shù),其內(nèi)容是動(dòng)態(tài)的。該關(guān)鍵字只能在使用游標(biāo)作為數(shù)據(jù)類型時(shí)使用。關(guān)鍵字OUTPUT表示這是一個(gè)輸出參數(shù),可以把存儲(chǔ)過(guò)程執(zhí)行的結(jié)果信息返回應(yīng)用程序。
default用于指定參數(shù)的默認(rèn)值。
  RECOMPILE選項(xiàng)表示重新編譯該存儲(chǔ)過(guò)程。該選項(xiàng)只是在需要的時(shí)候才使用,例如經(jīng)常需要改變數(shù)據(jù)庫(kù)模式時(shí)。
  ENCRYPTION選項(xiàng)用來(lái)加密創(chuàng)建存儲(chǔ)過(guò)程的文本,防止他人查看。
  選項(xiàng)FOR REPLICATION主要用于復(fù)制過(guò)程中。注意,該選項(xiàng)不能和選項(xiàng)RECOMPILE同時(shí)使用。
  AS是一個(gè)關(guān)鍵字,表示其后的內(nèi)容是存儲(chǔ)過(guò)程的語(yǔ)句。參數(shù)sql-statement[…n]表示在一個(gè)存儲(chǔ)過(guò)程中可以包含多個(gè)Transact-SQL語(yǔ)句。
  2、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)
  在頻繁訪問(wèn)數(shù)據(jù)庫(kù)的系統(tǒng)中,開(kāi)發(fā)者都樂(lè)于使用存儲(chǔ)過(guò)程,這與存儲(chǔ)過(guò)程的下列優(yōu)點(diǎn)是分不開(kāi)的。
 、 存儲(chǔ)過(guò)程可以與其他應(yīng)用程序共享應(yīng)用程序的邏輯,從而確保一致的數(shù)據(jù)訪問(wèn)和操縱。
  ⑵ 存儲(chǔ)過(guò)程提供了一種安全機(jī)制。如果用戶被授予執(zhí)行存儲(chǔ)過(guò)程權(quán)限,那么即使該用戶沒(méi)有訪問(wèn)在執(zhí)行該存儲(chǔ)過(guò)程中所參考的表或視圖的權(quán)限,該用戶也可以完全執(zhí)行該存儲(chǔ)過(guò)程而不受到影響。因此,可以創(chuàng)建存儲(chǔ)過(guò)程來(lái)完成所有的增加、刪除等操作,并且可以通過(guò)編程控制上述操作中對(duì)信息的訪問(wèn)權(quán)限。
 、 存儲(chǔ)過(guò)程執(zhí)行速度快,便于提高系統(tǒng)的性能。由于存儲(chǔ)過(guò)程在第一次執(zhí)行之后,其執(zhí)行規(guī)劃就駐存在過(guò)程高速緩沖存儲(chǔ)區(qū)中,在以后的操作中,只需從過(guò)程高速緩沖存儲(chǔ)區(qū)中調(diào)用編譯好的二進(jìn)制形式存儲(chǔ)過(guò)程來(lái)執(zhí)行。
  ⑷ 使用存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)傳輸時(shí)間。如果有一千條Transact-SQL語(yǔ)句的命令,一條一條地通過(guò)網(wǎng)絡(luò)在客戶機(jī)和服務(wù)器之間傳送,那么這種傳輸所耗費(fèi)的時(shí)間將很長(zhǎng)。但是,如果把這一千條Transact-SQL語(yǔ)句的命令寫(xiě)成一條較為復(fù)雜的存儲(chǔ)過(guò)程命令,這時(shí)在客戶機(jī)和服務(wù)器之間網(wǎng)絡(luò)傳輸所需的時(shí)間就會(huì)大大減少。
  SQL Server 2000數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的調(diào)用
  VB作為當(dāng)今應(yīng)用極為普遍的數(shù)據(jù)庫(kù)客戶端開(kāi)發(fā)工具之一,對(duì)客戶端應(yīng)用程序調(diào)用服務(wù)器端存儲(chǔ)過(guò)程提供了強(qiáng)大的支持。特別是隨著VB6.0的推出,VB客戶端應(yīng)用程序可以方便地利用ADO的對(duì)象和集合來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的調(diào)用。
  在筆者編寫(xiě)的科技檔案管理系統(tǒng)中,就是采用VB作為開(kāi)發(fā)平臺(tái),采用SQL Server2000數(shù)據(jù)庫(kù)管理數(shù)據(jù),在這個(gè)科技檔案管理系統(tǒng)中有海量的數(shù)據(jù),并且對(duì)數(shù)據(jù)庫(kù)有頻繁的訪問(wèn),利用存儲(chǔ)過(guò)程訪問(wèn)數(shù)據(jù)庫(kù)節(jié)省了執(zhí)行時(shí)間,大大提高了系統(tǒng)的性能。
  1、ADO簡(jiǎn)介
  ADO控件(也稱為ADO Data控件)與VB固有的Data控件相似。使用ADO Data控件,可以利用Microsoft ActiveX Data Objects(ADO)快速建立數(shù)據(jù)庫(kù)綁定控件和數(shù)據(jù)提供者之間的連接。
  ADO Data控件可以實(shí)現(xiàn)以下功能:
  ·連接一個(gè)本地?cái)?shù)據(jù)庫(kù)或遠(yuǎn)程數(shù)據(jù)庫(kù)。
  ·打開(kāi)一個(gè)指定的數(shù)據(jù)庫(kù)表,或定義一個(gè)基于結(jié)構(gòu)化查詢語(yǔ)言(SQL)的查詢、存儲(chǔ)過(guò)程或該數(shù)據(jù)庫(kù)中的表的視圖的記錄集合。
  ·將數(shù)據(jù)字段的數(shù)值傳遞給數(shù)據(jù)綁定控件,可以在這些控件中顯示或更改這些數(shù)值。
  ·添加新的記錄,或根據(jù)更改顯示在綁定的控件中的數(shù)據(jù)來(lái)更新一個(gè)數(shù)據(jù)庫(kù)。
  2、數(shù)據(jù)庫(kù)的連接
  數(shù)據(jù)庫(kù)的連接可通過(guò)ADO控件實(shí)現(xiàn),為此,必須在工程部件中選擇Microsoft ADO Data Control 6.0 (OLEDB),然后在窗體中添加ADO控件。利用ADO連接數(shù)據(jù)庫(kù)有兩種方法,具體如下。
  1) 通過(guò)ADODC屬性頁(yè)實(shí)現(xiàn)連接
  在ADODC屬性頁(yè)中選擇生成按鈕,進(jìn)入數(shù)據(jù)鏈接屬性對(duì)話框;然后選擇該對(duì)話框中的連接屬性頁(yè),選擇或輸入服務(wù)器名稱和數(shù)據(jù)庫(kù)等重要信息;最后測(cè)試連接,連接成功后,按確定按鈕,返回到屬性頁(yè)對(duì)話框,可獲得連接字符串,如下例:
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Science_File;Data Source=Data_Server
  其中sa是用戶名;Science_File是數(shù)據(jù)庫(kù)名;Data_Server是數(shù)據(jù)庫(kù)名。
  通過(guò)下列語(yǔ)句,即可連接到指定的數(shù)據(jù)庫(kù):
dim odbcstr as String, adocon As New ADODB.Connection
odbcstr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Science_File;Data Source=Data_Server"
adocon.Open odbcstr '連接到數(shù)據(jù)庫(kù)
  2) 直接使用連接語(yǔ)句實(shí)現(xiàn)
  連接數(shù)據(jù)庫(kù)的語(yǔ)句如下:
Dim ado as ADODC
ado.ConnectionString = "Provider=SQLOLEDB.1;Password=" & User_Pwd & ";Persist Security Info=True;User ID=" & User_Name & ";Initial Catalog=" & Data_Name & ";Data Source=" & server_name
  其中User-Pwd是用戶密碼;User_Name是用戶名;Data_Name是數(shù)據(jù)庫(kù)名;server_name是服務(wù)器名。
  連接數(shù)據(jù)庫(kù)成功后就可以調(diào)用存儲(chǔ)過(guò)程執(zhí)行操作。
  3、存儲(chǔ)過(guò)程的調(diào)用
  假設(shè)有一個(gè)名為doc_ProcName存儲(chǔ)過(guò)程,該存儲(chǔ)過(guò)程有一個(gè)輸入?yún)?shù),一個(gè)輸出參數(shù)。
  1) 直接傳遞參數(shù)調(diào)用存儲(chǔ)過(guò)程
  直接傳遞參數(shù)方法主要通過(guò)以下幾個(gè)步驟來(lái)實(shí)現(xiàn):
 。1) 通過(guò)ADODB的Connection對(duì)象打開(kāi)與數(shù)據(jù)源的連接;
  (2) 通過(guò)ActiveConnection指定Command對(duì)象當(dāng)前所屬的Connection對(duì)象;
  (3) 通過(guò)CommandText屬性設(shè)置Command對(duì)象的源,即要調(diào)用的存儲(chǔ)過(guò)程;
 。4) 通過(guò)CommandType屬性確定Command對(duì)象的源類型,如果源類型為存儲(chǔ)過(guò)程CommandType即為adCmdStoredProc;
 。5) 通過(guò)Command對(duì)象的Parameters集合向所調(diào)用的存儲(chǔ)過(guò)程傳遞參數(shù),其中對(duì)象Parameters(0)為執(zhí)行存儲(chǔ)過(guò)程的返回值,返回值為0則執(zhí)行存儲(chǔ)過(guò)程成功;
  (6) 通過(guò)Eexecute方法執(zhí)行在 CommandText 屬性中指定的存儲(chǔ)過(guò)程。
  以存儲(chǔ)過(guò)程doc_ProcName為例,關(guān)鍵代碼如下:
Dim strS As String '定義一變量
Dim adoconn As New ADODB.Connection 'Connection 對(duì)象代表了打開(kāi)與數(shù)據(jù)源的連接。
Dim adocomm As New ADODB.Command 'Command 對(duì)象定義了將對(duì)數(shù)據(jù)源執(zhí)行的指定命令。
Dim ReturnValue As Integer '調(diào)用存儲(chǔ)過(guò)程的返回值
adoconn.ConnectionString = Adodc1.ConnectionString 'Adodc1為窗體中的ADO控件,并已成功連接數(shù)據(jù)庫(kù)
adoconn.Open
Set adocomm.ActiveConnection = adoconn '指示指定的 Command對(duì)象當(dāng)前所屬的 Connection對(duì)象。
adocomm.CommandText = "doc_ProcName" '設(shè)置Command對(duì)象源。
adocomm.CommandType = adCmdStoredProc '通知提供者CommandText屬性有什么,它可能包括Command對(duì)象的源類型。設(shè)置這個(gè)屬性優(yōu)化了該命令的執(zhí)行。
adocomm.Parameters(1) = "1"
adocomm.Parameters(2) = "OutputParameters" 'OutputParameters可以為任意的字符串或數(shù)字
adocomm.Execute
ReturnValue = adocomm.Parameters(0) '存儲(chǔ)過(guò)程的返回值,返回0則成功執(zhí)行。
strS = adocomm.Parameters(2) '把存儲(chǔ)過(guò)程的輸出參數(shù)的值賦給變量strS
  2) 追加參數(shù)法調(diào)用存儲(chǔ)過(guò)程
  追加參數(shù)通過(guò)CreateParameter方法,用來(lái)指定屬性創(chuàng)建新的Parameter對(duì)象。具體語(yǔ)法如下:
Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)
  ·Name 可選,字符串,代表 Parameter 對(duì)象名稱。
  ·Type 可選,長(zhǎng)整型值,指定 Parameter 對(duì)象數(shù)據(jù)類型。
  ·Direction 可選,長(zhǎng)整型值,指定 Parameter 對(duì)象類型。
  ·Size 可選,長(zhǎng)整型值,指定參數(shù)值最大長(zhǎng)度(以字符或字節(jié)數(shù)為單位)。
  ·Value 可選,變體型,指定 Parameter 對(duì)象值。
  這種方法與上面一種方法的分別主要在于,追加參數(shù)的方法在向存儲(chǔ)過(guò)程傳遞參數(shù)時(shí),這種方法首先通過(guò)CreateParameter方法為存儲(chǔ)過(guò)程創(chuàng)建參數(shù),然后通過(guò)Append方法將創(chuàng)建的參數(shù)追加到Parameters集合中去。
  仍然以存儲(chǔ)過(guò)程doc_ProcName的調(diào)用為例,關(guān)鍵代碼如下:
Dim mRst As ADODB.Recordset 'Recordset 對(duì)象表示的是來(lái)自基本表或命令執(zhí)行結(jié)果的記錄全集。
Dim prm As ADODB.Parameter 'Parameter 對(duì)象代表參數(shù)或與基于參數(shù)化查詢或存儲(chǔ)過(guò)程的Command 對(duì)象相關(guān)聯(lián)的參數(shù)。
adoconn.ConnectionString = Adodc1.ConnectionString
adoconn.Open
Set adocomm.ActiveConnection = adoconn
adocomm.CommandText = "doc_ProcName"
adocomm.CommandType = adCmdStoredProc
Set prm = adocomm.CreateParameter("parameter1", adTinyInt, adParamInput, , "1")
adocomm.Parameters.Append prm
Set prm = adocomm.CreateParameter("parameter2", adInteger, adParamOutput)
adocomm.Parameters.Append prm
Set mRst = adocomm.Execute
ReturnValue = adocomm.Parameters(0)
  以上代碼中未定義的變量以及未注釋的語(yǔ)句與前述相同。
  結(jié)束語(yǔ)
  在應(yīng)用程序中調(diào)用服務(wù)器端存儲(chǔ)過(guò)程,不僅能顯著提高整個(gè)應(yīng)用的性能,而且能加強(qiáng)對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的保護(hù)。VB為客戶端應(yīng)用程序調(diào)用存儲(chǔ)過(guò)程提供了一組方便而有效的方法。

文章評(píng)論

欄目導(dǎo)航

本類推薦文章

關(guān)于本站 | 網(wǎng)站幫助 | 廣告合作 | 下載聲明 | 友情連接 | 網(wǎng)站地圖
Copyright © 20098-2010 uzzf下載站. All Rights Reserved .