Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 5 of 11
To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
Once a sequence is created, you can access its values in SQL statements with the CURRVAL
pseudocolumn (which returns the current value of the sequence) or the NEXTVAL
pseudocolumn (which increments the sequence and returns the new value).
See Also:
|
To create a sequence in your own schema, you must have CREATE
SEQUENCE
privilege.
To create a sequence in another user's schema, you must have CREATE
ANY
SEQUENCE
privilege.
The following statement creates the sequence ESEQ
:
CREATE SEQUENCE eseq INCREMENT BY 10;
The first reference to ESEQ.NEXTVAL
returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|