Sequence

Sequences

The sequence is a database object, which is used to generate unique integers for use as primary key for a column. There are two steps while working with a sequence

1. Creating Sequence
2. Attaching or Binding Sequence

Creating Sequence

Since a sequence is a database object, it contains the values with which it generates starting, increment value and maximum value etc.

Syntax :

Create sequence <sequence name >Start with <value>

Increment by <value>Max value / [no max value] <value>

Min value / [no min value] <value>Cycle/no cycle

Cache/no cache <value>Order/no order

Start with : it is a keyword to specifying the starting value in the sequence creation

Increment by : this parameter accepts the number by which we want to increment the value in the sequence. If you want to decrement you must specify the negative value. By default the increment value is ‘1’ (not zero)

Max value : specifies the maximum value or limit that the sequence can generate

No max value : if you cannot specify the maximum limit, use this parameter. The default value is No max value.(not compulsory)

Min value : specifies the sequences minimum value or limit. It represent the number upto which you want to decrement the value.

No Min value : The default value is No Max value.(not compulsory)

Cycle : specifies that the sequence continues to generate value after reaching either its maximum or minimum value.

No cycle : it represents that the sequence should stop generation of numbers when the max value is reached. This is the default one.

Cache : it specifies how many values of the sequence oracle pre allocates and keeps in the memory for the fast access. This keyword generates ‘n’ numbers immediately after creating sequence. Where ‘n’ is the number followed by cache. Default is ‘20’

No cache : this is the default one.

Order : generates the sequences in the order of request

No order : this is the default one

Attaching Sequence

If sequence is created it can be attached to the table for storing the value of the sequence into the table or for generating the column values according to the parameters in the sequence.

There are two ways the sequence can be attached

1. Insert Command
2. Update Command

currval : specifies sequence current value

nextval : specifies sequence next value

Example :

Using Insert Command

Syntax :

Insert into <table name>(columns list) values(sequencename.nextval,……);

First create a dummy table

• create table temporary(sno number(3), name varchar2(10));

Next, create a sequence with the following way

• create sequence myseq start with 100 increment by 1 maxvalue 110  min value 100;
• insert into temporary values(myseq.nextval,'&sname');

similarly insert values in to the temporary table, when the sequence.next Val reaches to 110, it automatically gives an error message.

ERROR at line 1:

ORA-08004: sequence MYSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

• select * from temporary;

SNO NAME

--------- ----------

100 one

101 two

102 three

103 four

104 five

105 six

106 seven

107 eight

108 nine

109 ten

110 eleven

Using Update Command

Syntax :

Update <table name> set <column Name>=<sequencename.nextval>, ------;

create a sequence with the following way

create sequence myseq1 start with 1 increment by 1 maxvalue 10

min value 1;

Updating only one value

update sample set sno=myseq1.nextval where sname=’kk’;

Updating Multiple Values

update sample set sno=myseq1.nextval;

select * from sample;

EMPNO ENAME SAL

--------- ---------- ---------

1 Nithya 5600

2 Saloni 5400

3 Pooja 5400

4 Aruna 7600

5 Sound 5600

6 Mahesh 4500

7 Nandhini 3500

Note :

1. If you are updating a table using a sequence, which contains more rows than the sequence can generate, we will get an error message
2. For example, if a table contains 12 rows, but the sequence can generate maximum of 11 rows only. So we get error message

Altering a Sequence

A sequence can be altered using the Alter Command. But we can not alter the start with value.

Syntax :

Alter sequence <sequence name > Increment by <value>

Maxvalue /NoMaxValue <value>Minvalue /NoMinValue <value>

Cycle / NoCycle Cache/NoCache <value>

Order/noorder

Example

create sequence myseq2 start with 100 increment by 1 maxvalue 110

minvalue 100;

we altered the above sequence

Alter sequence myseq2 increment by 2 maxvalue 120;

Update sample set sno=myseq2.nextval;

select * from sample;

EMPNO ENAME SAL

--------- ---------- ---------

100 Nithya 5600

102 Saloni 5400

104 Pooja 5400

106 Aruna 7600

108 Sound 5600

110 Mahesh 4500

112 Nandhini 3500

Again Altering the above sequence

Alter sequence myseq1 nomaxvalue;

update sample set sno=myseq1.nextval;

now you can update the values as many times as you want, since there is no maximum limit to stop the generation of numbers

Drop sequence :

This command is used to drop any sequence

Syntax : drop sequence <sequence name>

Example : drop sequence myseq1;

Note :

Even though u altered the sequence, it will generate numbers from where it has left before altering the sequence and continues according to ur altered form

RowID:

When ever we insert a row, the system automatically generates Rowid’s and appends the rowid column and stores the values in it. each rowid is unique from one another.

To see Rowid’s of emp table, give the following command

Select rowid from emp;

Select rowid from dept;

To delete any Duplicate Rows (Using Row id’s)

If any duplicate rows are present in any table, then with the help of rowid, we can able to delete that particular records

select rowid, eno, ename, sal from sample;

ROWID SNO NAME

-------------------------------------- ----------

AAAAcUAACAAAAEbAAA 1 Nithya

AAAAcUAACAAAAEbAAB 2 Saloni

AAAAcUAACAAAAEbAAC 1 Mahesh

AAAAcUAACAAAAEbAAD 4 Aruna

AAAAcUAACAAAAEbAAE 2 Saloni

AAAAcUAACAAAAEbAAF 2 Saloni

Delete from customer where rowid=’ AAAAcUAACAAAAEbAAF’;

Delete from customer where rowid=’ AAAAcUAACAAAAEbAAE’