Friday, November 18, 2016

42 Working with Sequences

  •  ·         Command that automatically generates sequential numbers- CREATE SEQUENCE (CREATE SEQUENCE some_name)
  • ·         Generates a numeric value- Sequences
  • ·         Returns the next available sequence value- NEXTVAL
  • ·         Specifies the interval between sequence numbers- INCREMENT BY (INCREMENT BY n)
  • ·         Specifies a maximum value of 10^27 for an ascending sequence and -1 for a descending sequence (default)- NOMAXVALUE
  • ·         returns the current sequence value- CURRVAL
  • ·         specifies the minimum sequence value- MINVALUE (MINVALUE n)
  • ·         specifies whether the sequence continues to generate values after reaching its maximum or minimum values- CYCLE/NOCYCLE
  • ·         specifies a minimum value of 1 for an ascending sequence and – (10^26) for a descending sequence (default)- NOMINVALUE
  • ·         specifies a maximum or default value the sequence can generate- MAXVALUE/NOMAXVALUE (MAXVALUE n | NOMAXVALUE)
  • ·         specifies the first sequence number to be generated- START WITH (START WITH n)
  • ·         specifies how many values the Server pre-allocates and keeps in memory- CACHE/NOCACHE (CACHE n | NOCACHE)

1.  Using CREATE TABLE AS subquery syntax, create a seq_d_songs table of all the columns in the DJs on Demand database table d_songs. Use the SELECT * in the subquery to make sure that you have copied all of the columns.
CREATE TABLE seq_d_songs
AS ( SELECT * FROM d_songs);
DESCRIBE seq_d_songs;
DESCRIBE d_songs;
SELECT * FROM d_songs;
SELECT * FROM seq_d_songs;

2.  Because you are using copies of the original tables, the only constraints that were carried over were the NOT NULL constraints. Create a sequence to be used with the primary-key column of the seq_d_songs table. To avoid assigning primary-key numbers to these tables that already exist, the sequence should start at 100 and have a maximum value of 1000. Have your sequence increment by 2 and have NOCACHE and NOCYCLE. Name the sequence seq_d_songs_seq.
CREATE SEQUENCE seq_d_songs_seq

When I alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order.
3.  Query the USER_SEQUENCES data dictionary to verify the seq_d_songs_seq SEQUENCE settings.
SELECT * FROM user_sequences WHERE sequence_name = UPPER('seq_d_songs_seq');

4.  Insert two rows into the seq_d_songs table. Be sure to use the sequence that you created for the ID column. Add the two songs shown in the graphic.

Surfing Summer

Victory Victory
5 min

INSERT INTO seq_d_songs (id,title,duration,artist,type_code)
VALUES(seq_d_songs_seq.NEXTVAL,'Surfing Summer',NULL,NULL,12);
INSERT INTO seq_d_songs (id,title,duration,artist,type_code)
VALUES(seq_d_songs_seq.NEXTVAL,'Victory Victory','5 min',NULL,12);
SELECT * FROM seq_d_songs ORDER BY id DESC;
SELECT * FROM user_sequences WHERE sequence_name = UPPER('seq_d_songs_seq');

5.  Write out the syntax for seq_d_songs_seq to view the current value for the sequence. Use the DUAL table. (Oracle Application Developer will not run this query.)

CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL.
6.  What are three benefits of using SEQUENCEs?
·         May be used to generate identity column values - the unique numbers. They also avoid concurrency issues.
·         Save time and efforts of coding.
·         Same sequence may be used in multiple tables since they are generated independent of the table using it, but I will prefer to use one sequence for one purpose.
·         There is cache option available in sequences.
·         These are sharable objects and multiple users can access it.

7.  What are the advantages of caching sequence values?
Using cache option gives a slight performance advantage as the numbers are pre-allocated and stored in-memory.

8.  Name three reasons why gaps may occur in a sequence?

·         Rolling back a statement containing a sequence.
·         System crash if system caches values in-memory
·         Same sequence being used by multiple tables.


1.  Create a table called “students”. You can decide which columns belong in that table and what datatypes these columns require. (The students may create a table with different columns; however, the important piece that must be there is the student_id column with a numeric datatype. This column length must allow the sequence to fit, e.g. a column length of 4 with a sequence that starts with 1 and goes to 10000000 will not work after student #9999 is entered.)
CREATE TABLE students(
student_id NUMBER(6) CONSTRAINT sdt_sdt_id_pk PRIMARY KEY,
fname VARCHAR2(12),
lname VARCHAR(20),
sex CHAR(1),
major VARCHAR2(24)

2.  Create a sequence called student_id_seq so that you can assign unique student_id numbers for all students that you add to your table.

CREATE SEQUENCE student_id_seq

SELECT * FROM user_sequences WHERE sequence_name = UPPER('student_id_seq');

3.  Now write the code to add students to your STUDENTS table, using your sequence “database object.”
INSERT INTO students(student_id ,fname,lname ,sex ,major )
INSERT INTO students(student_id ,fname,lname ,sex ,major )
SELECT * FROM user_sequences WHERE sequence_name = UPPER('student_id_seq');

SELECT * FROM students;

No comments:

Post a Comment