ORACLE:
包:
CREATE OR REPLACE PACKAGE "GFGS" is
TYPE t_ComREFCS is REF CURSOR;
procedure test(v_result out t_ComREFCS);
end GFGS;
包体:
CREATE OR REPLACE PACKAGE BODY "GFGS" is
procedure test(v_result out t_ComREFCS)
as
begin
open v_result for select * from wuyong;
end;
end GFGS;
这是我的代码,运行不能通过:
Dim mycmd As ADODB.Command
Set mycmd = New ADODB.Command
Set rs = New ADODB.Recordset
mycmd.ActiveConnection = myconn
mycmd.CommandText = "{call GFGS.test({resultset 200, v_result})}"
mycmd.CommandType = adCmdText
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
Set rs = mycmd.Execute()
rs.OpenMsgBox RS.RecordCountSet rs = NothingSet mycmd = Nothing
包:
CREATE OR REPLACE PACKAGE "GFGS" is
TYPE t_ComREFCS is REF CURSOR;
procedure test(v_result out t_ComREFCS);
end GFGS;
包体:
CREATE OR REPLACE PACKAGE BODY "GFGS" is
procedure test(v_result out t_ComREFCS)
as
begin
open v_result for select * from wuyong;
end;
end GFGS;
这是我的代码,运行不能通过:
Dim mycmd As ADODB.Command
Set mycmd = New ADODB.Command
Set rs = New ADODB.Recordset
mycmd.ActiveConnection = myconn
mycmd.CommandText = "{call GFGS.test({resultset 200, v_result})}"
mycmd.CommandType = adCmdText
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
Set rs = mycmd.Execute()
rs.OpenMsgBox RS.RecordCountSet rs = NothingSet mycmd = Nothing
錯誤訊息: 必須宣告識別項v_result.有誰知道啊?
PL/SQL 代码:
CREATE OR REPLACE PACKAGE "PKG_TEST" AS
TYPE myrcType IS REF CURSOR;
FUNCTION get(strSQL VARCHAR) RETURN myrcType;
END pkg_test;
CREATE OR REPLACE PACKAGE BODY "PKG_TEST" AS
FUNCTION get(strSQL IN VARCHAR) RETURN myrcType IS
rc myrcType;
BEGIN
OPEN rc FOR strSQL;
RETURN rc;
END get;
END pkg_test;
VB 代码:
Private Sub Command1_Click()
On Error GoTo cursorErr:
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
cnn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=oraAny;Extended Properties=PLSQLRSet=1"
cnn.Open
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "{CALL scott.pkg_test.get(?)}"
.Parameters.Append .CreateParameter("strSQL", adVarChar, adParamInput, 100, "SELECT * FROM TAB")
End With
rst.CursorType = adOpenStatic
rst.LockType = adLockReadOnly
Set rst.Source = cmd
rst.Open
MsgBox rst.RecordCount
Set rst = Nothing
Set cmd = Nothing
Exit Sub
cursorErr:
Set cmd = Nothing
Set rst1 = Nothing
MsgBox Err.Description
End Sub