Infolinks

Sunday 15 July 2012

Sequences

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’

No comments:

Post a Comment