如何使用存贮查询

　　 

不知道你有没有这样的体会，在用ASP写一个数据库的应用的时候，总是很容易就会被那些麻烦的SQL语句搞的昏头转向。看起来好好的语句，一到执行起来，总是有各种各样的错误，然后就是一些莫名其妙的错误代码，让人摸不着头脑。这个问题再VB中也同样存在。

　　这时，很多有经验的程序员会建议你使用存贮过程来代替运行时生成SQL语句执行查询任务。这确实是一个好的办法。使用存贮查询相对而言有很多好处,而且大多数所带来的效率是明显的。在下面的这个表中，可以看到这两者的一个大概的对比：

存贮查询 VS. 运行时查询

CC" cellpadding=0 width=559%>

存贮查询	运行时查询
执行的更快：因为它已经预先被数据库引擎优化并编译好了。	执行的相对慢一些： 因为它时再运行时被解释执行的，不管怎样，解释执行的速度和编译执行是没法比的。
容易改变： 因为它只要更改在保存数据库中的相应的查询语句就可以了，而不必改动程序中的调用代码。	改起来很麻烦： 因为整个应用程序都要因为它的改动作一些相应的改动，以使得程序能够顺利运行。
灵活性一般：只能通过参数调用。而参数调用也是一个比较麻烦的事。	灵活性很高： 因为它可以用参数查询，或者嵌入变量查询等多种查询方式。
容易维护：因为只有一个查询语句存在于数据库中，使用查询时都是只对对这个查询语句的调用。	维护相对难一些： 因为对? 个查询任务都要单独的写出SQL语句来执行查询。
容易阅读： 因为调用存贮查询的语句都很简短，也不用管那些那些烦人符号。	很难阅读： 因为在语句中还包含了很多连接号，变量，而一些其它的字符。

　　由上表可以看出，一般的运行时查询都存在的问题是：1，查询语句难于更改，往往牵一发而动全身。2，效率不高，解释执行和速度的是很难统一的，这在Jet或者SQL Server中更是如此。3，写起来麻烦，看起来不爽。

　　好的解决办法事把查寻语句从你的代码中移出，而放在数据库中实现。一方面，可以减少你的应用程序的复杂性，另一方面，如果你想改变查询内容，或者发现在查询中有一些问题，只需在数据库中更改就好了，而对于应用程序的代码仍然是适用的。这样要更新的只是数据库，而不是整个应用程序本身，工作量大大减少了。

用MICROSOFT ACCESS 2000创建存贮查询

　　Microsoft Aclearcase/" target="_blank" >ccess提供了最简单的方法来创建存贮查询。Access的一个很好的特性就是能够让你用图形化的方法来创建正确的SQL语句。比起VisData而言，Access要易用的多。

　　可以按照以下的步骤来在Access 2000中创建存贮查询：

1. 运行Access然后打开一个数据库。
2. 在菜单中选择插入-$#@62;查询。
3. 选择设计视图。
4. 添加想要处理的表和字段到设计视图中。
5. 如果愿意，可以选者是否根据某个字段进行排序。
6. 选择是否显示指定的字段。
7. 指定查询准则，比如: (= "John")或者($#@60;52) 等等表达式，对于复杂的准则，可以在准则栏中单击右键，调出表达式生成器来简化表达式的输入。
8. 可以通过选择菜单上的视图-$#@62;SQL视图来察看和修改 查询所对应的SQL语句。
9. 保存后再执行可以看到查询的结果。

　　当然，如果你对SQL语言非常熟悉的话，也可以直接选择SQL视图，再在其中输入SQL语句就行了。创建带参数的存贮查询。

　　在查询中的参数扮演的就像是Visual Basic应用程序中的变量的角色。当在存贮查询中能够使用“变量”后，运行时查询相对于存贮查询而言，几乎就没有任何的优势了，你可以用存贮查询来完成任何的查询工作。创建这种类型的存贮查询也不难，跟着我一步步来吧：

1. 在设计视图中创建或者打开一个查询。
2. 在菜单上选择查询-$#@62;参数。
3. 一个名为参数查询的窗口弹出来，在里面输入参数名称和相应的数据类型。参数名通常用“p”作为前缀，后边再跟上一到三个标志数据类型的前缀，这样就容易阅读和辨认了。比如，一个双精度类型的参数可以取名为pdblMyParameter或者pdMyParameter。只是要注意使用相同的前缀来标志相同类型的前缀，否则读起来就很困难了。
4. 点击“确定”，然后选者菜单上的视图-$#@62;SQL视图，进入查询的SQL视图。
5. 假设已经建立了一个名为pEndDate和一个名为pDescription 的参数，那么在SQL序列的顶部，可以看到如下雨句： 　
   　　　　　　　 PARAMETERS pEndDate DateTime, pDescription Text ( 255 )
   跟在参数名后面的是其对应的数据类型。
6. 从上面可以知道参数定义的语法： 　
   　　　　　　　 "PARAMETERS" VariableName DataType;
   　　　　　　（多个参数间用逗号分隔）
7. 现在你就可以在查询的任何地方使用定义好的参数了。
   　　　　　　　 例如： SELECT * FROM MyTable WHERE MyField $#@62; pMyNumber ORDER BY MyField

　　同样，在SQL视图中直接输入SQL语句更为简单。在输入参数时，可以把参数名放在“[”和“]”之间来标明，或者直接打一个“？”来告诉数据库引擎这儿需要输入一个参数。其它的地方就和一般的SQL语句没什么区别了。

　

　　对于不带参数的存贮查询的调用是非常简单的，只要把存贮查询的名称当作一个SQL语句作为参数传递给相应的变量就可以了。比如，在ADO中，command对象的commandText属性值是要执行的SQL语句字符串，在执行存贮查询时，这个属性不再是一个SQL语句，而是要执行的存贮查询的名称字符串。因为比较简单，就不再详细举例说明了。

　　下面的部分将描叙如何在VB工程中用DAO和ADO来使用带参数的存贮查询。

在DaO中使用参数存贮查询

　　DAO的存贮查询对象QueryDef的参数集合使得我们能够在DAO中为参数存贮查询传递参数，得到返回的结果。QueryDef对象代替了在DAO3.5版本之前的ListParameters方法。
　　假设有下面的SQL语句已经作为存贮过程保存在数据库中了，名称为qryGetAuthorByID：
　　　　　　　　　　　 SELECT * FROM Authors WHERE Au_ID = pID
　　　　　　　　　　　 SELECT * FROM Authors WHERE Au_ID = pID
　　在这个查询中参数名是Pid，下面的代码演示如何在DAO使用参数查询。

Option Explicit　

Private db As Database 定义数据库对象

Private qd As QueryDef 定义存贮查询对象

Private rs As Recordset 定义记录集

Private Sub Form_Load()

　 打开数据库

Set db = OpenDatabase(App.Path & "\test.mdb")

设定查询

Set qd = db.QueryDefs("qryGetAuthorByID")

End Sub

Private Sub cmdGetAuthorByID_Click()

给参数赋值

qd.Parameters("pID").Value = txtID.Text

执行查询并返回结果集

Set rs = qd.OpenRecordset

If rs.RecordCount = 0 Then

　　 MsgBox "There are no authors having ID #" & txtID.Text & "."

Else

　　　 MsgBox "Author= " & rs!Author

End If

End Sub

　

在ADO中使用参数存贮查询

　　下面的代码示例了如何在ADO中使用参数存贮查询　

Option Explicit

　

Private cn As New Connection 　定义连接对象

Private cm As New Command 定义命令对象

Private rs As New Recordset 定义纪录集对象

Private p As New Parameter 定义参数对象

Private Sub Form_Load()

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _
　 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _
　"\test.md

End Sub

　这一段用来建立数据库连接。

Private Sub cmdGetAuthorByID_Click()

On Error Resume Next

Set cm = New Command

设置command对象的运行参数

cm.ActiveConnection = cn

cm.CommandType = adCmdStoredProc

cm.CommandText = "qryGetAuthorByID"

为参数查询提供参数值

Set p = cm.CreateParameter("pID", adInteger, adParamInput, , txtID.Text)

把参数对象添加到命令对象中

cm.Parameters.Append p

执行查询并把结果返回到记录集中

Set rs = cm.Execute

显示记录集中的记录

If rs.EOF And rs.BOF Then

　　　 MsgBox "No authors found."

Else

　　　 MsgBox "Author= " & rs!Author

End If

End Sub

　　两种方法都执行了查询qryGetAuthorByID，把文本框txtID 的内容传递给了参数pID， 使用起来都很简单。而比较运行时的查询，效率自然要高一些了。

　　在ASP中也可以通过ADO来使用参数查询，这样就可以摆脱那些无穷无尽的引号的烦恼了。它的实现原理和在VB中使用没有很大的区别，我就不再列出详细的代码，只时指出一些特别的地方。

　　在ASP中有两种方法可以实现参数查询。说是两种，实际上也就是一种，都是使用ADO的Command对象来实现的。但是参数传递的方法却有两种。

　　一种就是像上面那样，先建立Parameter对象，然后把Parameter对象添加到Command对象中。还有一种来的简单一些，直接使用参数数组，例如，如果还是要执行上面的那个参数查询，只要这样：

Redim param(0) ‘声明含有一个元数的数组

Param(0)=CLng(pid) ‘把转换成相应的参数数据类型后传递过去

Set rs=cmd.execute(qryGetAuthorByID,param)

　　这样就完成了一次查询任务。特别要注意的是第二行中的CLng数据类型转换，是不能够申略的，当初就是因为它的缘故，烦了我好几天。

　　好了，大致的内容就是这么多了。其实在Access 2000中的存贮过程所能作的只是简单的查询，功能不是很强。而在SQL SERVER中，存贮过程的威力就远远不只是查询了。善用好存贮查询，必将令你的数据库应用更加出色。

参考：Stored Queries: The Pros, Cons, and How-Tos by Ara Mahdessian

　　　Active Server Pages & 数据库 王国荣

原文转自：http://www.ltesting.net