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)（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為用戶端應用程式調用預存程序提供了一組方便而有效方法。