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