Notes 0006

Working With Sequences

What are sequences

Sequences are sequential lists of numbers that are generated by the database. You can use these numbers to provide primary key values for tables that don't have an obvious primary key. For example, you can use a sequence to generate the customer id when entering a new customer into the system.

Creating

We create sequences similarly to tables:

CREATE SEQUENCE sequence_name
[INCREMENT BY number]
[START WITH start_number]
[MAXVALUE max_value]
[NOMAXVALUE]
[MINVALUE min_value]
[CYCLE]
[NOCYCLE]
[CACHE num_of_values_to_cache]
[NOCACHE]
[ORDER]
[NOORDER];

The individual parts of the command are explained next:

INCREMENT BY: specifies by what number this sequence is incremented. Note that this value can be negative. The default is 1.

START WITH: Where does this sequence start. Default is 1.

MAXVALUE: what is the max value of this sequence? Limit is around 10²⁷. Default value is NOMAXVALUE.

MINVALUE: what is the min value of this sequence? (again, note that increment value can be negative). Default is no NOMINVALUE.

CYCLE: CYCLE refers to the ability of the sequence to cycle back to the beginning after reaching the MAXVALUE. Default is NOCYCLE.

CACHE: The database can grab and cache a chunk of sequences at once, instead of updating the sequence every time you access it. This cache value specifies how many numbers the db grabs at once. Note that this may cause gaps in your sequences, like one client getting value 1234 and after db restart, the next client getting 1250. Cache is generally used though, and these gaps aren't of too much concern as long as the values themselves are unique. Default cache is 20.

NOCACHE: Don't do any caching. (similar to setting cache to 1?)

ORDER: Ensures that sequences are granted in exact chronological order in which they were requested. Default is NOORDER, meaning that numbers can be in any order, but they will be unique. Again, just as with caching, we aren't usually interested in the number itself, we're mostly interested in that it is unique.

Using Sequences (inserting)

We can insert data using sequence values. For example, in our PRODUCT table from Notes 0002, we can add another product: Monitor via a sequence. First, let's create the sequence though:

CREATE SEQUENCE product_sequance START WITH 5;

Note that we'd want to start our sequence with a 5, since our last product in db is product_id 4. We can easily find the max by doing a max:

select max(product_id) from product;

Anyway, back to sequences...

If we want to insert data using the next sequence value, we have to specify sequence_name.NEXTVAL inside our INSERT command. For example:

INSERT INTO product VALUES (product_sequance.NEXTVAL,'monitor',47,399.99);

Now, let's do the select...

select product_id, description from product;

Which produces:

PRODUCT_ID DESCRIPTION
---------- ------------
         3 mice
         4 keyboard
         5 monitor

Notice that our product id for monitor is 5, which was generated by the sequence. With this type of sequences capability, we don't have to worry about creating unique primary key values for product_id:

INSERT INTO product VALUES (product_sequance.NEXTVAL,'speakers',164,9.99);
INSERT INTO product VALUES (product_sequance.NEXTVAL,'stapler',324,14.99);
INSERT INTO product VALUES (product_sequance.NEXTVAL,'calculator',124,7.99);
INSERT INTO product VALUES (product_sequance.NEXTVAL,'quickcam',42,99.98);
INSERT INTO product VALUES (product_sequance.NEXTVAL,'harddrive',30,199.99);

Which eventually creates:

PRODUCT_ID DESCRIPTION
---------- ------------
         3 mice
         4 keyboard
         5 monitor
         6 speakers
         7 stapler
         8 calculator
         9 quickcam
        10 harddrive

Note that product_id values are incrementing and that all of them are unique.

Retrieving Sequence Values

We can also retrieve sequence values:

SELECT product_sequance.NEXTVAL FROM DUAL;

Which in our case, returns:

   NEXTVAL
----------
        11

Now, if you select it again, it will return:

   NEXTVAL
----------
        12

Everytime you select the next value, you get the next value (and the sequence gets incremented).

If you want to retrieve the current sequence value without updating it, you get CURRVAL. For example:

SELECT product_sequance.CURRVAL FROM DUAL;

Which does not increment, and will return the same value no matter how many times you run it.

   CURRVAL
----------
        12

Note that sequences are defined on sessions, so if you never got NEXTVAL from a sequence, then you can't get CURRVAL. CURRVAL will only work after you did NEXTVAL in that session.

Viewing Sequences

You can view info about a sequence by doing a select on user_sequences:

SELECT * FROM USER_SEQUENCES;

Which produces:

SEQUENCE_NAME     MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
---------------- ---------- ---------- ------------ - - ---------- -----------
PRODUCT_SEQUANCE          1 1.0000E+27            1 N N         20          25

Removing Sequences

You remove a sequence by dropping it (similar to table dropping):

DROP SEQUENCE product_sequance;

That's about it for sequences...

© 2006, Particle