1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle/PLSQL: Sequences (Autonumber)

Discussion in 'SQL PL/SQL' started by SBH, Nov 22, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Sequence is database object created to generate progressive number values, incrementing at a definite rate. The article covers the usage areas of Sequence and its features.

    2. Introduction

    Sequence is a database object which generates number values automatically. The generated numbers may be unique or may be recycled (non unique). In most of the cases, unique sequence generator serves as the primary key of the tables.

    The number generation process is handled internally by Oracle. In terms of performance, it supersedes other methods of sequence generation. They are independent schema object and can be used with multiple tables.

    [​IMG]

    3. Syntax

    Code (Text):
    CREATE SEQUENCE [sequence name]
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}];
    Explanation of the clauses

    [START WITH n] is the clause for a sequence to specify the starting value of the sequence.
    [INCREMENT BY n] specifies the step to be followed the number generation. It is 1 by default.
    [MAXVALUE n] is the maximum value the sequence can generate.
    [MINVALUE n] is the minimum value of the sequence.
    [CYCLE | NOCYCLE] specifies whether the sequence should recycle once the maximum value is reached. NOCYCLE is by default. Not required for Primary key generating sequence.
    [CACHE | NOCACHE n] is the count of pre allocated sequence value oracle keeps in memory. Its value is 20 by default.

    4. NEXTVAL and CURRVAL

    NEXTVAL generates the next value of the sequence while CURRVAL returns the last generated value i.e. current pointing value of the sequence.

    In a database session, CURRVAL cannot be the first operational method on the sequence. This is because current value of a sequence in a session is stored only once it is generated. Therefore, NEXTVAL must be called before CURRVAL function.

    Syntax

    Code (Text):
    Sequence.NEXTVAL
    Sequence.CURRVAL
    4.1. Notes

    • CURRVAL and NEXTVAL can only be used in the Outer SQL of a select statement.
    • CURRVAL and NEXTVAL can be used in INSERT statement to substitute a column primary key. It can be used both as a subquery clause and also in VALUES clause.
    • CURRVAL and NEXTVAL can be used to update values in the tables.
    • CURRVAL and NEXTVAL cannot be in VIEW select list, with DISTINCT keyword, with GROUP BY, HAVING, or ORDER BY clauses, and DEFAULT expression in a CREATE TABLE or ALTER TABLE statement.


    5. Illustrations

    Code (SQL):
    SQL> CREATE SEQUENCE SEQ_CUST
        START WITH 1
        INCREMENT BY 1;

    SEQUENCE created.

    SQL> SELECT SEQ_CUST.CURRVAL FROM DUAL;
    SELECT seq_cust.currval FROM dual;         
    *
    ORA-08002: SEQUENCE SEQ_CUST.CURRVAL IS NOT yet defined IN this SESSION

    SQL> SELECT SEQ_CUST.NEXTVAL FROM DUAL;
    1

    SQL> SELECT SEQ_CUST.CURRVAL FROM DUAL;
    1

    SQL> INSERT INTO CUSTOMERS (CUST_ID, CUST_NAME, BRANCH_ID, SALES)
        VALUES
        (SEQ_CUST.NEXTVAL, 'SEQUENCE TEST',20, 5000);

     1 ROW inserted.

    SQL> SELECT SEQ_CUST.CURRVAL FROM DUAL;
    2
    6. Modifying the sequence

    Sequence Owner can modify a sequence to alter the increment value, min value, max value, and cycle or cache clauses only. Please note that the changes done would be reflected in the upcoming numbers.
    Syntax:

    Code (Text):
    ALTER SEQUENCE [sequence name]
    INCREMENT BY n
    MAXVALUE n
    NOCACHE
    NOCYCLE
    7. Dropping the sequence

    Sequence can be removed from the database using DROP command. It is DDL command which carried feature of auto-commit the ongoing transaction in the current session.

    To remove the sequence from the database, we drop the sequence using DROP command as shown below.

    Code (Text):
    DROP SEQUENCE [sequence name]
    8. Caching Sequence values

    Caching is the concept used to speed up the Oracle’s performance. In sequences, as soon as first reference to sequence is made, the sequence cache is populated and for each of the next call to sequence, Oracle takes the value from sequence cache. Once the last value of the cache is fetched, again the cache is populated.

    A major drawback with the sequence generator is that it cannot be rolled back. If a number sequence is lost, it cannot be recovered back. If a transaction using the sequence is rolled back, the changes are undone but on the cost of losing a sequence number. Similar is the case, when system crashes or system is used for multiple tables.
     

    Attached Files:

    • seq.JPG
      seq.JPG
      File size:
      7.7 KB
      Views:
      7,332
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you want all of the sequence numbers to fall 'in order' regardless of the session generating the value a sequence can be created with the keyword 'ORDER'. That's confusing on the face of it because the sequence values each user 'sees' are in order. The 'ORDER' option is to preserve the order of sequence values across sessions, to more closely coincide with a process date if one is stored in the row. And you can use both caching and cross-session ordering when creating a sequence:
    SQL> create sequence empseq 2 start with 1 increment by 1 cache 20 order nomaxvalue nocycle;Sequence created.SQL>but Oracle will let the ORDER option take precendence over caching, which makes sense given that you've asked for all of your sequence values to be in order, regardless which session generates them.
     
  3. neha1311

    neha1311 Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Dear sir,
    It is used for me to learn autonumbers. I am a retired lecturer and software trainer.
    Thanks
    Iqbal Ahmed Md. M.Sc.,
    computerpro1311@gmail.com
    India