This article mainly describes the actual operation steps for correctly creating the DB2 SQL stored PROCEDURE (create procedure (SQL) statement). First, we start with the syntax format, at the same time, there is a description of its syntax. The following is the main content of the article.
The syntax format is as follows:
- >>-CREATE PROCEDURE--procedure-name----------------------------->
- >--+----------------------------------------------------+--*---->
- ’-(--+------------------------------------------+--)-’
- | .-,------------------------------------. |
- | V .-IN----. | |
- ’---+-------+--parameter-name--data-type-+-’
- +-OUT---+
- ’-INOUT-’
- >--+-------------------------+--*------------------------------->
- ’-SPECIFIC--specific-name-’
- .-DYNAMIC RESULT SETS 0--------. .-MODIFIES SQL DATA-.
- >--+------------------------------+--*--+-------------------+--->
- ’-DYNAMIC RESULT SETS--integer-’ +-CONTAINS SQL------+
- ’-READS SQL DATA----’
- .-NOT DETERMINISTIC-. .-CALLED ON NULL INPUT-.
- >--*--+-------------------+--*--+----------------------+--*----->
- ’-DETERMINISTIC-----’
- .-INHERIT SPECIAL REGISTERS-. .-7 OLD SAVEPOINT LEVEL-.
- >--+---------------------------+--*--+---------------------+---->
- ’-7 NEW SAVEPOINT LEVEL-’
- .-LANGUAGE SQL-. .-7 EXTERNAL ACTION----.
- >--7 *--+--------------+--*--+--------------------+--*------------>
- ’-7 NO EXTERNAL ACTION-’
- >--+------------------------------+--3 *-------------------------->
- ’-3 PARAMETER CCSID--+-3 ASCII---+-’
- ’-3 UNICODE-’
- >--| SQL-procedure-body |--------------------------------------><
- SQL-procedure-body:
- |--SQL-procedure-statement--------------------------------------|
Syntax description
1. procedure-name: name of a stored procedure. In the same database mode, a stored procedure with the same name and number of parameters cannot exist, it does not work even if the parameter type is different.
2. (IN | OUT | INOUT parameter-name data-type,...): input parameter
IN: input parameters
OUT: Output Parameters
INOUT: Used as input and output parameters
Parameter-name: the parameter name, which is a unique identifier in this stored procedure.
Data-type: parameter type, which can receive SQL types and created tables. Long varchar, long vargraphic, DATALINK, REFERENCE, and user-defined types are not supported.
3. SPECIFIC specific-name: the unique name (alias), which can be replaced by the stored procedure name. This SPECIFIC name is used for the dorp stored procedure, or you can add a View to the stored procedure.
But cannot call the stored procedure. If this parameter is not specified, the database automatically generates a name for the yymmddhhmmsshhn timestamp. Aliases are recommended.
4. dynamic result sets integer: specify the maximum number of results returned by the stored procedure. Although there is no return statement in the stored procedure, it can return the result set.
5. contains SQL, reads SQL data, and modifies SQL data: Specifies the SQL access level in the stored procedure.
Contains SQL: indicates that the stored procedure can be executed. Neither SQL data can be read nor SQL data can be modified.
Reads SQL data: indicates that the stored procedure can be executed and the SQL statement can be read, but the SQL DATA cannot be modified.
Modifies SQL data: indicates that the stored procedure can execute all SQL statements. Allows you to add, delete, and modify data in a database.
6. DETERMINISTIC or not deterministic: indicates that the stored procedure is dynamic or non-dynamic. The value returned dynamically is uncertain. The values returned for each non-dynamic stored procedure execution are the same.
7. called on null input: indicates that the stored procedure can be CALLED, regardless of whether all INPUT parameters are NULL, and all OUT or INOUT parameters can return a NULL or non-NULL value. Check whether the parameter is NULL.
8. inherit special registers: indicates that special registers are inherited.
9. old savepoint level or new savepoint level: Create a storage point. The old savepoint level is the default storage point.
10. language SQL: specifies that the Program subject uses the SQL LANGUAGE.
11. external action or no external action: indicates whether the stored procedure executes activities that modify the database status, rather than using the database manager. The default value is
External action. If no external action is specified, the database determines the Best optimization solution.
12. parameter ccsid: Specify the encoding of all output string data. By default, the UNICODE encoding database is parameter ccsid unicode, and other databases are parameter ccsid 3 ASCII by default.
13. SQL-procedure-body: body of the stored procedure
Example 1: generate a DB2 SQL stored procedure and return the average employee salary. returns the amount of employees who have exceeded the average salary. The result set includes the name, position, and salary fields (see the example database sample for db2 ).
- CREATE PROCEDURE MEDIAN_RESULT_SET
- (
- OUT medianSalary DOUBLE
- )
- RESULT SETS 1
- LANGUAGE SQL
- BEGIN
- DECLARE v_numRecords INT DEFAULT 1;
- DECLARE v_counter INT DEFAULT 0;
- DECLARE c1 CURSOR FOR
- SELECT CAST(salary AS DOUBLE)
- FROM staff
- ORDER BY salary;
- DECLARE c2 CURSOR WITH RETURN FOR
- SELECT name, job, CAST(salary AS INTEGER)
- FROM staff
- WHERE salary > medianSalary
- ORDER BY salary;
- DECLARE EXIT HANDLER FOR NOT FOUND
- SET medianSalary = 6666;
- SET medianSalary = 0;
- SELECT COUNT(*) INTO v_numRecords
- FROM STAFF;
- OPEN c1;
- WHILE v_counter < (v_numRecords / 2 + 1)
- DO
- FETCH c1 INTO medianSalary;
- SET v_counterv_counter = v_counter + 1;
- END WHILE;
- CLOSE c1;
- OPEN c2;
- END
The above content is an introduction to the DB2 SQL stored procedure syntax official authoritative guide, I hope you can gain some benefits.