|
Creating SQL Sequences
Sequences are database objects that automatically generate sequential lists of numbers and they are usefull when creating surrogate key values for primary key fields. For example, you can generate range of numbers starting from 1 to up when new record is added to the table, 1 could be value of the primary field for the first record and so on. Every sequence must have unique name. The first command and the only required command is CREAT SEQEUNCE which creates the sequence. Sequence name follows this command and the rest of the commands such as START WITH, INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, CACHE, ORDER, etc are optional. Here is an example to create a sequence:
CREATE SEQUENCE employee_number_sec
START WITH 1
INCREMENT BY 1
| This example simply creates a sequence called employee_number_sec that starts with 1 and increments by 1 each time the sequence runs new record. |
When the sequence is created, the next thing you want do is insert into a table.
Say that you want insert a sequence value for employee_id field in the employee table. Here is how you would accomplish that:
INSERT INTO employees VALUES
(
employee_number_sec.NEXTVAL, 00000234,'J','Morgan',
);
| We inserted one record into the employees table using sequence generated values for the employees_id field. |
NEXTVAL is a command that generates next available for the sequence. Remember you have to have record in the department table with department_id of 0000234 in order to insert this record successfully. You can view the current available sequence value by issuing this command:
SELECT employee_number_sec.CURRVAL FROM DUAL; You can also view all the sequences you created from dual by this command:
SELECT * FROM user_sequence;.
|
|| 
|| 
|| 
Introduction
Retrieve Data
