给个例子你吧~
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 SubcursorErr:
Set cmd = Nothing
Set rst1 = Nothing
MsgBox Err.Description
End Sub
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 SubcursorErr:
Set cmd = Nothing
Set rst1 = Nothing
MsgBox Err.Description
End Sub
解决方案 »
- SQL难题求发放贷款额度最高的银行
- 把NAME列中相同的姓名的ID改成一个
- win server 2008 64bit上调oci时狂吃内存
- 对象库(object libraries)不是属于某个form的吗?它和对象组(object groups)有什么区别
- 关于创建数据库时,诸如共享池、缓冲区高速缓存之类的参数的设定
- 如何用JDBC从Oracle中获取max_idle_time
- 如何在一个bat文件中执行一个sql语句?急急急!!!
- 求一个排序的实现方法
- oracle就那么简单??
- 为什么我在9i客户端的企业管理器用表数据编辑器看到的中文是乱码,而用sql explorer和pl/sql developer中文都能正常显示?
- 求一循环更新数据的语句或存储过程
- 存储过程中循环怎么做啊
??? btw: 建议你看明上面的例子,再做说吧.
PLS-00221:'GET'不是过程活尚未定义
ORA-06550:第一行,第七列
PL/SQL:Statement ignored怎么回事啊?