Sequences

A sequence is a database item that generates a sequence of integers. You typically use the integers generated by a sequence to populate a numeric primary key column. In this section, you ll learn how to:

• Create a sequence

• Get information on a sequence from the data dictionary

• Use a sequence

• Modify a sequence

• Drop a sequence

Creating a Sequence

You create a sequence using the CREATE SEQUENCE statement, which has the following syntax:

 CREATE SEQUENCE  sequence_name  [START WITH  start_num  ] [INCREMENT BY  increment_num  ] [ { MAXVALUE  maximum_num  NOMAXVALUE } ] [ { MINVALUE  minimum_num  NOMINVALUE } ] [ { CYCLE  NOCYCLE } ] [ { CACHE  cache_num  NOCACHE } ] [ { ORDER  NOORDER } ]; 

where

• sequence_name specifies the name you assign to the sequence.

• START WITH start_num specifies the integer to start the sequence. The default start number is 1.

• INCREMENT BY increment_num specifies the integer to increment the sequence by. The default increment number is 1. The absolute value of increment_num must be less than the difference between maximum_num and minimum_num .

• MINVALUE minimum_num specifies the maximum integer of the sequence. minimum_num must be less than or equal to start_num , and minimum_num must be less than maximum_num .

• NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10 ²⁶ for a descending sequence. NOMINVALUE is the default.

• MAXVALUE maximum_num specifies the maximum integer of the sequence. maximum_num must be greater than or equal to start_num , and maximum_num must be greater than minimum_num .

• NOMAXVALUE specifies the maximum is 10 ²⁷ for an ascending sequence or “1 for a descending sequence. NOMAXVALUE is the default.

• CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value. When an ascending sequence reaches its maximum value, the next value generated is the minimum. When a descending sequence reaches its minimum value, the next value generated is the maximum.

• NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value. NOCYCLE is the default.

• CACHE cache_num specifies the number of integers to keep in memory. The default number of integers to cache is 20. The minimum number of integers that may be cached is 2. The maximum integers that may be cached is determined by the formula CEIL( maximum_num - minimum_num )/ABS( increment_num ).

• NOCACHE specifies no integers are to be stored.

• ORDER guarantees the integers are generated in the order of the request. You typically use ORDER when using Real Application Clusters.

• NOORDER doesn t guarantee the integers are generated in the order of the request. NOORDER is the default.

The following example creates a sequence named test_seq:

 CREATE SEQUENCE test_seq; 

Since this CREATE SEQUENCE statement omits the optional parameters, the default values are used. This means that parameters such as start number and the increment by numbers are set to the default of 1.

The next example creates a sequence named test2_seq that supplies values for the optional parameters:

 CREATE SEQUENCE test2_seq START WITH 10 INCREMENT BY 5 MINVALUE 10 MAXVALUE 20 CYCLE CACHE 2 ORDER; 

The final example creates a sequence named test3_seq that starts at 10 and counts down to 1:

 CREATE SEQUENCE test3_seq START WITH 10 INCREMENT BY -1 MINVALUE 1 MAXVALUE 10 CYCLE CACHE 5; 

Getting Information on Sequences

You get information on your sequences from user_sequences, which forms part of the data dictionary. Table 10-6 describes the columns in user_sequences.

Table 10-6: Some Columns in user_sequences

Column	Type	Description
sequence_name	VARCHAR2(30)	Name of the sequence.
min_value	NUMBER	Minimum value.
max_value	NUMBER	Maximum value.
increment_by	NUMBER	Number to increment or decrement sequence by.
cycle_flag	VARCHAR2(1)	Whether the sequence cycles. Set to Y or N .
order_flag	VARCHAR2(1)	Whether the sequence is ordered. Set to Y or N .
cache_size	NUMBER	Number of sequence values stored in memory.
last_number	NUMBER	Last number that was generated or cached by the sequence.

Note  

You can get information on all the sequences you have access to using all_sequences.

The following example retrieves the details for the sequences from user_sequences:

  COLUMN sequence_name FORMAT a6   SELECT * FROM user_sequences;  SEQUEN MIN_VALUE  MAX_VALUE  INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------ ---------- ---------- ------------ - - ---------- ----------- ORDER_          1 1.0000E+27            1 N N         20          21 STATUS 2_SEQ TEST_S          1 1.0000E+27            1 N N         20          21 EQ TEST2_         10         20            5 Y Y          2          20 SEQ TEST3_          1         10           -1 Y N          5           5 SEQ 

Using a Sequence

A sequence generates a series of numbers. A sequence contains two pseudo columns named currval and nextval that you use to get the current value and the next value from the sequence.

Before retrieving the current value you must initialize a sequence by retrieving the next value. When you select test_seq.nextval the sequence is initialized to 1. For example, the following SELECT statement retrieves test_seq.nextval; notice you use dual in the FROM clause:

  SELECT test_seq.nextval FROM dual;  NEXTVAL ----------          1 

The first value in test_seq sequence is 1. Once initialized, you can get the current value from the sequence using currval. For example:

  SELECT test_seq.currval FROM dual;  CURRVAL ----------          1 

When you select currval , nextval remains unchanged; nextval only changes when you select nextval to get the next value. The following example selects test_seq.nextval and test_seq.currval. Notice these values are both 2:

  SELECT test_seq.nextval, test_seq.currval FROM dual;  NEXTVAL    CURRVAL ---------- ----------          2          2 

Selecting test_seq.nextval gets the next value in the sequence, which is 2; test_seq.currval is also 2 at that point.

The next example initializes test2_seq by selecting test_seq2.nextval. Notice the first value in the sequence is 10:

  SELECT test2_seq.nextval FROM dual;  NEXTVAL ----------         10 

The maximum value for test_seq2 is 20, and the sequence was created with the CYCLE option, which means the sequence will cycle back to 10 once it reaches the maximum of 20:

  SELECT test2_seq.nextval FROM dual;  NEXTVAL ----------         15  SELECT test2_seq.nextval FROM dual;  NEXTVAL ----------         20  SELECT test2_seq.nextval FROM dual;  NEXTVAL ----------         10 

The next example initializes test3_seq and retrieves some of the values:

  SELECT test3_seq.nextval FROM dual;  NEXTVAL ----------         10  SELECT test3_seq.nextval FROM dual;  NEXTVAL ----------          9  SELECT test3_seq.nextval FROM dual;  NEXTVAL ----------          8 

Populating a Primary Key Using a Sequence

You ll typically use a sequence to populate a primary key of a table when that primary key is an integer. The following statement recreates the order_status2 table. You ll be using a sequence to populate the id column of order_status2:

 CREATE TABLE order_status2 (   id INTEGER     CONSTRAINT order_status2_pk PRIMARY KEY,   status VARCHAR2(10),   last_modified DATE DEFAULT SYSDATE ); 

The following example creates a sequence named order_status2_seq that will be used to populate the id column of the order_status2 table:

 CREATE SEQUENCE order_status2_seq NOCACHE; 

Tip  

When using a sequence to populate a primary key column, you should typically use NOCACHE to avoid gaps in the sequence of numbers. The gaps occur because when the database is shut down any cached values are lost.

The following INSERT statements add rows to order_status2. Notice the value for the id column is set using order_status2_seq.nextval:

 INSERT INTO order_status2 (   id, status, last_modified ) VALUES (   order_status2_seq.nextval, 'PLACED', '01-JAN-2006' ); INSERT INTO order_status2 (   id, status, last_modified ) VALUES (   order_status2_seq.nextval, 'PENDING', '01-FEB-2006' ); 

The following SELECT statement retrieves the rows from order_status2. Notice the id column is set to the first two values (1 and 2) from the order_status2_seq sequence:

  SELECT * FROM order_status2;  ID STATUS     LAST_MODI ---------- ---------- ---------          1 PLACED     01-JAN-06          2 PENDING    01-FEB-06 

Modifying a Sequence

You modify a sequence using the ALTER SEQUENCE statement. There are some limitations on what you can modify in a sequence, which include the following:

• You cannot change the start value of a sequence.

• The minimum value cannot be more than the current value of the sequence ( currval ).

• The maximum value cannot be less than the current value of the sequence ( currval ).

The following example modifies test_seq to increment the sequence of numbers by 2:

 ALTER SEQUENCE test_seq INCREMENT BY 2; 

When you modify test_seq the new values generated by the sequence will be incremented by 2. For example, the last number generated by test_seq in the previous section was 2, so when you retrieve test_seq.nextval the value returned is 4. The following examples get the current value and next value of test_seq using test_seq.currval and test_seq.nextval:

  SELECT test_seq.currval FROM dual;  CURRVAL ----------          2  SELECT test_seq.nextval FROM dual;  NEXTVAL ----------          4 

Dropping a Sequence

You drop a sequence using DROP SEQUENCE. The following example drops test3_seq:

 DROP SEQUENCE test3_seq; 

This concludes the discussion of sequences. In the next section, you ll learn about indexes.