1 / 15

Mastering Sequences in Oracle Database

Understand how to create, modify, and utilize sequences in Oracle Database to generate unique values, manage gaps, and enhance database performance.

edwardharvey
Download Presentation

Mastering Sequences in Oracle Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter ThirteenSequencesDr. Chitsaz Objectives: Sequence objects Create and use sequences Application of sequences

  2. Objects • Object Types: • Table • View • Sequence • Index • Synonym

  3. SEQUENCE • Automatically generates sequence number (For rows in tables) • Is sharable among tables. • Typically used to create a primary key value (Unique numbers and may have gaps) • Stored and generated independently of tables. So the same sequence may be used in several tables.

  4. CREATE SEQUENCE: CREATE SEQUENCE sequence [INCREMENT BY n] --negative number to decrement [START With n] [MAXVALUE n| NOMAXVALUE ] [MINVALUE n| NOMINVALUE ] [CYCLE | NOCYCLE ] --start over or not [CACHE n | NOCACHE ]; --n numbers stored in memory for efficiency; default is 20;

  5. CREATE SEQUENCE: Example: CREATE SEQUENCE id_seq INCREMENT BY 1 START WITH 1000 MAXVALUE 99999 NOCACHE NOCYCLE ;

  6. CONFIRMING SEQUENCE: Since it is an object, it can be query: SELECT sequence_name, min_value, max_value, increment_by, last_number --next number stored FROM user_sequences; -- or use SEQ

  7. CONFIRMING SEQUENCE: • NEXTVAL: --next available sequence number • CURRVAL: --present value • 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 • SELECT union/intersect/minus • Subquery in SELECT, INSERT, or UPDATE

  8. USING A SEQUENCE: • INSERT INTO student (id, name) VALUES (id_seq.NEXTVAL, '&name'); • View the current value of the sequence id_seq: • SELECT id_seq.CURRVAL FROM dual;

  9. USING A SEQUENCE: SELECT id_seq.CURRVAL, id_seq.NEXTVAL FROM dual; --if currval and nextval is used in the same query, both return the same number.

  10. USING A SEQUENCE: INSERT INTO student_course (Id , C_num, Dept_name) VALUES (Stud_Seq.NEXTVAL, Course_Seq.NEXTVAL , ’&Dept_name’);

  11. Gaps in sequence values: it is possible to have gaps in the sequence: • A rollback • The system crashes • A sequence is used in another table

  12. Modifying a sequence: ALTER SEQUENCE id_seq INCREMENT BY 10 START WITH 1000 MAXVALUE 99999 NOCACHE NOCYCLE;

  13. 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, the sequence must be dropped and recreated.

  14. Dropping a Sequence: • DROP SEQUENCE id_seq;

  15. Question? • id_seq is 150; We would like to change the lastvalue to 350.

More Related