IST480: Sequences.

Sequences.

To create a sequence Oracle uses the create sequence command with the following syntax:

create sequence SeqName
  [ increment by integer_value ]
  [ start with integer_value ]
  [ maxvalue integer_value | nomaxvalue ]
  [ minvalue integer_value | nominvalue ]
  [ cycle | nocycle ]
  [ cache integer_value | nocache]
  [ order | noorder ];

Where:

increment by indicates how the sequence numbers should change. The default increment is 1. Increment can be both positive and negative. A negative number will cause decrement (decrease the value of) the sequence.
start with indicates the initial value of the sequence. The default start value for ascending sequences is minvalue and maxvalue for descending.
minvalue is the lowest number the sequence will generate. The default is 1 for ascending sequences.
maxvalue is the highest number the sequence will generate. The default is -1 for ascending sequences.
cycle option makes the sequence to restart either type of sequence where its max value or min value is reached.
cache allows a preallocated set of sequence numbers to be kept in memory. The default is 20. The value set must be less than max value minus min value.
order guarantees that the sequence numbers will be assigned to instances requesting them in the order the request are received. This is useful in applications requiring a history of the sequence in with the transactions took place.

Examples:

oracle> create sequence MyFirstSequence;
oracle> create sequence MySecondSequence 
         increment by 3 
         start with 100;
oracle> create sequence MyThirdSequence
         maxvalue 40
         minvalue 30
         increment by 3
         cache 3
         cycle;

To use a sequence we can use two of its properties: NextVal and CurrVal. The NextVal attached to a sequence name tells Oracle that you want the next available sequence number from this particular sequence. This is guaranteed to be unique; Oracle will not give it to anyone else, even if it gets a request at the same time.

To use the same number more than once (such as insert into related tables), CurrVal value is used, after the first use of NextVal.

Example:

oracle> select MyFirstSequence.NextVal 
          from dual;
   NEXTVAL
----------
        1

1 row selected.
oracle> select MyFirstSequence.NextVal 
          from dual;
   NEXTVAL
----------
        2

1 row selected.
oracle> select MyFirstSequence.CurVal 
          from dual;
   NEXTVAL
----------
        2

1 row selected.

Assignment: To practice with this please create a couple of sequences and try to get their values. Please open more than one Oracle session to test your sequences.

To view all your sequences you can use inner Oracle view USER_SEQUENCES that includes the following information about the sequences of the user running the select statement:

name of the sequence

min value

max value

increment value

cyclic or not

ordered or not

size of the cache

last number given.

If you are interested in all sequences created in Oracle or in sequences of a particular user you may use service Oracle table ALL_SEQUENCES that contains all the information above and the name of the user who own a sequence.