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

INCREMENT BY 2

START WITH 100

MAXVALUE 1000

NOCYCLE

NOCACHE;

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.

ID	TITLE	DURATION	ARTIST	TYPE_CODE
	Surfing Summer	-	-	12
	Victory Victory	5 min	-	12

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.)

SELECT seq_d_songs_seq.CURRVAL FROM DUAL;

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.

Extras:

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

INCREMENT BY 1

START WITH 1

MAXVALUE 999999

NOCYCLE

NOCACHE;

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 )

VALUES(student_id_seq.NEXTVAL,'Hemant','Kumar','M','Web');

INSERT INTO students(student_id ,fname,lname ,sex ,major )

VALUES(student_id_seq.NEXTVAL,'Hemant2','Kumar2','M','CyberSec');

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

SELECT * FROM students;