A sequence is a database object in Oracle Database that generates unique numbers. A Sequence is mostly used for generating primary key values. For example, you can create a sequence and use it to insert a new record to your table shown as follows:
SQL> CREATE SEQUENCE member_id_seq MINVALUE 1 MAXVALUE 1000 START WITH 1 INCREMENT BY 2 NOCYCLE CACHE 5; Sequence created. SQL> INSERT INTO member (id, name) VALUES (member_id_seq.NEXTVAL, 'Thomas'); 1 row created.
The sequence CACHE is used to allocate a set of sequence numbers and keeps them in memory to allow faster access. You can use NOCACHE option to turn off the cache. When the last of the sequence numbers in the cache has been used, the database reads another set of numbers into the cache. CYCLE and NOCYCLE option indicates if the sequence can or cannot generate more values after reaching its maximum or minimum value.
You can check the current value of a sequence using CURRVAL. When selecting the NEXTVAL, the sequence will automatically increment. The following commands shows sequence increases:
You can check the current value of a sequence using CURRVAL. When selecting the NEXTVAL, the sequence will automatically increment. The following commands shows sequence increases:
SQL> select member_id_seq.nextval from dual; NEXTVAL ---------- 3 SQL> select member_id_seq.nextval from dual; NEXTVAL ---------- 5
You can also use ALTER command to reset incremental sequence values.
SQL> ALTER SEQUENCE seq1 INCREMENT BY -1; SQL> select member_id_seq.curval from dual; CURVAL ---------- 3 SQL> select member_id_seq.nextval from dual; NEXTVAL ---------- 2
You can drop a sequence using the DROP command shown as follows:
SQL> drop sequence member_id_seq;