Search from 9CBS http://search.9cbs.net/expert/topic/2280/2280860.xml?temp=2.169436e-02 Forum JCC0128 netizen's speech [Delphi Oracle Report Solutions (1)] Delphi Adjusting Oracle Storage procedure (strap return cursor, no return value) Keywords: Delphi, Oracle stored procedure, cursor, return data set, report
Note: Delphi 6 Oracle 8.1.6
1. Create a package and the package
1. Attachment: Construction Table AACLASS for the following test
Create Table AACLASS (CID VARCHAR2 (50), CNAME VARCHAR2 (50), 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);
2. Build a package:
Create or replace package pkg_jcctest1 AS
TYPE RC_CLASS IS REF CURSOR;
- Request P1, P2 and the poor, returned multiple values return procedure getSubandsum2 (P1 Number, P2 Number, Resultcursor out rc_class) by cursors; - Query the data set that meets the conditions, return to the data set by the cursor to return Procedure getClass2 (a in number, resultcursor out rc_class;
- Insert a record in the table, do not return the result set, I use AdoQuery call (Adodataset seems to return result set) Procedure INSERTCLASS (p_cid varcha2, p_cname varcha2, p_pnumber number); END PKG_JCCTEST1;
3. Building an enabling body
Create Or Replace Package Body PKG_JCCTEST1 AS
Procedure getSubadsum2 (P1 Number, P2 Number, Resultcursor Out RC_CLASS) ISBEGIN OPEN RESULTCURSOR for SELECT P1-P2 AS "SUM", P1 P2 AS "SUB" from Dual; End;
Procedure getClass2 (a in number, resultcursor out rc_class) isbegin
Open resultcursor for select aaclass. * from aaclass where pnumber> a;
END;
Procedure INSERTCLASS (p_cid varchar2, p_cname var) ISBEGIN INSERT INTO AACLASS VALUES (P_CID, P_CNAME, P_PNUMBER); - CommT;
II. In Delphi, use the adodataset to call the first stored procedure 1. Use the Adoconnection1 to connect the database (driver to Oracle Provider for Ole DB), ** and join this section in the connection string: PLSQLRSET = 1; as shown below : Provider = oraoledb.racle.1; Password = kxd; Persist security info = true; user ID = kxd; data source = test3; plsqlrset = 12. Adding Adodataset1 on the form indicates that the connection is connected to the above AdoConnection1, where you can put a button below , Click the button to call the package process created in the first step and return to the data set. The code is as follows:
Procedure tForm1.Button1Click (Sender: Tobject); Var Aresult, BRESULT: Integer; begin adodataset1.close; adodataset1.commandtype: = cmdtext; adodataset1.parameters.clear;
// *** When using the Call method to call the Oracle process, the parameters must be passed, even if the parameters you want to pass are the parameters of the normal executive // output cursor, do not need to be specified !!!!!!, this function is three Parameters, we only need to pass two parameters here. Adodataset1.commandtext: = '{call pkg_jcctest1.getsubandsum2 (?,?)}'
// *** C sequence, CREATEPARAM must be placed after the CommandText assignment statement.
// Create the first parameter, corresponding to the first one in CALL, ftinteger is the type, 10 is length, 45 is incoming real-gate value adodataset1.parameters.createParameter ('p1', ftinteger, pdinput, 10,45 ); // Create a second parameter, automatically automatically with the second parameters in CREATEPARAMETER correspond to AdodataSet1.Parameters.createParameter ('P2', Ftinteger, PDInput, 10, 4);
// The open method of AdoDataSet1 is called, and the data set (the cursor for the package process) adodataset1.open;
/ / According to the stored procedure, only one record is recorded, so don't use the While Do to traverse the data set, directly take the data.
// The field name here will return // defined by the stored procedure of the field name corresponding to the cursor in the package, Returns the stored procedure, such as: Open results "SUM", P1 P2 AS "SUB "from dual; // Take the corresponding field value to ARESULT: = adodataset1.fields.fieldbyname ('Sub'). Value; BRESULT: = adodataset1.fields.fieldbyname ('sum'). value;
// ShowMessage (INTTOSTR (BRESULT)); ShowMessage
END;
III. Using the AdodataSet to call the second stored process in Delphi
Or use the above-described AdodataSet1 to call the second stored procedure, no change, plus the second button, click the time code as follows: procedure tform1.button2click (sender: TOBJECT); begin adodataset1.close; adodataset1.commandType: = cmdtext Adodataset1.Parameters.clear;
// *** When using the Call method to call the Oracle process, the parameter must be passed, even if the parameters you want to pass are the parameters of the common sense // output cursor, do not need to be specified !!!!!!, this function is two Parameters, we only need to pass a parameter here. Adodataset1.commandtext: = '{call pkg_jcctest1.getclass2 (?)}'
// *** C sequence, CREATEPARAM must be placed after the CommandText assignment statement.
// Create the first parameter, corresponding to the first one in CALL, ftinteger is the type, 10 is length, 20 is incoming real-gate Adodataset1.Parameters.createParameter ('p1', ftinteger, pdinput, 10, 20 );
// The open method of AdoDataSet1 is called, and the data set (the cursor for the package process) adodataset1.open;
While Not Adodataset1.eof Do Begin ShowMessage ('CID:'). Value) '--cname:' string (AdoDataSet1.fieldByname ('cname'). Value) ' --Pnumber: ' string (AdoDataSet1.fieldByname (' pnumber '). Value); adodataset1.next; end; end;
Four Use AdoQuery to call the third process, do not return the data set
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, 50, 'c11'); AdoQuery1.Parameters.CreateParameter ( 'P2 ', ftstring, pdinput, 50,' cn11 '); AdoQuery1.Parameters.createParameter (' p3 ', ftinteger, pdinput, 50, 25);
Adoquery1.execsql; end;
5 Use AdoQuery to call the first process, return to the data set.
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;
Sixth question about the three-layer system
The two layers of solve, three-layer similar. Intermediate layer with tadodataset or tadoquery ( tdataSetProvider), the connection string of the middle layer AdoConnection plus plsqlrset = 1; the client uses ClientDataSet, the same size, for example, as follows:
Begin // Call the corresponding process clientDataSet1.close; ClientDataSet1.Params.clear;
ClientDataSet1.commandtext: = '{Call PackageName.ProcedureName (?,?)}'; ClientDataSet1.Params.createParam (ftinteger, 'paramname1', ptinput); clientDataSet1.Open;
My own level is limited, if there is something wrong with the wrong, please correct it!
