26. Sequences

SQL

Sequences : It is used to generate the sequence number automatically

Syntax : SQL> Create sequence
start with
Incremented by

Ex: SQL> create sequence empl_seq
start with 115
increment by 1;

Sequence created

SQL> select empl_seq.nextval from dual;

NEXTVAL

   115

SQL> select empl_seq.nextval from dual;

NEXTVAL

   116

SQL> select empl_seq.nextval from dual;

NEXTVAL

   117

SQL> select empl_seq.CURRVAL from dual;

CURRVAL

117 

Use below example to insert empno automatically by creasing

SQL> insert into emp values (empl_seq.nextval,
‘&ename’,
&sal,
‘&job’,
‘&doj’,
&deptid)
Enter value for ename: abc
Enter value for sal: 15000
Enter value for job: manager
Enter value for doj: 10-oct-12
Enter value for deptid: 555

SQL>/
Enter value for ename: kkk
Enter value for sal: 14000
Enter value for job: k
Enter value for doj: 10-mar-12
Enter value for deptid: 444

SQL> select * from emp;
check the empno is generated

Removing Sequence

SQL> Drop sequence empl_seql;

Leave a Reply

Your email address will not be published. Required fields are marked *