VB调用ORACLE存储过程.doc

在ADO中调用存储过程一直是一个困扰大家的问题。其实，关于ADO调用存储过程的 方法在很多书中都有讲到，标准的做法无非是按照以下步骤进行： 1、生成并初始化一个_CommandPtr对象； 2、生成调用存储过程需要的参数，这些参数都是_ParameterPtr对象； 3、按照顺序将使用_CommandPtr的Append方法为存储过程提供参数（包括输入参数 和输出参数）； 4、为_CommandPtr对象指定需要使用的ADO连接； 5、使用_CommandPtr的Execute方法调用存储过程； 6、从结果中获取返回参数的值（如果有的话）。 具体的过程在此我不详细描述，我想看看本文附带的代码就应该很明白了。 在这里我想就我使用ADO调用存储过程时的一些体会说明一下。 1、关于CreateParameter函数 该函数的原型为：CreateParameter (Name, Type, Direction, Size, Value) 其中Name是参数的名称，可以指定也可以不指定； Type是一个DataTypeEnum值，指定参数的类别，取值有adInteger（整型）、adChar（字符/字符串型）等； Direction是一个ParameterDirectionEnum值，其取值为adParamInput、adParamInputOutput、 adParamOutput、adParamReturnValue、adParamUnknown； Size是一个Long类型的值，指示该参数值以字节计算的最大长度，例如对int型，该值可以取为sizeof(int)， 对Long型，该值可以取为sizeof(long)，对字符串型，可以使用该字符串的长度； Value是一个variant类型的值，是该参数的取值。 在这里需要注意的是，Type参数、Direction参数以及Size参数一定要和存储过程定义时的参数相吻合， 例如，如果有下面一个存储过程 CREATE PROCEDURE SMS_Proc_Handle_All (UserID Integer, SourAddr Varchar(15), DestAddr varchar(5000), AvValue Single output, ReturnInfo varchar(100) output ) 则Type参数的取值依次为adInteger、adChar、adChar、adSingle，adChar； Direction参数的取值依次为adParameterIn、adParameterIn、adParameterIn、adParameterOut、adParameterOut； 对于输入参数，Size的值可以根据实际数值来定，对于输出参数，最好是根据定义确定（上例中ReturnInfo参数的 Size值可以取为100）。CreateParameter 方法创建具有指定属性的新的 Parameter 对象。语法Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)返回值返回 Parameter 对象。参数Name 可选。String 值，包含 Parameter 对象的名称。 Type 可选。DataTypeEnum 值，指定 Parameter 对象的数据类型。 Direction 可选。ParameterDirectionEnum 值，指定 Parameter 对象的类型。 Size 可选。Long 值，指定参数值的最大长度（以字符或字节为单位）。 Value 可选。Variant，指定 Parameter 对象的值。 说明使用 CreateParameter 方法创建具用指定的名称、类型、方向、大小和值的新的 Parameter 对象。在参数中传送的任何值都将写入相应的 Parameter 属性。此方法不会将 Parameter 对象自动追加到 Command 对象的 Parameters 集合。这样就可以设置附加属性，在将 Parameter 对象追加到集合时，ADO 将使这些附加属性的值生效。如果在 Type 参数中指定变长数据类型，在将其追加到 Parameters 集合之前必须传送 Size 参数或者设置 Parameter 对象的 Size 属性；否则将发生错误。如果在 Type 参数中指定数字型数据类型（adNumeric 或 adDecimal），也必须设置 NumericScale 和 Precision 属性。DataTypeEnum指定 Field、Parameter 或 Property 的数据类型。在下表的“说明”列的括号中显示了相应的 OLE DB 类型指示符。有关 OLE DB 数据类型的详细信息，请参阅 OLE DB Programmers Reference 中的 Chapter 13 和附录 A 。常量值说明AdArray（不适用于 ADOX。）0x2000一个标志值，通常与另一个数据类型常量组合，指示该数据类型的数组。 adBigInt20指示一个八字节的有符号整数 (DBTYPE_I8)。adBinary128指示一个二进制值 (DBTYPE_BYTES)。adBoolean11指示一个布尔值 (DBTYPE_BOOL)。adBSTR8指示以 Null 终止的字符串 (Unicode) (DBTYPE_BSTR)。adChapter136指示一个四字节的子集值，标识子行集合中的行 (DBTYPE_HCHAPTER)。adChar129指示一个字符串值 (DBTYPE_STR)。adCurrency6指示一个货币值 (DBTYPE_CY)。货币是一个定点数字，小数点右侧有四位数字。该值存储为八字节、范围为 10,000 的有符号整数。adDate7指示日期值 (DBTYPE_DATE)。日期保存为双精度数，数字的整数部分是从 1899 年 12 月 30 日算起的天数，小数部分是一天当中的片段时间。adDBDate133指示日期值 (yyyymmdd) (DBTYPE_DBDATE)。adDBTime134指示时间值 (hhmmss) (DBTYPE_DBTIME)。adDBTimeStamp135指示日期/时间戳（yyyymmddhhmmss 加十亿分之一的小数）(DBTYPE_DBTIMESTAMP)。adDecimal14指示具有固定精度和范围的确切数字值 (DBTYPE_DECIMAL)。adDouble5指示一个双精度浮点值 (DBTYPE_R8)。adEmpty0指定没有值 (DBTYPE_EMPTY)。adError10指示一个 32 位的错误代码 (DBTYPE_ERROR)。adFileTime64指示一个 64 位的值，表示从 1601 年 1 月 1 日开始的 100 个十亿分之一秒间隔的数量 (DBTYPE_FILETIME)。adGUID72指示全局唯一标识符 (GUID) (DBTYPE_GUID)。adIDispatch9指示指向 COM 对象上 IDispatch 接口的指针 (DBTYPE_IDISPATCH)。 注意ADO 目前不支持这种数据类型。使用它可能导致不可预料的结果。adInteger3指示一个四字节的有符号整数 (DBTYPE_I4)。adIUnknown13指示指向 COM 对象上 IUnknown 接口的指针 (DBTYPE_IUNKNOWN)。 注意ADO 目前不支持这种数据类型。使用它可能导致不可预料的结果。adLongVarBinary205指示一个长二进制值。adLongVarChar201指示一个长字符串值。adLongVarWChar203指示一个以 Null 终止的长 Unicode 字符串值。adNumeric131指示具有固定精度和范围的确切数字值 (DBTYPE_NUMERIC)。adPropVariant138指示一个 Automation PROPVARIANT (DBTYPE_PROP_VARIANT)。adSingle4指示一个单精度浮点值 (DBTYPE_R4)。adSmallInt2指示一个双字节的有符号整数 (DBTYPE_I2)。adTinyInt16指示一个单字节的有符号整数 (DBTYPE_I1)。adUnsignedBigInt21指示一个八字节的无符号整数 (DBTYPE_UI8)。adUnsignedInt19指示一个四字节的无符号整数 (DBTYPE_UI4)。adUnsignedSmallInt18指示一个双字节的无符号整数 (DBTYPE_UI2)。adUnsignedTinyInt17指示一个单字节的无符号整数 (DBTYPE_UI1)。adUserDefined132指示一个用户定义的变量 (DBTYPE_UDT)。adVarBinary204指示一个二进制值（仅限于 Parameter 对象）。adVarChar200指示一个字符串值。adVariant12指示一个 Automation Variant (DBTYPE_VARIANT)。 注意ADO 目前不支持这种数据类型。使用它可能导致不可预料的结果。adVarNumeric139指示一个数字值（仅限于 Parameter 对象）。adVarWChar202指示一个以 Null 终止的 Unicode 字符串。adWChar130指示一个以 Null 终止的 Unicode 字符串 (DBTYPE_WSTR)。你可以使用 ADODB.Command 来调用存储过程，如： Dim adoComm As Object / 创建一个对象，我们用来调用存储过程 Set adoComm = CreateObject(ADODB.Command) With adoComm / 设置连接，假设 adoConn 为已经连接的 ADODB.Connection 对象 .ActiveConnection = adoConn / 类型为存储过程，adCmdStoredProc = 4 .CommandType = 4 / 存储过程名称 .CommandText = 你的存储过程名称 / 设置输入参数 .Parameters.Item(输入参数).Value = 值 / 执行存储过程 .Execute If .Parameters.Item(返回参数名称).Value = True Then Else End If End With / 释放对象 Set adoComm = NothingDim adocomm As New ADODB.CommandDim prmRetVal As ADODB.ParameterDim prmId As ADODB.Parameteradocomm.ActiveConnection = connadocomm.CommandType = adCmdStoredProcadocomm.CommandText = sp_del_office_byID Set prmRetVal = adocomm.CreateParameter(Param0, adInteger, adParamReturnValue, 0) Set prmId = adocomm.CreateParameter(OfficeID, adInteger, adParamInput, 0) adocomm.Parameters.Append prmRetVal adocomm.Parameters.Append prmId adocomm.Parameters(1).Value = 0 adocomm.Execute MsgBox adocomm.Parameters(0).Value1. 这也是最简单的方法,两个输入参数，无返回值： set connection = server.createobject(adodb.connection) connection.open someDSN Connection.Execute procname varvalue1, varvalue2 将所有对象清为nothing，释放资源 connection.close set connection = nothing 2. 如果要返回 Recordset 集: set connection = server.createobject(adodb.connection) connection.open someDSN set rs = server.createobject(adodb.recordset) rs.Open Exec procname varvalue1, varvalue2,connection 将所有对象清为nothing，释放资源 rs.close connection.close set rs = nothing set connection = nothing 3. 以上两种方法都不能有返回值，（Recordset除外），如果要得到返回值，需要用Command的方法。 首先说明，返回值有两种。一种是在存储过程中直接return一个值，就象C和VB的函数返回值那样；另一种是可以返回多个值，存 储这些值的变量名称需要在调用参数中先行指定。 这个例子要处理多种参数，输入参数，输出参数，返回记录集以及一个直接返回值（够全了吧？） 存储过程如下： use pubs GO - 建立存储过程 create procedure sp_PubsTest - 定义三个参数变量，注意第三个，特别标记是用于输出 au_lname varchar (20), intID int, intIDOut int OUTPUT AS SELECT intIDOut = intID + 1 SELECT * FROM authors WHERE au_lname LIKE au_lname + % -直接返回一个值 RETURN intID + 2 调用该存储过程的asp程序如下： % Dim CmdSP Dim adoRS Dim adCmdSPStoredProc Dim adParamReturnValue Dim adParaminput Dim adParamOutput Dim adInteger Dim iVal Dim oVal Dim adoField Dim adVarChar 这些值在 VB 中是预定义常量，可以直接调用，但在 VBScript 中没有预定义 adCmdSPStoredProc = 4 adParamReturnValue = 4 adParaminput = 1 adParamOutput = 2 adInteger = 3 adVarChar = 200 iVal = 5 oVal = 3 建一个command对象 set CmdSP = Server.CreateObject(ADODB.Command) 建立连结 CmdSP.ActiveConnection = Driver=SQL Server;server=(local);Uid=sa;Pwd=;Database=Pubs 定义command 对象调用名称 CmdSP.CommandText = sp_PubsTest 设置command调用类型是存储过程 (adCmdSPStoredProc = 4) CmdSP.CommandType = adCmdSPStoredProc 往command 对象中加参数 定义存储过程有直接返回值，并且是个整数，省缺值是4 CmdSP.Parameters.Append CmdSP.CreateParameter(RETURN_VALUE, adInteger, adParamReturnValue, 4) 定义一个字符型输入参数 CmdSP.Parameters.Append CmdSP.CreateParameter(au_lname, adVarChar, adParaminput, 20, M) 定义一个整型输入参数 CmdSP.Parameters.Append CmdSP.CreateParameter(intID, adInteger, adParamInput, , iVal) 定义一个整型输出参数 CmdSP.Parameters.Append CmdSP.CreateParameter(intIDOut, adInteger, adParamOutput, oVal) 运行存储过程，并得到返回记录集 Set adoRS = CmdSP.Execute 把每个记录打印出来，其中的字段是虚拟的，可以不用管 While Not adoRS.EOF for each adoField in adoRS.Fields Response.Write adoField.Name & = & adoField.Value & & vbCRLF Next Response.Write adoRS.MoveNext Wend 打印两个输出值： Response.Write intIDOut = “ & CmdSP.Parameters(intIDOut).Value & Response.Write Return value = & CmdSP.Parameters(RETURN_VALUE).Value & 大扫除 Set adoRS = nothing Set CmdSP.ActiveConnection = nothing Set CmdSP = nothing % 在asp中调用sql server的存储过程可以加快程序运行速度 1.调用存储过程的一般方法 先假设在sql server中有一存储过程dt_users： CREATE PROCEDURE dbo.dt_users AS select * from users return GO 第一种方法是不利用command对象，直接用recordset对象 set rs=server.createobject(adodb.recordset) sql=exec dt_users rs.open sql,conn,1,1 这样就可 第二种方法是利用command对象 set comm=server.createobject(mand) mantype=4 set comm.activeconnection=conn mandtext=dbo.dt_users set rs=server.c