How Does Oracle return a dataset through a stored procedure?

This article mainly teaches you how to correctly use the Oracle stored procedure so that it can return the actual operation steps of the relevant dataset, we all know that the returned data sets in the stored procedures in Oracle mainly serve to be returned through the actual application parameters of the ref cursor data, the returned data parameters should be of the out or in out type.

When defining an Oracle stored procedure, you cannot directly specify the parameter's data type as ref cursor. Instead, you must first redefine 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 Oracle 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;  
 
 

2. Calling the Oracle Stored Procedure for the returned dataset in Delphi

You can use the TstoredProc or TQuery control to call and store the returned dataset. The dataset is returned through the parameters of the TstoredProc or TQuery control. Note that the DataType type of the parameter is ftCursor, And 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 an introduction to the returned data sets in the Oracle stored procedure. I hope you will find some gains.