Oracle认证：Oracle通过存储过程返回数据集
一、使用存储过程返回数据集Oracle中存储过程返回数据集是经由过程ref cursor类型数据的参数返回的，而返回数据的参数应该是out或in out类型的。
因为在界说存储过程时无法直接指定参数的数据类型为：ref cursor，而是首先经由过程以下体例将ref cursor进行了重界说：
create or replace package FuxjPackage is
type FuxjResultSet is ref cursor;
--还可以界说其他内容
end FuxjPackage;
再界说存储过程：
create or replace procedure UpdatefuxjExample (sDM in char,sMC in char, pRecCur in out FuxjPackage.FuxjResultSet)
as
begin
update fuxjExample set mc=sMC where dm=sDM;
if SQL%ROWCOUNT=0 then
rollback;
open pRecCur for
select '0' res from dual;
else
commit;
open pRecCur for
select '1' res from dual;
end if;
end;
和
create or replace procedure InsertfuxjExample (sDM in char,sMC in char, pRecCur in out FuxjPackage.FuxjResultSet)
as
begin
insert into FuxjExample (dm,mc) values (sDM,sMC);
commit;
open pRecCur for
select * from FuxjExample;
end;
Oracle认证：Oracle通过存储过程返回数据集
</p>二、在Delphi中挪用返回数据集的存储过程可以经由过程TstoredProc或TQuery控件来挪用执行返回数据集的存储，数据集经由过程TstoredProc或TQuery控件的参数返回，注重参数的DataType类型为ftCursor，而参数的ParamType类型为ptInputOutput。
使用TstoredProc执行UpdatefuxjExample的相关设置为：
object StoredProc1: TStoredProc
DatabaseName = 'UseProc'
StoredProcName = 'UPDATEFUXJEXAMPLE'
ParamData = http://www.qnr.cn/pc/ora/study/201001/</pp item/pp DataType = ftString/pp Name = 'sDM'/pp ParamType = ptInput/pp end/pp item/pp DataType = ftString/pp Name = 'sMC'/pp ParamType = ptInput/pp end/pp item/pp DataType = ftCursor/pp Name = 'pRecCur'/pp ParamType = ptInputOutput/pp Value = http://www.qnr.cn/pc/ora/study/201001/Null/pp end>
end
执行体例为：
StoredProc1.Params.Items.AsString:=Edit1.Text; //给参数赋值;
StoredProc1.Params.Items.AsString:=Edit2.Text; //给参数赋值;
StoredProc1.Active:=False;
StoredProc1.Active:=True; //返回结不美观集
使用TQuery执行InsertfuxjExample的相关设置为：
object Query1: TQuery
DatabaseName = 'UseProc'
SQL.Strings = (
'begin'
' InsertfuxjExample(sDM=>M,sMC=>:mc,pRecCur=>:RecCur);'
'end;')
ParamData = http://www.qnr.cn/pc/ora/study/201001/</pp item/pp DataType = ftString/pp Name = 'DM'/pp ParamType = ptInput/pp end/pp item/pp DataType = ftString/pp Name = 'mc'/pp ParamType = ptInput/pp end/pp item/pp DataType = ftCursor/pp Name = 'RecCur'/pp ParamType = ptInputOutput/pp end>
end
Oracle认证：Oracle通过存储过程返回数据集
</p> 执行体例为：Query1.Params.Items.AsString:=Edit3.Text; //给参数赋值;
Query1.Params.Items.AsString:=Edit4.Text; //给参数赋值;
Query1.Active:=False;
Query1.Active:=True;
if SQL%ROWCOUNT=0 then
rollback;
open pRecCur for
select '0' res from dual;
else
commit;
open pRecCur for
select '1' res from dual;
end if;
end;
和
create or replace procedure InsertfuxjExample (sDM in char,sMC in char, pRecCur in out FuxjPackage.FuxjResultSet)
as
begin
insert into FuxjExample (dm,mc) values (sDM,sMC);
commit;
open pRecCur for
select * from FuxjExample;
end;
页:
[1]