DB2 SQL stored procedure syntax for the classic edition

This article mainly describes the DB2 SQL stored procedure and the authoritative grammar guide. The following is a detailed introduction to the DB2 SQL stored procedure and grammar authoritative guide. The first step is to CREATE a SQL stored PROCEDURE (SQL) statement.

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 the unique identifier in this DB2 SQL 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 DB2 SQL 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: Subject of the DB2 SQL stored procedure

Example 1: generate an 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.