SQL Sequence

Sequence

SEQUENCE :- Sequence is a auto number generator in Oracle. 

Syntax:-
 
CREATE SEQUENCE
INCREMENT BY
START WITH
MAXVALUE / NOMAXVALUE
MINVALUE / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER;

Note:-  NEXTVAL is used  to retrieve the next value.
            CURRVAL is used to retrieved for current value of  Sequence .  
            After creation of Sequence, First value will access by ".nextval".There is    no current value to the sequence until the next value has been called at least once

Examples:- Sequence with all default values
SQL>create SEQUENCE seq1;

Access value from Sequence
SQL>select seq1.nextval from dual;
SQL>select seq1.currval from dual;

Note:- NEXTVAL and CURRVAL returns the same value for each row of a select
select seq1.nextval, seq1.currval, seq1.nextval, seq1.currval from dual;

Drop Sequence :- Drop Sequence is used to drop the sequence

Example:-
SQL>drop sequence seq1;

CREATE SEQUENCE seq_reverse INCREMENT BY -5;

Data dictionary for Sequence:-
1) dba_sequences    
2) all_sequences    
3) user_sequences

create sequence seq8 start with 5
increment by 2 maxvalue 999999 minvalue 1 CYCLE cache 10;

Note:- Cache value must be greater than 1
SQL>select seq2.nextval from dual;
SQL>select seq2.currval from dual;
SQL>create sequence seq9 start with 15
increment by -3 maxvalue 999 minvalue -999 cycle cache 2;
SQL>create sequence seq10 start with 15
increment by -3 maxvalue 999 minvalue -999 cycle cache 2;

SQL>select seq2.currval from dual;
SQL>select seq3.nextval from dual;

SQL>create sequence seq6 start with 5
increment by 2 maxvalue 15 minvalue 2 cycle cache 2;
SQL>select seq5.currval from dual;
SQL>select seq6.nextval from dual;

SQL>create sequence seq11 start with 5
increment by 2 maxvalue 999999 minvalue 1 nocycle nocache ;

Generating alpha numeric number from Sequence:-
 Example:-
SQL>select 'AMZ'||to_char(sysdate,'ddmmyyyyhh24miss')||seq6.nextval from dual;

Inserting Sequence value into table:-
Example:-
SQL>drop table x;
SQL>create table x (id number(10),name varchar2(20));
SQL>insert into x values (seq1.nextval,'Ganju');
SQL>insert into x values (seq1.currval,'Suresh');
SQL>select * from x;

SQL>alter sequence seq1 increment by -3 minvalue -999 cycle;

Note:- "Start with" can not be alter

SQL>alter sequence seq1 nocycle NOCACHE;
SQL>alter sequence seq1 cycle nocache;
SQL>alter sequence seq1 nocycle nomaxvalue;

Note :- Ascending sequences that CYCLE must specify MAXVALUE

SQL>alter sequence seq1 nocycle nominvalue;

Note:-Descending sequences that CYCLE must specify MINVALUE

Use of sequence in Oracle PLSQL:-in Oracle11G, we can directly assign value of sequence to variable.

Example:-  
in Oracle 11G

set serveroutput on;
declare
x number;
begin
x:=seq1.nextval;
dbms_output.put_line(x);
end;

in Oracle 10G

declare
x number;
begin
select seq1.netval into x from dual;
dbms_output.put_line(x);
end;