Friday 30 May 2014

Sequence in Oracle

What is a Sequence?
  • A sequence is a user created database object that can be shared by multiple users to generate unique integers.
  • A typical usage for sequences is to create a primary key value, which must be unique for each row.
  • The sequence is generated and incremented (or decremented) by an internal Oracle routine.
  • This can be a time-saving object because it can reduce the amount of application code needed to write a sequence-generating routine.
  • Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.
You create a sequence using the CREATE SEQUENCE statement, which has the following syntax:
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
Creating a sequence and then get the next value
Once initialized, you can get the current value from the sequence using currval.
You can’t use CURRVAL just after a sequence creation. It will throw an error.
When you select currval , nextval remains unchanged; nextval only changes when you select nextval to get the next value.
Getting Information on Sequences
You get information on your sequences from user_sequences.
Modifying a Sequence
  • You modify a sequence using the ALTER SEQUENCE statement.
  • 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 ).
Removing a Sequence
• Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.
• Once removed, the sequence can no longer be referenced.

1 comment:

  1. West Bengal Board of Secondary Education (WBBSE) Government Agency 1st, 2nd, third, 4th, 5th Class Final Exam Conducted will Start from Month of Jun (Expected) 2021,SCERT West Bengal 5th Class e-Books WB 1st, 2d, third, 4th, 5th Class Practical Exam 2021 can be Start from Month of March or May 2021 (Expected), Students 10thmodelpaper.In Provide West Bengal 1st, 2d, third, 4th, fifth Class Study Material Bengali, English, Medium Pdf Format

    ReplyDelete