学着网上的例子写的,提示出错,请高手看看,不胜感谢!!!建包
CREATE OR REPLACE PACKAGE TEST_RETURN_PACK
AS
type ResultData is ref cursor;--定义动态游标
procedure test_getpack_data(user_id in number,rec_value out ResultData );
END;
/包体
CREATE OR REPLACE PACKAGE BODY TEST_RETURN_PACK
ASprocedure test_getpack_data(user_id in number,rec_value out ResultData) as
begin
open rec_value for SELECT * FROM MS_POINT WHERE MS_ID<=user_id;
end;
END;
/用VB调试返回结果集的代码:
Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strconn As String
Dim strcall As String
Private Sub Command1_Click()
Dim strcall_test As String
Dim param As Parameter
strconn = "Provider=OraOLEDB.Oracle.1;Data Source=db_source;User ID=user_name;Password=psw;Persist Security Info=True;Extended Properties=''"
strcall = "{call TEST_RETURN_PACK.test_getpack_data(?,{resultset 24,rec_value})}"
With cmd
.CommandText = strcall
.ActiveConnection = strconn
.CommandType = adCmdText
End With rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient
Set param = cmd.CreateParameter("user_id", adNumeric, adParamInput, 8, 99)
cmd.Parameters.Append param
'Set param = cmd.CreateParameter("rec_value", adUserDefined, adParamOutput)
'cmd.Parameters.Append param''-------------如果不加上面的二行代码提示出错:ORA-06550: 第 1 行, 第 45 列:
'PLS-00201: 必须说明标识符 'REC_VALUE'
'ORA-06550: 第 1 行, 第 7 列:
'PL/SQL: Statement ignored'由于返回的是游标结果集 ,我不知道在VB里是用什么类型、大小之类的,如果不填那几个参数也不行'------------如果加上那二行提示出错:多步 OLE DB 操作产生错误。如果可能,请检查每个 OLE DB 状态值。没有工作被完成。
Set rs.Source = cmd
rs.Open
While Not rs.EOF
MsgBox " " & rs(0) & ", " & rs(1) & ", " & rs(2) & ", " & rs(3)
'对结果集的处理在这里增加代码
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set cmd = NothingEnd Sub
CREATE OR REPLACE PACKAGE TEST_RETURN_PACK
AS
type ResultData is ref cursor;--定义动态游标
procedure test_getpack_data(user_id in number,rec_value out ResultData );
END;
/包体
CREATE OR REPLACE PACKAGE BODY TEST_RETURN_PACK
ASprocedure test_getpack_data(user_id in number,rec_value out ResultData) as
begin
open rec_value for SELECT * FROM MS_POINT WHERE MS_ID<=user_id;
end;
END;
/用VB调试返回结果集的代码:
Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strconn As String
Dim strcall As String
Private Sub Command1_Click()
Dim strcall_test As String
Dim param As Parameter
strconn = "Provider=OraOLEDB.Oracle.1;Data Source=db_source;User ID=user_name;Password=psw;Persist Security Info=True;Extended Properties=''"
strcall = "{call TEST_RETURN_PACK.test_getpack_data(?,{resultset 24,rec_value})}"
With cmd
.CommandText = strcall
.ActiveConnection = strconn
.CommandType = adCmdText
End With rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient
Set param = cmd.CreateParameter("user_id", adNumeric, adParamInput, 8, 99)
cmd.Parameters.Append param
'Set param = cmd.CreateParameter("rec_value", adUserDefined, adParamOutput)
'cmd.Parameters.Append param''-------------如果不加上面的二行代码提示出错:ORA-06550: 第 1 行, 第 45 列:
'PLS-00201: 必须说明标识符 'REC_VALUE'
'ORA-06550: 第 1 行, 第 7 列:
'PL/SQL: Statement ignored'由于返回的是游标结果集 ,我不知道在VB里是用什么类型、大小之类的,如果不填那几个参数也不行'------------如果加上那二行提示出错:多步 OLE DB 操作产生错误。如果可能,请检查每个 OLE DB 状态值。没有工作被完成。
Set rs.Source = cmd
rs.Open
While Not rs.EOF
MsgBox " " & rs(0) & ", " & rs(1) & ", " & rs(2) & ", " & rs(3)
'对结果集的处理在这里增加代码
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set cmd = NothingEnd Sub
http://dev.21tx.com/2004/01/16/10185.html