6.8 How do I…Create a sequence to generate a unique number?Problem
I need to generate a sequential number in my application. The number will be used as the primary key value of a table and has to be unique. How do I create a sequence to generate a unique number for a primary key before inserting a record in a table, and how do I change the behavior of an existing sequence?
Technique
The CREATE SEQUENCE statement is used to create a new sequence. The ALTER SEQUENCE statement is used to change the values of certain sequence parameters. There is no direct way to change the value of a sequence. In order to change the value of a sequence, the sequence must be dropped and re-created with the new parameters. Information about sequences contained in the database can be queried from the USER_SEQUENCES and ALL_SEQUENCES data dictionary views.
The CURRVAL pseudo-column within a sequence allows a sequence value to be queried without incrementing it. The CURRVAL pseudo-column cannot be queried until a NEXTVAL pseudo-column is queried for the sequence during the session. This keeps an application from using a sequence value created by another user or application.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Execute the CHP6_23.SQL file, which contains a CREATE SEQUENCE statement to create a new sequence:
SQL> START CHP6_23
1 CREATE SEQUENCE NEW_SEQ06;
Sequence created.
The CREATE SEQUENCE keywords are required to create a new sequence. Because no other options are specified, the sequence will contain default values for all the parameters (see Table 6.4) and start with 1. The NEW_SEQ06.NEXTVAL pseudo-column can be queried within an application to increment the sequence and return the next value of the sequence.
Table 6.4 Options available with CREATE SEQUENCE/ALTER SEQUENCE
Keyword Purpose INCREMENT BY n Increment value for sequence counter, positive for ascending sequence or negative for descending sequence (default +1). START WITH n First sequence number to be generated; cannot be used in ALTER SEQUENCE (default 1). MAXVALUE n Highest number the sequence will generate; (default –1 for descending sequence). NOMAXVALUE Maximum value of 10^27 for an ascending sequence or –1 for a descending sequence (default). MINVALUE n Lowest number the sequence will generate (default 1 for ascending sequence). NOMINVALUE Minimum value of 1 for an ascending sequence or –(10^26) for a descending sequence (default). CYCLE Sequence restarts after reaching MAXVALUE or MINVALUE (not default). NOCYCLE Error when MAXVALUE hit for ascending sequence and MINVALUE hit for descending sequence (default). CACHE n Number of pre-allocated sequence values kept in memory (default 20). NOCACHE Sequence values are not pre-allocated (not default). ORDER Guarantees sequence numbers are generated in the order of requests, required for parallel mode operation (not default). NOORDER No specific order to sequence number generation (default). 2. Run the CHP6_24.SQL file, as shown in Figure 6.24, which contains a CREATE SEQUENCE statement to generate descending numbers.
Line 1 contains the keywords required to create the sequence and give it a name. The START WITH clause contained in line 2 specifies that the sequence starts at 1,000. The MAXVALUE clause specified in line 2 provides the largest value the sequence can contain, and the negative number in the INCREMENT BY clause in line 4 creates a descending sequence. The first value returned when the sequence is queried will be 1,000, because it was specified in the START WITH clause. The negative value in the INCREMENT BY clause decreases the sequence each time it is queried.
3. Run the CHP6_25.SQL file, which contains two ALTER SEQUENCE statements: one to change the maximum value of an ascending sequence and another to change the minimum value of a descending sequence. Figure 6.25 shows the results of running the two statements in SQL*Plus.
Both statements fail as the current value of a sequence has to be changed to satisfy either ALTER SEQUENCE, and Oracle does not allow the current value of a sequence to be changed in an ALTER statement. An ALTER SEQUENCE statement is issued in a similar fashion as the CREATE SEQUENCE, except that START WITH is not available. To change the value of a sequence, you must DROP and CREATE it again.
4. Run the CHP6_26.SQL file, which contains two ALTER SEQUENCE statements: one to modify the minimum and maximum values associated with the ascending sequence and another to remove the minimum value from the descending sequence. Figure 6.26 shows the results of running the two statements in SQL*Plus.
In the first ALTER SEQUENCE statement, line 1 contains the keywords required to modify the parameters of a sequence. Lines 2 and 3 use the MINVALUE and MAXVALUE keywords to affect the sequence parameter values. In the second ALTER SEQUENCE statement, line 2 removes the minimum value of the sequence by specifying the NOMINVALUE clause. Other sequence parameter values are left unchanged by the both statements. Whenever a MAXVALUE or MINVALUE specification is explicitly provided, NOMAXVALUE and NOMINVALUE defaults get overridden respectively.
5. Run the CHP6_27.SQL file, which contains queries to the NEXTVAL and CURRVAL pseudo-columns of the NEW_SEQ06 sequence. CURRVAL returns the current value of the sequence, while NEXTVAL increments the sequence and returns the new value. Figure 6.27 shows the results of queries in SQL*Plus.
The DUAL system table is used as the source of both queries and always returns a single value. Once the NEXTVAL pseudo-column is retrieved from the sequence in the current session, the CURRVAL pseudo-column can be used to retrieve the same number repeatedly. The value returned will always be the last value returned by the NEXTVAL pseudo-column in the same session.
6. Run the CHP6_28.SQL file, as shown in Figure 6.28, which contains a query to display sequence information from your current schema.
The columns returned by the query on the USER_SEQUENCES view show you the name of the sequence, the minimum and maximum values, and how the sequence counter is incremented. The columns C and O in the listing stand for CYCLE_FLAG and ORDER_FLAG, and have a value of N as a sequence is created with a NOCYCLE and NOORDER by default. The LAST_NUMBER column contains the last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was actually used. This value is not continuously updated during database operation and is intended for use after a warm start or import. The CURRVAL and NEXTVAL pseudo-columns should always be used to retrieve the sequence value, which guarantee that a unique sequence value is always returned in an Oracle session. The USER_SEQUENCES and ALL_SEQUENCES views contain the sequences available to a user. The ALL_SEQUENCES view contains sequences owned by the user account and those granted to the user account.
7. If you have not run the CHP6_3.SQL script so far, you must run it to create the COMPANY06 table before you run the CHP6_29.SQL file, which contains an INSERT statement that uses a sequence to generate a unique number for the primary key of the COMPANY06 table. Figure 6.29 shows the results of running the statement in SQL*Plus.
The NEXTVAL pseudo-column of the NEW_SEQ06 sequence is used within an INSERT statement. In the event that multiple requests to insert records in the same table came in parallel, a unique value for the COMPANY_ID column is guaranteed for each request.
How It Works
Step 1 creates a sequence with default values for parameters. When a sequence is created with default values, it starts with 1 and has no upper limit. In Step 2, a descending sequence is created. Steps 3 and 4 demonstrate modifying parameters associated with sequences. In Step 5, sequential values are queried from a sequence using the NEXTVAL and CURRVAL pseudo-columns. The USER_SEQUENCES view is queried in Step 6, to display information about sequences created in the first two steps. Step 7 demonstrates how a sequence can be used for generating a unique primary key within an INSERT statement.
Comments
Sequences are the best way to generate integer keys in Oracle, and sequential numbered primary keys result in improved performance. After creating a sequence, privileges need to be granted to user accounts or roles just like tables. The ALTER SEQUENCE statement can be used to change most of the parameters set for a sequence, but not the value of a sequence. The ALL_SEQUENCES and USER_SEQUENCES data dictionary views provide infor-mation about sequences in the database. If you need to list information about the sequences in your current schema, use the USER_SEQUENCES view. If you need to know about sequences to which you have been granted privileges, use the ALL_SEQUENCES view. To retrieve the value of a sequence, the NEXTVAL pseudo-column must be queried before the CURRVAL pseudo-column can be queried within the session. One sequence can be used for many tables, or a separate sequence can be created for each table requiring generated keys.
To create a sequence, you must have a CREATE SEQUENCE or CREATE ANY SEQUENCE privilege. To alter a sequence, you must have the ALTER SEQUENCE or ALTER ANY SEQUENCE privilege. Once the sequence is created, it can be used by the current user account or any user account granted SELECT privileges.