Compound statements implement a small procedural language that provides the building blocks from which SQL Procedures are composed. A compound statement can contain a large number of interacting SQL statements that are limited only by the size of the RWTSA that must transport the syntax to the DBMS. For details about size adjustment to the RWTSA, see the information about the TASKS Multi-User startup option in the CA Datacom/DB Database and System Administration Guide. in the CA Datacom/DB Database and System Administration Guide. When a compound statement is executed, an execution context is created for the statements the compound statement contains. That execution context consists of the following:
The set of statements to which variables and other context-related information is visible is referred to as scope. Scope consists of all statements and contexts enclosed by a compound statement. Compound statements cannot exist outside of a procedure, but they can be nested. Nested compound statements are most frequently used for condition handling.
Following is the syntax for the compound statement:
Note: Combinations and multiples of these two optional choices can be used.
►►─┬──────────────────────┬─ BEGIN ATOMIC ────────────────────────────────────► └─ start-label: ───────┘ ┌─────────────────────────────────┐ ►─▼─┬─────────────────────────────┬─┴────────────────────────────────────────► ├─ SQL-variable-declarations ─┤ └─ condition-declarations ────┘ ┌─────────────────────────────────┐ ┌─────────────────────────┐ ►─▼─┬─────────────────────────────┬─┴─▼─┬─────────────────────┬─┴────────────► └─ declare-cursor-stmt; ──────┘ └─ condition-handler ─┘ ►─ proc-SQL-stmt-list ─ END ─┬─────────────┬─────────────────────────────────►◄ └─ end-label ─┘
Expansion of Where SQL-variable-declarations are defined as
┌─ , ─────────────┐ ├── DECLARE ─▼─ variable-name ─┴─ datatype ───────────────────────────────────► ►─┬─────────────────────────────┬─ ; ────────────────────────────────────┤ └─ DEFAULT ─┬─ literal ─────┬─┘ ├─ NULL ────────┤ ├─ USER ────────┤ └─ SYSTEM USER ─┘
Expansion of Where condition-declarations are defined as
├── DECLARE ─ condition-name ─ CONDITION ─────────────────────────────────────► ►─┬───────────────────────────────────────────────┬─ ; ──────────────────┤ └─ FOR ─ SQLSTATE ─ VALUE ─ character-constant ─┘
Expansion of Where condition-handler is defined as
┌─ , ──────────────────┐ ├── DECLARE ─┬─ CONTINUE ─┬─ HANDLER FOR ─▼─┬─ condition-name ─┬─┴────────────► ├─ EXIT ─────┤ ├─ sqlstate-value ─┤ └─ UNDO ─────┘ ├─ SQLEXCEPTION ───┤ ├─ SQLWARNING ─────┤ └─ NOT FOUND ──────┘ ►─ proc-SQL-stmt ─ ; ────────────────────────────────────────────────────┤
Expansion of Where proc-SQL-stmt-list is defined as
┌──────────────────────────┐ ├──▼─ proc-SQL-stmt ─ ; ──────┴───────────────────────────────────────────┤
Following are descriptions of the parts of the compound statement syntax:
(Optional) A start-label: is an SQL identifier (followed by a colon) that can be used in various flow-control statements to mark the destination of a branch. When you specify an end-label you must also specify a matching start-label.
We recommend you do not label a compound statement using the SQL Procedure name. When the SQL Procedure name is used, we cannot guarantee any specific result or its consistency over time.
Specifying ATOMIC prevents a procedure from containing a COMMIT or a non-savepoint ROLLBACK. Specify ATOMIC after BEGIN and specify an END before the end-label.
(Optional) In SQL-variable-declarations each variable receives the default value on entry to a compound statement. Variables without defaults are assigned NULL values on entry. Each declaration can include a list of variables separated by commas. The variable-name is an SQL identifier.
The scope (range of visibility) of these variables is the BEGIN-END pair, including any compound statements contained within and any error handlers that execute while the contained compound statement is executing.
Indentical variable names can be disambiguated using start-label.variable-name where the start label is the start-label of a compound statement.
(Optional) The DEFAULT clause, an optional part of the SQL-var-declarations clause, specifies a default from among the choices that follow:
Specifies the default to be a literal. Be certain the literal agrees with the data type of the column. The specified literal can be up to 20 bytes long or the length of the column involved, whichever is shorter. You can specify a default value for a character column where the column is greater than 20 bytes long, but the default literal itself is limited to 20 bytes with the remaining bytes padded with blanks by the system.
Specifies the default as NULL.
Specifies the default to be the current authorization ID.
Specifies the default to be the accessor ID of the currently signed-on user.
(Optional) Each of the condition-declarations relates a name to an SQLSTATE or any user-signaled condition (see the information on the SIGNAL statement on SIGNAL Statement). A condition handler can be written for any declared condition, including those defined by the user and not related to any SQLSTATE. Declared conditions are visible to all compound statements executing within their context. For instructions and examples of conditions and handlers, see Diagnostics and Condition Handling.
Note: The names NOT FOUND, SQLWARNING, and SQLEXCEPTION are pre-defined conditions assigned to SQLSTATE subclasses 02, 01, and any other non-00 subclass, respectively.
(Optional) The condition-handler defines an SQL statement (a proc-SQL-stmt that can be a compound statement) to be executed when a given condition occurs. If a condition occurs for which multiple handlers have been written, SQL chooses and executes the most appropriate handler, after searching available handlers in the inner-most context first, as follows.
If no appropriate handler is found, the SQL statement precipitating the error or warning condition aborts with that condition.
Specific SQLSTATEs and condition names are prevented by CA Datacom from being associated with more than one handler, except when the duplicate handler applies only to a generalized condition under which that SQLSTATE is classified, preventing an ambiguity by following the previously given rules. For instructions and examples of conditions and handlers, see Diagnostics and Condition Handling.
After execution of the handler-action statement(s) without any errors or warnings, the SQLSTATE is reset to 00000 and procedure execution continues as separately described for each of the handler types, CONTINUE, EXIT, and UNDO.
If an error handler generates an unexpected (rather than signaled) error or warning condition, that condition is handled like any other error. To avoid endless loops in error handling, we only handle errors generated in the top level error handler. Unhandled error handler errors cause a RESIGNAL command to execute, followed by an exit from the handler and execution continuation as prescribed by any handler executed on the RESIGNAL. On exit from the handlers, the DIAGNOSTICS AREA STACK (see GET DIAGNOSTICS in GET DIAGNOSTICS Statement) contains information on any unresolved errors. Unresolved errors cause an abort of the executing procedure statement.
If a SIGNAL, RESIGNAL, or RAISE ERROR statement is executed as part of a handler, CA Datacom/DB assumes that the signaled condition is intended to be seen by the caller of the procedure, and CA Datacom/DB therefore exits with that error intact and unhandled. After successful execution of a CONTINUE type handler, procedure execution resumes with the statement following the one that generated the error.
EXIT handlers operate in a similar way except that when they complete, procedure execution continues after execution of an implied LEAVE statement (see LEAVE Statement) whose target-label is the one attached to the compound statement in which the handler was defined. The result is that the compound statement containing the statement generating the error aborts but with no error.
UNDO handlers start by performing a ROLLBACK that terminates at the save-point that SQL automatically establishes at the start of the compound statement in which the handler was defined, and closing all cursors open in that context. The handler-action statements then execute. If no unhandled conditions occur, then the condition precipitating the handler execution is cleared, and procedure execution continues after execution of an implied LEAVE statement (see LEAVE Statement) whose target label is the one attached to the compound statement under which the handler was defined. In other words, the compound statement containing the statement generating the error aborts but with no error.
Note: SIGNAL, RAISE ERROR, and RESIGNAL statements that execute inside handlers do not activate additional condition handling. They instead cause the handler to be exited with the signaled condition and cause the execution of the compound statement that triggered the handler to abort. In addition, the compound statement that contains the handler definition is aborted if it is different from the compound statement that triggered the handler, the same as would happen if the handler failed to resolve the triggering condition. The SIGNAL, RAISE ERROR, and RESIGNAL statements should therefore be positioned as the last statements in your condition handler.
A proc-SQL-stmt-list is a list of proc-SQL-stmt statements, each terminated by a semicolon.
|
Copyright © 2014 CA.
All rights reserved.
|
|