1. Use stored procedures to return data sets
The data set returned by stored procedures in Oracle is returned by parameters of ref cursor type data, and the parameters that return data should be of type out or in out type.
Since the data type of the parameter cannot be directly specified when defining a stored procedure is: ref cursor, the ref cursor is first redefined by the following method:
create or replace package FuxjPackage is
type FuxjResultSet is ref cursor;
--You can also define other content
end FuxjPackage;
Define the stored procedure again:
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;
and
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;
2. Call the stored procedure that returns the data set in Delphi
The storage that executes the return data set can be called through the TstoredProc or TQuery control. The data set is returned through the parameters of the TstoredProc or TQuery control. Note that the DataType of the parameter is ftCursor, and the ParamType of the parameter is ptInputOutput.
Use TstoredProc to execute the relevant settings of UpdatefuxjExample as:
object StoredProc1: TStoredProc
DatabaseName = 'UseProc'
StoredProcName = 'UPDATEFUXJEXAMPLE'
ParamData = <
item
DataType = ftString
Name = 'sDM'
ParamType = ptInput
end
item
DataType = ftString
Name = 'sMC'
ParamType = ptInput
end
item
DataType = ftCursor
Name = 'pRecCur'
ParamType = ptInputOutput
Value = Null
end>
end
The execution method is:
StoredProc1.Params.Items[0].AsString:=Edit1.Text; // Assign value to the parameter;
StoredProc1.Params.Items[1].AsString:=Edit2.Text; //Assign the parameter;
StoredProc1.Active:=False;
StoredProc1.Active:=True; //Return result set
Use TQuery to execute the relevant settings of InsertfuxjExample as:
object Query1: TQuery
DatabaseName = 'UseProc'
SQL.Strings = (
'begin'
' InsertfuxjExample(sDM=>M,sMC=>:mc,pRecCur=>:RecCur);'
'end;')
ParamData = <
item
DataType = ftString
Name = 'DM'
ParamType = ptInput
end
item
DataType = ftString
Name = 'mc'
ParamType = ptInput
end
item
DataType = ftCursor
Name = 'RecCur'
ParamType = ptInputOutput
end>
end
The execution method is:
Query1.Params.Items[0].AsString:=Edit3.Text; //Assign the parameter;
Query1.Params.Items[1].AsString:=Edit4.Text; // Assign value to the parameter;
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;
and
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;
2. Call the stored procedure that returns the data set in Delphi
The storage that executes the return data set can be called through the TstoredProc or TQuery control. The data set is returned through the parameters of the TstoredProc or TQuery control. Note that the DataType of the parameter is ftCursor, and the ParamType of the parameter is ptInputOutput.
Use TstoredProc to execute the relevant settings of UpdatefuxjExample as:
object StoredProc1: TStoredProc
DatabaseName = 'UseProc'
StoredProcName = 'UPDATEFUXJEXAMPLE'
ParamData = <
item
DataType = ftString
Name = 'sDM'
ParamType = ptInput
end
item
DataType = ftString
Name = 'sMC'
ParamType = ptInput
end
item
DataType = ftCursor
Name = 'pRecCur'
ParamType = ptInputOutput
Value = Null
end>
end
The execution method is:
StoredProc1.Params.Items[0].AsString:=Edit1.Text; // Assign value to the parameter;
StoredProc1.Params.Items[1].AsString:=Edit2.Text; //Assign the parameter;
StoredProc1.Active:=False;
StoredProc1.Active:=True; //Return result set
Use TQuery to execute the relevant settings of InsertfuxjExample as:
object Query1: TQuery
DatabaseName = 'UseProc'
SQL.Strings = (
'begin'
' InsertfuxjExample(sDM=>M,sMC=>:mc,pRecCur=>:RecCur);'
'end;')
ParamData = <
item
DataType = ftString
Name = 'DM'
ParamType = ptInput
end
item
DataType = ftString
Name = 'mc'
ParamType = ptInput
end
item
DataType = ftCursor
Name = 'RecCur'
ParamType = ptInputOutput
end>
end
The execution method is:
Query1.Params.Items[0].AsString:=Edit3.Text; //Assign the parameter;
Query1.Params.Items[1].AsString:=Edit4.Text; // Assign value to the parameter;
Query1.Active:=False;
Query1.Active:=True;
Attachment: Simple framework for creating stored procedures that return datasets
1.
create or replace package TestPackage is
type TestResultSet is ref cursor;
end TestPackage ;
2.
create or replace procedure Test
(
pRecCur in out TestPackage .TestResultSet
)
as
Begin
open pRecCur for
select * from table;
end;