How to call Oracle's stored Procedure return dataset in Delphi

oracle| Stored Procedures | data
Selected from CSDN http://search.csdn.net/Expert/topic/2280/2280860.xml?temp=2.169436E-02 forum JCC0128 Netizen's speech

Delphi+oracle Report Solution (i) Delphi stored procedures that invoke Oracle (striped return cursors, no value two)
Keywords: delphi, Oracle Stored procedures, cursors, return data sets, reports

Note: Delphi 6+ Oracle 8.1.6

I. Create package and package body

1. Attach: Build table Aaclass for the following test

CREATE TABLE Aaclass (CID VARCHAR2), CNAME VARCHAR2 (m), Pnumber number (10,0));

INSERT into Aaclass values (' C1 ', ' cn1 ', 10);
INSERT into aaclass values (' C2 ', ' cn2 ', 40);
INSERT into Aaclass values (' C1 ', ' cn3 ', 30);
Commit

2. Build the package:

CREATE OR REPLACE PACKAGE pkg_jcctest1
As

Type rc_class is REF CURSOR;


--To find the P1,P2 and the difference, return multiple values through the cursor return
Procedure GetSubAndSum2 (P1 number,p2 number,
Resultcursor out Rc_class);

--queries the dataset that satisfies the condition, returns the dataset through the cursor
Procedure GetClass2 (A in number,resultcursor out Rc_class);

--Inserting a record into the table, and not returning the result set, I call with Adoquery (Adodataset as if required to return the result set)
Procedure Insertclass (P_cid varchar2, P_cname varchar2,
P_pnumber number);
End Pkg_jcctest1;



3. Build the package body

CREATE OR REPLACE PACKAGE body Pkg_jcctest1
As

Procedure GetSubAndSum2 (P1 number,p2 number,
Resultcursor out Rc_class)
Is
BEGIN
Open Resultcursor for
Select P1-p2 as "sum", p1+p2 as "sub" from dual;
End;


Procedure GetClass2 (A in number,resultcursor out Rc_class)
Is
Begin

Open Resultcursor for
Select aaclass.* from Aaclass where Pnumber >a;

End;

Procedure Insertclass (P_cid varchar2, P_cname varchar2,
P_pnumber number)
Is
Begin
INSERT into Aaclass values (p_cid,p_cname,p_pnumber);
--commit;
End;





Two. Using Adodataset in Delphi to invoke the first stored procedure above
1. Using the AdoConnection1 connection database (driven by Oracle Provider for OLE DB),
* * and add this section to the connection string: plsqlrset=1; As shown below:
PROVIDER=ORAOLEDB.ORACLE.1; Password=kxd; Persist Security info=true; User Id=kxd;data Source=test3; Plsqlrset=1

2. Add AdoDataSet1 on the form to indicate that the connection is the AdoConnection1 above, you can put a button below, click the button to invoke the package procedure created in the first step, and return the dataset. The code looks like this:


Procedure Tform1.button1click (Sender:tobject);
Var
Aresult, Bresult:integer;
Begin
Adodataset1.close;
Adodataset1.commandtype: = Cmdtext;
ADODataSet1.Parameters.Clear;

When invoking the Oracle process using the call method, the parameters must be passed by the?, even if the parameters you want to pass are commonsense.
The parameter of the output cursor does not need to specify a!!!!!!, and this function takes three parameters, we only need to pass two parameters here.
Adodataset1.commandtext: = ' {call pkg_jcctest1. GetSubAndSum2 (?,?)} ' ;

C order, Createparam must be placed after the CommandText assignment statement.

Create the first argument, corresponding to the first in call, Ftinteger is the type, 10 is the length, and 45 is the passed-in argument value
ADODataSet1.Parameters.CreateParameter (' P1 ', ftinteger,pdinput,10,45);
Creates a second parameter that automatically corresponds to the second parameter in call according to the order of CreateParameter
ADODataSet1.Parameters.CreateParameter (' P2 ', ftinteger,pdinput,10,4);

The following calls the ADODataSet1 open method, which returns the dataset (the cursor of the corresponding package procedure)
Adodataset1.open;

Depending on the stored procedure, the dataset has only one record, so there is no need for a while to traverse the dataset and fetch the data directly.

The field name here is based on the field name corresponding to the return cursor in the package procedure
The defined stored procedure returns a cursor such as: Open Resultcursor for
Select P1-p2 as "sum", p1+p2 as "sub" from dual;
Take the corresponding field value out to
Aresult: = ADODataSet1.Fields.FieldByName (' Sub '). Value;
Bresult: = ADODataSet1.Fields.FieldByName (' Sum '). Value;

Show results
ShowMessage (IntToStr (Aresult));
ShowMessage (IntToStr (bresult));

End




Three. Use Adodataset in Delphi to invoke the second stored procedure above


Or use the above AdoDataSet1 to invoke the second stored procedure, without any changes, add the second button, click the code as follows:

Procedure Tform1.button2click (Sender:tobject);
Begin
Adodataset1.close;
Adodataset1.commandtype: = Cmdtext;
ADODataSet1.Parameters.Clear;

When invoking the Oracle process using the call method, the parameters must be passed by the?, even if the parameters you want to pass are commonsense.
The parameter of the output cursor does not need to specify a!!!!!!, and this function takes two parameters, we only need to pass one parameter here.
Adodataset1.commandtext: = ' {call pkg_jcctest1. GetClass2 (?)} ' ;

C order, Createparam must be placed after the CommandText assignment statement.

Create the first argument, corresponding to the first in call, Ftinteger is the type, 10 is the length, and 20 is the passed-in argument value
ADODataSet1.Parameters.CreateParameter (' P1 ', ftinteger,pdinput,10,20);


The following calls the ADODataSet1 open method, which returns the dataset (the cursor of the corresponding package procedure)
Adodataset1.open;

While does adodataset1.eof do
Begin
ShowMessage (' cid: ' +string, Adodataset1.fieldbyname (' CID '). Value) +
'--cname: ' + string (adodataset1.fieldbyname (' CNAME '). Value) +
'--pnumber: ' + string (adodataset1.fieldbyname (' Pnumber '). Value)
) ;
Adodataset1.next;
End;
End





Four uses the Adoquery to invoke the third procedure, does not return the dataset's

Procedure Tform1.button3click (Sender:tobject);
Begin
Adoquery1.close;
AdoQuery1.Parameters.Clear;

AdoQuery1.SQL.Clear;

AdoQuery1.SQL.Add (' {call pkg_jcctest1. GetSubAndSum2 (?,?)} ') ;
AdoQuery1.Parameters.CreateParameter (' P1 ', Ftstring,pdinput, M, ' C11 ');
AdoQuery1.Parameters.CreateParameter (' P2 ', Ftstring,pdinput, M, ' cn11 ');
AdoQuery1.Parameters.CreateParameter (' P3 ', Ftinteger,pdinput, 50,25);

Adoquery1.execsql;
End


Five uses the Adoquery to invoke the first procedure, returns the data set's.


Procedure Tform1.button4click (Sender:tobject);
Begin
Adoquery1.close;
AdoQuery1.Parameters.Clear;

AdoQuery1.SQL.Clear;

AdoQuery1.SQL.Add (' {call pkg_jcctest1. GetSubAndSum2 (?,?)} ') ;
AdoQuery1.Parameters.CreateParameter (' P1 ', Ftinteger,pdinput, 50,25);
AdoQuery1.Parameters.CreateParameter (' P2 ', Ftinteger,pdinput, 50,22);

Adoquery1.open;

ShowMessage string (adoquery1.fieldbyname (' Sub '). Value) + '-' +
String (adoquery1.fieldbyname (' Sum '). Value));
End

Six. With regard to the three-tier system, such issues

The two layers are solved, three layers are similar.
The middle layer is tadodataset or tadoquery (+tdatasetprovider), the ADOConnection connection string of the middle layer plus plsqlrset=1;
The client uses Clientdataset, which is similar to the following examples:

Begin
Call the appropriate procedure
Clientdataset1.close;
ClientDataSet1.Params.Clear;

Clientdataset1.commandtext: = ' {call Packagename.procedurename (?,?)} ';
ClientDataSet1.Params.CreateParam (Ftinteger, ' ParamName1 ', ptinput);
Clientdataset1.open;

End;


My level is limited, if there is improper and wrong place please correct me!