vb中如何通过一个带参数的存储过程返回一个记录集,如
CREATE OR REPLACE PROCEDURE xp_test
(
p_cur IN OUT PkSmsType.p_Cursor,
strParam in varchar2 --命令参数
)
ISbegin open p_cur for select * from sm_temp where col001 = strParam;end test
create or replace package PkSmsType is
type p_cursor is Ref cursor;
end PkSmsType;
CREATE OR REPLACE PROCEDURE xp_test
(
p_cur IN OUT PkSmsType.p_Cursor,
strParam in varchar2 --命令参数
)
ISbegin open p_cur for select * from sm_temp where col001 = strParam;end test
create or replace package PkSmsType is
type p_cursor is Ref cursor;
end PkSmsType;
InParam1 = "yyyy"
Dim InParam(0 To 0, 0 To 4) As Variant InParam(0, 0) = "strOPNo"
InParam(0, 1) = adVarChar
InParam(0, 2) = adParamInput
InParam(0, 3) = 100
InParam(0, 4) = InParam1
Dim Conn1 As ADODB.connection
Dim Cmd1 As ADODB.command
Dim Rs1 As ADODB.Recordset
Dim param_cnt As Integer
Dim i As Integer Set Conn1 = New ADODB.connection
Conn1.ConnectionString = "Provider=OraOLEDB.Oracle.1; Password=tvpwd; Persist Security Info=True; User ID=tv; Data Source=dawnOrcl"
Conn1.Open Set Cmd1 = New ADODB.command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "xp_test({resultset 0, p_cur},?)"
Cmd1.CommandType = adCmdText
param_cnt = UBound(InParam)
For i = 0 To param_cnt
If InParam(i, 2) = adParamInput Or InParam(i, 2) = adParamInputOutput Then
Cmd1.Parameters.Append Cmd1.CreateParameter( _
InParam(i, PARAM_NAME), InParam(i, PARAM_TYPE), _
InParam(i, PARAM_DIRE), InParam(i, PARAM_SIZE), InParam(i, PARAM_VALU))
End If
Next i
Set Rs1 = Cmd1.Execute
Set rs = Rs1