Sequence Objects

In Chapter 3, "Overview of SQL PL Language Elements," you learned how to use sequence objects and identity columns to generate incremental numeric values. These methods are commonly used to generate ID values for primary keys. Other relational database products on the market each have their own way of generating incremental values. For vendors who need to create applications to support multiple database products, stored procedures are sometimes used to encapsulate database-specific syntax so that the application layer remains database-agnostic.

Here, the previous discussion of sequence objects is extended to demonstrate how a stored procedure can be used to provide a consistent syntax (across multiple database vendors) for obtaining the next value of a given sequence object. The stored procedures can be maintained along with other database objects. An example is shown in Figure 10.23.

Figure 10.23. Sequence value generation wrapped in an SQL procedure.

 CREATE PROCEDURE seqnextval ( IN p_seqname VARCHAR(50)                             , OUT p_nextval INT )     LANGUAGE SQL     SPECIFIC seqnextval     -- applies to LUW and iSeries     -- WLM ENVIRONMENT <env>-- applies to zSeries snv: BEGIN     -- DECLARE VARIABLES     DECLARE v_dynstmt VARCHAR(100);     -- DECLARE CONDITIONS                                      -- (1)     -- SQLSTATE 42704, object not found     DECLARE seq_notfound CONDITION FOR SQLSTATE '42704';     -- SQLSTATE 42501, object privilege error     DECLARE obj_access_error CONDITION FOR SQLSTATE '42501';     -- DECLARE CURSORS     DECLARE c_nextval CURSOR FOR v_prepstmt;     -- DECLARE HANDLERS                                        -- (2)     DECLARE EXIT HANDLER FOR seq_notfound         RESIGNAL SQLSTATE '70000'          SET MESSAGE_TEXT = 'Sequence Object NOT FOUND';     DECLARE EXIT HANDLER FOR obj_access_error         RESIGNAL SQLSTATE '70001'          SET MESSAGE_TEXT = 'User encounters privilege problems';     -- Procedure body     SET v_dynstmt = 'SELECT NEXTVAL for ' || p_seqname || ' FROM SYSIBM.SYSDUMMY1' ;                                            -- (3)     PREPARE v_prepstmt FROM v_dynstmt;                         -- (4)     OPEN c_nextval;     FETCH c_nextval INTO p_nextval;                            -- (5)     CLOSE c_nextval; END snv 

Whereas code manageability is improved by wrapping NEXTVAL in stored procedures, keep in mind the overhead needed to load and run stored procedures.

This stored procedure takes a sequence object name as input and returns the next sequence value. The statement on Line (3) is used to generate the value and must be prepared dynamically on Line (4) because the sequence object name is not known until the time of execution. The generated value is then fetched on Line (5) and stored in the OUT parameter.

Conditions and error handlers are defined on Lines (1) and (2) to catch object-not-found and privilege errors. If the given sequence is not defined or the sequence schema name is not correctly provided, the stored procedure will result in an OBJECT NOT FOUND error. A privilege error may result if the user does not have USAGE on the sequence or the user does not have EXECUTE on the stored procedure package itself. For more information about privileges required for creating or executing a stored procedure, refer to Appendix E, "Security Considerations in SQL Procedures."

The behavior of a sequence object can be changed with the ALTER SEQUENCE. Another stored procedure can be defined to alter a sequence object with specific options (see Figure 10.24).

Figure 10.24. Sequence object alteration wrapped in an SQL procedure.

 CREATE PROCEDURE alterseq ( IN p_seqname VARCHAR(50)                           , IN p_options VARCHAR(100) )     LANGUAGE SQL     SPECIFIC alterseq              -- applies to LUW and iSeries     -- WLM ENVIRONMENT <env>       -- applies to zSeries aseq: BEGIN     -- DECLARE VARIABLES     DECLARE v_dynstmt VARCHAR(100);     DECLARE v_retcode INTEGER DEFAULT 0;     -- DECLARE CONDITIONS     -- SQLSTATE 42704, object not found     DECLARE seq_notfound CONDITION FOR SQLSTATE '42704';     -- SQLSTATE 42501, object privilege error     DECLARE obj_access_error CONDITION FOR SQLSTATE '42501';     -- DECLARE HANDLERS     DECLARE EXIT HANDLER FOR seq_notfound         RESIGNAL SQLSTATE '70000'          SET MESSAGE_TEXT = 'Sequence Object NOT FOUND';     DECLARE EXIT HANDLER FOR obj_access_error         RESIGNAL SQLSTATE '70001'          SET MESSAGE_TEXT = 'User encounters privilege problems';     DECLARE EXIT HANDLER FOR SQLEXCEPTION         RESIGNAL SQLSTATE '70002'          SET MESSAGE_TEXT = 'SQL Exception raised';     -- Procedure body     SET v_dynstmt = 'ALTER SEQUENCE ' || p_seqname || ' ' || p_options;     PREPARE v_prepstmt FROM v_dynstmt;     EXECUTE v_prepstmt; END aseq 

To test the ALTERSEQ stored procedure, you can issue

 call alterseq('staff_seq', 'restart with 500') 

This will RESTART the sequence number at 500.