Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Sequence

A sequence is a database object, which can generate unique, sequential integer values.
It can be used to automatically generate primary key or unique key values.
A sequence can be either in an ascending or descending order.

Syntax:
      Create sequence <seq_name> [increment bty n] [start with n] [maxvalue n] [minvalue n] [cycle/nocycle] [cache/nocache];

By default the sequence starts with 1, increments by 1 with min value of 1 and with no cycle,no cache.
Cache option pre-allocates a set of sequence numbers and retains them in memory for faster access.

Ex:
     SQL> create sequence s;
     SQL> create sequence s increment by 10 start with 100 minvalue 5 maxvalue 200 cycle cache 20;

USING SEQUENCE

SQL> create table student(no number(2),name varchar(10));
SQL> insert into student values(s.nextval, ‘saketh’);

Ø Initially currval is not defined and nextval is starting value.
Ø After that nextval and currval are always equal.

CREATING ALPHA-NUMERIC SEQUENCE

SQL> create sequence s start with 111234;
SQL> Insert into student values (s.nextval || translate (s.nextval,’1234567890’,’abcdefghij’));

ALTERING SEQUENCE

We can alter the sequence to perform the following.
Ø Set or eliminate minvalue or maxvalue.
Ø Change the increment value.
Ø Change the number of cached sequence numbers.

Ex:
     SQL> alter sequence s minvalue 5;
     SQL> alter sequence s increment by 2;
     SQL> alter sequence s cache 10;

DROPPING SEQUENCE




SQL> drop sequence s;
Share this article :

0 comments:

Post a Comment