Oracle returns a data set in the stored procedure and used in Delphi

First, returning to the data set in Oracle using a stored procedure Returns the data set is returned by the parameters of the REF CURSOR type data, while the parameters of the return data should be OUT or IN OUT type. Since the data type of the parameter cannot be specified directly in the defined stored procedure is: Ref cursor, the REF CURSOR is first redefined by the following method: create or replace package fuxjpackage istype fuxjresultset is Ref cursor; - can also define other content end FuxjPackage; re-defined stored procedures: create or replace procedure UpdatefuxjExample (sDM in char, sMC in char, pRecCur in out FuxjPackage.FuxjResultSet) fuxjExample set mc = sMC where dm = sDM asbeginupdate;

if SQL% ROWCOUNT = 0 thenrollback; open pRecCur forselect '0' res from dual; elsecommit; open pRecCur forselect '1' res from dual; end if; end; and create or replace procedure InsertfuxjExample (sDM in char, sMC in char, pRecCur in out FuxjPackage.FuxjResultSet) asbegininsert into FuxjExample (dm, mc) values ​​(sDM, sMC); commit; open pRecCur forselect * from FuxjExample; end; two, in Delphi call returns a data set stored procedures or by TstoredProc TQuery The control is called to perform the storage of the returned dataset, and the data set returns through the parameter of the TSTOREDPROC or TQUERY control. Note that the DataType type of the parameters is ftcursor, and the parameter's paramtype type is PTINPUTOUTPUT.

UpdatefuxjExample performed using TstoredProc related to: object StoredProc1: TStoredProcDatabaseName = 'UseProc'StoredProcName =' UPDATEFUXJEXAMPLE'ParamData = End execution method is: StoredProc1.Params.Items [0] .sstring: = edit1.text; // assign the parameter; StoredProc1.Params.Items [1] .sstring: = Edit2.Text; / / assigned to the parameters; StoredProc1.Active: = False; StoredProc1.Active: = True; // return result sets TQuery used to perform InsertfuxjExample related: object Query1: TQueryDatabaseName = 'UseProc'SQL.Strings = (' begin '' InsertfuxjExample (sDM => M, sMC =>: mc, pRecCur =>: RecCur); '' end; ') ParamData = END execution method is: query1.params.Items [0] .sstring: = Edit3.Text; // give parameter assignment; query1.Params.Items [1] .sstring: = Edit4.Text ; // assign a value for the parameter; query1.ac TIVE: = false; query1.active: = true;

if SQL% ROWCOUNT = 0 thenrollback; open pRecCur forselect '0' res from dual; elsecommit; open pRecCur forselect '1' res from dual; end if; end; and create or replace procedure InsertfuxjExample (sDM in char, sMC in char, pRecCur in out FuxjPackage.FuxjResultSet) asbegininsert into FuxjExample (dm, mc) values ​​(sDM, sMC); commit; open pRecCur forselect * from FuxjExample; end; two, in Delphi call returns a data set stored procedures or by TstoredProc TQuery The control is called to perform the storage of the returned dataset, and the data set returns through the parameter of the TSTOREDPROC or TQUERY control. Note that the DataType type of the parameters is ftcursor, and the parameter's paramtype type is PTINPUTOUTPUT.

UpdatefuxjExample performed using TstoredProc related to: object StoredProc1: TStoredProcDatabaseName = 'UseProc'StoredProcName =' UPDATEFUXJEXAMPLE'ParamData = End execution method is: StoredProc1.Params.Items [0] .sstring: = edit1.text; // assign the parameter; StoredProc1.Params.Items [1] .sstring: = Edit2.Text; / / assigned to the parameters; StoredProc1.Active: = False; StoredProc1.Active: = True; // return result sets TQuery used to perform InsertfuxjExample related: object Query1: TQueryDatabaseName = 'UseProc'SQL.Strings = (' begin '' InsertfuxjExample (sDM => M, sMC =>: mc, pRecCur =>: RecCur); '' end; ') ParamData = END execution method is: query1.params.Items [0] .sstring: = Edit3.Text; // give parameter assignment; query1.Params.Items [1] .sstring: = Edit4.Text ; // assign a value for the parameter; query1.ac TIVE: = false; query1.active: = true; attached: Create a simple framework for stored procedures

1.create or replace package TestPackage istype TestResultSet is ref cursor; end TestPackage; 2.create or replace procedure Test (pRecCur in out TestPackage .TestResultSet) asbeginopen pRecCur forselect * from table; end;