oracle 中的存储过程
SQL> CREATE OR REPLACE PROCEDURE huanzheng(
2 IN_RE_NUMBER IN VARCHAR2,
3 IN_OLD IN VARCHAR2)
4 IS
5 BEGIN
6 update duzhe set re_number=IN_RE_NUMBER
7 where re_number=IN_OLD;
8 update jieyue set re_number=IN_RE_NUMBER
9 where re_number=IN_OLD;
10 update yuyue set re_number=IN_RE_NUMBER
11 where re_number=IN_OLD;
12 commit;
13 END huanzheng;
14 /过程已创建。
SQL> execute huanzheng ('ye','qqq');PL/SQL 过程已成功完成。SQL> select re_number from duzhe; <---修改记录成功RE_NUMBER
--------------------
s016919
e
yeSQL> select re_number from jieyue;RE_NUMBER
--------------------
s016919
ye
ye
ye
Set cmd = New ADODB.Command
Set rs1 = New ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim mrc As ADODB.Recordset
Dim cmd As ADODB.Command
Dim txtsql As String
Dim param As ADODB.Parameter
Const sign = "未定义" Private Sub Command1_Click()
Opendatabase <----定义的连接数据库的函数,而且打开没问题
With cmd
.ActiveConnection = mConn
.CommandText = "huanzheng"
.CommandType = adCmdStoredProc
End With
Set param = cmd.CreateParameter("IN_RE_NUMBER", adChar, adParamInput, 20, Trim(Text1.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("IN_OLD", adChar, adParamInput, 20, Trim(Text1.Text))
cmd.Parameters.Append param
Set rs1 = cmd.Execute
Set cmd = Nothing
Set rs1 = Nothing
End If
MsgBox "成功更改", vbExclamation, sign <--运行之后显示提示,但是到数据库中一看,没有修 改成功!
SQL> CREATE OR REPLACE PROCEDURE huanzheng(
2 IN_RE_NUMBER IN VARCHAR2,
3 IN_OLD IN VARCHAR2)
4 IS
5 BEGIN
6 update duzhe set re_number=IN_RE_NUMBER
7 where re_number=IN_OLD;
8 update jieyue set re_number=IN_RE_NUMBER
9 where re_number=IN_OLD;
10 update yuyue set re_number=IN_RE_NUMBER
11 where re_number=IN_OLD;
12 commit;
13 END huanzheng;
14 /过程已创建。
SQL> execute huanzheng ('ye','qqq');PL/SQL 过程已成功完成。SQL> select re_number from duzhe; <---修改记录成功RE_NUMBER
--------------------
s016919
e
yeSQL> select re_number from jieyue;RE_NUMBER
--------------------
s016919
ye
ye
ye
Set cmd = New ADODB.Command
Set rs1 = New ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim mrc As ADODB.Recordset
Dim cmd As ADODB.Command
Dim txtsql As String
Dim param As ADODB.Parameter
Const sign = "未定义" Private Sub Command1_Click()
Opendatabase <----定义的连接数据库的函数,而且打开没问题
With cmd
.ActiveConnection = mConn
.CommandText = "huanzheng"
.CommandType = adCmdStoredProc
End With
Set param = cmd.CreateParameter("IN_RE_NUMBER", adChar, adParamInput, 20, Trim(Text1.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("IN_OLD", adChar, adParamInput, 20, Trim(Text1.Text))
cmd.Parameters.Append param
Set rs1 = cmd.Execute
Set cmd = Nothing
Set rs1 = Nothing
End If
MsgBox "成功更改", vbExclamation, sign <--运行之后显示提示,但是到数据库中一看,没有修 改成功!
我以前真的没有见过,不知道行不行。(我的数据库是MSSQL,不是ORACLE,道理一样!)下面跟楼主分享一下我调用带参数存储过程的方式。举例如下:
create procedure sp_test(@para int, @result int output)
as
begin
set @result = @para * 2
end
这里的参数@result是输出参数。通过ADO的Command对象调用该存储过程:
cmd_Test.CommandType = adCmdStoredProc
cmd_Test.CommandText = "sp_Test"
cmd_Test.Parameters("@para").Value = 5
cmd_Test.Prepared = True
cmd_Test.Execute则现在可以通过如下方法得到返回值:
cmd_Test.Parameters("@result").Value\