使用SQL存儲(chǔ)過程有什么好處
■SQL存儲(chǔ)過程執(zhí)行起來比SQL命令文本快得多。當(dāng)一個(gè)SQL語句包含在存儲(chǔ)過程中時(shí)，服務(wù)器不必每次執(zhí)行它時(shí)都要分析和編譯它。
■調(diào)用存儲(chǔ)過程，可以認(rèn)為是一個(gè)三層結(jié)構(gòu)。這使你的程序易于維護(hù)。如果程序需要做某些改動(dòng)，你只要改動(dòng)存儲(chǔ)過程即可
■你可以在存儲(chǔ)過程中利用Transact-SQL的強(qiáng)大功能。一個(gè)SQL存儲(chǔ)過程可以包含多個(gè)SQL語句。你可以使用變量和條件。這意味著你可以用存儲(chǔ)過程建立非常復(fù)雜的查詢，以非常復(fù)雜的方式更新數(shù)據(jù)庫。
■最后，這也許是最重要的，在存儲(chǔ)過程中可以使用參數(shù)。你可以傳送和返回參數(shù)。你還可以得到一個(gè)返回值（從SQL RETURN語句）。
環(huán)境:WinXP+VB6+sp6+SqlServer2000
數(shù)據(jù)庫: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
存儲(chǔ)過程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
存儲(chǔ)過程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環(huán)境中，添加DataGrid控件，4個(gè)按鈕，6個(gè)文本框
代碼簡單易懂。
‘引用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
'這里用第一種方法使用存儲(chǔ)過程添加數(shù)據(jù)
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,會(huì)發(fā)生一個(gè)精度無效的錯(cuò)誤
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
'這里用第二種方法使用存儲(chǔ)過程添加數(shù)據(jù)
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
'這里用第三種方法使用連接對象來插入數(shù)據(jù)
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
'利用存儲(chǔ)過程顯示數(shù)據(jù)
‘要處理多種參數(shù)，輸入?yún)?shù)，輸出參數(shù)以及一個(gè)直接返回值
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
'輸入?yún)?shù)
Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))
cmd.Parameters.Append param
'輸出參數(shù)
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
'連接數(shù)據(jù)庫
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 '設(shè)置為客戶端
mConn.Open
End Sub
'關(guān)閉數(shù)據(jù)連接
Private Sub Form_Unload(Cancel As Integer)
mConn.Close
Set mConn = Nothing
End Sub
