Distributed DBA: SQL stored procedure knowledge Summary (1)

DB2 stored procedure: Basic knowledge

When you classify remote servers and databases on the server on the client workstation, a simple DB2 client/server environment exists. In this environment, each time an SQL statement is executed on the database on the remote server, the statement itself is sent from the client to the database on the server over the network. Then the database processes the statement and the result is sent back to the client through the network. This means that for each SQL statement executed, both messages must go through the network. Therefore, applications that execute a large number of SQL operations will generate a large amount of network communication.

A stored procedure is a name SQL statement group written and directly stored in the database (in some cases, including operating system calls ). Stored Procedures provide the following advantages:

Reduces network traffic. For SQL statements encoded in the stored procedure, messages are not sent over the network. If the stored procedure is designed correctly, only the data required by the client application is sent over the network.

Improve the Performance of server-intensive work. Because a small amount of data is sent over the network and processed on the server, complex queries and other server-intensive work can be executed faster.

Separation and reuse of business logic. When a business rule is merged into a stored procedure, the stored procedure can be called only as needed to reuse the logic multiple times. In addition, ensure that the same business rule logic is implemented in all applications that use it. If the business rules change, you only need to change the logic in the stored procedure; you do not need to change the application that calls the stored procedure.

Access the server. Because stored procedures run directly on the server workstation, they can take advantage of any additional memory, faster processors, or other resources that the database server may have. In addition, stored procedures can execute many DB2 management commands, which can only be run on the server. Finally, because stored procedures are not limited to executing database activities, they can take advantage of any additional software installed on the server.

However, there are two points to note. First, all input data must be transferred from the application to the stored procedure during the call. Second, the result dataset generated by the stored procedure is returned to the application only after the stored procedure is executed. In other words, no interaction can occur between an application and a stored procedure while the stored procedure is running.

Create an SQL stored procedure

For DB2 on Linux, Unix, and Windows platforms, three different types of stored procedures can be used: SQL, external, and source stored procedures. As shown in the name, the SQL stored procedure consists of SQL statements and SQL PL objects. On the contrary, external stored procedures are made up of C, C ++, Java, COBOL, and other advanced programming languages. Source stored procedures are stored based on other SQL or external stored procedures.

The stored PROCEDURE is created by executing the create procedure SQL statement. This statement has three forms (one for each type of Stored Procedure). The basic syntax used to create an SQL stored procedure is as follows: create procedure [ProcedureName] ([ParamType] [ParamName] [DataType],...)

CREATE PROCEDURE [ProcedureName] ( [ParamType] [ParamName] [DataType] ,...) ＜SPECIFIC [SpecificName]＞ ＜DYNAMIC RESULT SETS 0 | DYNAMIC RESULT SETS [NumResultSets]＞ ＜CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA＞ ＜DETERMINISTIC | NOT DETERMINISTIC＞ ＜CALLED ON NULL INPUT＞ ＜LANGUAGE SQL＞ [ProcedureBody]

Where:

Specifies the name of the stored procedure.

ParamType indicates that the parameters marked by ParamName are input (IN), output (OUT), or both (INOUT.

ParamName identifies the name of the stored procedure parameter.

The data type of the parameter receiving and/or sending that the Stored Procedure expects to be a ParamName identity.

SpecificName identifies the specific name specified for the stored procedure. When a private name is specified to a stored PROCEDURE, you can delete the stored PROCEDURE by referencing the private name in a special form of drop SQL statement (DROP SPECIFIC PROCEDURE [SpecificName. However, if no private name is specified, both the stored procedure name and the Stored Procedure signature (that is, the list of data types used by each stored procedure parameter) must be provided as the input of the DROP statement. A special name cannot be used to call a stored procedure.

NumResultSets indicates that the stored procedure returns a result dataset and identifies the number of returned datasets.

ProcedureBody identifies a single SQL statement or one or more compound SQL statements to be executed when a stored procedure is called.

Note: parameters or options displayed in square brackets ([]) are required. parameters/options displayed in angle brackets (<>) are not required. You can find the complete syntax of the create procedure statement in DB2 9 SQL Reference-volume 2 (see references on page 1. The clause is used to identify the type of SQL statements encoded in the Stored Procedure body. The available values are as follows:

Contains SQL. The stored procedure body contains executable SQL statements that neither read data nor modify data.

Reads SQL DATA. The stored procedure body contains executable SQL statements that read but do not modify the data.

Modifies SQL DATA. The stored procedure body contains executable SQL statements that read and modify data.

The clause is used to identify whether the same result is always returned when the same (DETERMINISTIC) or different (not deterministic) parameter values are passed. For example, a stored procedure that increases by 15% for any value passed to it will be considered as terministic, and a stored procedure that uses the TIMESTAMP_ISO () function to generate a unique ID will be considered not deterministic.

Finally, the sub-statement indicates that the stored procedure is called even when a null value is provided for one or more input parameters.

Listing 1 shows a create procedure Statement consisting of a simple SQL stored PROCEDURE, designed to convert the temperature from Fahrenheit to Celsius.

Listing 1. Creating a simple SQL stored procedure

CREATE PROCEDURE conv_temp.f_to_c(IN temp_f REAL, OUT temp_c REAL) DYNAMIC RESULT SETS 0 CONTAINS SQL DETERMINISTIC LANGUAGE SQL BEGIN DECLARE temp_value REAL; SET temp_value = (temp_f - 32); SET temp_c = (5 * temp_value) / 9; END