程式師世界 >> 數據庫知識 >> Access數據庫 >> 關於Access數據庫 >> 在 Access 中使用“存儲過程”

在 Access 中使用“存儲過程”

編輯：關於Access數據庫

我們已經熟悉在 ASP 中通過調用 SQL Server 存儲過程來執行數據庫操作，不過大家是否知道，在桌面級數據庫 Access 中，我們也能夠創建並使用“存儲過程”？
Access + ASP 是開發輕量級 Web 應用程序的絕佳組合：簡單，快速，兼容性好，但是性能通常不高。並且，用 ADODB.Connection 和 Recordset 對象來執行 SQL 語句的方式，也有一些不方便，因為帶參數的 SQL 語句的參數值也常常是拼接到字符串中，於是便有了諸如“單引號問題”這樣的麻煩。使用存儲過程的一個好處就是，支持 SQL 語句參數值的另外提供。

　　事實上，Access（2000 及以上版本）中所謂“存儲過程”，和 SQL Server 中的 Stored Procedure 是不能比的。它只能算是“Stored Procedure Lite”，不支持多條 SQL 語句，不支持邏輯語句（呵呵，畢竟不是 T-SQL）等等，我也還不清楚它是不是預編譯了。不過，正如同 VBScript 實現的所謂“類”僅僅具有封裝性，對於代碼結構的“美化”和程序重用性具有很大促進一樣，Access 的“輕量存儲過程”，對於規范，小出錯幾率的數據庫操作應該也有幫助，並且性能可能會有提高。

　　下面我譯 step by step 的方式，介紹如何在 Access 中創建存儲過程，然後在 ASP 程序中使用它。

　　（一）在 Access 中創建“存儲過程”

　　不知道大家的 Access 應用水平如何，反正它對於我來說，僅僅就是一個 MDB 數據庫文件的創建工具，我只會新建 MDB 文件，然後在 Access 的界面中創建表，索引，約束等等，over～

　　Access 中的“查詢”，就扮演了存儲過程的角色。下文中我提到的 Access 的“存儲過程”或者“查詢”都是指這個東西

　　對於“查詢”的創建，Access 提供了一個傻瓜工具，類似 VS.Net 裡邊建立 DataAdapter 時的向導。不過我喜歡直接寫 SQL 代碼

　　好，下面先看看我們這個簡單的例子中所使用的數據庫的表結構。
wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　然後在 Access 主界面上點擊左側的“查詢”按鈕，再在右邊雙擊“在設計視圖中創建查詢”，以打開查詢設計視圖。
wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

這時彈出的是可視化的查詢生成器，我們首先添加 SQL 語句需要涉及的表。
wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　添加表之後，在設計視圖上點擊鼠標右鍵，選擇“SQL 視圖”，以切換到 SQL 代碼編輯窗口。
wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　好，下面說說 Access 的存儲過程的特點。

　　Access 的查詢，我目前的感覺是對 SQL 語句的一個包裝，或許進行了某種優化如預編譯。我們不能像寫 SQL Server 存儲過程那樣使用多重操作，事務，邏輯判斷，循環等等……
wwww.iTbulo.combJJXD

　但是我們使用 Access 存儲過程的主要目的，就是使用參數額外提供的查詢，使用存儲過程，我們不必再面對將參數值拼接到 SQL 語句字符串中時遇到的各種麻煩，比如：

　　代碼:
　　--------------------------------------------------------------------------------
　　Dim sql
　　sql = "SELECT * FROM Users WHERE UserName = '" & userName & "'"
　　--------------------------------------------------------------------------------

　　以上代碼中，如果字符串變量 userName 中含有“'”單引號，則會報錯。我們必須手工轉化：

　　代碼:
　　--------------------------------------------------------------------------------
　　Dim sql
　　sql = "SELECT * FROM Users WHERE UserName = '" & Replace(userName, "'", "''")
　　& "'" '轉化為連續兩個單引號
　　--------------------------------------------------------------------------------

　　而使用帶參數查詢，我們的 SQL 語句可以寫為：

　　代碼:
　　--------------------------------------------------------------------------------
　　Dim sql
　　sql = "SELECT * FROM Users WHERE UserName = @userName"
　　--------------------------------------------------------------------------------

　　然後把參數 @userName 的值以 Command 對象的 Parameter 屬性來傳入即可，很方便直觀。

　　代碼:
　　--------------------------------------------------------------------------------
　　With cmd
　　'創建參數對象
　　.Parameters.Append .CreateParameter("@userName")

　　'給各參數指定值
　　.Parameters("@userName") = userName
　　End With
　　--------------------------------------------------------------------------------

　　這裡還要說明 Access 存儲過程中參數的使用。和 SQL Server 的存儲過程中用 @ 變量指定參數，然後同名傳入參數對象不同，Access 中的參數，是以“順序”而非“名字”來識別的。傳入的參數無需指定名字，隨便起，SQL 中的參數名字也可以隨便起，只要傳入參數值時，按照 SQL 語句中的參數出現順序指定就行了。通常，我們使用 Command 對象的 Execute 方法，直接傳入參數值數組來執行～

　　代碼:
　　--------------------------------------------------------------------------------
　　cmd.Execute , Array(userName)
　　--------------------------------------------------------------------------------

　　再比如，你的一個 Access 存儲過程這麼寫：

　　代碼:
　　--------------------------------------------------------------------------------
　　select * from Users where UserName = p_UserName and BookTitle = p_bookTitle
　　--------------------------------------------------------------------------------

　　你可以就這麼執行，通過傳入參數值數組，但是順序要對應：

　　代碼:
　　--------------------------------------------------------------------------------
　　cmd.Execute , Array(userName, bookTitle)
　　--------------------------------------------------------------------------------

　　OK，看看我們的例子中使用的兩個查詢，一個是寫入數據。寫好 SQL 語句後存並命名。
wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　另一個讀取數據的存儲過程代碼。
wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

使用存儲過程

然後我們可以在 ASP 程序中調用這些存儲過程了。

　　這裡可以看到為什麼我說 Access 中的查詢就是它的存儲過程——我們的 Command 對象的 CommandType 屬性設置的是 4，即 Stored Proc！

　　so...

　　以下的代碼很簡單：

　　代碼:
　　--------------------------------------------------------------------------------
　　 wwww.iTbulo.combJJXD

<%
　　Option Explicitwwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　Dim s
　　Randomize
　　s = Rnd * 100wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　Dim conn, cmd
　　Set conn = Server.CreateObject("ADODB.Connection")
　　Set cmd = Server.CreateObject("ADODB.Command")wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("sp.mdb")wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　With cmd
　　.ActiveConnection = conn
　　.CommandType = &H0004 '存儲過程
　　.CommandText = "AddNewData"
　　End Withwwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　cmd.Execute , Array(CStr(Now()), CSng(s))wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　With cmd
　　.ActiveConnection = conn
　　.CommandType = &H0004 '存儲過程
　　.CommandText = "GetData"
　　End Withwwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　Dim resultRS, resultArray
　　Set resultRS = cmd.Execute(, Null)wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　If Not resultRS.EOF Then
　　resultArray = resultRS.GetRows()
End Ifwwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　Set resultRS = Nothing
　　Set cmd = Nothing
　　conn.Close
　　Set conn = Nothingwwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD

　　Response.Write "<ul>"
　　Dim i
　　For i = 0 To UBound(resultArray, 2)
　　Response.Write "<li>" & resultArray(0, i)
　　Response.Write " " & resultArray(1, i)
　　Response.Write " " & resultArray(2, i)
　　Response.Write "</li>"
　　Next
　　Response.Write "</ul>"
　　%>wwww.iTbulo.combJJXD

　　--------------------------------------------------------------------------------

　　運行結果。
wwww.iTbulo.combJJXD

wwww.iTbulo.combJJXD