ACCESS中的存储过程——参数查询

最新推荐文章于 2025-04-09 09:33:16 发布

转载最新推荐文章于 2025-04-09 09:33:16 发布 · 4.9k 阅读

文章标签：

#access #存储 #sqlserver #vba #forms #vbscript

数据库专栏收录该内容

27 篇文章

订阅专栏

本文介绍了Access数据库中参数查询的概念及使用方法，包括如何构建参数查询、在VBA中生成和调用参数查询等内容。此外，还探讨了参数查询在实际应用中的优势。

Access 的 MDB / MDE 中存在类似存储过程得概念（建立时也可以使用 DDL 语句的 create procedure 语句建立），但是叫参数查询，并且一个参数查询只支持一条 JET SQL 语句，因而 JET SQL 不存在程序流控制语句，所有的程序流控制都交由 VBA 控制。也就是说 T-SQL 中的 IF 以及 CASE 语句在 JET SQL 中不存在，但是部分功能可以用 IIF 函数以及 SWITCH 函数代替，具体内容请参考 ACCESS 帮助。JET SQL中也可以定义“变量”，但是这个变量和 T-SQL 中的变量是不同的概念，因此在 JET SQL 中被称为“参数”。

事实上，Access（2000 及以上版本）中所谓“存储过程”，和 SQL Server 中的 Stored Procedure 是不能比的。它只能算是“Stored Procedure Lite”，不支持多条 SQL 语句，不支持逻辑语句（呵呵，毕竟不是 T-SQL）等等，我也还不清楚它是不是预编译了。不过，正如同 VBScript 实现的所谓“类”仅仅具有封装性，对于代码结构的“美化”和程序重用性具有很大促进一样，Access 的“轻量存储过程”，对于规范，小出错几率的数据库操作应该也有帮助，并且性能可能会有提高。

一、在ACCESS中构建参数查询

关键字 PARAMETERS 可构建参数

PARAMETERS aa Short, bb Short;
SELECT 表1.ID
FROM 表1
WHERE (((表1.ID)>[aa] And (表1.ID)<=[bb]));

上述参数查询在打开时会提示用户输入 [aa] [bb] 这两个参数。ACCESS 中还有一种参数查询是直接用窗体的某个控件来传递参数给查询，代码如下

SELECT 表1.ID
FROM 表1
WHERE (((表1.ID)>FORMS!窗体A!控件A And (表1.ID)<=FORMS!窗体A!控件B));

当“窗体A”打开时，双击这个查询时无需输入参数，查询会自动调用窗体上控件A控件B这两个控件的值来作为参数。

二、在VBA中生成和调用参数查询

参数查询代码如下：

PARAMETERS strA Text;
INSERT INTO 表1 ( hh )
values ([stra])

VBA 或者 VB 调用参数查询如下：

Public Function AppendX()
    Dim cmdByRoyalty As ADODB.Command
    Dim prmByRoyalty As ADODB.Parameter
    Dim rstByRoyalty As ADODB.Recordset
    Dim intRoyalty As String
    Set cmdByRoyalty = New ADODB.Command
    cmdByRoyalty.CommandText = "查询1"
    cmdByRoyalty.CommandType = adCmdStoredProc
    intRoyalty = Trim(InputBox("输入参数:"))
    Set prmByRoyalty = cmdByRoyalty.CreateParameter("strA", adChar, adParamInput, 255)
    cmdByRoyalty.Parameters.Append prmByRoyalty
    prmByRoyalty.Value = intRoyalty
    Set cmdByRoyalty.ActiveConnection = CurrentProject.Connection
    Set rstByRoyalty = cmdByRoyalty.Execute
End Function

三、何时使用参数查询

我们使用 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)

五、参考链接：

（1）http://access911.net/fixhtm/79FAB21E12DC.htm?tt=

（2）http://access911.net/fixhtm/72FAB21E15DC.htm?tt=

（3）http://access911.net/fixhtm/71FAB51E12DC.htm?tt=

（4）http://www.haishui.net/view.php?tid=18&id=278