在线急盼,DOA开发Oracle数据库应用中,如何在Delphi操作返回值是游标的存储过程

sandygood 2003-09-15 12:00:42
在线急盼,DOA开发Oracle数据库应用中,如何在Delphi操作返回值是游标的存储过程。
比如,下面的存储过程如何实现传递参数和取回结果:
CREATE OR REPLACE PROCEDURE CCATS1000.cp_dw_applicationlic(i_serverip VARCHAR2,
my_cursor IN OUT C112_DT.CurTyp) AS
BEGIN
OPEN my_cursor FOR
SELECT * FROM applicationlic WHERE serverip = i_serverip;
RETURN;
END;
最好有源码。
我的E-Mail:jianghfonline@163.com
...全文
86 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
jianghfonline 2003-09-19
  • 打赏
  • 举报
回复
Using a cursor variable as a TOracleQuery
Because a cursor variable is equivalent to a TOracleQuery with a select statement, DOA implements the cursor variable type as a TOracleQuery. To use a cursor variable, you need at least two TOracleQuery components: one with a PL/SQL block to call the procedure that opens the cursor, and one for the cursor itself:
begin
with Query1 do
begin
Clear;
SQL.Add('begin');
SQL.Add(' employee.opencursor(:p_empcursor, :p_order)');
SQL.Add('end;');
DeclareVariable('p_empcursor', otCursor);
DeclareVariable('p_order', otString);
SetComplexVariable('p_empcursor', CursorQuery);
SetVariable('p_order', 'ename');
Execute;
end;
with CursorQuery do
begin
Execute;
while not Eof do
begin
Memo.Items.Add(Field('ename'));
Next;
end;
end;
end;

The packaged procedure employee.opencursor might look like this:
type t_empcursor is ref cursor return emp%rowtype;

procedure getcursor(p_empcursor in out t_empcursor, p_order in varchar2) is
begin
if p_order = 'ename' then
open p_empcursor for select * from emp order by ename;
elsif p_order = 'empno'
open p_empcursor for select * from emp order by empno;
else
open p_empcursor for select * from emp;
end if;
end;

In this example, Query1 calls the packaged function employee.opencursor to open a cursor to select all employees in a certain order. The CursorQuery is assigned to the p_empcursor variable. You need to use the SetComplexVariable method for this. Next, all rows are fetched and the employee names are displayed in a memo.
Using a cursor variable in a TOracleDataSet
To create a TOracleDataset based on a cursor variable, enter a PL/SQL block with a call to the procedure that opens a cursor in the SQL property, for example:
begin
employee.opencursor(:p_empcursor, :p_order);
end;

If you declare the :p_empcursor variable as otCursor, the dataset will detect the cursor variable, and will retrieve the records from the cursor after executing the PL/SQL block when the dataset is opened or refreshed.
To make the results of a cursor variable in a dataset updateable, you must include the rowid in the cursor, just like with the select statement of a normal dataset. To do this, you can define a cursor type and procedure in a package as follows:
cursor empcursor is select emp.*, rowid from emp;
type t_empcursor is ref cursor return empcursor%rowtype;
procedure opencursor(p_empcusor in out t_empcursor, p_order in varchar2);

Now that the rowid is included, the dataset can use it to lock, refresh, update or delete records. You also need to specify the updating table in the UpdatingTable property of the dataset, in this case: emp. Normally the dataset would determine the updating table by inspecting the select statement in the SQL property, but this is invisible in case of a cursor variable.
sandygood 2003-09-16
  • 打赏
  • 举报
回复
不好意思,我想问的是DOA中调用上面的存储过程,并且得到返回的游标,请大家多多帮忙。
kuangning 2003-09-15
  • 打赏
  • 举报
回复
在ADO中偶用
ADOquery.sql.text := '{call 用户名.CCATS1000('+$39+'127.0.0.1'+#39+')}';
ADOquery.open
sandygood 2003-09-15
  • 打赏
  • 举报
回复
OracleDataSet,OracleQuery和OraclePackage组件具体如何实现上面的操作,这些组件中有没有Cursor对象,就象Jsp中的的一样?
具体的如何操作呢,不知能否在具体一点点,谢谢!!!
路人陈 2003-09-15
  • 打赏
  • 举报
回复
先定义一个record

type TabCol is record
(
<Field> <Datatype>,
<Field> <Datatype>
);
再用while循环取游标中的记录值
用select id,name ....into:.....from 表名 where
然后返回record
OK

2,495

社区成员

发帖
与我相关
我的任务
社区描述
Delphi 数据库相关
社区管理员
  • 数据库相关社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧