Chapter 10 stored procedures

This chapter describes how to use stored procedures. A stored procedure is a program on the database server. It has two types. A SELECT query is used to retrieve data. The retrieved data can be returned to the customer in the form of a dataset. The other is similar to an insert or delete query. It does not return data, but only executes an action. Some servers allow the same stored procedure to return both data and perform actions.
10.1 Overview
On Different Types of servers, stored procedures work in different ways. For example, the InterBase Server can return data in the form of output parameters, while other servers such as Microsoft SQL Server and Sybase can return data and information in the form of datasets.
In Delphi 4, you can use the tstoredproc component or tquery component to access and manipulate stored procedures on the server. Which one to choose depends on how the stored procedure is written and how data is returned and used. Both the tstoredproc component and the tquery component are inherited from tdataset.
The tstoredproc component is suitable for executing stored procedures that do not need to return data and return information through output parameters. The Params attribute of the tstoredproc component is used to manage these parameters. At the same time, the getresults function of the tstoredproc component can explicitly apply for the returned results. In short, the tstoredproc component is suitable for executing stored procedures that do not need to return results or only return results through output parameters.
The tquery component is suitable for executing stored procedures that can return data sets, including stored procedures for returning data sets through output parameters on the InterBase Server. Of course, the tquery component is also suitable for executing stored procedures that do not need to return results or only return results through output parameters.
Parameters can be passed to both the stored procedure and the customer program. The former is called the output parameter and the latter is called the input parameter. For some servers, only one value can be passed for the output parameter, while some servers allow the output parameter to pass a dataset.
10.2 when to use stored procedures
If the server defines a stored procedure, you should decide whether to use the stored procedure as needed. Stored procedures are usually tasks that are frequently executed. These tasks are usually performed for a large number of records. Executing stored procedures on the server can improve the performance of applications. This is because:
. Servers often have powerful computing power and speed.
. Avoid downloading a large amount of data to the client, and reduce the amount of transmission on the network.
For example, assume that an application needs to compute a piece of data, which involves many records. If the stored procedure is not used, download the data to the client, resulting in a sharp increase in network traffic.
In addition, the client may be an old computer, and its operation speed is very slow. After switching to a stored procedure, the server will quickly calculate the data and transmit only one data to the client, which is very efficient.
10.3 how to use stored procedures
How an application uses a stored procedure depends on how the stored procedure is written and how data is returned and used.
10.3.1 general steps for using Stored Procedures
There are several steps to access the stored procedure on the server:
Step 1: place a tstoredproc component on a form or data module.
Step 2: Set the databasename attribute to specify a database, which can be a BDE alias or an application-specific alias (if you use the tdatabase component to connect to the database ).
Step 3: Set the storedprocname attribute to specify the name of the stored procedure. If the databasename attribute is correctly set, you can select a stored procedure from a drop-down list. Because different stored procedures are often executed at runtime, The storedprocname attribute is generally set at runtime.
Step 4: Click the ellipsis next to Params to open an editor. If Step 2 and Step 3 are correctly set, all input and output parameters are displayed in this editor. Otherwise, this editor is empty.
It should be noted that not all servers can provide relevant parameter information. If the server does not provide information about the parameters, you must create these parameters by yourself.
10.3.2 prepare and execute the Stored Procedure
Before executing the stored procedure, it is best to notify the server to prepare it. This requires calling the prepare function of the tstoredproc component, for example:
Storedproc1.prepare;
Note: If the application changes the parameter information at runtime, you must call the prepare function again. To execute the stored procedure, you can call the execproc function of the tstoredproc component. The program example is as follows:
Storedproc1.params [0]. asstring: = edit1.text;
Storedproc1.prepare;
Storedproc1.execproc;
NOTE: If prepare is not called before execproc is called, The tstoredproc component automatically prepares the parameters. After the stored procedure is executed, the preparation is automatically canceled. However, if a stored procedure needs to be executed multiple times, it is best to call prepare explicitly and call the unprepare function when the stored procedure is no longer required.
After a stored procedure is executed, it may return the following data types:
First, you can use a standard data control to display data in a dataset.
Second, output parameters.
. 3. Status information.
10.4 create a stored procedure
Stored procedures are generally written using specialized tools. However, here we will introduce how to use SQL statements to dynamically create stored procedures at runtime. For different servers, even stored procedures with the same function may have different SQL statements. Therefore, you must check the server documentation in advance.
10.4.1 use SQL statements to create a stored procedure
To create a stored procedure using SQL statements, you must use the SQL attributes of the tquery component. If you want to use parameters in the stored procedure, you must set the paramcheck attribute of the tquery component to false.
The following example demonstrates how to use an SQL statement to create a stored procedure:
With query1 do
Begin
Paramcheck: = false;
With SQL do
Begin
Clear;
Add ('create procedure get_max_emp_name ');
Add ('returns (max_name char (15 ))');
Add ('as'); add ('begin ');
Add ('select max (last_name )');
Add ('from employee ');
Add ('into: max_name ;');
Add ('suspend ;');
Add ('end'); end;
Execsql;
End;
Of course, you can also use SQL explorer to create a stored procedure.
10.4.2 use the tquery component to retrieve a dataset
To use the tquery component to retrieve a dataset from a stored procedure, you must set the SQL attribute correctly. In the SELECT statement, replace the table name with the stored procedure name. If an input parameter needs to be passed in a stored procedure, enclose the parameter values in parentheses following the stored procedure in the format of the Object Pascal language. Multiple Input parameters must be separated by commas.
For example, the InterBase Server has a stored procedure named get_emp_proj. It needs to pass an input parameter named emp_no and an output parameter named proj_id to pass the execution result. 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_id
From employee_project
Where emp_no =: emp_no
Into: proj_id
Do
Suspend;
End
Correspondingly, to retrieve the dataset through the stored procedure above, the SQL statement can be written as follows:
Select *
From get_emp_proj (52)
10.4.3 use the tstoredproc component to retrieve a dataset
To retrieve a dataset from a stored procedure using the tstoredproc component, you must set the storedprocname attribute to specify the name of a stored procedure. If the input parameters need to be passed in the stored procedure, the parameters can be provided through the Params attribute or the parambyname function.
For example, a stored procedure named get_employees on the Sybase server has an input parameter named @ emp_no. The code for this stored procedure is as follows:
Create procedure get_employees @ emp_no smallint
As select emp_name, employee_no from employee_table
Where (employee_no = @ emp_no)
Correspondingly, to retrieve a dataset through the stored procedure above, the program should write as follows:
With storedproc1 do
Begin
Close;
Parambyname ('@ emp_no'). assmallint: = 52;
Active: = true;
End;
10.4.4 use the tquery component to retrieve data through Parameters
The data returned by parameters using the tquery component 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, replace the table name with the stored procedure name in the SELECT statement.
If multiple output parameters exist, you can select some of the output parameters or use an asterisk to select all output parameters.
If the input parameter needs to be passed in the stored procedure, enclose the parameter values with a pair of parentheses after the stored procedure. Multiple Input parameters must be separated by commas.
For example, on the InterBase Server, a stored procedure is get_high_emp_name. The last_name field of the employee table is returned using an output parameter high_last_name.
The code for this stored procedure is as follows:
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
Correspondingly, the SQL statement should be written as follows:
Select high_last_name
From get_high_emp_name
10.4.5 use the tstoredproc component to retrieve data through Parameters
To use the tstoredproc component to retrieve data through parameters, you must first set the storedprocname attribute to specify a stored procedure. If the input parameters need to be passed in the stored procedure, the parameters can be provided through the Params attribute or the parambyname function.
After execproc is called to execute the stored procedure, the output parameters can be accessed through the Params attribute or the parambyname function.
For example, the InterBase Server has a stored procedure named get_high_emp_name, which returns the last_name field of the employee table through an output parameter named high_last_name.
The code for this stored procedure is as follows:
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
Correspondingly, to retrieve data through the above stored procedure, the program should write as follows:
With storedproc1 do
Begin
Storedprocname: = 'get _ high_emp_name 'execproc;
Edit1.text: = parambyname ('high _ last_name '). asstring;
End;
10.4.6 use the tquery component to execute an action
Some stored procedures do not return data. They only execute some actions. For example, to delete a record, you can use the delete statement to directly Delete the record or execute a stored procedure.
To execute an action using the tquery component, you must include the name of the stored procedure to be executed in the SQL statement. If the input parameter needs to be passed in the stored procedure, enclose the parameter values with a pair of parentheses after the stored procedure. Multiple Input parameters must be separated by commas.
For example, the InterBase Server has a stored procedure named add_emp_proj, which is used to add a record to the employee_project table.
The code for this stored procedure is as follows:
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
Correspondingly, the SQL statement should be written as follows:
Execute procedure add_emp_proj (20, 'guide ');
10.4.7 use the tstoredproc component to execute an action
To use the tstoredproc component to execute an action, you must first set the storedprocname attribute to specify a stored procedure. You can use the Params attribute or the parambyname function to provide input parameters (if needed ).
For example, the InterBase Server has a stored procedure named add_emp_proj, which is used to add a record to the employee_project table. For its code, see the previous section.
To execute this stored procedure, the program should write as follows:
With storedproc1 do
Begin
Storedprocname: = 'add _ emp_proj ';
Execproc;
End;
10.5 stored procedure parameters
To execute the stored procedure on the server, some parameters are often required. These parameters are divided into four types:
The first type is the input parameter, which is passed by the customer program to the stored procedure.
The second type is the output parameter. The stored procedure returns the result to the client program.
The third type is the input/output parameter, which can be used to pass a value to a stored procedure or to return a result to the client program.
The fourth type is the state parameter. The stored procedure returns an error message to the client program.
Note that not all servers support the preceding four types of parameters. For example, Interbase does not support status parameters.
You can use the Params attribute of the tstoredproc component to access the parameters of the stored procedure (tparam object ). If the storedprocname attribute is correctly set during the design period, the Params attribute automatically contains the parameters of the stored procedure. Otherwise, you must set the parameters yourself.
10.5.1 input parameters
The input parameter is used to pass a value from a client program to a stored procedure. The value is actually an SQL statement passed to the stored procedure. If a stored procedure has input parameters, you must assign values to the input parameters before executing the stored procedure.
If you use the tquery component to execute the stored procedure, you can enclose the input parameters in a pair of parentheses and separate them with commas, just like calling the object PASCAL process. For example, if you want to run a stored procedure named get_emp_proj, it needs to pass an input parameter whose value is 52. The SQL statement is as follows:
Select proj_id
From get_emp_proj (52)
If you use the tstoredproc component to execute the stored procedure, you can access each input parameter through the Params attribute or parambyname function. You must assign values to the input parameters before executing the stored procedure. For example, if you want to execute a stored procedure named get_emp_proj, it needs to pass an input parameter named emp_no. Its data type is smallint and its value is 52. The corresponding program code should be written as follows:
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 program by the stored procedure. Output parameters are assigned values by stored procedures. The value of output parameters can only be accessed after the client program executes the stored procedure.
To access the output parameter value, you can use the Params attribute of the tstoredproc component or the parambyname function. For example, the following code displays the output parameter value in an edit box:
With storedproc1 do
Begin
Execproc;
Edit1.text: = Params [0]. asstring;
End;
Most stored procedures have one or several output parameters. An output parameter can return either a separate value or a dataset.
Note: Some servers such as Informix may not provide parameter information. They can only view the output parameters in the stored procedure code.
10.5.3 Input/Output Parameters
Input/output parameters can be used to pass values to stored procedures by the customer program or return results to the customer program. That is to say, the same parameter has two roles. As an input parameter, it must be assigned a value before execution of the stored procedure. As an output parameter, you can only access its value after the stored procedure is executed.
For example, the Oracle server has a stored procedure. Its in_outvar parameter is an input/output parameter. The code for this stored procedure is as follows:
Create or replace procedure update_the_table (in_outvar in out integer)
As
Begin
Update alltypetable
Set number82rows = in_outvar
Where keyfield = 0;
In_outvar: = 1;
End
Update_the_table;
Correspondingly, to execute the above stored procedure, the program code should be written as follows:
With storedproc1 do
Begin
Parambyname ('in _ outvar '). asinteger: = 103;
Execproc; integervar: = parambyname ('in _ outvar '). asinteger;
End;
10.5.4 status parameters
In addition to the dataset or output parameters, some stored procedures can return a status parameter. Status parameters do not need to be assigned values in advance. They can be accessed only after the stored procedure is executed.
To access the output parameter value, you can use the Params attribute of the tstoredproc component or the parambyname function. For example, the following code accesses the byoutputparam parameter:
Datevar: = storedproc1.parambyname ('byoutputparam'). asdate;
10.5.5 how to access parameters during design
If the databasename and storedprocname attributes are correctly set during the design period, you can see these parameters during the design period. For the input parameters, you can set their values. However, some database servers do not provide the parameter information of stored procedures. In this case, sqlexplorer can only be used to view the Stored Procedure Code and find the parameter name and type, then manually create these parameters in the object observer.
To access parameters during the design period, click the ellipsis next to the Params attribute to open the editor shown in 10.1:
Figure 10.1 stored procedure parameters
You can click the button on the toolbar to create a new parameter. You can click the button to delete a parameter. You can click the button to move the Parameter order up. click the button to move the Parameter order down.
Select a parameter. The object viewer displays the properties of this parameter synchronously. The paramtype attribute must be set to specify the usage type of the parameter. It can be set to input, output, input/output, or result. The datatype attribute must also be set to specify the Data Type of the parameter. Note: For Oracle stored procedures, to return a dataset, you must set the datatype attribute to ftcursor. For input parameters or input/output, you must set the value attribute to assign values to the parameters. You cannot assign values to output and status parameters.
10.5.6 how to access parameters during runtime
If the server does not provide information about the parameters, you must create the parameters yourself. At runtime, you can create a parameter through create of tparam or addparam of tparams.
For example, on the InterBase Server, there is a stored procedure named get_emp_proj, which has an input parameter named emp_no and an output parameter named 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
The following two parameters are dynamically created through programming:
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 how each parameter in the Params attribute matches the stored procedure parameter.
If the parambindmode attribute is set to pbbyname (default), the parameters in the Params attribute match those in the stored procedure by name.
If parambindmode is set to pbbynumber, the parameters in the Params attribute match the parameters in the stored procedure by serial number.
We recommend that you set the parambindmode attribute to pbbyname. Because Parameter order is not required for name matching, it is often wrong to match by serial number. However, in some cases, matching by serial number may be required because some servers do not provide the parameter name.