Sequences : Create Sequence « Sequences « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Sequences
  3. Create Sequence

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.

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

  1. The default start_num is 1.
  2. The default increment number is 1.
  3. The absolute value of increment_num must be less than the difference between maximum_num and minimum_num.
  4. minimum_num must be less than or equal to start_num, and minimum_num must be less than maximum_num.
  5. NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10^26 for a descending sequence.
  6. NOMINVALUE is the default.
  7. maximum_num must be greater than or equal to start_num, and maximum_num must be greater than minimum_num.
  8. NOMAXVALUE specifies the maximum is 10^27 for an ascending sequence or C1 for a descending sequence.
  9. NOMAXVALUE is the default.
  10. CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value.
  11. When an ascending sequence reaches its maximum value, the next value generated is the minimum.
  12. When a descending sequence reaches its minimum value, the next value generated is the maximum.
  13. NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
  14. NOCYCLE is the default.
  15. CACHE cache_num specifies the number of integers to keep in memory.
  16. The default number of integers to cache is 20.
  17. The minimum number of integers that may be cached is 2.
  18. The maximum integers that may be cached is determined by the formula CEIL(maximum_num - minimum_num)/ABS(increment_num).
  19. NOCACHE specifies no integers are to be stored.
  20. ORDER guarantees the integers are generated in the order of the request.
  21. You typically use ORDER when using Real Application Clusters.
  22. NOORDER doesn't guarantee the integers are generated in the order of the request.
  23. NOORDER is the default.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813

SQL>
SQL>
SQL> create sequence LOGICAL_ASSIGNMENT_ID_SEQ
  2  start with 1;

Sequence created.

SQL>
SQL> drop   sequence LOGICAL_ASSIGNMENT_ID_SEQ;

Sequence dropped.

SQL>
SQL>








5.1.Create Sequence
5.1.1.Sequences
5.1.2.Create the simplest sequence
5.1.3.Creating a sequence and then get the next value
5.1.4.Test sequence step
5.1.5.CREATE SEQUENCE test_seq;
5.1.6.Using a sequence to populate a table's column
5.1.7.create sequence deptno_seq start with 50 increment by 10
5.1.8.Create a sequence starting with '100000000 order'
5.1.9.Create a sequence
5.1.10.CREATE SEQUENCE myTableIDSeq INCREMENT BY 1 START WITH 1000;
5.1.11.Create a sequence starting with 1000
5.1.12.create sequence emps_seq start with 501 increment by 10