Oracle returns related datasets through its actual stored procedures

The following articles mainly introduce the actual operating scheme for Oracle to return the relevant dataset through the stored procedure. The first thing we need to know is how to correctly use the stored procedure to return the dataset, we all know that the data set returned by the stored procedure in the Oracle database is returned by the parameters of Oracle Data of the ref cursor type.

The returned data parameters should be of the out or in out type, because the data type of the parameter cannot be directly specified when the stored procedure is defined: ref cursor, instead, Oracle redefines the ref cursor using the following method:

 
 

  
  
create or replace package FuxjPackage is  
  
  
type FuxjResultSet is ref cursor;  
 
 

You can also define other content

 
 

  
  
end FuxjPackage; 
 
 

Then define the stored procedure:

 
 

  
  
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;  
 
 

Call the stored procedure of the returned dataset in Delphi

You can use the TstoredProc or TQuery control to call the storage of the returned dataset. The Oracle dataset is returned through the parameters of the TstoredProc or TQuery control. Note that the DataType type of the parameter is ftCursor, the ParamType type of the parameter is ptInputOutput.

Use TstoredProc to execute UpdatefuxjExample and set it:

 
 

  
  
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 above content is the description of the data set returned by Oracle through the stored procedure. I hope it will help you in this regard.