What is sequence in oracle plsql?

Sequence is used for creating value for primary key generation. Sequence guarantees generation of a new value, irrespective of a session or a user, by default. Sequence doesn’t give guarantee of order in generating values. So, the sequence guarantees uniqueness, not ordering.

E .g. if a sequence is referred from the terminal one, you may get values, such as 1,2,3,4, and if from another terminal, you may get values, such as 11,12,13,14

Values from a sequence can be accessed using any of the two methods NEXTVAL and CURVAL

NEXTVAL gives current value and increments the sequence.

CURVAL only returns the current value.

You can create a sequence using the following parameters:

·         INCREMENT BY: the sequence is incremented by this value.

·         MINVALUE/ NOMINVALUE: this decides the initial value of sequence.

·         MAXVALUE/NOMAXVALUE: it is the maximum value of a sequence0

·         CYCLE/NOCYCLE: if it is a cycle parameter then the values generated will be cyclic order, i.e. after reaching MAXVALUE the generation will start from MINVALUE.

·         CACHE/NOCACHE: if it is cache, then the call to NEXTVAL puts that many values in the session memory. i.e. if the cache is 20, then first call to NEXTVAL caches the first 20 values. If there is a sudden shutdown, then these values are lost.

·         ORDER/NOORDER: it indicates that the generated values are ordered according to time.

Create sequence test_seq

Increment by 1

Start with 1

Nomaxvalue

Nocycle

Cache 10;