VB 返回数据集例子: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
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
解决方案 »
- 怎样写group by处理交叉组合的统计?
- 哪个有pl/sql函数和存储过程的练习题不?且有答案的,谢谢
- 偶然发现的一个网址,有兴趣大家可以看看
- 无法启动 OracleOraHome90HTTPServer服务,错误1067,进程意外终止。
- 归档模式下,对表进行DML操作,是否可以不产生日志?
- 我这个句子运行效率非常低,怎么能改善一下呢?
- 修改了机器名字和ip,oracle dba studio不能正常连接到数据库实例。请问如何解决!
- 汉字无法插入数据库(非常用汉字)
- 怎么把SEQUENCE的值恢复为初始值?
- 请问ORACLE进阶应该学些什么?
- 数据库查询(在线急)
- 急,在线等!Oracle中有没有一个“年份”再加上n年的函数?
-----------------------------------------------------------------------
做了一个 delphi 调用 Oracle 存储过程并返回数据集的例子.
一、先在 Oracle 建好
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get(i_test INTEGER,p_rc OUT myrctype);
END pkg_test;
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get(i_test INTEGER,p_rc OUT myrctype) IS
BEGIN
IF i_test = 0 THEN
OPEN p_rc FOR SELECT SYSDATE FROM dual;
ELSE
OPEN p_rc FOR SELECT * FROM tab;
END IF;
END get;
END pkg_test;二、用 delphi 调用
建一个窗体,拖动控件 AdoConnection1 , ADOStoredProc1 和 Button1.
procedure TForm1.Button1Click(Sender: TObject);
begin
try
with ADOConnection1 do
begin
ConnectionString:=
'Provider=OraOLEDB.Oracle.1;'
+ 'Password=密码;'
+ 'Persist Security Info=True;'
+ 'User ID=用户名;'
+ 'Data Source=数据库名;'
+ 'Extended Properties="PLSQLRSet=1;"';
Open;
end;
except
showMessage('连接不成功');
exit;
end; try
with ADOStoredProc1 do
begin
Connection := ADOConnection1;
Parameters.Clear;
ProcedureName:= 'pkg_test.get';
Parameters.CreateParameter('p1',ftInteger,pdInput,10,1);
Open;
end;
except
showMessage('无法执行过程.');
end;
end;
而且说是写过程但怎么出来PACKAGE 了?