Sequences in SQL

A sequence is a database object that generates integers according to rules specified at the time the sequence is created. A sequence automatically generates unique numbers and is sharable between different users in Oracle. Sequences have many purposes in database systems—the most common of which is to generate primary keys automatically. However, nothing binds a sequence to a table’s primary key, so in a sense it’s also a sharable object
Sequences are created with the create sequence statement
CREATE SEQUENCE
START WITH
INCREMENT BY
MINVALUE
MAXVALUE
CYCLE
ORDER
CACHE
1. Start with n Enables the creator of the sequence to specify the first value generated by the sequence. Once created, the sequence will generate the value specified by start with the first time the sequence’s NEXTVAL virtual column is referenced. If no start with value is specified, Oracle defaults to a start value of 1.
2. Increment by n Defines the number by which to increment the sequence every time the NEXTVAL virtual column is referenced. The default for this clause is 1 if it is not explicitly specified. You can set n to be positive for incrementing sequences or negative for decrementing or countdown sequences.
3. Minvalue n Defines the minimum value that can be produced by the sequence. If no minimum value is specified, Oracle will assume the default, nominvalue.
4. Maxvalue n Defines the maximum value that can be produced by the sequence. If no maximum value is desired or specified, Oracle will assume the default, nomaxvalue.
5. Cycle Enables the sequence to recycle values produced when maxvalue or minvalue is reached. If cycling is not desired or not explicitly specified, Oracle will assume the default, nocycle. You cannot specify cycle in conjunction with nomaxvalue or nominvalue. If you want your sequence to cycle, you must specify maxvalue for incrementing sequences or minvalue for decrementing or countdown sequences.
6. Cache n Enables the sequence to cache a specified number of values to improve performance. If caching is not desired or not explicitly specified, Oracle will assume the default, which is to cache 20 values.
7. Order Enables the sequence to assign values in the order in which requests are received by the sequence. If order is not desired or not explicitly specified, Oracle will assume the default, noorder.
Example 1:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,…}. It will cache up to 20 values for performance.
Example 2:
The below sequence is a dercrment one. It starts with 100 and decreases by 1.
CREATE SEQUENCE XX_Notification_number
START WITH 100
INCREMENT BY -1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20
Referencing Sequences in Data Changes
Sequence-value generation can be incorporated directly into data changes made by insert and update statements. This direct use of sequences in insert and update statements is the most common use for sequences in a database. In the situation where the sequence generates a primary key for all new rows entering the database table, the sequence would likely be referenced directly from the insert statement. Note, however, that this approach sometimes fails when the sequence is referenced by triggers. Therefore, it is best to reference sequences within the user interface or within stored procedures. The following statements illustrate the use of sequences directly in changes made to tables:
INSERT INTO expense(expense_no, empid, amt, submit_date)
VALUES(countdown_20.nextval, 59495, 456.34, ’21-NOV-99′);
SEQUENCE_NAME.NEXTVAL & SEQUENCE_NAME.CURRVAL
Once the sequence is created, it is referenced using the CURRVAL and NEXTVAL pseudocolumns. The users of the database can view the current value of the sequence by using a select statement. Similarly, the next value in the sequence can be generated with a select statement. Because sequences are not tables—they are only objects that generate integers via the use of virtual columns—the DUAL table acts as the “virtual” table from which the virtual column data is pulled. As stated earlier, values cannot be placed into the sequence; instead, they can only be selected from the sequen
Example 3:
Select XX_Notification_number.NEXTVAL from dual
Select XX_Notification_number.CURRVAL from dual
Alter sequence
The time may come when the sequence of a database will need its rules altered in some way. For example, you may want sequence XX_Notification_number to decrement by a different number. Any parameter of a sequence can be modified by issuing the alter sequence statement. The following is an example:

Alter Sequence sequence_name//Write new values of the sequence parameters
START WITH 100
INCREMENT BY -1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20
Example 4:
alter sequence XX_Notification_number
increment by -2;