ASP 3.0高级编程（四十一）
程序员文章站
2022-06-23 16:37:21
9.2.3 存储过程
存储过程的使用是command对象得到应用的一个领域。存储过程（有时也称存储查询）是存储在中...
9.2.3 存储过程
存储过程的使用是command对象得到应用的一个领域。存储过程（有时也称存储查询）是存储在中预先定义的sql查询语句。
为什么应该创建和使用存储过程而不是在代码中直接使用sql字符串呢？主要有以下几个理由：
· 存储过程被数据库编译过。这样可以产生一个“执行计划”，因此数据库确切地知道它将做什么，从而加快了过程的执行速度。
· 存储过程通常被数据库高速缓存，这样使它们运行得更快，因为此时不需要从磁盘中读取它们。并非所有的数据库都支持这种缓存机制，比如微软的access就不支持，而sql server却支持。
· 通过指定数据库中的表只能被存储过程修改，可以确保数据更安全。这意味着具有潜在危险的sql操作不会执行。
· 可以避免将asp代码和冗长的sql语句混在一起，从而使asp代码更易于维护。
· 可以将所有sql代码集中存放于服务器。
· 可以在存储过程中使用输出参数，允许返回记录集或其他的值。
一般说来，存储过程几乎总是比相当的sql语句执行速度快。
为了使用存储过程，只要将存储过程的名字作为命令文本，并设置相应的类型。例如，考虑前面更新书价的例子。如果在sql server上创建一个存储过程，可以编写代码：
create procedure usp_updateprices
as
update titles
set price = price * 1.10
where type=business
对于微软的access数据库，可以使用一个简单的更新查询语句完成相同的任务，如图9-1所示：
图9-1 使用微软的access数据库完成更新查询
要在asp网页中运行该存储过程，只需要使用以下代码：
set cmdupdate = server.createobject("adodb.command")
cmdupdate.activeconnection = strconn
cmdupdate.commandtext = "usp_updateprices"
cmdupdate.commandtype = adcmdstoredproc
cmdupdate.execute , , adexecutenorecords
这只是运行存储过程。没有记录集返回，因为只是在更新数据。需要记住的是，除非确实需要，不要创建记录集。
虽然这样做也可以，但并不是很灵活，因为仅仅处理一种类型的书。更好的做法是创建一个允许我们选择书类型的过程，这样就不必为每类书创建一个过程。同样也可去掉固定的10%更新，这样使得灵活性更好。那么，如何才能做到这一点呢，很简单，使用参数。
1． 参数
存储过程的参数（或变量）与一般的过程和函数的参数一样，可以传到函数内部，然后函数可以使用它的值。sql server（其他数据库也一样，包括access）中的存储过程都具有这样的功能。
为了使存储过程能处理多种类型的书，甚至允许用户指定价格的增加（或减少），需要增加一些参数：
create procedure usp_updateprices
@type char(12),
@percent money
as
update titles
set price = price * (1 + @percent / 100)
where type = @type
现在，存储过程usp_updateprices带有两个参数：
· 一个是书的类型（@type）。
· 一个是书价变化的百分比（@percent）。
与vbscript的函数一样，这些参数都是变量。然而，与vbscript和其他脚本语言不同的是：在这些脚本语言中的变量都是variant类型，而sql变量具有确定的类型(char、money等等)。必须遵守sql变量的命名规范，即变量必须以符号@开始。
注意，我们让百分数作为一个整数（如10代表10%），而不是作为一个分数值传入此过程。这只是让存储过程变得更直观一些。
2． parameters集合
那么，现在有了带参数的存储过程，但如何通过ado来调用它呢？我们已经见到了如何用command对象调用不带参数的存储过程，实际上，它们之间并没有什么不同。不同之处在于parameters集合的使用。
parameters集合包含存储过程中每个参数的parameter对象。然而，ado并不会自动地知道这些参数是什么，因此，必须用createparameter方法创建它们，采用下面的形式：
set parameter = command.createparameter (name, [type], [direction], [size], [value])
参数及说明如表9-3所示：
表9-3 createparameter方法的参数及说明
参 数
说 明
name
参数名。这是parameters集合中的参数名，不是存储过程中的参数名。然而，使用相同的名字是一个好的做法
type
参数的数据类型。可以是一个addatatype常数，详见附录
direction
参数的方向，指明是参数向存储过程提供信息，还是存储过程向ado返回信息。可以是下面的值之一：
adparaminput，参数是传给存储过程的输入参数
adparamoutput，参数是从存储过程检索出的输出参数
adparaminputoutput，参数可同时作为输入和输出参数
adparamreturnvalue，该参数包含存储过程返回的状态
size
参数长度。对于固定长度的类型，比如整型，该值可以忽略
value
参数的值
一旦创建了参数就可以将其追加到parameters集合中，例如：
set parvalue = cmdupdate.createparameter("@type", advarwchar, adparaminput, _
12, "business")
cmdupdate.parameters.append parvalue
set parvalue = cmdupdate.createparameter("@percent", adcurrency, _
adparaminput, , 10)
cmdupdate.parameters.append parvalue
没有必要显式地创建一个对象去保存参数，缺省的variant类型已经可以工作得相当好。如果不想创建一个变量，也可以走捷径，例如下面的代码：
cmdupdate.parameters.append = _
cmdupdate.createparameter("@percent", adcurrency, adparaminput, , 10)
这使用createparameter方法返回一个parameter对象，并用append方法接收它。这种方法比使用变量运行得快，却加长了代码行，可读性比较差。可以根据自己的爱好选择其中一种方法。
参数加到parameters集合后，就保留在其中，因此，不一定在创建参数时就为每个参数赋值。可以在命令运行前的任何时候设置参数的值。例如：
cmdupdate.parameters.append = _
cmdupdate.createparameter("@percent", adcurrency, adparaminput)
cmdupdate.parameters("@percent") = 10
前一章提到了访问集合中的值有好几种方法，parameters集合并没有什么不同。上面的例子使用参数的名字在集合中检索参数，也可以使用索引号进行检索：
cmdupdate.parameters(0) = 10
以上代码对参数集合中第一个（parameters集合从
存储过程的使用是command对象得到应用的一个领域。存储过程（有时也称存储查询）是存储在中预先定义的sql查询语句。
为什么应该创建和使用存储过程而不是在代码中直接使用sql字符串呢？主要有以下几个理由：
· 存储过程被数据库编译过。这样可以产生一个“执行计划”，因此数据库确切地知道它将做什么，从而加快了过程的执行速度。
· 存储过程通常被数据库高速缓存，这样使它们运行得更快，因为此时不需要从磁盘中读取它们。并非所有的数据库都支持这种缓存机制，比如微软的access就不支持，而sql server却支持。
· 通过指定数据库中的表只能被存储过程修改，可以确保数据更安全。这意味着具有潜在危险的sql操作不会执行。
· 可以避免将asp代码和冗长的sql语句混在一起，从而使asp代码更易于维护。
· 可以将所有sql代码集中存放于服务器。
· 可以在存储过程中使用输出参数，允许返回记录集或其他的值。
一般说来，存储过程几乎总是比相当的sql语句执行速度快。
为了使用存储过程，只要将存储过程的名字作为命令文本，并设置相应的类型。例如，考虑前面更新书价的例子。如果在sql server上创建一个存储过程，可以编写代码：
create procedure usp_updateprices
as
update titles
set price = price * 1.10
where type=business
对于微软的access数据库，可以使用一个简单的更新查询语句完成相同的任务，如图9-1所示：
图9-1 使用微软的access数据库完成更新查询
要在asp网页中运行该存储过程，只需要使用以下代码：
set cmdupdate = server.createobject("adodb.command")
cmdupdate.activeconnection = strconn
cmdupdate.commandtext = "usp_updateprices"
cmdupdate.commandtype = adcmdstoredproc
cmdupdate.execute , , adexecutenorecords
这只是运行存储过程。没有记录集返回，因为只是在更新数据。需要记住的是，除非确实需要，不要创建记录集。
虽然这样做也可以，但并不是很灵活，因为仅仅处理一种类型的书。更好的做法是创建一个允许我们选择书类型的过程，这样就不必为每类书创建一个过程。同样也可去掉固定的10%更新，这样使得灵活性更好。那么，如何才能做到这一点呢，很简单，使用参数。
1． 参数
存储过程的参数（或变量）与一般的过程和函数的参数一样，可以传到函数内部，然后函数可以使用它的值。sql server（其他数据库也一样，包括access）中的存储过程都具有这样的功能。
为了使存储过程能处理多种类型的书，甚至允许用户指定价格的增加（或减少），需要增加一些参数：
create procedure usp_updateprices
@type char(12),
@percent money
as
update titles
set price = price * (1 + @percent / 100)
where type = @type
现在，存储过程usp_updateprices带有两个参数：
· 一个是书的类型（@type）。
· 一个是书价变化的百分比（@percent）。
与vbscript的函数一样，这些参数都是变量。然而，与vbscript和其他脚本语言不同的是：在这些脚本语言中的变量都是variant类型，而sql变量具有确定的类型(char、money等等)。必须遵守sql变量的命名规范，即变量必须以符号@开始。
注意，我们让百分数作为一个整数（如10代表10%），而不是作为一个分数值传入此过程。这只是让存储过程变得更直观一些。
2． parameters集合
那么，现在有了带参数的存储过程，但如何通过ado来调用它呢？我们已经见到了如何用command对象调用不带参数的存储过程，实际上，它们之间并没有什么不同。不同之处在于parameters集合的使用。
parameters集合包含存储过程中每个参数的parameter对象。然而，ado并不会自动地知道这些参数是什么，因此，必须用createparameter方法创建它们，采用下面的形式：
set parameter = command.createparameter (name, [type], [direction], [size], [value])
参数及说明如表9-3所示：
表9-3 createparameter方法的参数及说明
参 数
说 明
name
参数名。这是parameters集合中的参数名，不是存储过程中的参数名。然而，使用相同的名字是一个好的做法
type
参数的数据类型。可以是一个addatatype常数，详见附录
direction
参数的方向，指明是参数向存储过程提供信息，还是存储过程向ado返回信息。可以是下面的值之一：
adparaminput，参数是传给存储过程的输入参数
adparamoutput，参数是从存储过程检索出的输出参数
adparaminputoutput，参数可同时作为输入和输出参数
adparamreturnvalue，该参数包含存储过程返回的状态
size
参数长度。对于固定长度的类型，比如整型，该值可以忽略
value
参数的值
一旦创建了参数就可以将其追加到parameters集合中，例如：
set parvalue = cmdupdate.createparameter("@type", advarwchar, adparaminput, _
12, "business")
cmdupdate.parameters.append parvalue
set parvalue = cmdupdate.createparameter("@percent", adcurrency, _
adparaminput, , 10)
cmdupdate.parameters.append parvalue
没有必要显式地创建一个对象去保存参数，缺省的variant类型已经可以工作得相当好。如果不想创建一个变量，也可以走捷径，例如下面的代码：
cmdupdate.parameters.append = _
cmdupdate.createparameter("@percent", adcurrency, adparaminput, , 10)
这使用createparameter方法返回一个parameter对象，并用append方法接收它。这种方法比使用变量运行得快，却加长了代码行，可读性比较差。可以根据自己的爱好选择其中一种方法。
参数加到parameters集合后，就保留在其中，因此，不一定在创建参数时就为每个参数赋值。可以在命令运行前的任何时候设置参数的值。例如：
cmdupdate.parameters.append = _
cmdupdate.createparameter("@percent", adcurrency, adparaminput)
cmdupdate.parameters("@percent") = 10
前一章提到了访问集合中的值有好几种方法，parameters集合并没有什么不同。上面的例子使用参数的名字在集合中检索参数，也可以使用索引号进行检索：
cmdupdate.parameters(0) = 10
以上代码对参数集合中第一个（parameters集合从