The COMPOUND statement executes other SQL statements of a routine in a common context. Common local data (variables and exception names), common local cursors, and common local exception routines apply for these SQL statements.
The COMPOUND statement may only be specified in a routine, i.e. in the context of a CREATE PROCEDURE or CREATE FUNCTION statement. It is then the only statement in the routine. Routines and their use in SESAM/SQL are described in detail in chapter "Routines".
[ label :]
BEGIN [[NOT] ATOMIC]
[ local_data ]
[ local_cursor ]
[ local_exception_handling ]
[ routine_sql_statement; [ routine_sql_statement; ]... ]
END [ label ]
label
The label in front of the COMPOUND statement (start label) indicates the start of the COMPOUND statement. It may not be identical to another label in the COMPOUND statement.
The start label need only be specified when the COMPOUND statement is to be terminated by means of a LEAVE statement
The label at the end of the COMPOUND statement (end label) indicates the end of the COMPOUND statement. If the end label is specified, the start label must also be specified. Both labels must be identical.
[NOT] ATOMIC
Determines whether the COMPOUND statement is atomic or non-atomic.This specification influences local exception handling, see "COMPOUND - Execute SQL statements in a common context".If nothing is specified, NOT ATOMIC applies.
local_data
Defines local variables and exception names for the COMPOUND statement, see section "Local data".
local_cursor
Defines local cursors for the COMPOUND statement, see section "Local cursors".
local_exception_handling
Defines local exception routines for the COMPOUND statement, see section "Local exception routines".
SQLSTATEs of classes 40xxx and SQLSTATEs from class '50xxx' cannot be handled in the local exception routines. When such an SQLSTATE occurs, the routine is immediately aborted. In the case of an SQLSTATE of class '40xxx', the entire transaction is also reset.
SQLSTATEs which are not specified explicitly in the exception routines in the form of a class or explicitly ("unspecified SQLSTATEs") are not handled by any exception routine. The same applies when no local exception handling is defined. In these cases SESAM/SQL automatically performs exception handling as follows:
SQLSTATEs of classes '01xxx’ (warning) or '02xxx’ (no data) are ignored, i.e. the routine is continued as when the SQL statement is executed successfully (SQLSTATE = '00000').
The following actions are performed for SQLSTATEs which are not in class '01xxx', '02xxx' or '40xxx':
Open local cursors are closed.
When ATOMIC is specified in the COMPOUND statement, all updates made in the context of the COMPOUND statement are undone.
When NOT ATOMIC (default value) is specified in the COMPOUND statement, only updates made in the context of the errored SQL statement are undone.
The COMPOUND statement and with it the routine are aborted. The SQL statement in which the routine was used returns the SQLSTATE concerned.
routine_sql_statement
SQL statement which is to be executed in the COMPOUND statement. An SQL statement is concluded with a ";" (semicolon). Multiple SQL statements can be specified one after the other. They are executed in the order specified.No (further) COMPOUND statement may be specified in the COMPOUND statement. In other words, no nested COMPOUND statements are permitted (exception: local exception routines, see "COMPOUND - Execute SQL statements in a common context".) No privileges are checked before an SQL statement is executed. An SQL statement in a routine may access the parameters of the routine and (if the statement is part of a COMPOUND statement) local variables, but not host variables.
The syntax and meaning of routine_sql_statement are described centrally in section "SQL statements in routines". The SQL statements named there may not be used.
Example
You will find examples in chapter "Routines" and in the demonstration database of SESAM/SQL (see the “ Core manual”).
See also
CREATE PROCEDURE, CREATE FUNCTION, CALL, DROP PROCEDURE, DROP FUNCTION, CASE, FOR, IF, ITERATE, LEAVE, LOOP, REPEAT, SET, WHILE, RETURN, GET DIAGNOSTICS, SIGNAL, RESIGNAL, SELECT, INSERT, UPDATE, DELETE, MERGE, OPEN, FETCH, UPDATE, DELETE, CLOSE
Local data
Local data comprises variables or exception names which can only be addressed in the COMPOUND statement.
A data type and, if required, a default value is defined for variables. They have no indicator variable. They can be used in local cursor definitions, local exception routines, and the SQL statements of the COMPOUND statement.
par_).To facilitate understanding, exception names define a name for an exception (without specifying an associated SQLSTATE) or a name for an SQLSTATE. They can be used in local exception routines, see "COMPOUND - Execute SQL statements in a common context".
local_data ::= DECLARE declaration ; [DECLARE declaration ;] ...
declaration ::=
{
local_variable [, local_variable ], ... data_type [ default ] |
error_name CONDITION [FOR sqlstate ];
}
sqlstate ::= SQLSTATE [VALUE] alphanumeric_literal
Multiple variables of the same data type with the same SQL default value can be specified one after another, separated by "," (comma). The definition of a local date is concluded with “;” (semicilon). Multiple definitions can be specified one after the other.
local_variable
Name of the local variable. The names of all local variables must differ from each other, from the local exception names, and from the names of the routine’s parameters.
data_type
Data type of the local variable. Only unqualified local variables exist. dimension may not be specified.
default
Specifies the SQL default value for the local variable. The assignment rules for default values apply, see section "Default values for table columns").
exception_name
Name of an exception or SQLSTATE.
All exception names of all local variables must differ from each other, from the local variables, and from the names of the routine’s parameters.
FOR sqlstate
SQLSTATE (alphanumeric literal with the length 5) which is named by exception_name. The restrictions for the set of SQLSTATEs must be borne in mind, see "Local exception routines".
FOR sqlstate omitted
Local exception names without FOR clause can be triggered only by a SIGNAL or RESIGNAL statement. They are mapped to the SQLSTATE '45000' (unspecified user exception) and reported to the application program. exception_name appears as an insert in the error message.
Example
Definition of local variables.
DECLARE a,b,c SMALLINT DEFAULT 0;
DECLARE mytim TIME(3) DEFAULT CURRENT_TIME;
Definition of exception names:
DECLARE Tab_not_accessible CONDITION FOR SQLSTATE '42SQK'; DECLARE "CHECK problem" CONDITION FOR SQLSTATE '23SA1'; DECLARE "Unknown problem" CONDITION;
Local cursors
With the definition of local cursors, cursors are defined which can only be addressed in the COMPOUND statement. The names of the local cursors must differ from each other.
Local cursors can be used in local exception routines and the SQL statements of the COMPOUND statement.
The SQL statements STORE and RESTORE are not permitted for local cursors.
local_cursor ::= { declare_cursor_statement ; } ...
A cursor definition is concluded with a ";" (semicolon).
Multiple cursor definitions can be specified one after the other.
declare_cursor_statement
DECLARE CURSOR statement (see section "DECLARE CURSOR - Declare cursor" ) with which the local cursor is defined. The WITH HOLD clause may not be specified.
A local cursor differs from a normal cursor only in its limited area of validity.
Example
See section "DECLARE CURSOR - Declare cursor".
Local exception routines
local_exception_handling ::= exception_routine ; [ exception_routine ;]...
exception_routine ::= DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR
error_list { routine_sql_statement | compound_statement }
error_list ::= { class_list | sqlstate_or_error_list }
class_list ::= { SQLEXCEPTION | SQLWARNING | NOT FOUND }
[{ SQLEXCEPTION | SQLWARNING | NOT FOUND }] ...]
sqlstate_or_error_list ::= { sqlstate | error_name }
[,{ sqlstate | alphanumeric_literal | error_name },...]
sqlstate ::= SQLSTATE [VALUE] alphanumeric_literal
The definition of local exception routines determines what response is made when, during processing of an SQL statement in the context of the COMPOUND statement, an SQLSTATE ≠ '00000' is reported.
The SQLSTATEs of the classes 0xxxx (with the exception of SQLSTATE = '00000'), '1xxxx', '2xxxx', '3xxxx', and '4xxxx' (with the exception of class '40xxx') can be handled.
Exception routines are concluded with ";" (semicolon). Multiple exception routines can be specified one after the other.
When an SQLSTATE ≠ '45000' occurs (defined in the class_list or as sqlstate or exception_name), the exception routine for the specified SQLSTATE is executed.
If the SQLSTATE '45000' (unspecified user condition) occurs as a result of a SIGNAL or RESIGNAL statement, the exception_name of the exception information is evaluated, and the corresponding exception routine is executed.
DECLARE
Type of exception handling in accordance with the SQLSTATE. See also the section "Success of an SQL statement in a routine"
CONTINUE
The updates which were made in the context of the errored SQL statement are undone.
The exception routine's SQL statement is executed.
If this SQL statement was terminated without success, the routine is aborted, and this SQLSTATE is returned to the user.
If this SQL statement was terminated error free, the routine is continued. The SQL statement which reported the SQLSTATE and consequently triggered exception handling is regarded as successful.
EXIT
The updates which were made in the context of the errored SQL statement are undone.
The exception routine's SQL statement is executed. Open local cursors are closed.
If this SQL statement was terminated without success, the routine is aborted, and this SQLSTATE is returned to the user.
If this SQL statement was terminated error free, the routine is terminated. The COMPOUND statement is regarded as successful (SQLSTATE = '00000' is returned).
UNDO (permitted only when ATOMIC is specified in the COMPOUND statement)
All updates which were made in the context of the COMPOUND statement are undone.
The exception routine's SQL statement is executed. Open local cursors are closed.
If this SQL statement was terminated without success, the routine is aborted, and this SQLSTATE is returned to the user.
If this SQL statement was terminated error free, the routine is terminated. The COMPOUND statement is regarded as successful (SQLSTATE = '00000' is returned).
class_list
Specification of SQLSTATE sets:
SQLWARNING indicates the SQLSTATEs of class 01xxx (warning).
NOT FOUND indicates the SQLSTATEs of class 02xxx (no data).
SQLEXCEPTION indicates all other SQLSTATEs of classes 0xxxxx through 4xxxx (with the exception of the SQLSTATE '00000' and class 40xxx) which can be handled in the context of an exception routine.
sqlstate
Explicit specification of SQLSTATEs.
Each alphanumeric literal must represent an SQLSTATE in 5 characters (digits or uppercase letters). Only SQLSTATEs which can be handled in the context of an exception routine may be specified.
The list below shows examples of SQLSTATEs for which separate exception handling can make sense (see the " Messages" manual):
01004 | String data was truncated on the right |
Other SQLSTATEs, e.g. syntax errors, are best handled via one of the previously described sets of SQLSTATEs.
exception_name
Name of an exception or SQLSTATE, see "Local data".
Only exception names which can be handled in the context of an exception routine may be specified.
routine_sql_statement
SQL statement which is to be executed in the exception routine.
The syntax and meaning of routine_sql_statement are described centrally in section "SQL statements in routines". The SQL statements named there may not be used.
compound_statement
COMPOUND statement which contains multiple SQL statements, see section "COMPOUND - Execute SQL statements in a common context". Except for the permissible routine_sql_statements, a COMPOUND statement specified here may not contain any definitions of local data, cursors, or exception routines.
Example
Definition of unqualified exception handling with two exception routines.
DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND SET eot=1; DECLARE EXIT HANDLER FOR SQLSTATE '23SA0' BEGIN END;