Exception Declaration

Object Declaration in SQL

Cursor Declaration

Handler Declaration (SQL)

handler_declaration ::= DECLARE { CONTINUE | EXIT | REDO | UNDO } HANDLER FOR exception 
    {, exception } statement;
exception ::=  exception_identifier | SQLSTATE [ VALUE ] sqlstate | SQLEXCEPTION | SQLWARNING 
    | NOT FOUND 

A handler specified by the statement is assigned to a specified exception or a group of exceptions by this declaration, and may be used to treat these exceptions in the extent of this declaration (in this block statement).

If the exception_identifier is specified in the handler declaration, it must be defined earlier in the exception declaration. The handler is executed, if this exception is activated by the SIGNAL statement. The same exception may not be used twice in the same handler declaration. If a sqlstate is defined in the exception declaration, then the handler is assigned to this sqlstate as well.

If the SQLSTATE sqlstate is defined in the handler declaration, then the handler is called each time this sqlstate occurs.

If the SQLEXCEPTION, SQLWARNING or NOT FOUND keywords are specified in the handler declaration, then the handler is executed when any error occurs (except for the reading of non-existent records using the FETCH statement for SQLEXCEPTION, any warning for SQLWARNING or when reading a nonexistent record in the FETCH statement for NOT FOUND). The exception Reading a nonexistent record (sqlstate '02000') is unique, because the handler must be declared each time the LOOP...FETCH...END structure is used.

If an exception occurs when executing an SQL statement, the closest handler is searched for (first in the internal statement block, then in the external statement blocks). If a handler is found, the following will determine execution:

• CONTINUE: The handler is executed and the statements that follow the statement that caused the exception is executed in the normal way (especially for exception type "handlable exception condition" and "not found").
• EXIT: The handler is executed and the statements outside the statement block that contain the handler definition are executed in the normal way (especially for exception type "rollback exception condition").
• UNDO: A rollback is executed, returning to the state before entering the statement block. The handler is executed, then the statements outside of the statement block that contain the handler definition are executed in the normal way.
• REDO: A ROLLBACK is executed, returning to the state before entering the statement block. The handler is executed, then the statements from the statement block that contain the handler definition are executed again.

The REDO and UNDO handles may be declared only in an atomic statement block. Transaction statements may not be used in these handlers.

Global handles of the 602SQL Server are declared in the Module_globals procedure.

Example:

The next row must be tested while going through a cursor.

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET err_notfound=TRUE; END;
LabelLoop: LOOP 
    FETCH NEXT FROM cur INTO ThisCompany, ThisValue; 
    IF err_notfound IS TRUE THEN LEAVE LabelLoop; END IF;
...

If the item not found in the table, assign number -1:

...
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '21000' BEGIN SET it_num = -1; END;
      SELECT id_item INTO it_num
      FROM TItems WHERE ...; 
    END;
...

An error 22018 can occur in the conversion function (i.e. cannot converted), in this case return NULL.

FUNCTION `Convert( IN inputChar NCLOB) ) RETURNS CLOB COLLATE CZ_ISO;
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '22018' BEGIN RETURN NULL; END;
  RETURN CAST(inputChar) AS CLOB COLLATE CZ_ISO);
END

Exception Declaration

Object Declaration in SQL

Cursor Declaration