版次注意事項


|38.6 Chapter 7. SQL Procedures now called Chapter 7. SQL Control Statements

|Control statements are SQL statements that allow SQL to be used in a |manner similar to writing a program in a structured programming language. |SQL control statements can be used in the body of a routine, trigger or |a dynamic compound statement.

|This chapter contains the syntax and descriptions of the supported SQL |control statements, along with the SQL-procedure-statement.

|38.6.1 SQL Procedure Statement

|The SQL Procedure Statement information changes to the following:

|SQL Procedure Statement

|

|This chapter contains syntax diagrams, semantic descriptions, rules, and |examples of the use of the statements that constitute the procedure body |of an SQL routine, trigger, or dynamic compound statement.

|Syntax

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

|Note:

    |

  1. |This statement is only supported in the scope of an |SQL Procedure.

  2. |This statement is only supported within a trigger, SQL function, |or SQL method. It must be the outermost 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.

|In the context |of a trigger, an SQL function or method, or a dynamic compound statement, |only the dynamic compound statement, the FOR statement and the WHILE |statement may be labeled.

|SQL-statement
|In the body of an SQL procedure, all executable SQL statements can |be contained, with the exception of the following: |

|

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

|38.6.2 FOR

|FOR

| | |

|The FOR statement executes a statement or group of statements for each |row of a table.

|Syntax

|>>-+---------+---FOR--for-loop-name--AS------------------------->
|   '-label:--'
| 
|>-----+-------------------------------+--select-statement---DO-->
|      |                         (1)   |
|      '-cursor-name--CURSOR FOR-------'
| 
|      .-------------------------------.
|      V                               |
|>--------SQL-procedure-statement--;---+--END FOR----+--------+-><
|                                                    '-label--'
| 

|Note:

  1. |This option can only be used in the context of an SQL Procedure. |

|Description |

|label
|Specifies the label for the FOR statement. If the beginning label is |specified, that label can be used in LEAVE and ITERATE statements. If |the ending label is specified, it must be the same as the beginning label.

|for-loop-name
|Specifies a label for the implicit compound statement generated to implement |the FOR statement. It follows the rules for the label of a compound statement |except that it cannot be used with and ITERATE or LEAVE statement within |the FOR statement. The for-loop-name is used to qualify the column |names returned by the specified select-statement.

|cursor-name
|Names the cursor that is used to select rows from the result table from |the SELECT statement. If not specified, DB2 generates a unique cursor |name.

|select-statement
|Specifies the SELECT statement of the cursor. All columns in the select |list must have a name and there cannot be two columns with the same name.

|In a trigger, function, method, or dynamic compound statement, the select-statement must consist of only a fullselect with |optional common table expressions.

|SQL-procedure-statement
|Specifies a statement (or statements) to be invoked for each row of |the table. |

|Rule |

|Examples

|In the following example, the for-statement is used to iterate over the |entire employee table. For each row in the table, the SQL variable fullname is set to the last name of the employee, followed by a comma, |the first name, a blank space, and the middle initial. Each value for fullname is inserted into table tnames.

|   BEGIN
|      DECLARE fullname CHAR(40);
|      FOR vl AS
|         SELECT firstnme, midinit, lastname FROM employee
|            DO
|            SET fullname = lastname || ',' || firstnme ||' ' || midinit;
|            INSERT INTO tnames VALUE (fullname);
|      END FOR
|   END

|38.6.3 Compound Statement changes to Compound Statement (Procedure)

|A procedure compound statement groups other statements together in an SQL |procedure. You can declare SQL variables, cursors, and condition handlers |within a compound statement.

|The syntax diagram now has a title: procedure-compound-statement.

|                         .-NOT ATOMIC--.
|>>-+---------+--BEGIN----+-------------+------------------------>
|   '-label:--'           '-ATOMIC------'
| 
|>-----+-----------------------------------------------+--------->
|      |  .-----------------------------------------.  |
|      |  V                                         |  |
|      '-----+-| SQL-variable-declaration |-+---;---+--'
|            +-| condition-declaration |----+
|            '-| return-codes-declaration |-'
| 
|>-----+--------------------------------------+------------------>
|      |  .--------------------------------.  |
|      |  V                                |  |
|      '----| statement-declaration |--;---+--'
| 
|>-----+-------------------------------------+------------------->
|      |  .-------------------------------.  |
|      |  V                               |  |
|      '----DECLARE-CURSOR-statement--;---+--'
| 
|>-----+------------------------------------+-------------------->
|      |  .------------------------------.  |
|      |  V                              |  |
|      '----| handler-declaration |--;---+--'
| 
|      .-------------------------------.
|      V                               |
|>--------SQL-procedure-statement--;---+---END--+--------+------><
|                                               '-label--'
| 
|SQL-variable-declaration
| 
|               .-,--------------------.
|               V                      |
||---DECLARE-------SQL-variable-name---+------------------------->
| 
|                     .-DEFAULT NULL-------.
|>-----+-data-type----+--------------------+-+-------------------|
|      |              '-DEFAULT--constant--' |
|      '-RESULT_SET_LOCATOR--VARYING---------'
| 
|condition-declaration
| 
||---DECLARE--condition-name--CONDITION--FOR--------------------->
| 
|                 .-VALUE-.
|     .-SQLSTATE--+-------+---.
|>----+-----------------------+---string-constant----------------|
| 
|statement-declaration
| 
|             .-,-----------------.
|             V                   |
||---DECLARE-----statement-name---+---STATEMENT------------------|
| 
|return-codes-declaration
| 
||---DECLARE----+-SQLSTATE--CHAR (5)--+---+--------------------+-|
|               '-SQLCODE--INTEGER----'   '-DEFAULT--constant--'
| 
|handler-declaration
| 
||---DECLARE----+-CONTINUE-+---HANDLER--FOR---------------------->
|               +-EXIT-----+
|               '-UNDO-----'
| 
|      .-,-----------------------------------.
|      V               .-VALUE-.             |
|>---------+-SQLSTATE--+-------+--string--+--+------------------->
|          +-condition-name---------------+
|          +-SQLEXCEPTION-----------------+
|          +-SQLWARNING-------------------+
|          '-NOT FOUND--------------------'
| 
|>----SQL-procedure-statement------------------------------------|
| 
|

|statement-declaration
|A statement-declaration declares a list of one or more names |that are local to the compound statement. A statement name cannot be the same |as another statement name within the same compound statement. |

|38.6.4 RETURN

|RETURN

| | |

|The RETURN statement is used to return from the routine. For SQL functions |or methods, it returns the result of the function or method. For an SQL |procedure, it optionally returns an integer status value.

|Syntax

|>>-RETURN--+---------------------------------------------------------+->
|           +-expression----------------------------------------------+
|           +-NULL----------------------------------------------------+
|           '-+---------------------------------------+---fullselect--'
|             |       .-,--------------------------.  |
|             |       V                            |  |
|             '-WITH-----common-table-expression---+--'
| 
|>--------------------------------------------------------------><
| 

|Description |

|expression
|Specifies a value that is returned from the routine: |

|NULL
|Specifies that the function or method returns a null value of the data |type defined in the RETURNS clause. NULL cannot be specified for a RETURN |from a procedure.

|WITH common-table-expression
|Defines a common table expression for use with the fullselect that follows.

|

|fullselect
|Specifies the row or rows to be returned for the function. The number |of columns in the fullselect must match the number of columns in the |function result (SQLSTATE 42811). In addition, the static column types |of the fullselect must be assignable to the declared column types |of the function result, using the rules for assignment to columns |(SQLSTATE 42866).

|The fullselect cannot be specified |for a RETURN from a procedure.

|If the routine is a scalar |function or method, then the fullselect must return one column (SQLSTATE |42823) and, at most, one row (SQLSTATE 21000).

|If the routine |is a row function, it must return, at most, one row (SQLSTATE 21505).

|If the routine is a table function, it can return zero or |more rows with one or more columns. |

|

|Rule |

|Version |

|Examples

|Use a RETURN statement to return from an SQL stored procedure with a status |value of zero if successful, and -200 if not.

|   BEGIN
|   ...
|      GOTO FAIL
|   ...
|      SUCCESS: RETURN 0
|      FAIL: RETURN -200
|   END

|38.6.5 SIGNAL

|The SIGNAL SQLSTATE Statement is no longer used, in favor of this usage.

|SIGNAL

| | |

|The SIGNAL statement is used to signal an error or warning condition. It |causes an error or warning to be returned with the specified SQLSTATE, along |with optional message text.

|Syntax

|                         .-VALUE-.
|>>-SIGNAL----+-SQLSTATE--+-------+--sqlstate-string-constant--+->
|             '-condition-name---------------------------------'
| 
|>-----+--------------------------------------------------------+-><
|      +-SET--MESSAGE_TEXT-- = --+-variable-name--------------+-+
|      |                         '-diagnostic-string-constant-' |
|      |                         (1)                            |
|      '-(--diagnostic-string--)--------------------------------'
| 

|Note:

  1. |This option is only provided within the scope of a CREATE TRIGGER |statement for compatibility with older versions of DB2. |

|Description |

|SQLSTATE VALUE sqlstate-string-constant
|The specified string constant represents an SQLSTATE. It must be a |character string constant with exactly 5 characters that follow the rules |for SQLSTATEs: |

|In the context of either a dynamic compound statement, trigger, |SQL function, or SQL method, the following rules must also be applied: |

|If the SQLSTATE does not conform to these rules, an error is raised |(SQLSTATE 428B3).

|condition-name
|Specifies the name of the condition. The condition name must be unique |within the procedure and can only be referenced within the compound statement |in which it is declared.

|SET MESSAGE_TEXT=
|Specifies a string that describes the error or warning. The string is |returned in the SQLERRMC field of the SQLCA. If the actual string is longer |than 70 bytes, it is truncated without warning. This clause can only be |specified if a SQLSTATE or condition-name is also specified (SQLSTATE |42601). |

|variable-name
|Identifies an SQL variable that must be declared within the compound |statement. The SQL variable must be defined as a CHAR or VARCHAR data |type.

|diagnostic-string-constant
|Specifies a character string constant that contains the message |text. |

|diagnostic-string
|An expression with a type of CHAR or VARCHAR that returns a character |string of up to 70 bytes to describe the error condition. If the string |is longer than 70 bytes, it will be truncated. This option is only provided |within the scope of a CREATE TRIGGER statement, for compatibility with |older versions of DB2. Regular use is not recommended. |

|Version |

|Examples

|An SQL procedure for an order system that signals an application error |when a customer number is not known to the application. The ORDERS table |includes a foreign key to the CUSTOMER table, requiring that the CUSTNO |exist before an order can be inserted.

|   CREATE PROCEDURE SUBMIT_ORDER
|      (IN ONUM INTEGER, IN CNUM INTEGER,
|       IN PNUM INTEGER, IN QNUM INTEGER)
|       SPECIFIC SUBMIT_ORDER
|       MODIFIES SQL DATA
|      LANGUAGE SQL
|      BEGIN
|         DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
|            SIGNAL SQLSTATE '75002'
|               SET MESSAGE_TEXT = 'Customer number is not known';
|         INSERT INTO ORDERS (ORDERNO, CUSTNO, PARTNO, QUANTITY)
|            VALUES (ONUM, CNUM, PNUM, QNUM);
|      END


[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]