在 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 代码

好，下面先看看我们这个简单的例子中所使用的数据库的表结构。

screen.width-500)this.style.width=screen.width-500;">此主题相关图片
screen.width-500)this.style.width=screen.width-500;">
然后在 Access 主界面上点击左侧的“查询”按钮，再在右边双击“在设计视图中创建查询”，以打开查询设计视图。
screen.width-500)this.style.width=screen.width-500;">此主题相关图片
screen.width-500)this.style.width=screen.width-500;">
这时弹出的是可视化的查询生成器，我们首先添加 SQL 语句需要涉及的表。
screen.width-500)this.style.width=screen.width-500;">此主题相关图片
screen.width-500)this.style.width=screen.width-500;">
添加表之后，在设计视图上点击鼠标右键，选择“SQL 视图”，以切换到 SQL 代码编辑窗口。
screen.width-500)this.style.width=screen.width-500;">此主题相关图片
screen.width-500)this.style.width=screen.width-500;">
好，下面说说 Access 的存储过程的特点。

Access 的查询，我目前的感觉是对 SQL 语句的一个包装，或许进行了某种优化如预编译。我们不能像写 SQL Server 存储过程那样使用多重操作，事务，逻辑判断，循环等等……

但是我们使用 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 语句后保存并命名。

screen.width-500)this.style.width=screen.width-500;">此主题相关图片
screen.width-500)this.style.width=screen.width-500;">
（二）使用存储过程

然后我们可以在 ASP 程序中调用这些存储过程了。

这里可以看到为什么我说 Access 中的查询就是它的存储过程——我们的 Command 对象的 CommandType 属性设置的是 4，即 Stored Proc！

so...

以下的代码很简单：


代码:
<%
Option Explicit

Dim s
Randomize
s = Rnd * 100

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

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

With cmd
.ActiveConnection = conn
.CommandType = &H0004 ''存储过程
.CommandText = "AddNewData"
End With

cmd.Execute , Array(CStr(Now()), CSng(s))

With cmd
.ActiveConnection = conn
.CommandType = &H0004 ''存储过程
.CommandText = "GetData"
End With

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

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

Set resultRS = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

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>"
%>



运行结果。
screen.width-500)this.style.width=screen.width-500;">此主题相关图片
screen.width-500)this.style.width=screen.width-500;">
感觉起来，速度似乎很快，呵呵～

不知道这样在 Access 中使用存储过程的意义大不大，不过确实很好玩

ASP 正在没落，不过我仍然很喜欢它的小快灵～

参考资料：

http://aspalliance.com/andrewmooney/default.aspx?article=16

http://support.microsoft.com/default.aspx?scid=kb;EN-US;304352