VB实现SQLServer2000存储过程调用

VB 实现 SQL Server 2000 存储过程调用 摘要：本文以 SQL Server2000存储过程为例，探讨了存储过程的优点，并以 Visual Basic6.0程序设计语言 给出了调用存储过程的关键代码。 关键字：数据库；SQL Server2000；存储过程；应用程序；VB 存储过程 存储过程是存储在服务器上的一组预编译的 Transact-SQL语句，是一种封装重复任务操作的，支 持用户提供的变量，具有强大的编程功能。它类似于 DOS系统中的 BAT文件。在 BAT文件中，可以包含 一组经常执行的命令，这组命令通过 BAT文件的执行而被执行。同样的道理，可以把要完成某项任务的许 多 Transact-SQL语句写在一起，组织成存储过程的形式，通过执行该存储过程就可以完成这项任务。存储 过程与 BAT文件又有差别，即存储过程已经进行了预编译。 1、创建存储过程的方法 在 Transact-SQL语言中，创建存储过程可以使用 CREATE PROCEDURE语句，其语法形式如下： CREATE PROC[EDURE] procedure_name[;number] [{@parameter data_type}[VARYING][=default][OUTPUT] ]],…n] [WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement[…n] 在上面的 CREATE PROCEDURE语句中，方括号"[ ]"中的内容是可选的，花括号"{}"中的内容是必须 出现的，不能省略，[，…n]示前面的参数样式，可以重复出现。竖线"|"表示两边的选项可以任选一个。 下面该语句中各种选项的含义。 CREATE PROCEDURE是关键字，也可以写成 CREATE PROC。 procedure_name是该存储过程的名称，名称可以是任何符合命名规则的标示符。名称后的 [；number] 参数表示可以定义一系列的存储过程名称，这些存储过程的数量由 number指定。 参数名称可以使用@parameter data_type来指定。在 Transact-SQL语言中，用户定义的参数名称前面加 "@"符号，这些数据类型是 Transact-SQL语言允许的各种数据类型，包括系统提供的数据类型和用户定义 的数据类型。 当参数类型为 cursor时，必须使用关键字 VARYING和 OUTPUT。VARYING表示结果集可以是一个 输出参数，其内容是动态的。该关键字只能在使用游标作为数据类型时使用。关键字 OUTPUT表示这是一 个输出参数，可以把存储过程执行的结果信息返回应用程序。 default用于指定参数的默认值。 RECOMPILE选项表示重新编译该存储过程。该选项只是在需要的时候才使用，例如经常需要改变数 据库模式时。 ENCRYPTION选项用来加密创建存储过程的文本，防止他人查看。 选项 FOR REPLICATION主要用于复制过程中。注意，该选项不能和选项 RECOMPILE同时使用。 AS是一个关键字，表示其后的内容是存储过程的语句。参数 sql-statement[…n]表示在一个存储过程中 可以包含多个 Transact-SQL语句。 2、存储过程的优点 在频繁访问数据库的系统中，开发者都乐于使用存储过程，这与存储过程的下列优点是分不开的。 ⑴ 存储过程可以与其他应用程序共享应用程序的逻辑，从而确保一致的数据访问和操纵。 ⑵ 存储过程提供了一种安全机制。如果用户被授予执行存储过程权限，那么即使该用户没有访问在执 行该存储过程中所参考的表或视图的权限，该用户也可以完全执行该存储过程而不受到影响。因此，可以 创建存储过程来完成所有的增加、删除等操作，并且可以通过编程控制上述操作中对信息的访问权限。 ⑶ 存储过程执行速度快，便于提高系统的性能。由于存储过程在第一次执行之后，其执行规划就驻存 在过程高速缓冲存储区中，在以后的操作中，只需从过程高速缓冲存储区中调用编译好的二进制形式存储 过程来执行。 ⑷ 使用存储过程可以减少网络传输时间。如果有一千条 Transact-SQL语句的命令，一条一条地通过 网络在客户机和服务器之间传送，那么这种传输所耗费的时间将很长。但是，如果把这一千条 Transact-SQL 语句的命令写成一条较为复杂的存储过程命令，这时在客户机和服务器之间网络传输所需的时间就会大大 减少。 SQLSQLSQLSQL ServerServerServerServer 2000200020002000数据库存储过程的调用 VB作为当今应用极为普遍的数据库客户端开发工具之一，对客户端应用程序调用服务器端存储过程 提供了强大的支持。特别是随着 VB6.0的推出，VB客户端应用程序可以方便地利用 ADO的对象和集合来 实现对数据库存储过程的调用。 在笔者编写的科技档案管理系统中，就是采用 VB作为开发平台，采用 SQL Server2000数据库管理数 据，在这个科技档案管理系统中有海量的数据，并且对数据库有频繁的访问，利用存储过程访问数据库节 省了执行时间，大大提高了系统的性能。 1、ADO简介 ADO控件（也称为 ADO Data控件）与 VB固有的 Data控件相似。使用 ADO Data控件，可以利用 Microsoft ActiveX Data Objects（ADO）快速建立数据库绑定控件和数据提供者之间的连接。 ADO Data控件可以实现以下功能： ·连接一个本地数据库或远程数据库。 ·打开一个指定的数据库表，或定义一个基于结构化查询语言（SQL）的查询、存储过程或该数据库中 的表的视图的记录集合。 ·将数据字段的数值传递给数据绑定控件，可以在这些控件中显示或更改这些数值。 ·添加新的记录，或根据更改显示在绑定的控件中的数据来更新一个数据库。 2、数据库的连接 数据库的连接可通过 ADO控件实现，为此，必须在工程部件中选择 Microsoft ADO Data Control 6.0 (OLEDB)，然后在窗体中添加 ADO控件。利用 ADO连接数据库有两种方法，具体如下。 1) 通过 ADODC属性页实现连接 在 ADODC属性页中选择生成按钮，进入数据链接属性对话框；然后选择该对话框中的连接属性页， 选择或输入服务器名称和数据库等重要信息；最后测试连接，连接成功后，按确定按钮，返回到属性页对 话框，可获得连接字符串，如下例： Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Science_File;Data Source=Data_Server 其中 sa是用户名；Science_File是数据库名；Data_Server是数据库名。 通过下列语句，即可连接到指定的数据库： dim odbcstr as String, adocon As New ADODB.Connection odbcstr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Science_File;Data Source=Data_Server" adocon.Open odbcstr '连接到数据库 2) 直接使用连接语句实现 连接数据库的语句如下： Dim ado as ADODC ado.ConnectionString = "Provider=SQLOLEDB.1;Password=" & User_Pwd & ";Persist Security Info=True;User ID=" & User_Name & ";Initial Catalog=" & Data_Name & ";Data Source=" & server_name 其中 User-Pwd是用户密码；User_Name是用户名；Data_Name是数据库名；server_name是服务器名。 连接数据库成功后就可以调用存储过程执行操作。 3、存储过程的调用 假设有一个名为 doc_ProcName存储过程，该存储过程有一个输入参数，一个输出参数。 1) 直接传递参数调用存储过程 直接传递参数方法主要通过以下几个步骤来实现： （1） 通过 ADODB的 Connection对象打开与数据源的连接； （2） 通过 ActiveConnection指定 Command对象当前所属的 Connection对象； （3） 通过 CommandText属性设置 Command对象的源，即要调用的存储过程； （4） 通过 CommandType属性确定 Command对象的源类型，如果源类型为存储过程 CommandType 即为 adCmdStoredProc； （5） 通过 Command对象的 Parameters集合向所调用的存储过程传递参数，其中对象 Parameters（0） 为执行存储过程的返回值，返回值为 0则执行存储过程成功； （6） 通过 Eexecute方法执行在 CommandText 属性中指定的存储过程。 以存储过程 doc_ProcName为例，关键代码如下： Dim strS As String '定义一变量 Dim adoconn As New ADODB.Connection 'Connection 对象代表了打开与数据源的连接。 Dim adocomm As New ADODB.Command 'Command 对象定义了将对数据源执行的指定命 令。 Dim ReturnValue As Integer '调用存储过程的返回值 adoconn.ConnectionString = Adodc1.ConnectionString 'Adodc1为窗体中的 ADO 控件，并已 成功连接数据库 adoconn.Open Set adocomm.ActiveConnection = adoconn '指示指定的 Command 对象当前所属的 Connection对象。 adocomm.CommandText = "doc_ProcName" '设置 Command对象源。 adocomm.CommandType = adCmdStoredProc '通知提供者 CommandText属性有什么，它可 能包括 Command对象的源类型。设置这个属性优化了该命令的执行。 adocomm.Parameters(1) = "1" adocomm.Parameters(2) = "OutputParameters" 'OutputParameters可以为任意的字符串或数 字 adocomm.Execute ReturnValue = adocomm.Parameters(0) '存储过程的返回值，返回0则成功执行。 strS = adocomm.Parameters(2) '把存储过程的输出参数的值赋给变量 strS 2) 追加参数法调用存储过程 追加参数通过 CreateParameter方法，用来指定属性创建新的 Parameter对象。具体语法如下： Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value) ·Name 可选，字符串，代表 Parameter 对象名称。 ·Type 可选，长整型值，指定 Parameter 对象数据类型。 ·Direction 可选，长整型值，指定 Parameter 对象类型。 ·Size 可选，长整型值，指定参数值最大长度（以字符或字节数为单位）。 ·Value 可选，变体型，指定 Parameter 对象值。 这种方法与上面一种方法的分别主要在于，追加参数的方法在向存储过程传递参数时，这种方法首先 通过 CreateParameter方法为存储过程创建参数，然后通过 Append方法将创建的参数追加到 Parameters集 合中去。 仍然以存储过程 doc_ProcName的调用为例，关键代码如下： Dim mRst As ADODB.Recordset 'Recordset 对象表示的是来自基本表或命令执行结果的记录 全集。 Dim prm As ADODB.Parameter 'Parameter 对象代表参数或与基于参数化查询或存储过程的 Command 对象相关联的参数。 adoconn.ConnectionString = Adodc1.ConnectionString adoconn.Open Set adocomm.ActiveConnection = adoconn adocomm.CommandText = "doc_ProcName" adocomm.CommandType = adCmdStoredProc Set prm = adocomm.CreateParameter("parameter1", adTinyInt, adParamInput, , "1") adocomm.Parameters.Append prm Set prm = adocomm.CreateParameter("parameter2", adInteger, adParamOutput) adocomm.Parameters.Append prm Set mRst = adocomm.Execute ReturnValue = adocomm.Parameters(0) 以上代码中未定义的变量以及未注释的语句与前述相同。