Sequences (SQL)

A sequence is an application object that generates integer values (64-bit) primarily used for unique keys.

A sequence is not bound to a table, therefore it can be used for creating keys that are unique to a group of tables.

Sequences can be created, modified and created either from the 602SQL Client or by the CREATE SEQUENCE, ALTER SEQUENCE and DROP SEQUENCE statements. Sequences are designated by name in the application context. Details about sequence syntax can be found here.

Sequence Properties

You can specify the minimum and maximum values, along with the step for generating values in a sequence definition. If the step is a negative number, the numbers are generated in descending order. You can also specify the initial value of a sequence and a flag that specifies if the sequence should should repeat periodically after a certain limit is reached. If the limit is reached and the flag for repeating is not set, then the error sqlstate W0219 (SEQUENCE_EXHAUSTED) occurs when an attempt is made to generate the next value.

If the client requests a new value from the sequence and later rolls the transaction back, the allocated value is not returned to the sequence and it will not be used again.

The last value the client receives from a unique key generator in a table (default value UNIQUE) or from a sequence, can be found in the global system variable @@IDENTITY.

Getting Values from a Sequence (Oracle Compatible Method)

  sequence_name.CURRVAL  
  sequence_name.NEXTVAL

NEXTVAL is the next value in a sequence. CURRVAL is the current value that was last returned by NEXTVAL. CURRVAL may not be used before NEXTVAL, or the error sqlstate W0220 (NO_CURRENT_VAL) occurs.

Each client will preserves its own CURRVAL value, therefore different clients will have different CURRVAL values at the same time.

Getting Values from a Sequence (SQL 2003 Compatible Method)

NEXT VALUE FOR sequence_name

If this value is evaluated multiple times inside a statement, it will return the same value each time. For record iteration statements (UPDATE, INSERT ... SELECT), the same value is returned for each call that deals with processing a single record.

Getting Values from a Sequence to a Client Variable

The next value generated by the sequence SEQ may be obtained from a client program into a global integer variable VAL using this statement:

  SET :>val=SEQ.NEXTVAL

Sequences in the Client Developer Interface

A sequence also can be created or modified using the interactive designer.

The selected sequence description can be displayed in the Output window of the 602SQL Client from the Source tab. The next value in the sequence is displayed in the comments before the definition. If a buffer is defined for the sequence, then the displayed value may not be the next NEXTVAL value, since some unused values may still be in the buffer.

The next value is part of the sequence definition when exporting an application with data. The next value of the sequence is equal to the initial value when exporting an application without data.

602SQL Variances

Sequences are not part of the SQL 92 or SQL3 standards, but they are mentioned in the SQL 2003 standard. Sequences were present in Oracle and 602SQL before this standard was introduced. Both sequence versions are fully implemented in the current 602SQL version, because each version has its own advantages and disadvantages.

List of topics:

Triggers

SQL Language in 602SQL

Cursors in SQL