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;

SQL Synonym

Synonym

Synonym:-A synonym is an alias for a table, view, snapshot, sequence, procedure, function,
or package.

There are two types to SYNONYMS they are
1) PRIVATE SYNONYM :-This can be access only who created .
2) PUBLIC SYNONYM :-This can be access by any user.

Syntax for Private SYNONYMS:-

Create or replace synonym for ;

Syntax for Public SYNONYMS:-

Create or replace public synonym for ;

CREATE SYNONYMS:-
Example:- Below is the private synonym

SQL>create or replace synonym employee for emp;

Data selection from private synonym:-

SQL>select * from employee;

Example:- Below is the public synonym
SQL>create or replace public synonym employee1 for emp;--Public

Data selection from public synonym:-

SQL>select * from employee1;

Note:- Only public synonym can be access by any user but private synonym can be accessed by creator.

Dropping Synonyms:-

SQL>drop synonym employee;  --drop private synonym
SQL>drop public synonym employee1;  --drop public synonym

Listing information about synonyms:-
SQL>select * from user_synonyms;