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