Chapter Thirteen Sequences Dr' Chitsaz

1
Chapter ThirteenSequencesDr. Chitsaz

• Objectives
• objects
• Create and use sequences
• Application of sequences

2
Objects

• Object Types
• Table
• View
• Sequence
• Index
• Synonym

3
SEQUENCE

• Automatically generates sequence number
• Is sharable
• Typically used to create a primary key value
• Stored and generated independently of tables

4
CREATE SEQUENCE

• CREATE SEQUENCE sequence
• INCREMENT BY n
• START With n
• MAXVALUE n NOMAXVALUE
• MINVALUE n NOMINVALUE
• CYCLE NOCYCLE
• CACHE n NOCACHE

5
CREATE SEQUENCE

• CREATE SEQUENCE ID
• INCREMENT BY 4
• START WITH 1000
• MAXVALUE 99999
• NOCACHE
• NOCYCLE

6
CONFIRMING SEQUENCE

• SELECT sequence_name,
• min_value,
• max_value,
• increment_by,
• last_number
• FROM user_sequences //SEQ

7
CONFIRMING SEQUENCE

• NEXTVAL
• CURRVAL
• NEXTVAL CURRVAL can not be used in
• SELECT list of a view
• SELECT with distinct clause
• SELECT with group by
• SELECT having
• SELECT order by
• Subquery in SELECT, INSERT, or UPDATE

8
USING A SEQUENCE

• INSERT INTO student (id, name)
• VALUES (id.NEXTVAL, 'name')
• SELECT id.CURRVAL
• FROM dual

9
USING A SEQUENCE

• INSERT INTO
• student_course(Id ,C_num,
  Dept_name)
• VALUES
• (Stud_Seq.NEXTVAL, Course_Seq.NEXTVAL
  ,Dept_name)

10
Gaps in sequence values

• A rollback
• The system crashes
• A sequence is used in another table

11
Modifying a sequence

• ALTER SEQUENCE id
• INCREMENT BY 10
• START WITH 1000
• MAXVALUE 99999
• NOCACHE
• NOCYCLE

12
Rules for Modifying a Sequence

• You must be the owner or have the ALTER privilege
• Only future sequence numbers are affected
• To restart a sequence sequence must be dropped
  and recreated.

13
Dropping a Sequence

• DROP SEQUENCE id