Chapter 10, this chapter, this chapter describes how to use stored procedures. The stored procedure is a program of the database server side, which has two types. A similar to the SELECT query, used to retrieve data, and the retrieved data can be returned to the customer in the form of a dataset. Another similar to INSERT or DELETE query, does not return data, just performing an action. Some servers allow the same stored procedure to return data and perform actions.
10.1 overview
On different types of servers, the mode of operation of the stored procedure is different. For example, for an Interbase server, it can return data in the form of an output parameter, and for other servers such as MicrosoftSQL Server and Sybase, data and information can be returned in the form of a data set.
In Delphi 4, to access and manipulate the stored procedures on the server, you can use the TSTOREDPROC component or the TQuery component. As for which one is selected, how is the stored procedure itself written, how the data returns and uses which server. The TSTOREDPROC components and Tquery components are inherited from TDataSet.
The TSTOREDPROC component is adapted to perform stored processes that do not need to return data and return information by outputting parameters. The Params property of the TSTOREDPROC component is used to manage these parameters, and the GetResults function of the TSTOREDPROC component can explicitly apply for return results. In summary, the TSTOREDPROC component is suitable for performing stored processes that do not need to return results or only by outputting the output parameter.
The TQUERY component is suitable for performing stored procedures that can return the data set, including the stored procedure for returning the data set by output parameters on the InterBase server. Of course, the TQUERY member is also suitable for performing stored processes that do not need to return results or return results by output parameters.
The parameters can be passed from the stored procedure to the client, or may be passed to the stored procedure by a client program, and the former is called an output parameter, and the latter is called an input parameter. For some servers, the output parameters can only pass a value, while the server allows the output parameters to pass a data set.
10.2 When is the stored procedure
If the server defines a stored procedure, you should decide whether to use the stored procedure as needed. The stored procedure is usually some tasks that are often executed, which are often done for a large number of records. Perform a stored procedure on the server to improve the performance of the application. This is because:
The server often has powerful computing power and speed.
Avoid downloading a lot of data to the client, reducing the amount of transmission on the network.
For example, suppose one application needs to calculate a data, which needs to involve many records. If you do not use the stored procedure, download these data to the client, resulting in a sharp increase in traffic on the network.
Not only that, the client may be an old-fashioned computer, which is very slow. After switching to the stored procedure, the server will quickly calculate the data, and simply pass a data to the client, the efficiency is very obvious.
10.3 How to use stored procedures
How does the application use stored procedures, depending on how the stored procedure itself is written, how the data returns and uses which server.
10.3.1 General steps using stored procedures
To access the stored procedures on the server, it is generally such a few steps:
The first step is placed on the form or data module.
Step 2, set the DatabaseName property to specify a database, which can be set to the BDE alias or application-specific alias (if you connect the database with the TDATABASE component).
In the third step, set the StoredProcName property to specify the name of the stored procedure. If the DatabaseName property is set correctly, you can select a stored procedure from a drop-down list. Since different stored processes are often performed at runtime, StoredProcName properties are typically set at runtime. In the fourth step, click the Ovotible button on the edge of Params to open an editor. If the second step and third step are set correctly, all input and output parameters will be displayed in this editor, otherwise this editor is empty.
To explain, not all servers can provide information about parameters. If the server does not provide information about parameters, you have to build these parameters yourself.
10.3.2 Preparing and performing a stored procedure
Before performing the stored procedure, it is best to inform the server first, this is to call the prepare function of the TSTOREDPROC component, for example:
StoredProc1.prepare;
Note: If the application changes the information of the parameter in the running period, you must re-call the prepare function. To perform a stored procedure, you can call the Execproc function of the TSTOREDPROC component, and the program is as follows:
StoredProc1.Params [0] .sstring: = Edit1.Text;
StoredProc1.prepare;
StoredProc1.execproc;
Note: If the prepare is called before calling Execproc, the TSTOREDPROC component will automatically prepare the parameters. After the stored procedure is executed, the preparation is automatically canceled. However, if a stored procedure is repeatedly executed repeatedly, it is best to call prepare, and the unprepare function is called when the stored procedure is not required.
After performing the stored procedure, it may return such data:
First, the data set can display the data in standard data controls.
Second, the output parameters.
The third is status information.
10.4 Creating a stored procedure
The stored procedure is generally written in a special tool. However, what is it necessary to introduce how to create a stored procedure in the running period with a SQL statement. For different servers, even the stored procedures of the same function, the SQL statement may also be different, so the documentation of the server must be checked in advance.
10.4.1 Creating a stored procedure using the SQL statement
To create a stored procedure using the SQL statement, you should use the SQL property of the TQUERY component. If you want to use the parameters during the stored procedure, you must set the ParamCheck property of the TQuery component to false.
The following example demonstrates how to create a stored procedure with a SQL statement:
With query1 do
Begin
PARAMCHECK: = FALSE;
With sql do
Begin
Clear;
Add ('CREATE Procedure Get_max_emp_name ");
Add ('Returns)');
Add ('as'); add ('begin');
Add ('SELECT MAX (Last_name)');
Add ('from Employee');
Add ('INTO: max_name;');
Add ('susnd; ");
Add ('end');
EXECSQL;
END;
Of course, you can also create stored procedures with SQL Explorer.
10.4.2 Retrieving data sets with TQuery components
To retrieve the data set from the stored procedure with the TQUERY component, you must set the SQL property correctly. In the SELECT statement, you want to replace the name of the table with the name of the stored procedure. If the stored procedure needs to pass the input parameters, the value of the parameter is enclosed in the stored proximity by the stored procedure. If there are multiple input parameters, there is a comma with a comma between each other. For example, there is a stored procedure on the InterBase server called get_emp_proj, which needs to pass an input parameter called EMP_NO, and pass the execution result via an output parameter called Proj_ID. Here is the code of this stored procedure:
Create Procedure Get_Emp_Proj (EMP_NO Smallint)
Returns (Proj_id Char (5))
AS
Begin
For select proj_id
From Employee_Project
WHERE EMP_NO =: EMP_NO
INTO: Proj_ID
DO
Suspend;
End
Correspondingly, to retrieve data sets through the stored procedure, SQL statement can be written this:
SELECT *
From get_emp_proj (52)
10.4.3 Retrieval data set with TSTOREDPROC components
To retrieve the dataset from the stored procedure with the TSTOREDPROC component, you must set the StoredProcName property to specify the name of a stored procedure. If the stored procedure needs to pass the input parameters, you can provide parameters via the params property or parambyname function.
For example, there is a stored procedure on the Sybase server called get_employees, which has an input parameter called @EMP_NO. Here is the code of this stored procedure:
Create Procedure Get_Employees @emp_no smallint
As SELECT EMP_NAME, EMPLOYEE_NO from Employee_Table
WHERE (Employee_no = @EMP_NO)
Accordingly, the program should be written in this memory process, and the program should write:
With storedproc1 do
Begin
CLOSE;
PARAMBYNAME ('@ Emp_no'). Assmallint: = 52;
Active: = True;
END;
10.4.4 Retrieving data by parameter with a TQuery component
The data returned by the TQuery component through the parameter is a record, even if the stored procedure has only one output parameter. Therefore, the application needs to retrieve the value of each field from the returned data.
First, to replace the name of the table with the name of the store in the SELECT statement.
If there are multiple output parameters, you can select some of the output parameters, or you can use an asterisk to indicate all output parameters.
If the stored procedure needs to pass the input parameters, enclose the value of the parameters after the stored procedure is used. If there are multiple input parameters, there is a comma with a comma between each other.
For example, there is a stored procedure on the InterBase server called get_high_emp_name, and returns the last_name field of the EMPLOYEE table via an output parameter called high_last_name.
Here is the code of this stored procedure:
Create Procedure Get_High_Emp_name
Returns (high_last_name char (15))
AS
Begin
SELECT MAX (last_name)
From Employee
INTO: HIGH_LAST_NAME;
Suspend;
End
Accordingly, the SQL statement should write this:
SELECT high_LAST_NAMEFROM GET_HIGH_EMP_NAME
10.4.5 Retrieving data by parameter with TSTOREDPROC components
To use the TSTOREDPROC component to retrieve data by parameter, first set the StoredProcName property to specify a stored procedure. If the stored procedure needs to pass the input parameters, you can provide parameters via the params property or parambyname function.
After calling ExecProc executing the stored procedure, the output parameters can be accessed via the params attribute or parambyname function.
For example, there is a stored procedure on the InterBase server called get_high_emp_name, and returns the last_name field of the Employee table via an output parameter called HIGH_LAST_NAME.
Here is the code of this stored procedure:
Create Procedure Get_High_Emp_name
Returns (high_last_name char (15))
AS
Begin
SELECT MAX (last_name) from Employee
INTO: HIGH_LAST_NAME;
Suspend;
End
Accordingly, to retrieve data through the stored procedure above, the program should write:
With storedproc1 do
Begin
StoredProcname: = 'get_high_emp_name'Execproc;
Edit1.Text: = parambyname ('high_last_name'). Asstring;
END;
10.4.6 Execute an action with the TQuery component
Some stored procedures do not return data, they just do some movements. For example, to delete a record, you can use the delete statement to delete records, or a stored procedure can also be performed.
To perform an action with the TQUERY component, you need to include the name of the stored procedure to be executed in the SQL statement. If the stored procedure needs to pass the input parameters, enclose the value of the parameters after the stored procedure is used. If there are multiple input parameters, there is a comma with a comma between each other.
For example, there is a stored procedure called add_emp_proj on the InterBase server to add a record to the Employee_Project table.
Here is the code of this stored procedure:
Create Procedure Add_emp_proj (EMP_NO Smallint, Proj_id Char (5))
AS
Begin
Begin
INSERT INTO EMPLOYEE_PROJECT (EMP_NO, Proj_ID)
VALUES (: Emp_no,: Proj_id);
When Sqlcode -530 Do
EXCEPTION UNKNOWN_EMP_ID;
End
Suspend;
End
Accordingly, the SQL statement should write this:
Execute procedure add_emp_proj (20, 'guide');
10.4.7 Execute an action with a TSTOREDPROC component
To perform an action with the TSTOREDPROC component, first set the StoredProcName property to specify a stored procedure. You can provide input parameters (if needed) via the params property or parambyname function.
For example, there is a stored procedure called add_emp_proj on the InterBase server to add a record to the Employee_Project table. See the previous section, please refer to the previous section.
To perform this stored procedure, the program should write this:
With storedproc1 do
Begin
StoredProcname: = 'add_emp_proj';
EXECPROC; END;
10.5 Parameters of the stored procedure
To perform a stored procedure on the server, some parameters are often passed. These parameters are divided into four types:
The first kind is called an input parameter, and the value is transmitted to the stored procedure.
The second type is called an output parameter, and the result is returned from the client program.
The third type is called an input / output parameter, which can be transmitted from the client program to the stored procedure or by the stored procedure to return a result.
The fourth type is called a status parameter, and the error message is returned from the user program to the client program.
To explain, not all servers support the above four types of parameters, for example, InterBase does not support status parameters.
The parameters of the stored procedure can be accessed through the params attribute of the TSTOREDPROC component (TPARAM object). If the StoredProcName property is set correctly at the design period, the parameters of the stored procedure will be automatically included in the params property, otherwise you need to create parameters yourself.
10.5.1 Enter parameters
The input parameter is used by the customer program to the stored procedure, and the value is actually transmitted to the SQL statement during the stored procedure. If a stored procedure has an input parameter, you must assign a value to the input parameter before performing the stored procedure.
If the stored procedure is performed with the TQuery component, the input parameters can be enclosed with a pair of parenthesis, separated from each other, just like the process of calling Object Pascal. For example, suppose to perform a stored procedure called get_emp_proj, it needs to pass an input parameter, its value is 52, the SQL statement is as follows:
Select Proj_ID
From get_emp_proj (52)
If the stored procedure is performed with the TSTOREDPROC component, each input parameter can be accessed via the params property or parambyname function. To assign the input parameter before performing the stored procedure. For example, suppose to perform a stored procedure called get_emp_proj, it needs to pass an input parameter called EMP_NO, its data type is Smallint, its value is 52, the corresponding program code should be written:
With storedproc1 do
Begin
PARAMBYNAME ('EMP_NO'). Assmallint: = 52;
EXECPROC;
END;
10.5.2 Output parameters
The output parameter is used to pass the result to the client by the stored procedure. The output parameter is assigned by the stored procedure, and the client can only access the value of the output parameter after executing the stored procedure.
To access the value of the output parameter, you can pass the params attribute or parambyname function of the TSTOREDPROC component or the parambyname function. For example, the following code displays the value of the output parameter into an edit box:
With storedproc1 do
Begin
EXECPROC;
Edit1.text: = params [0] .sstring;
END;
Most stored procedures have one or more output parameters, and the output parameters can return a separate value or return a data set.
Note: Some servers such as Informix may not provide information about parameters, only from the code of the stored procedure, there is no output parameters.
10.5.3 Input / Output Parameters
The input / output parameter can be used by the client program to store the stored procedure, or the stored procedure can also return a result to the client program, that is, the same parameter has two roles. As an input parameter, it must be assigned to it before the stored procedure is executed. As an output parameter, it can only be accessed after the stored procedure is performed.
For example, there is a stored procedure in the Oracle server, and its IN_outVar parameter is an input / output parameter. The code for this stored procedure is as follows:
CREATE OR REPLACEDURE UPDATE_THE_TABLE (in_outvar in out interest) AS
Begin
Update allTyPetable
Set number82fld = in_outvar
WHERE keyfield = 0;
IN_outvar: = 1;
End
UPDATE_THE_TABLE;
Accordingly, to perform the above stored procedure, the program code should write this:
With storedproc1 do
Begin
PARAMBYNAME ('in_outvar'). Asinteger: = 103;
EXECPROC; IntegerVar: = parambyname ('in_outvar'). Asinteger;
END;
10.5.4 Status parameters
In addition to returning data sets or output parameters, some stored procedures can also return a status parameter. The status parameter does not need to be assigned first, and only the value can be accessed after the stored procedure is executed.
To access the value of the output parameter, you can pass the params attribute or parambyname function of the TSTOREDPROC component or the parambyname function. For example, the following code is accessed byoutputParam parameters:
Datevar: = storedProc1.Parambyname ('byoutputparam'). Asdate;
10.5.5 How to access parameters in design?
If the DatabaseName and StoredProcName properties are properly set up in the design period, they can see these parameters in the design period. For input parameters, they can set their values. However, some database servers do not provide parameter information for the stored procedure, in which case only SQLExplorer can only view the code of the stored procedure, find the name and type of the parameters, and then manually establish these parameters in the object viewer.
To access the parameters in the design period, you can click the I omitted button on the edge of the params property to open the editor shown in Figure 10.1:
Figure 10.1 Parameters of the stored procedure
Click the button on the toolbar to create a new parameter, click the button to delete a parameter, click the button to shift the order of the parameters, click the button to move the order of the parameters.
Select one of the parameters, the object viewer will synchronize the properties of the parameter. Where the paramtype property must be set to specify the type of use of the parameter, you can set it to input, Output, Input / Output, or Result. DataType properties must also be set to specify the data type of the parameter. Note: For Oracle's stored procedures, to return data sets, you must set the DataType property to FTCursor. For input parameters or input / output, the value value must be set to the parameter. You cannot assign an output parameter and status parameter.
10.5.6 How to access parameters in the running period
If the server does not provide information about parameters, you must establish a parameter yourself. At runtime, a parameter can be created via TParam's Create or TParams' addParam.
For example, there is a stored procedure on the InterBase server called get_emp_proj, which has an input parameter called EMP_NO and an output parameter called Proj_ID. The code for this stored procedure is as follows:
Create Procedure Get_Emp_Proj (EMP_NO Smallint)
Returns (Proj_id Char (5))
AS
Begin
For select proj_idfrom Employee_Project
WHERE EMP_NO =: EMP_NO
INTO: Proj_ID
DO
Suspend;
End
By programming dynamically, these two parameters are dynamically:
VAR
P1, P2: TPARAM;
Begin
...
With storedproc1 do
Begin
StoredProcname: = 'get_emp_proj';
Params.clear;
P1: = tParam.create (params, ptinput);
P2: = tParam.create (params, ptoutput);
Tryparams [0] .Name: = 'EMP_NO'; params [1] .name: = 'proj_id';
PARAMBYNAME ('EMP_NO'). Assmallint: = 52;
EXECPROC; Edit1.Text: = parambyname ('proj_id'). Asstring;
Finallyp1.free;
P2.free;
END;
END;
...
END;
10.5.7 PARAMBINDMODE attribute
This attribute is used to set each of the parameters in the params property to match the parameters of the stored procedure.
If the parambindmode property is set to PBBYNAME (default), the parameter in the params property is matched according to the parameter of the stored procedure.
If parambBindMode is set to PBByNumber, the parameters in the params property match the parameters of the stored procedure match the parameters of the stored procedure.
It is recommended to set the parambindMode property to pbbyName, because the order in which the parameters are matched, while the serial number is often easier to make mistakes. However, in some cases, you may need to match according to serial number because some servers do not provide the name of the parameters.
