在线急盼,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:[email protected]
比如,下面的存储过程如何实现传递参数和取回结果:
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:[email protected]
解决方案 »
- delphi 连接mysql 使用case 出现的一个问题
- 哪里能下载ARCGIS engine SDK for delphi?
- 请问DBCHART能否实现和计?
- adoquery的一个奇怪问题!
- Ta = class of Tb
- 如果切换BUTTON的图片,像QQ一样。是读取地址,而不是DATA类型
- adoquery1.post 之后,取当前的纪录值,为什么取到的还是前一个纪录.
- 送分题!如何修改dbgrid上标题行上的字体?
- 各位大虾救急啊,这个问题已经困扰我很久了。(有关DBMemo)
- 为何ClientDataSet1.ApplyUpdates(-1)不能更新数据?
- 关于数据库连接
- 一个很菜的问题!
(
<Field> <Datatype>,
<Field> <Datatype>
);
再用while循环取游标中的记录值
用select id,name ....into:.....from 表名 where
然后返回record
OK
具体的如何操作呢,不知能否在具体一点点,谢谢!!!
ADOquery.sql.text := '{call 用户名.CCATS1000('+$39+'127.0.0.1'+#39+')}';
ADOquery.open
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.