vb调用oracle数据库返回游标的存储过程
来源:互联网 发布:动画运动规律知乎 编辑:程序博客网 时间:2024/05/16 08:23
首先看我的oracle数据包
其次包的主体实现:
再次,用VB调用存储过程开始:
大家看到了,我第二个参数的类型,我写的是adUserDefined,后测试不行.它并不像C#那样,可以写cmd.Parameters.Add("rst",OracleType.Cursor); 后查了很多资料,才解决.
完整代码:
说明:
1,text1是输入参数值,
2,text2没有多大意义,我是为了测试是否可以movelast
3,感谢这个帖子,http://www.80diy.com/home/20060323/15/4634977.html,
4,给出C#调用代码,用C#代码调用是比较简单的.
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;
-- 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;
-- 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()
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
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;
• }
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;
• }
- vb调用oracle数据库返回游标的存储过程
- jdbc中调用oracle 返回游标类型的存储过程
- jdbc中调用oracle 返回游标类型的存储过程
- VBA调用带游标返回值的ORACLE存储过程
- java通过prepareCall调用oracle返回游标的存储过程
- MFC调用postgres数据库返回游标存储过程的疑惑
- del调用返回游标的存储过程
- oracle 在一个存储过程中调用另一个返回游标的存储过程
- oracle 在一个存储过程中调用另一个返回游标的存储过程
- oracle 在一个存储过程中调用另一个返回游标的存储过程
- Oracle存储过程返回游标
- oracle 存储过程返回游标
- ASP.NET调用oracle存储过程返回多个游标
- oracle 动态调用存储过程,返回游标类型
- java jdbc 调用 oracle存储过程 返回游标
- java jdbc 调用 oracle存储过程 返回游标
- Java jdbc调用oracle存储过程返回游标例子
- SSM-Mybatis调用Oracle存储过程返回结果集(游标)
- autotools
- Hadoop初见
- 因子分析到底有什么用处?
- GridView的几个事件(如实现: 行的双击/单击/捕捉键盘按键/鼠标悬浮/移出效果)
- 制作Javascript弹出窗口技巧九则
- vb调用oracle数据库返回游标的存储过程
- 一个mount U盘的脚本
- GridView 72般绝技
- vc中 控件的移动
- 超级病毒变形引擎
- showModalDialog()、showModelessDialog()方法使用详解
- .NET环境下五种不同的邮件发送解决方案
- 内核模块编程入门总结(1)
- 终于UbuntuStudio上无线上网了