sequence| PSEUDO columns | CURRVAL| NEXTVAL| LEVEL| ROWNUM| ROWID
Sequence
Pseudo columns
à
Pseudo
column behave like a table column, but it is not actually store in a table.
à
Upon
pseudo column only select can be possible, DML operations can’t be possible to
implement.
à
The
available pseudo columns are
1.
CURRVAL
2.
NEXTVAL
3.
LEVEL
4.
ROWNUM
5.
ROWID
à CURRVAL, NEXTVAL are applied upon sequence
schema object. These are only in select statement, value clause of insert
statement, set clause of update statement.
à CURRVAL, NEXTVAL can’t be used in
·
A
sub queries
·
A
view, SNAPSHOT query
·
Select
statement with group by or order by
·
Select
statement with distinct operation.
·
Select
statement with set operator.
·
The
where clause of select statement.
·
The
default value of column.
·
The
condition of check constraint.
à The 1st reference to the
NEXTVAL written the sequence initial value.
à Before CURRVAL can be used for
sequence in a session 1st sequence should be implemented with
NEXTVAL.
à A sequence can be incremented only
in single SQL statement.
à The CURRVAL& NEXTVAL should be qualify
with the name of sequence like sequence name.CURRVAL, sequence name.NEXTVAL.
SEQUENCE
à It is a schema object, it can
generate unique sequential values.
à The sequence values are used for
primary key, unique values.
à For another schema they want to use
sequence in another schema they specify like this
Schemaname.sequencename.CURRVAL
Schemaname.sequencename.NEXTVAL
à Creating sequence
Syntax
Create sequence <sequencename>
[increment by n]
[start with n]
[max value n/ no max value]
[min value n/ no min value]
[cycle/ no cycle]
[cache/ no cache]
Order/ no order;
à Creation of incremental sequence and
how to use in insert statement
Create sequence deptno_seq
Increment by 10
Start with 10
Minvalue 0
Maxvalue 9999
Nocache
Nocycle;
Step2: - how to see sequence
Select deptno_seq.NEXTVAL from dual;
Select deptno_seq.CURRVAL from dual;
Step3: - how to insert into tdept table
Insert into tdept (deptno_seq.NEXTVAL,
’SOFTWARE’, ‘HYD’);
à Creation of sequence with cycle
Create sequence deptno_seq
Increment by 10
Start with 10
Minvalue 0
Maxvalue 9999
Nocache
cycle;
à Creation of decremental sequence
with cycle
Create sequence deptno_seq
Increment by -10
Start with 10
Minvalue 0
Maxvalue 9999
Nocache
cycle;
Modify the sequence
à It can be done by alter command, by
using alter command we can modify INCREMENT value, MAXVALUE, MINVALUE, CYCLE
option, CACHE option.
à The start with option can’t be
changed.
à Example
Alter sequence deptno_seq
Increment by 10
Minvalue 10
Maxvalue 9999
Nocache
Nocycle;
Drop sequence
à Drop sequence deptno_seq;
LEVEL
à
It
is used in hierarchical query to identify the hierarchical status in
number format.
à
It
written 1 for root node and 2 for its child node and so on…
à
To
establish a hierarchical relation ship with level we need START WITH &
CONNECT BY clauses.
Example
select level, ename, empno,
mgr, job from emp start with job='PRESIDENT' connect by prior empno=mgr;
à
Select nth highest value for a table
Select level, max(sal)
from emp where level=&levelno connect by prior sal>sal group by level;
à
Select nth lowest value for a table
Select level, min(sal)
from emp where level=&levelno connect by prior sal<sal group by level;
à
Hierarchical queries
Start with: - it specify
root row of hierarchy
Connect by: - it
specifies the relation between parent rows and child rows
With in condition we
must use PRIOR to ensure relation from root row
Example
select ename, empno, mgr,
job from emp start with job='PRESIDENT' connect by prior empno=mgr;
SYS_CONNECT_BY_PATH
it written the path of a
column from root node to child node
select ename, SYS_CONNECT_BY_PATH
(ename, ‘ to ’) “PATH” from emp start with job='PRESIDENT' connect by prior
empno=mgr;
ROWNUM
à Oracle automatically assign a rownum
value to each row when it is retrieved.
à The 1st row select has
rownum 1 and 2nd row has rownum 2 and so on…
à > is not possible on rownum
à Rownum is temporary, and it is valid
for only that select statement.
à It can be used at select, where,
group, having clauses.
à
Examples
Select rownum, empno,
ename, deptno from emp;
select lpad (' ', rownum,'*')
from emp;
Select rownum, empno,
ename, deptno from emp where rownum<5;
Select rownum, empno,
ename, deptno from emp where job=’MANAGER’ and rownum=1;
à
How to display alternating rows from table
Order by even number
Select rownum, ename
from emp group by rownum, ename having mod(rownum,2)=0 order by rownum;
Order by odd number
Select rownum, ename
from emp group by rownum, ename having mod(rownum,2)=1 order by rownum;
à
Queries for top n records
Select rownum, ename
from emp where rownum<=5;
Display all top salary
records by salary wise
Select ename, sal,
rownum from (select rownum, ename, sal from emp order by sal desc);
Display nth highest
salary
Select ename, sal, rownum from
(select rownum, ename, sal from emp order by sal desc) group by rownum, ename,
sal having rownum=1;
ROWID
à It is an exact physical address of a
row, oracle automatically assign rowid to each row.
à It can be used in select, where,
group by, having clause.
à Examples
Select rowid, empno,
ename from emp;
select rowid, e.* from
emp e where rowid='AAAS1VAABAAAb2JAAA';
à
How to delete duplicate rows by using rowid
1st check for
duplicate rows
Select * from emp e
where rowid<(select max(rowid) from emp f where e.empno=f.empno);
Then delete duplicate
rows
delete from emp e where rowid not in
(select min(rowid) from emp f where group by empno);
Comments
Post a Comment