SQL Reference

SQL Procedure Statement

The procedure body in an SQL stored procedure definition contains the source statements for the stored procedure.

This chapter contains syntax diagrams, semantic descriptions, rules, and examples of the use of the statements that constitute the procedure body.

If an SQL control statement is specified as the SQL procedure body, multiple statements can be specified within the control statement. These statements are defined as SQL procedure statements.

Syntax

>>-label:----+-| SQL-control-statement |-+---------------------><
             '-| SQL-statement |---------'
 
SQL-control-statement
 
|---+-ALLOCATE CURSOR statement----+----------------------------|
    +-assignment statement---------+
    +-ASSOCIATE LOCATORS statement-+
    +-CASE statement---------------+
    +-compound statement-----------+
    +-FOR statement----------------+
    +-GET DIAGNOSTICS statement----+
    +-GOTO statement---------------+
    +-IF statement-----------------+
    +-ITERATE statement------------+
    +-LEAVE statement--------------+
    +-LOOP statement---------------+
    +-REPEAT statement-------------+
    +-RESIGNAL statement-----------+
    +-RETURN statement-------------+
    +-SIGNAL statement-------------+
    '-WHILE statement--------------'
 

Description

label:

Specifies the label for an SQL procedure statement. The label must be unique within a list of SQL procedure statements, including any compound statements nested within the list. Note that compound statements that are not nested may use the same label. A list of SQL procedure statements is possible in a number of SQL control statements.

SQL-statement

All executable SQL statements can be contained within an SQL procedure body with the exception of the following:

• CONNECT
• CREATE any object other than indexes, tables, or views
• DESCRIBE
• DISCONNECT
• DROP any object other than indexes, tables, or views
• FLUSH EVENT MONITOR
• REFRESH TABLE
• RELEASE (connection only)
• RENAME TABLE
• RENAME TABLESPACE
• REVOKE
• SET CONNECTION
• SET INTEGRITY

Note:

You may include CALL statements within an SQL procedure body, but these CALL statements can only call another SQL procedure. CALL statements within an SQL procedure body cannot call other types of stored procedures.