版次注意事項
|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.
|The SQL Procedure Statement information changes to the following:
|
|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:
|
- |This statement is only supported in the scope of an
|SQL Procedure.
- |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:
|
- |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
|or a C procedure. CALL statements within an SQL procedure body cannot
|call other types of stored procedures.
|
|
|
|
|
|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:
- |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
|
- |The select list must consist of unique column names and the table specified
|in the select list must exist when the procedure is created, or it must
|be a table created in a previous SQL procedure statement.
- |The cursor specified in a for-statement cannot be referenced outside the
|for-statement and cannot be specified in an OPEN, FETCH, or CLOSE statement.
|
|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
|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.
|
|
|
|
|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:
|
- |If the routine is a function or method, one of expression, NULL,
|or fullselect must be specified (SQLSTATE 42630) and the data
|type of the result must be assignable to the RETURNS type of the routine
|(SQLSTATE 42866).
- |A scalar expression (other than a scalar fullselect) cannot be specified
|for a table function (SQLSTATE 428F1).
- |If the routine is a procedure, the data type of expression must
|be INTEGER (SQLSTATE 428E2). A procedure cannot return NULL or a fullselect.
|
- |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
|
- |The execution of an SQL function or method must end with a RETURN (SQLSTATE
|42632).
- |In an SQL table or row function using a dynamic-compound-statement, the only RETURN statement allowed is the one at the end of the
|compound statement (SQLSTATE 429BD).
|
|Version
|
- |When a value is returned from a procedure, the caller may access the value
|using:
|
- |the GET DIAGNOSTICS statement to retrieve the RETURN_STATUS when the SQL
|procedure was called from another SQL procedure
- |the parameter bound for the return value parameter marker in the escape
|clause CALL syntax (?=CALL...) in a CLI application
- |directly from the SQLCA returned from processing the CALL of an SQL procedure
|by retrieving the value of SQLERRD[0] when the SQLCODE
|is not less than zero (assume a value of -1 when SQLCODE is less
|than zero).
|
|
|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
|The SIGNAL SQLSTATE Statement is no longer used, in favor of this usage.
|
|
|
|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:
- |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:
|
- |Each character must be from the set of digits ('0' through '9')
|or non-accented upper case letters ('A' through 'Z').
- |The SQLSTATE class (first two characters) cannot be '00',
|since this represents successful completion.
|
|In the context of either a dynamic compound statement, trigger,
|SQL function, or SQL method, the following rules must also be applied:
|
- |The SQLSTATE class (first two characters) cannot be '01' or
|'02', since these are not error classes.
- |If the SQLSTATE class starts with the numbers '0' through
|'6' or the letters 'A' through 'H', then
|the subclass (the last three characters) must start with a letter in
|the range of 'I' through 'Z'.
- |If the SQLSTATE class starts with the numbers '7',
|'8', '9', or the letters 'I' through
|'Z', then the subclass can be any of '0' through
|'9' or 'A' through 'Z'.
|
|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
|
- |If a SIGNAL statement is issued, the SQLCODE that is assigned is:
|
- |
+438 if the SQLSTATE begins with '01' or '02'
- |
-438 otherwise
|
- |If the SQLSTATE or condition indicates that an exception (SQLSTATE class
|other than '01' or '02') is signaled:
|
- |Then the exception is handled and control is transferred to a handler,
|provided that a handler exists in the same compound statement (or an
|outer compound statement) as the signal statement, and the compound statement
|contains a handler for the specified SQLSTATE, condition-name, or SQLEXCEPTION;
- |If the exception cannot be handled, then control is immediately returned
|to the end of the compound statement.
|
- |If the SQLSTATE or condition indicates that a warning (SQLSTATE class
|'01') or not found condition (SQLSTATE class '02') is signaled:
|
- |Then the warning or not found condition is handled and control is
|transferred to a handler, provided that a handler exists in the same
|compound statement (or an outer compound statement) as the signal statement,
|and the compound statement contains a handler for the specified SQLSTATE,
|condition-name, SQLWARNING (if the SQLSTATE class is '01'), or NOT FOUND
|(if the SQLSTATE class is '02');
- |If the warning cannot be handled, then processing continues with the
|next statement.
|
- |SQLSTATE values are comprised of a two-character class code value,
|followed by a three-character subclass code value. Class code values represent
|classes of successful and unsuccessful execution conditions.
|Any valid
|SQLSTATE value can be used in the SIGNAL statement. However, it is recommended
|that programmers define new SQLSTATEs based on ranges reserved for applications.
|This prevents the unintentional use of an SQLSTATE value that might be
|defined by the database manager in a future release.
|
- |SQLSTATE classes that begin with the characters '7' through
|'9', or 'I' through 'Z' may be defined. Within
|these classes, any subclass may be defined.
- |SQLSTATE classes that begin with the characters '0' through
|'6', or 'A' through 'H' are reserved for the
|database manager. Within these classes, subclasses that begin with the
|characters '0' through 'H' are reserved for the database
|manager. Subclasses that begin with the characters 'I' through 'Z'
|may be defined.
|
|
|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
[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]