Run stored procedures with DOA (transfer)
When using Delphi to develop C / S structures, in order to improve efficiency, a large amount of data processing is usually handed over to the background storage process. Since Delphi needs to operate and process various database files through BDE, it is not only less efficient, but there is a certain limitations, so consider using third-party tools DOA to improve the interaction efficiency, facilitate the delivery of the front and back.
DOA (ie Direct Oracle Access Abbreviation) is a tool for accessing Oracle, ALLROUND Automations, using the DOA component to skip BDE in a Delphi or C Builder development environment, and access Oracle directly through SQL NET. In the first time contact DOA, some programmers are confused about how to use the DOA call storage process, and the author will approve this specific example in combination with the data processing of the telecommunications integrated management system, and explain the specific methods and steps in detail.
Implementation 1. CallProcedure / Callxxxfunction with TraclePackage
With TraclePackage's CallProcedure, we can simply call the Oracle stored procedure, which is passed in the form of an array. When TraclePackage's parameterMode property is pmname, you must pass the parameters according to the name, and each parameter must have a string of the specified name, which is:
CallProcedure (ProcedureName, [Parametername1, Parameter1, Parametername2, Parameter2 ...];
When TraclePackage's parameterMode property is PMPositional, you want to pass parameters according to location:
CallProcedure (ProcedureName, [Parameter1, Parameter2, ...];
The output parameter is defined by passing the Parstring, Parinteger, Parfloat, Pardate, or ParBoolean constant, the output parameter value is obtained with the getParameter method after the process call, the format is:
PackageName.callProcedure ('procedurename "; getParameter (parameterid);
If there is no parameters, use Parnone to get:
PackageName.callProcedure ('ProcedureName', Parnone);
Similar to the above, we use TraclePackage Call ... function methods to simply call the Oracle function, call the corresponding method CallBooleanFunction, CallintegerFunction, or callstringfunction, call -tesFunction, CallFloatFunction, CallintegerFunction, or CallstringFunction, according to the return value.
2. Use TracleQuery or TracleDataSet Execute
The step of performing a stored procedure with TracleQuery or TracleDataSet is:
(1) Set the SQL property
Set the SQL property of TracleQuery or TracleDataSet:
BeginProcedureName (: Parameter1,: parameter2, ...);
(2) Define parameters
There are two ways to define the parameters. The DOA supports the PL / SQL table, which can be passed to the back / output parameter to the back storage procedure and functions. You can pass a lot of information by calling, so that in the C / S structure, network traffic is significantly reduced. Defining the PL / SQL table in TracleQuery or TracleDataSet You can use the Variable Properties Editor (Select the PL / SQL Table check box, then define the size of the table, if it is a string type, you need to define the string size), or Define the PL / SQL table (first modify the DeclareVariable method first, then call the DIMPLSQLTABLE method to define the size). (3) Parameter assignment
Assign the input parameter to assign the setvariable method, assign the value to the PL / SQL table first to create an array variable, assign each element of an array, and then assign a value to the PL / SQL table by this array with the setvariable method.
ArrayName: = VararrayCreate ([Lowbounds, Upbounds], VARTYPE); Table [Lowbounds]: = Value1; ... Table [Upbounds]: = VALUEN; OracleQueryName.SetVariable (': TableName', arrayname);
(4) Perform a stored procedure
Call the Execute method to perform a stored procedure, the code is as follows:
OracleQueryName. Execute;
(5) Get output parameters
When you get the output parameter value with the getVariable method, when the input / output parameter is the PL / SQL table, the return of the returned array variable is the same; when the output parameter is a PL / SQL table, the lower limit of the returned array variable is started from zero.
Application Example The following is a source code for calling the processing audit stored procedure in the telecommunications comprehensive statistical management system using the second method described above:
create or replace package pk_sh istype t_object is table of varchar2 (15) index by binary_integer; type t_formula is table of number index by binary_integer; procedure sp_audit (sh_time in varchar2, sh_dx in t_object, sh_gs in t_formula); end pk_sh;
We first created a package pk_sh in the server side, and two PL / SQL tables are defined in the package, where t_object stores the audit object, T_Formula stores the review formula ID, stored procedure sp_audit, based on the passed parameters (time, object, formula) on the background data Processing review.
The client is written with Delphi, that is, accessed sp_audit by DOA, and the specific source code is as follows:
VarOracleSession1: TOracleSession; OracleQuery1: TOracleQuery; Begin // database connection OracleSession1: = TOracleSession.Create (nil); OracleSession1.LogonDatabase: = 'chicago'; OracleSession1.LogonUsername: = 'scott'; OracleSession1.LogonPassword: = 'tiger'; Oraclesession1.connected: = true; OracleQuery: = TracleQuery.create (nil); OracleQuery1.Session: = ORACLESession1; // Create an array and assigning sh_dx: = varVariant; For i: = 0 to lv_object.items.count -1 dobeginsh_dx [i]: = lv_object.Items [i] .caption; end; sh_gs: = varArrayCreate ([0, lv_formula.items.count -1], varvariant); For i: = 0 to lv_formula.items.count -1 dobeginsh_gs [i]: = strt (lv_formula.items [i] .caption); end; sql_str: = 'pk_sh.sp_audit (: sh_time,: sh_dx,: sh_gs) ; '; with OracleQuery1 dobegin // Sets SQL attribute clear; sql.add (' begin '); sql.add (' ' sql_str); sql.add (' end; '); // Define Parameters deletevariables; DeclareVariable ( 'sh_time', otString; DECLAREVARIABLE ('sh_dx', otstring); declarevariable ('sh_gs', otinteger); dimplsqltable ('sh_dx', 2000, 15); DIMPLSQLTABLE 'sh_gs', 500, 0); // Parameter assignment setvariable (': sh_time', sh_time); setvariable (': sh_dx', sh_dx); setvariable (': sh_gs', sh_gs); // Execute stored procedure Execute; Free; end; oracles ;; oraclesession1.free; end; The above source code is developed with Delphi 5, ORACLE 8, and debugging under the Windows 98 / Windows2000 system platform.
Through the above analysis, use BDE to access Oracle, because it does not support the PL / SQL table, the parameters can only be passed by branch, and the stored procedure must be repeated multiple times, and the problem is completely resolved with DOA. In addition, set TracleQuery's Threaded Properties to TRUE to simply write multithreaded applications, and set the debug property to true, which can display the SQL statement and variable values when running to facilitate debugging.
