ASP 3.0高级编程（四十六）

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集合从0开始编号）参数进行了赋值。使用索引号比使用名字索引速度快，但很显然使用名字使代码更易读。 重点注意Parameters集合中参数的顺序必须与存储过程中参数的顺序相一致。 运行带参数的命令 一旦加入参数，就可立即运行命令，同时这些参数的值传入存储过程。现在可用一个友好的网页去更新用户选择的类型的书价。例如，假设有一个名为UpdatePrices.asp的网页，其运行时的界面如图9-2所示： 图9-2  UpdatePrices.asp网页运行时的界面 通过数据库中获取书类型的列表，可以很轻松地动态创建该页面。首先要做的是包含文件Connection.asp，该文件包含了连接字符串（保存在变量strConn中）以及对ADO常数的引用，这在前面的章节已经讨论过。 处理 SSI 文件时出错 接下来，可以创建一个窗体（在这儿不显示大量文本，仅仅用一个样本文件）。该窗体调用一个名为StoreProcedure.asp的文件。   Book Type:    现在开始编写ASP脚本从title表中读取书的类型。使用一个SQL字符串只返回唯一的书类型，然后将返回值放到HTML的OPTION标记中： 这样就有了两个简单界面。前者创建了一个供选择的项目列表，后者使用其中某个项目值更新数据。这是许多需要显示和更新数据的ASP页面的基础。 3．  传递数组参数 Parameters参数集合一般来说比较好用，但有时稍有麻烦（尤其对于新手）。好在有一种快捷方法，使用Execute方法的Parameters参数。例如，调用存储过程usp_UpdatePrices，但不使用Parameters集合。 创建一个Command对象，并同前面一样设置其属性。 ' Set cmdUpdate = Server.CreateObject("ADODB.Command") ' Set the properties of the command With cmdUpdate        .ActiveConnection = strConn        .commandText = usp_UpdatePrices"        .commandType = adCmdStroreProc 但这里正是差异所在。我们仅是通过Execute方法传递参数给存储过程，而不是创建参数并添加到集合中。        ' Execute the command        .Execute lngRecs, Array(strType, curPercent), adExecuteNoRecords End With 这里使用了Array函数，将单个变量转换为数组，以适于方法调用。这种方法当然也有缺点： · 只能使用输入参数。因为不能指定参数的类型和传递方向，而缺省为输入参数。 · 如果要多次调用存储过程，这种方法速度就比较慢，因为ADO将向数据存储询问参数的内容及数据类型。 集合方法和数组方法之间在速度上的差异非常之小，几乎可以忽略。所以，如果只有输入参数，可随便使用哪一种。实际上，人们更喜欢使用Parameters集合的方法，尽管它稍为繁琐，但是使参数的属性更加明确。 4．  输出参数 我们已经知道如何获得受命令影响的记录数，如果需要更多信息，却又不想返回一个记录集，怎么办？也许想从存储过程中返回两个或三个值，但又不想费心创建一个记录集。在这时，可以定义一个输出参数，其值由存储过程提供。 例如，对于更新书价的程序，如果想在更新之后找出最高价格，可将存储过程改成： CREATE PROCEDURE usp_UpdatePricesMax        @Type           Char(12),        @Percent              Money,        @Max            Money           OUTPUT AS BEGIN        UPDATE Titles        SET       Price = Price * (1 + @Percent / 100)        WHERE  Type = @Type        SELECT @Max = MAX(Price)        FROM    Titles END 这只是在执行更新后运行了一个简单的SELECT语句，并将值赋给输出参数。 现在可以改写StroreProcedure.asp的代码从而获取变量@MAX的值。 如果有不止一个输出参数，可用相同的方法访问。可以使用参数名或索引号取出集合中的值。