Oracle 11g: Sequences

===>  SQL Queries:  Creating A Sequence


create sequence dept_deptid_seq
                increment by 10
                start with 120
                maxvalue 9999
                nocache
                nocycle;

results

create sequence succeeded.

===> SQL Queries:  Using A Sequence

• NEXTVAL [nextvalue]
• CURRVAL[currentvalue]
• NEXTVAL must be issued first before CURRVAL has value

insert into departments(department_id,department_name,location_id)
values (dept_deptid_seq.NEXTVAL,'Support',2500);

select dept_deptid_seq.CURRVAL
from dual;

===>  SQL Queries:  Alter a Sequence

alter sequence dept_deptid_seq
               increment by 20
               maxvalue 999999
               nocache
               nocycle

Rules on modifying the sequence:

• Must have the privilege to Alter
• Only future sequence nos are affected
• To restart the sequence no, the sequence must be dropped and recreated

Results :
alter sequence dept_deptid_seq succeeded.

===>  SQL Queries:  Removing  a Sequence

drop sequence dept_deptid_seq;

Results

drop sequence dept_deptid_seq succeeded.