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;
