VB實現SQL Server 2000存儲過程調用

領測軟件測試網

作者：劉興權 陳奇 吳兵 劉雪松 時洪飛

　摘要：本文以SQL Server2000存儲過程為例，探討了存儲過程的優點，并以Visual Basic6.0程序設計語言給出了調用存儲過程的關鍵代碼。

　　關鍵字：數據庫；SQL Server2000；存儲過程；應用程序；VB
　
　　存儲過程

　　存儲過程是存儲在服務器上的一組預編譯的Transact-SQL語句，是一種封裝重復任務操作的方法，支持用戶提供的變量，具有強大的編程功能。它類似于DOS系統中的BAT文件。在BAT文件中，可以包含一組經常執行的命令，這組命令通過BAT文件的執行而被執行。同樣的道理，可以把要完成某項任務的許多Transact-SQL語句寫在一起，組織成存儲過程的形式，通過執行該存儲過程就可以完成這項任務。存儲過程與BAT文件又有差別，即存儲過程已經進行了預編譯。

　　1、創建存儲過程的方法

　　在Transact-SQL語言中，創建存儲過程可以使用CREATE PROCEDURE語句，其語法形式如下：

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語句中，方括號"[ ]"中的內容是可選的，花括號"{}"中的內容是必須出現的，不能省略，[，…n]表示前面的參數樣式，可以重復出現。豎線"|"表示兩邊的選項可以任選一個。

　　下面分析該語句中各種選項的含義。

　　CREATE PROCEDURE是關鍵字，也可以寫成CREATE PROC。

　　procedure_name是該存儲過程的名稱，名稱可以是任何符合命名規則的標示符。名稱后的[；number]參數表示可以定義一系列的存儲過程名稱，這些存儲過程的數量由number指定。

　　參數名稱可以使用@parameter data_type來指定。在Transact-SQL語言中，用戶定義的參數名稱前面加"@"符號，這些數據類型是Transact-SQL語言允許的各種數據類型，包括系統提供的數據類型和用戶定義的數據類型。

　　當參數類型為cursor時，必須使用關鍵字VARYING和OUTPUT。VARYING表示結果集可以是一個輸出參數，其內容是動態的。該關鍵字只能在使用游標作為數據類型時使用。關鍵字OUTPUT表示這是一個輸出參數，可以把存儲過程執行的結果信息返回應用程序。
default用于指定參數的默認值。

　　RECOMPILE選項表示重新編譯該存儲過程。該選項只是在需要的時候才使用，例如經常需要改變數據庫模式時。

　　ENCRYPTION選項用來加密創建存儲過程的文本，防止他人查看。

　　選項FOR REPLICATION主要用于復制過程中。注意，該選項不能和選項RECOMPILE同時使用。

　　AS是一個關鍵字，表示其后的內容是存儲過程的語句。參數sql-statement[…n]表示在一個存儲過程中可以包含多個Transact-SQL語句。

　　2、存儲過程的優點

　　在頻繁訪問數據庫的系統中，開發者都樂于使用存儲過程，這與存儲過程的下列優點是分不開的。

　�、� 存儲過程可以與其他應用程序共享應用程序的邏輯，從而確保一致的數據訪問和操縱。

　�、� 存儲過程提供了一種安全機制。如果用戶被授予執行存儲過程權限，那么即使該用戶沒有訪問在執行該存儲過程中所參考的表或視圖的權限，該用戶也可以完全執行該存儲過程而不受到影響。因此，可以創建存儲過程來完成所有的增加、刪除等操作，并且可以通過編程控制上述操作中對信息的訪問權限。

　�、� 存儲過程執行速度快，便于提高系統的性能。由于存儲過程在第一次執行之后，其執行規劃就駐存在過程高速緩沖存儲區中，在以后的操作中，只需從過程高速緩沖存儲區中調用編譯好的二進制形式存儲過程來執行。

　�、� 使用存儲過程可以減少網絡傳輸時間。如果有一千條Transact-SQL語句的命令，一條一條地通過網絡在客戶機和服務器之間傳送，那么這種傳輸所耗費的時間將很長。但是，如果把這一千條Transact-SQL語句的命令寫成一條較為復雜的存儲過程命令，這時在客戶機和服務器之間網絡傳輸所需的時間就會大大減少。

　　SQL Server 2000數據庫存儲過程的調用

　　VB作為當今應用極為普遍的數據庫客戶端開發工具之一，對客戶端應用程序調用服務器端存儲過程提供了強大的支持。特別是隨著VB6.0的推出，VB客戶端應用程序可以方便地利用ADO的對象和集合來實現對數據庫存儲過程的調用。

　　在筆者編寫的科技檔案管理系統中，就是采用VB作為開發平臺，采用SQL Server2000數據庫管理數據，在這個科技檔案管理系統中有海量的數據，并且對數據庫有頻繁的訪問，利用存儲過程訪問數據庫節省了執行時間，大大提高了系統的性能。

　　1、ADO簡介

　　ADO控件（也稱為ADO Data控件）與VB固有的Data控件相似。使用ADO Data控件，可以利用Microsoft ActiveX Data Objects（ADO）快速建立數據庫綁定控件和數據提供者之間的連接。

　　ADO Data控件可以實現以下功能：

　　·連接一個本地數據庫或遠程數據庫。

　　·打開一個指定的數據庫表，或定義一個基于結構化查詢語言（SQL）的查詢、存儲過程或該數據庫中的表的視圖的記錄集合。

　　·將數據字段的數值傳遞給數據綁定控件，可以在這些控件中顯示或更改這些數值。

　　·添加新的記錄，或根據更改顯示在綁定的控件中的數據來更新一個數據庫。

　　2、數據庫的連接

　　數據庫的連接可通過ADO控件實現，為此，必須在工程部件中選擇Microsoft ADO Data Control 6.0 (OLEDB)，然后在窗體中添加ADO控件。利用ADO連接數據庫有兩種方法，具體如下。

　　1) 通過ADODC屬性頁實現連接

　　在ADODC屬性頁中選擇生成按鈕，進入數據鏈接屬性對話框；然后選擇該對話框中的連接屬性頁，選擇或輸入服務器名稱和數據庫等重要信息；最后測試連接，連接成功后，按確定按鈕，返回到屬性頁對話框，可獲得連接字符串，如下例：

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Science_File;Data Source=Data_Server

　　其中sa是用戶名；Science_File是數據庫名；Data_Server是數據庫名。

　　通過下列語句，即可連接到指定的數據庫：

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 '連接到數據庫

　　2) 直接使用連接語句實現

　　連接數據庫的語句如下：

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是數據庫名；server_name是服務器名。

　　連接數據庫成功后就可以調用存儲過程執行操作。

　　3、存儲過程的調用

　　假設有一個名為doc_ProcName存儲過程，該存儲過程有一個輸入參數，一個輸出參數。

　　1) 直接傳遞參數調用存儲過程

　　直接傳遞參數方法主要通過以下幾個步驟來實現：

　�。�1） 通過ADODB的Connection對象打開與數據源的連接；

　�。�2） 通過ActiveConnection指定Command對象當前所屬的Connection對象；

　�。�3） 通過CommandText屬性設置Command對象的源，即要調用的存儲過程；

　�。�4） 通過CommandType屬性確定Command對象的源類型，如果源類型為存儲過程CommandType即為adCmdStoredProc；

　�。�5） 通過Command對象的Parameters集合向所調用的存儲過程傳遞參數，其中對象Parameters（0）為執行存儲過程的返回值，返回值為0則執行存儲過程成功；

　�。�6） 通過Eexecute方法執行在 CommandText 屬性中指定的存儲過程。

　　以存儲過程doc_ProcName為例，關鍵代碼如下：

Dim strS As String '定義一變量 Dim adoconn As New ADODB.Connection 'Connection 對象代表了打開與數據源的連接。 Dim adocomm As New ADODB.Command 'Command 對象定義了將對數據源執行的指定命令。 Dim ReturnValue As Integer '調用存儲過程的返回值 adoconn.ConnectionString = Adodc1.ConnectionString 'Adodc1為窗體中的ADO控件，并已成功連接數據庫 adoconn.Open Set adocomm.ActiveConnection = adoconn '指示指定的 Command對象當前所屬的 Connection對象。 adocomm.CommandText = "doc_ProcName" '設置Command對象源。 adocomm.CommandType = adCmdStoredProc '通知提供者CommandText屬性有什么，它可能包括Command對象的源類型。設置這個屬性優化了該命令的執行。 adocomm.Parameters(1) = "1" adocomm.Parameters(2) = "OutputParameters" 'OutputParameters可以為任意的字符串或數字 adocomm.Execute ReturnValue = adocomm.Parameters(0) '存儲過程的返回值，返回0則成功執行。 strS = adocomm.Parameters(2) '把存儲過程的輸出參數的值賦給變量strS

　　2) 追加參數法調用存儲過程

　　追加參數通過CreateParameter方法，用來指定屬性創建新的Parameter對象。具體語法如下：

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

　　·Name 可選，字符串，代表 Parameter 對象名稱。
　　·Type 可選，長整型值，指定 Parameter 對象數據類型。
　　·Direction 可選，長整型值，指定 Parameter 對象類型。
　　·Size 可選，長整型值，指定參數值最大長度（以字符或字節數為單位）。
　　·Value 可選，變體型，指定 Parameter 對象值。

　　這種方法與上面一種方法的分別主要在于，追加參數的方法在向存儲過程傳遞參數時，這種方法首先通過CreateParameter方法為存儲過程創建參數，然后通過Append方法將創建的參數追加到Parameters集合中去。

　　仍然以存儲過程doc_ProcName的調用為例，關鍵代碼如下：

Dim mRst As ADODB.Recordset 'Recordset 對象表示的是來自基本表或命令執行結果的記錄全集。 Dim prm As ADODB.Parameter 'Parameter 對象代表參數或與基于參數化查詢或存儲過程的Command 對象相關聯的參數。 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)

　　以上代碼中未定義的變量以及未注釋的語句與前述相同。

　　結束語

　　在應用程序中調用服務器端存儲過程，不僅能顯著提高整個應用的性能，而且能加強對數據庫數據的保護。VB為客戶端應用程序調用存儲過程提供了一組方便而有效的方法。

延伸閱讀

文章來源于領測軟件測試網 http://www.kjueaiud.com/