vb调用Oracle函数返回数据集的例子
发表于:2007-05-25来源:作者:点击数:
标签:
PL/SQL 代码: CREATE OR REPLACE PACKAGE "SCOTT"."PKG_TEST" AS TYPE myrcType IS REF CURSOR ; FUNCTION get (strbarcode VARCHAR ) RETURN myrcType; END pkg_test; CREATE OR REPLACE PACKAGE BODY "SCOTT"."PKG_TEST" AS FUNCTION get (strbarcode IN V
PL/SQL 代码:
CREATE OR REPLACE PACKAGE "SCOTT"."PKG_TEST"
AS TYPE myrcType
IS REF CURSOR;
FUNCTION get(strbarcode
VARCHAR)
RETURN myrcType;
END pkg_test;
CREATE OR REPLACE PACKAGE BODY "SCOTT"."PKG_TEST"
AS FUNCTION get(strbarcode
IN VARCHAR)
RETURN myrcType
IS rc myrcType;
BEGIN OPEN rc
FOR strbarcode;
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=PLSQ
LRSet=1"
cnn.Open
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "{CALL scott.pkg_test.get(?)}"
.Parameters.Append .CreateParameter("strBarCode", 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
原文转自:http://www.ltesting.net
|