1  SIGNAL_Control
   Passes the signaled SQLSTATE status parameter back to the
   application or SQL interface and terminates the current routine
   and all calling routines.
 

2  Environment
   You can use the SIGNAL statement in a compound statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 SIGNAL -+-> value-expr ---------------------+-----+
         +-> SQLSTATE VALUE 'string-literal'-+     |
   +-----------------------<-----------------------+
   ++----------------------------+------------------>
    +-> ( --> signal-arg --> ) --+

 

2  Arguments
 

3  signal-arg

   Specifies a value expression. The specified value is converted to
   a CHARACTER(80) CHARACTER SET UNSPECIFIED string and returned
   as a secondary message to the client application. If the
   value expression converts to a character string longer than 80
   characters, it is truncated.

   You can use the sql_get_error_text routine to extract the signal-
   arg text in an application.
 

3  string-literal

   A quoted string literal which represents the SQLSTATE value.
 

3  value_expr

   Expects a character value expression which is used as the
   SQLSTATE status parameter. Any provided value expression is
   converted to a CHAR(5) value which is passed to SIGNAL.

   See Value Expressions for more information on value expressions.
   See SQLCA for more information about SQLSTATE.
 

2  Examples
   Example 1: Using the SIGNAL and RETURN statements, multiline
   comments, and stored functions

   The example uses a table, NEXT_KEY_TABLE, to maintain a list
   of key names and their current values. In this example, only
   a single key is created with the name EMPLOYEE_ID. Each time
   the function is called, it fetches the value from the NEXT_KEY_
   TABLE and returns the next value. If the named key is not found,
   an error is returned (SQLSTATE 22023 is defined as "invalid
   parameter value").

   SQL> CREATE DOMAIN key_name
   cont>   CHAR(31)
   cont>   CHECK (VALUE IS NOT NULL)
   cont>   NOT DEFERRABLE;
   SQL> --
   SQL> CREATE TABLE next_key_table (
   cont>   next_key_val INTEGER NOT NULL,
   cont>   next_key_name key_name UNIQUE);
   SQL> --
   SQL> INSERT INTO next_key_table (next_key_name, next_key_val)
   cont>   VALUES ('EMPLOYEE_ID', 0);
   1 row inserted
   SQL> --
   SQL> CREATE MODULE tools
   cont>   LANGUAGE SQL
   cont>   FUNCTION next_key (IN :key_name key_name)
   cont>      RETURNS INTEGER
   cont>      COMMENT IS 'This routine fetches the next value of the'/
   cont>                 'specified entry in the sequence table.  The'/
   cont>                 'passed name is converted to uppercase before'/
   cont>                 'retrieval (see the DEFAULT clause for compound'/
   cont>                 'statements).  The UPDATE ... RETURNING statement'/
   cont>                 'is used to fetch the new value after the update.'/
   cont>                 'If no entry exists, then an error is returned.';
   cont>      BEGIN
   cont>        DECLARE :rc, :new_val INTEGER DEFAULT 0;
   cont>        DECLARE :key_name_upper key_name DEFAULT UPPER(:key_name);
   cont>        DECLARE :invalid_parameter CONSTANT CHAR(5) = '22023';
   cont> --
   cont>        UPDATE next_key_table
   cont>        SET next_key_val = next_key_val + 1
   cont>        WHERE next_key_name = :key_name_upper
   cont>        RETURNING next_key_val
   cont>        INTO :new_val;
   cont> --
   cont>        GET DIAGNOSTICS :rc = ROW_COUNT;
   cont>        TRACE 'NEXT_KEY is ', COALESCE(:new_val, 'NULL'), ', RC is ', :rc;
   cont> --
   cont>        IF :rc = 0 THEN
   cont>            TRACE 'No entry exists for KEY_NAME: ', :key_name_upper;
   cont>            SIGNAL :invalid_parameter;
   cont>         ELSE
   cont>            TRACE 'Returning new value for ', :key_name_upper, :new_val;
   cont>            RETURN :new_val;
   cont>        END IF;
   cont> --
   cont>      END;
   cont> END MODULE;
   SQL> --
   SQL> CREATE TABLE employee (
   cont>   employee_id         INTEGER,
   cont>   last_name           CHAR(20),
   cont>   birthday            DATE);
   SQL> --
   SQL> -- Turn on the TRACE flag so we can see the function working.
   SQL> --
   SQL> SET FLAGS 'TRACE';
   SQL> --
   SQL> INSERT INTO employee (employee_id, last_name, birthday)
   cont>   VALUES (next_key('EMPLOYEE_ID'), 'Smith', DATE'1970-1-1');
   ~Xt: NEXT_KEY is 1          , RC is 1
   ~Xt: Returning new value for EMPLOYEE_ID                    1
   1 row inserted
   SQL> --
   SQL> INSERT INTO employee (employee_id, last_name, birthday)
   cont>   VALUES (next_key('EMPLOYEE_ID'), 'Lee', DATE'1971-1-1');
   ~Xt: NEXT_KEY is 2          , RC is 1
   ~Xt: Returning new value for EMPLOYEE_ID                    2
   1 row inserted
   SQL> --
   SQL> INSERT INTO employee (employee_id, last_name, birthday)
   cont>   VALUES (next_key('EMPLOYEE_ID'), 'Zonder', DATE'1972-1-1');
   ~Xt: NEXT_KEY is 3          , RC is 1
   ~Xt: Returning new value for EMPLOYEE_ID                    3
   1 row inserted
   SQL> --
   SQL> SELECT * FROM employee ORDER BY EMPLOYEE_ID;
    EMPLOYEE_ID   LAST_NAME              BIRTHDAY
              1   Smith                  1970-01-01
              2   Lee                    1971-01-01
              3   Zonder                 1972-01-01
   3 rows selected
   SQL> --
   SQL> -- Show the error if the unknown key_name is passed.
   SQL> --
   SQL> INSERT INTO employee (employee_id, last_name, birthday)
   cont>   VALUES (next_key('EMPLOYEEID'), 'Zonder', DATE'1972-1-1');
   ~Xt: NEXT_KEY is 0          , RC is 0
   ~Xt: No entry exists for KEY_NAME: EMPLOYEEID
   %RDB-E-SIGNAL_SQLSTATE, routine "NEXT_KEY" signaled SQLSTATE "22023"

   Example 2: Specifying a Secondary Error

   SQL> BEGIN
   SQL> SIGNAL SQLSTATE 'RR000' (' Compound Statement Failed');
   cont> END;
   %RDB-E-SIGNAL_SQLSTATE, routine "(unnamed)" signaled SQLSTATE "RR000"
   -RDB-I-TEXT,  Compound Statement Failed