www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

SQL Reference

Datatypes
User Defined Types
XML Column Type
Identifier Case & Quoting
Wide Character Identifiers
Qualified Names
Literals, Brace Escapes
CREATE TABLE Statement
DROP TABLE Statement
CREATE INDEX Statement
DROP INDEX Statement
ALTER TABLE Statement
CREATE VIEW Statement
CREATE XML SCHEMA Statement
DROP XML SCHEMA Statement
Sequence Objects
INSERT Statement
UPDATE Statement
SELECT Statement
COMMIT WORK, ROLLBACK WORK Statement
CHECKPOINT, SHUTDOWN Statement
Stored Procedures as Views & Derived Tables
GRANT, REVOKE Statement
SET Statement
Anytime Queries
Best Effort Union
Standard and User-Defined Aggregate Functions
Virtuoso SQL Optimization
SQL Inverse Functions
SQL Grammar
Bitmap Indices
Transitivity in SQL
Fast Phrase Match Processor

8.16. Sequence Objects

Virtuoso supports sequence objects. These can be used to generate sequential numbers which can be used as unique identifiers. A sequence object is guaranteed never to give the same number twice. Each sequence has a name and a state. The state of a sequence is stored in the database at checkpoint time. Between checkpoints sequence states are logged so that a possible roll forward recovery will not lose information.

The SQL functions sequence_next() and sequence_set() are used to access and set the state of sequences. These take the name of the sequence as argument. This is a server/wide unique string. There are no restrictions on the length or character set of the sequence

Sequences do not have to be separately created. A sequence object will automatically be generated when first referenced by sequence_next() or sequence_set.

sequence_next (in name varchar) returns integer
sequence_set (in name varchar, in state integer, in mode integer)
		returns integer

Function sequence_next() returns the current state of the specified sequence and atomically increments it by one. The next call will thus return a number one greater than the previous. The sequence is shared between all connections and all transactions. Using a sequence never involves locking.

Function sequence_set() sets and returns the state of a sequence object. The next call to sequence_next() will return this same number. If mode equals 0, the state is set regardless of the previous state. If mode is non-zero, the state is set only if the new state is greater then the previous state. Calling sequence_set ('sequence'' , 0, 1) will always return the sequence's state without changing it.

Each autoincrement column corresponds to an internal sequence object. The name of the sequence object is'DB.DBA.' plus the concatenation of the table's qualifier, owner, table name and column name, e.g. 'DB.DBA.db.dba.my_table.ai_column'. The user does not normally need to know about the sequence associated with an autoincrement column unless he or she wishes to change the sequence values using the sequence_set() function and the sequence objects name.

See the section on identity columns under create table and the function identity_value and the related ODBC statement option SQL_GETLASTSERIAL for more.