本文介绍了如何使用VB调用Oracle数据库的存储过程来获取游标数据。展示了创建存储过程的代码，以及VB中设置参数、执行存储过程并绑定到DataGrid的方法。在VB中，通过设置参数类型为adUserDefined来处理游标，对比了C#调用的简单性。
首先看我的oracle数据包
create
or
replace
package Pkg_GetQuery
is
--
Author : ZM
--
Created : 2007-12-12 19:38:22
--
Purpose :
--
Public type declarations
type Cur_Ref
is
ref
cursor
;
Procedure
Proc_GetQueryStudinfo(strkey
varchar2
,cur_Result out Cur_Ref);
end
Pkg_GetQuery;
其次包的主体实现:
create
or
replace
package body Pkg_GetQuery
is
--
Private type declarations
procedure
Proc_GetQueryStudinfo(strkey
varchar2
,cur_Result out cur_Ref)
is
strsql
varchar2
(
1000
);
begin
strsql:
=
'
select * from StudInfo Where StudName Like
'''
||
strkey
||
'
%
'''
;
Open
Cur_Result
For
StrSql;
/*比较重要的一句*/
end
;
end
Pkg_GetQuery;
再次,用VB调用存储过程开始:
conn.ConnectionString
=
strconn
conn.CursorLocation
=
adUseClient
conn.Open
Set cmd.ActiveConnection
=
conn
cmd.CommandType
=
adCmdStoredProc
cmd.CommandText
=
"
STUD20050704006.Pkg_GetQuery.Proc_GetQueryStudInfo
"
Set para1
=
cmd.CreateParameter(
"
StrKey
"
, adVarChar, adParamInput,
1000
,
"
张
"
)
para1.Value
=
Text1.Text
cmd.Parameters.Append para1
'
Set para2 = cmd.CreateParameter("Cur_Result", adUserDefined, adParamOutput)
'
cmd.Parameters.Append para2
Set rs
=
cmd.Execute()
大家看到了,我第二个参数的类型,我写的是 adUserDefined,后测试不行.它并不像C#那样,可以 写cmd.Parameters.Add("rst",OracleType.Cursor); 后查了很多资料,才解决.
完整代码:
Dim strconn As String
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim para As New ADODB.Parameter
Dim rs As New ADODB.Recordset
Dim para1 As New ADODB.Parameter
Dim para2 As New ADODB.Parameter
Private Sub Command1_Click()
strconn = "Provider=MSDAORA.1;Password=*****;User ID=linux_wolfelite;Data Source=0.0.0.0/jkxoracl;Persist Security Info=True"
conn.ConnectionString = strconn
conn.CursorLocation = adUseClient
conn.Open
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "STUD20050704006.Pkg_GetQuery.Proc_GetQueryStudInfo"
' Set para = cmd.CreateParameter("score", adNumeric, adParamInput)
' para.Precision = 4
' para.NumericScale = 1
Set para1 = cmd.CreateParameter("StrKey", adVarChar, adParamInput, 1000, "张")
para1.Value = Text1.Text
cmd.Parameters.Append para1
' Set para2 = cmd.CreateParameter("Cur_Result", adUserDefined, adParamOutput)
' cmd.Parameters.Append para2
Set rs = cmd.Execute()
If rs.RecordCount > 0 Then
MsgBox rs.RecordCount
End If
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
If Not rs.BOF Then
rs.MoveLast
Text2.Text = rs(0).Value
End If
' Set rst = cmd.Execute(Null, Null, adCmdStoredProc)
' Set rs = Nothing
' Set conn = Nothing
End Sub
Private Sub Command2_Click()
rs.MovePrevious
Text2.Text = rs(0).Value
End Sub
说明:
1,text1是输入参数值,
2,text2没有多大意义,我是为了测试是否可以movelast
3,感谢这个帖子,http://www.80diy.com/home/20060323/15/4634977.html,
4,给出C#调用代码,用C#代码调用是比较简单的.
private DataTable ExecuteProcToDataSet(string Proc_Name,Hashtable HT,string
Ref_Cur_Name) {
• DataTable MyTable = new DataTable();
• try {
• OraConn = new OracleConnection(StrConnOracle);
• OraComm = new OracleCommand();
• OraComm.Connection = OraConn;
•
OraComm.CommandType = CommandType.StoredProcedure;
• OraComm.CommandText = Proc_Name;
• IDictionaryEnumerator Enumerator= HT.GetEnumerator();
•
while (Enumerator.MoveNext()) {
•
object
Value = Enumerator.Value;
• OracleParameter OraParam=new
OracleParameter(Enumerator.Key.ToString(), Value);
• OraComm.Parameters.Add(OraParam);
•
OracleParameter OraParamCur = new
OracleParameter(Ref_Cur_Name, OracleType.Cursor);
• OraParamCur.Direction = ParameterDirection.Output;
• OraComm.Parameters.Add(OraParamCur);
• DataSet MyDataSet = new DataSet();
• OraAdapter = new OracleDataAdapter(OraComm);
• OraAdapter.Fill(MyDataSet, "MyTable");
• MyTable = MyDataSet.Tables["MyTable"];
• }
• catch (Exception ex)
• {
• MessageBox.Show(ex.Message);
• }
• return MyTable;
• }
create
or
replace
package Pkg_GetQuery
is
--
Author : ZM
--
Created : 2007-12-12 19:38:22
--
Purpose :
--
Public type declarations
type Cur_Ref
is
ref
cursor
;
Procedure
Proc_GetQueryStudinfo(strkey
varchar2
,cur_Result out Cur_Ref);
end
Pkg_GetQuery;
create
or
replace
package body Pkg_GetQuery
is
--
Private type declarations
procedure
Proc_GetQueryStudinfo(strkey
varchar2
,cur_Result out cur_Ref)
is
strsql
varchar2
(
1000
);
begin
strsql:
=
'
select * from StudInfo Where StudName Like
'''
||
strkey
||
'
%
'''
;
Open
Cur_Result
For
StrSql;
/*比较重要的一句*/
end
;
end
Pkg_GetQuery;
再次,用VB调用存储过程开始:
conn.ConnectionString
=
strconn conn.CursorLocation
=
adUseClient conn.Open Set cmd.ActiveConnection
=
conn cmd.CommandType
=
adCmdStoredProc cmd.CommandText
=
"
STUD20050704006.Pkg_GetQuery.Proc_GetQueryStudInfo
"
Set para1
=
cmd.CreateParameter(
"
StrKey
"
, adVarChar, adParamInput,
1000
,
"
张
"
) para1.Value
=
Text1.Text cmd.Parameters.Append para1
'
Set para2 = cmd.CreateParameter("Cur_Result", adUserDefined, adParamOutput)
'
cmd.Parameters.Append para2
Set rs
=
cmd.Execute()
大家看到了,我第二个参数的类型,我写的是 adUserDefined,后测试不行.它并不像C#那样,可以 写cmd.Parameters.Add("rst",OracleType.Cursor); 后查了很多资料,才解决.
完整代码:
Dim strconn As StringDim conn As New ADODB.ConnectionDim cmd As New ADODB.CommandDim para As New ADODB.ParameterDim rs As New ADODB.RecordsetDim para1 As New ADODB.ParameterDim para2 As New ADODB.ParameterPrivate Sub Command1_Click()strconn = "Provider=MSDAORA.1;Password=*****;User ID=linux_wolfelite;Data Source=0.0.0.0/jkxoracl;Persist Security Info=True" conn.ConnectionString = strconn conn.CursorLocation = adUseClient conn.Open Set cmd.ActiveConnection = conn cmd.CommandType = adCmdStoredProc cmd.CommandText = "STUD20050704006.Pkg_GetQuery.Proc_GetQueryStudInfo" ' Set para = cmd.CreateParameter("score", adNumeric, adParamInput)' para.Precision = 4' para.NumericScale = 1 Set para1 = cmd.CreateParameter("StrKey", adVarChar, adParamInput, 1000, "张") para1.Value = Text1.Text cmd.Parameters.Append para1 ' Set para2 = cmd.CreateParameter("Cur_Result", adUserDefined, adParamOutput)' cmd.Parameters.Append para2 Set rs = cmd.Execute() If rs.RecordCount > 0 Then MsgBox rs.RecordCount End If Set DataGrid1.DataSource = rs DataGrid1.Refresh If Not rs.BOF Then rs.MoveLast Text2.Text = rs(0).Value End If ' Set rst = cmd.Execute(Null, Null, adCmdStoredProc)' Set rs = Nothing' Set conn = NothingEnd SubPrivate Sub Command2_Click()rs.MovePrevious Text2.Text = rs(0).ValueEnd Sub
1,text1是输入参数值,
2,text2没有多大意义,我是为了测试是否可以movelast
3,感谢这个帖子,http://www.80diy.com/home/20060323/15/4634977.html,
4,给出C#调用代码,用C#代码调用是比较简单的.
private DataTable ExecuteProcToDataSet(string Proc_Name,Hashtable HT,stringRef_Cur_Name) {• DataTable MyTable = new DataTable();• try {
• • OraConn = new OracleConnection(StrConnOracle);• OraComm = new OracleCommand();• OraComm.Connection = OraConn;•OraComm.CommandType = CommandType.StoredProcedure;• OraComm.CommandText = Proc_Name;• IDictionaryEnumerator Enumerator= HT.GetEnumerator();•while (Enumerator.MoveNext()) {•objectValue = Enumerator.Value;• OracleParameter OraParam=newOracleParameter(Enumerator.Key.ToString(), Value);• OraComm.Parameters.Add(OraParam);•OracleParameter OraParamCur = newOracleParameter(Ref_Cur_Name, OracleType.Cursor);• OraParamCur.Direction = ParameterDirection.Output;• OraComm.Parameters.Add(OraParamCur);• DataSet MyDataSet = new DataSet();• OraAdapter = new OracleDataAdapter(OraComm);• OraAdapter.Fill(MyDataSet, "MyTable");• MyTable = MyDataSet.Tables["MyTable"];• }• catch (Exception ex)• {• MessageBox.Show(ex.Message);• }• return MyTable;• }
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
