SQL procedure body

Statements that you can include in an SQL procedure body

A CREATE PROCEDURE statement for a native SQL procedure contains an SQL-routine-body, as defined in CREATE PROCEDURE statement (SQL - native procedure). The syntax diagram for SQL-routine-body defines the procedure body as a single SQL statement. The SQL statement can be one of the SQL statements that are shown in the syntax diagram for SQL-routine-body, or an SQL control statement. The syntax diagram for SQL-control-statement in SQL procedural language (SQL PL) identifies the control statements that can be specified.

A native SQL procedure can contain multiple SQL statements if the outermost SQL statement is an SQL-control-statement that includes other SQL statements. These statements are defined as SQL procedure statements. The syntax diagram in SQL-procedure-statement (SQL PL) identifies the SQL statements that can be specified within a control statement. The syntax notes for SQL-procedure-statement clarify the SQL statements that are allowed in a native SQL procedure.

Examples

The following examples show how to determine whether an SQL statement is allowed in an SQL procedure.

The syntax diagrams for the control statements indicate where semicolons are needed in an SQL procedure. If the procedure contains a single statement that is not a control statement, such as Example 1, then no semicolons are in the CREATE PROCEDURE statement. If the procedure consists of multiple statements, such as Example 2, use semicolons to separate SQL statements within the SQL procedure. Do not put a semicolon after the outermost control statement.

Example 1

CREATE PROCEDURE UPDATE_SALARY_1
 (IN EMPLOYEE_NUMBER CHAR(10),
 IN RATE DECIMAL(6,2))
 LANGUAGE SQL
 MODIFIES SQL DATA
 DETERMINISTIC
 COMMIT ON RETURN YES
   UPDATE EMP  A 
   SET SALARY = SALARY * RATE
   WHERE EMPNO = EMPLOYEE_NUMBER

The UPDATE statement ( A ) is an SQL statement that is allowed because it is listed in the syntax diagram for SQL-routine-body.

Example 2

CREATE PROCEDURE GETWEEKENDS(IN MYDATES DATEARRAY, OUT WEEKENDS DATEARRAY)
 BEGIN  A 
  -- ARRAY INDEX VARIABLES
  DECLARE DATEINDEX, WEEKENDINDEX INT DEFAULT 1;  B 
  -- VARIABLE TO STORE THE ARRAY LENGTH OF MYDATES,
  -- INITIALIZED USING THE CARDINALITY FUNCTION.
  DECLARE DATESCOUNT INT;  B 
  SET DATESCOUNT = CARDINALITY(MYDATES);  C 
  -- FOR EACH DATE IN MYDATES, IF THE DATE IS A SUNDAY OR SATURDAY,
  -- ADD IT TO THE OUTPUT ARRAY NAMED "WEEKENDS"
  WHILE DATEINDEX <= DATESCOUNT DO  D 
   IF DAYOFWEEK(MYDATES[DATEINDEX]) IN (1, 7) THEN  E 
    SET WEEKENDS[WEEKENDINDEX] = MYDATES[DATEINDEX];  C 
    SET WEEKENDINDEX = WEEKENDINDEX + 1;  C 
   END IF;
   SET DATEINDEX = DATEINDEX + 1;  C 
  END WHILE;
 END  A 

The SQL procedure has the following keywords and statements:

• The BEGIN and END keywords ( A ) indicate the beginning and the end of a compound statement.
• The DECLARE statements ( B ) are components of a compound statement, and define SQL variables within the compound statement.
• The SET assignment statements ( C ) are SQL control statements that assign values to SQL variables.
• The WHILE statement ( D ) and the IF statement ( E ) are SQL control statements.

A compound statement is an SQL control statement. SQL control statements are allowed in the SQL procedure body because SQL-control-statement is listed in the syntax diagram for SQL-routine-body of a CREATE PROCEDURE (SQL - native) statement.