|
使用SQL存储过程有什么好处 ■SQL存储过程执行起来比SQL命令文本快得多。当一个SQL语句包含在存储过程中时，服务器不必每次执行它时都要分析和编译它。 ■调用存储过程，可以认为是一个三层结构。这使你的程序易于维护。如果程序需要做某些改动，你只要改动存储过程即可 ■你可以在存储过程中利用Transact-SQL的强大功能。一个SQL存储过程可以包含多个SQL语句。你可以使用变量和条件。这意味着你可以用存储过程建立非常复杂的查询，以非常复杂的方式更新数据库。 ■最后，这也许是最重要的，在存储过程中可以使用参数。你可以传送和返回参数。你还可以得到一个返回值（从SQL RETURN语句）。 环境:WinXP+VB6+sp6+SqlServer2000 数据库:test 表:Users
| CREATE TABLE [dbo].[users] ( [id] [int] IDENTITY (1, 1) NOT NULL , [truename] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [regname] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [pwd] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [email] [text] COLLATE Chinese_PRC_CI_AS NULL , [jifen] [decimal](18, 2) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [id] |
| ) ON [PRIMARY] GO 存储过程select_users CREATE PROCEDURE select_users @regname char(20), @numrows int OUTPUT AS Select * from users SELECT @numrows = @@ROWCOUNT if @numrows = 0 return 0 else return 1 GO 存储过程insert_users CREATE PROCEDURE insert_users @truename char(20), @regname char(20),@pwd char(20),@sex char(20),@email char(20),@jifen decimal(19,2) AS insert into users(truename,regname,pwd,sex,email,jifen) values(@truename,@regname,@pwd,@sex,@email,@jifen) GO |
在VB环境中，添加DataGrid控件，4个按钮，6个文本框 代码简单易懂。 ‘引用microsoft active data object 2.X library
| Option Explicit Dim mConn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim rs3 As ADODB.Recordset |
| Dim rs4 As ADODB.Recordset Dim cmd As ADODB.Command Dim param As ADODB.Parameter |
'这里用第一种方法使用存储过程添加数据
| Private Sub Command1_Click() Set cmd = New ADODB.Command Set rs1 = New ADODB.Recordset cmd.ActiveConnection = mConn cmd.CommandText = "insert_users" cmd.CommandType = adCmdStoredProc Set param = cmd.CreateParameter("truename", adChar, adParamInput, 20, Trim(txttruename.Text)) cmd.Parameters.Append param Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text)) cmd.Parameters.Append param Set param = cmd.CreateParameter("pwd", adChar, adParamInput, 20, Trim(txtpwd.Text)) cmd.Parameters.Append param Set param = cmd.CreateParameter("sex", adChar, adParamInput, 20, Trim(txtsex.Text)) cmd.Parameters.Append param Set param = cmd.CreateParameter("email", adChar, adParamInput, 20, Trim(txtemail.Text)) cmd.Parameters.Append param |
‘下面的类型需要注意，如果不使用adSingle,会发生一个精度无效的错误
| Set param = cmd.CreateParameter("jifen", adSingle, adParamInput, 50, Val(txtjifen.Text)) cmd.Parameters.Append param Set rs1 = cmd.Execute Set cmd = Nothing Set rs1 = Nothing End Sub |
'这里用第二种方法使用存储过程添加数据
| Private Sub Command2_Click() Set rs2 = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = mConn cmd.CommandText = "insert_users" cmd.CommandType = adCmdStoredProc cmd.Parameters("@truename") = Trim(txttruename.Text) cmd.Parameters("@regname") = Trim(txtregname.Text) cmd.Parameters("@pwd") = Trim(txtpwd.Text) cmd.Parameters("@sex") = Trim(txtsex.Text) cmd.Parameters("@email") = Trim(txtemail.Text) cmd.Parameters("@jifen") = Val(txtjifen.Text) Set rs2 = cmd.Execute Set cmd = Nothing Set rs1 = Nothing End Sub |
'这里用第三种方法使用连接对象来插入数据
| Private Sub Command4_Click() Dim strsql As String strsql = "insert_users '" & Trim(txttruename.Text) & "','" & Trim(txtregname.Text) & "','" & Trim(txtpwd.Text) & "','" & Trim(txtsex.Text) & "','" & Trim(txtemail.Text) & "','" & Val(txtjifen.Text) & "'" Set rs3 = New ADODB.Recordset |
| Set rs3 = mConn.Execute(strsql) Set rs3 = Nothing End Sub |
'利用存储过程显示数据 ‘要处理多种参数，输入参数，输出参数以及一个直接返回值
| Private Sub Command3_Click() Set rs4 = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = mConn cmd.CommandText = "select_users" cmd.CommandType = adCmdStoredProc '返回值 Set param = cmd.CreateParameter("RetVal", adInteger, adParamReturnValue, 4) cmd.Parameters.Append param '输入参数 Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text)) cmd.Parameters.Append param '输出参数 Set param = cmd.CreateParameter("numrows", adInteger, adParamOutput) cmd.Parameters.Append param Set rs4 = cmd.Execute() If cmd.Parameters("RetVal").Value = 1 Then |
| MsgBox cmd.Parameters("numrows").Value Else MsgBox "没有记录" End If MsgBox rs4.RecordCount Set DataGrid1.DataSource = rs4 DataGrid1.Refresh End Sub '连接数据库 Private Sub Form_Load() Set mConn = New Connection mConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=yang" mConn.CursorLocation = adUseClient '设置为客户端 mConn.Open End Sub '关闭数据连接 Private Sub Form_Unload(Cancel As Integer) mConn.Close Set mConn = Nothing End Sub
|
|
表情
