SEQUENCES:
**********
-->Sequence is a database object, which is stored in database server and it is used to generate the sequence of number on a particular column of table automatically.
-->it automatically generates primary key or unique key values.
-->it can be either asc /desc order.
-->It is Used to generate the sequence number on a particular column of table automatically.
-->It is not related to any table.
-->Sequence will start generating values from its min value.
-->Default MIN Value is 1.
-->Default incremental value of sequence is +1.
-->ALL_SEQUENCES -- ONLY SEQUENCES WILL BE DISPLAYED
--<DBA_OBJECTS AND ALL_OBJECTS --any type of database objects
SYNTAX:
*******
CREATE [OR REPLACE] SEQUENCE <seq_name>
[START WITH <value>] --Default MIN value
[INCREMENT BY <value>] --Default 1
[MINVALUE <value>] --Default 1
[MAXVALUE <value>] --Default --10^27 --999999999999999999999999999
[NOCYCLE / CYCLE] --Default NOCYCLE
[NOCACHE / CACHE <size>]; --Default 20
-->Sequence can be incremental or decremental.
-->Increment by can have positive or negative values.
-->Positive value in the INCREMENT BY indicates Incremental sequence.(low value to high value)
-->Negative value in the INCREMENT BY indicates Decremental sequence.(high value to low value)
-->START WITH clause has the highest priority than the MINVALUE for the first cycle.
-->START WITH value can not be altered.
When a sequence is created with no user specified values then sequence will start generating the numbers from 1 and ends with 10 power 27.
INCREMENT BY 1 / n : By default a sequence is incremented by 1.
-------------------
When this clause is used it will allow a user to specify the increment either with +ve or -ve value.
Any interval can be specified for increment. If +ve value is specified it generates the sequence of numbers in Ascending order. If -ve values is specified it generates the sequence of numbers in Descending order.
START WITH n : This clause is used to specify the start value of a sequence.
---------------
It gets executed only for once.
Start value should be >=minvalue and <=maxvalue.
MINVALUE n : It is used to specify min value of a sequence.
-----------
MAXVALUE n : It is used to specify max value of a sequence.
------------
NOCYCLE : It is a default option which is set for a sequence and that sequence will be not reusable.
--------
CYCLE : It is used to make a sequence repeat with respect to gerarating the numbers between min value to max value and vice versa.
-------
CACHE n : It will support to create a buffer to store n number of values.
---------
-->Cache is a memory area which is used to access sequence value very fastly.
NO CACHE:
==========
--> IT IS DEFAULT PARAMETER.WHEN WE CREATED A SEQUENCE WITH "NO CACHE"
-->PARAMETER THEN THE SET OF SEQUENCE VALUES ARE STORING INTO DATABASE MEMORY.
EVERY TIME WE WANT ACCESS SEQUENCE NUMBERS THEN ORACLE SERVER WILL GO TO DATABASE MEMORY AND RETURN TO USER.SO THAT IT WILL DEGRADE THE PERFORMANCE OF AN APPLICATION.
CACHE n:
========
- WHEN WE CREATED A SEQUENCE WITH "CACHE " PARAMETER THEN SYSTEM IS ALLOCATING TEMP. MEMORY(CACHE) AND IN THIS MEMORY WE WILL STORE THE SET SEQUENCE NUMBERS.WHENEVER USER WANT TO ACCESS SEQUENCE NUMBERS THEN ORACLE SERVER WILL GO TO CACHE MEMORY AND RETURN TO USER.
- ACCESSING DATA FROM CACHE IS MUCH FASTER THAN ACCESSING DATA FROM DATABASE.IT WILL INCRESE THE PERFORMANCE OF AN APPLICATION.HERE "n" IS REPRESENT THE SIZE OF CACHE FILE.MINIMUM SIZE OF CACHE IS 2KB AND MAXIMUM SIZE OF CACHE IS 20KB.
It uses 2 pseudo columns:
************************
1. nextval - It gives the next value generated by the sequence.
2. currval - It gives the present value provided by the sequence.
how to access the methods?
sequencename.pseduocolname
seq.nextval
seq.currval
EX:
****
create sequence s1;
select s1.nextval from dual;
select s1.nextval from dual;
select s1.nextval from dual;
select s1.nextval from dual;
select s1.currval from dual;
EX:
****
CREATE SEQUENCE S2
START WITH 1
MINVALUE 1
INCREMENT BY 1
MAXVALUE 5
NOCYCLE;
/
CREATE TABLE XX_STUDENT
(
SNO NUMBER,
SNAME VARCHAR2(20)
);
/
INSERT INTO XX_STUDENT VALUES (S2.NEXTVAL,'MOHAN');
INSERT INTO XX_STUDENT VALUES (S2.NEXTVAL,'MADAN');
INSERT INTO XX_STUDENT VALUES (S2.NEXTVAL,'VINOD');
INSERT INTO XX_STUDENT VALUES (S2.NEXTVAL,'RAJA');
INSERT INTO XX_STUDENT VALUES (S2.NEXTVAL,'RANI');
INSERT INTO XX_STUDENT VALUES (S2.NEXTVAL,'SUNIL');
/
SELECT * FROM XX_STUDENT;
/
CREATE SEQUENCE S3
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
CACHE 2;
/
TRUNCATE TABLE XX_STUDENT;
/
INSERT INTO XX_STUDENT VALUES (S3.NEXTVAL,'MOHAN');
INSERT INTO XX_STUDENT VALUES (S3.NEXTVAL,'MADAN');
INSERT INTO XX_STUDENT VALUES (S3.NEXTVAL,'VINOD');
INSERT INTO XX_STUDENT VALUES (S3.NEXTVAL,'RAJA');
INSERT INTO XX_STUDENT VALUES (S3.NEXTVAL,'RANI');
INSERT INTO XX_STUDENT VALUES (S3.NEXTVAL,'SUNIL');
/
SELECT * FROM XX_STUDENT;
/
CREATE SEQUENCE SEQ1
START WITH 100
INCREMENT BY 1
MINVALUE 100
MAXVALUE 200
CYCLE
CACHE 10;
/
--EXAMPLE
CREATE TABLE XX_EMP1(ENAME VARCHAR2(20),EMPNO NUMBER(4),SALARY NUMBER(5));
INSERT INTO EMP1 VALUES('XXX',SEQ1.NEXTVAL,35000);
INSERT INTO EMP1 VALUES('YYY',SEQ1.NEXTVAL,45000);
INSERT INTO EMP1 VALUES('ZZZ',SEQ1.NEXTVAL,45000);
SELECT * FROM EMP1;
/
To interact with the sequence to generate the numbers, 2 pseudo columns plays and important role.
NEXTVAL ---- generates next value of a sequence
CURRVAL ---- displays current value of a sequence
NEXTVAL & CURRVAL can be used;
1. at SELECT statement
2. at INSERT,UPDATE statement
3. at Outer most queries
Restriction on NEXTVAL & CURRVAL;
1. Can't be used at DELETE command
2. Can't be used at Inner queries
3. Can't be used with DISTINCT operator
4. Can't be used at Group By Clause
ALTERING A SEQUENCE:
--------------------
Sequence which is created can be altered w.r.to minvalue, maxvalue, increment by value... etc. It does not support to change the START value.
Syntax:
ALTER SEQUENCE sequence_name
INCREMENT BY n
MINVALUE n
MAXVALUE n ......;
EX:
ALTER SEQUENCE SEQ1
INCREMENT BY 2
MINVALUE 150
MAXVALUE 300;
/
SQL> DESC USER_SEQUENCES;
SQL>
DROPING A SEQUENCE:
-------------------
A sequence which is created can be dropped using DROP SEQUENCE command.
DROP SEQUENCE seq_name;
DROP SEQUENCE sn1;
create table emp12(ename varchar2(20),empno varchar2(10),salary number(5))
insert into emp12 values('aaa','E'||s1.nextval,35000)
select * from emp12
VIEWING THE SEQUENCES INFORMATION:
----------------------------------
USER_SEQUENCES :
----------------
This predefined table provides the detail information about the sequences which are created in a database, which includes Sequence name,start value, min value, maxvalue,........ etc.
SQL> SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM USER_SEQUENCES;
DROP SEQUENCE <SEQUENCE_NAME>;
No comments:
Post a Comment