26. SEQUENCES

Sequences

·         Sequence is database object

·         It is a shared object

·         Sequence display the integer number

·         Sequence eliminate serialized and improves concurrency

·         Useful for multiple users across the DB

·         Useful in frontend applications we can define synonyms on sequences

  Syntax:   Create sequence [start with value]

                             [increment by value]

                             [minvalue value}nomin value]

                             [maxvalue value|nomax value]

                             [cycle|nocycle]

                             [cache value|no cache]

[order}no order]

[default]

Start with value:

·         It specifies with which value sequence has to start by default it starts with ‘1’.

·         Always start with values has to equal or greater than min value

Note: We can alter all other parameters except start with parameter

Increment by value:

·         It specifies with which value sequence has to increment so, to get next value.

·         By default it increment with ‘1’

·         This may also have ‘-ve value’

Min value:    Min value of the sequence

Max value:   Max value of the sequence

	Minvalue	Maxvalue
+ve	1	1*1027
-ve	-1*1026	-1

Cycle:

·         To generate the sequence values in cyclic manner(looping/iterations) we use ‘cycle’

·         Default ‘nocycle’

·         For the second iteration sequence starts with min value

 Cache:

·         It is a memory area which is in saea(instance), which stores the pre-generated value, so has to increase the performance

·         Cache values get vanished/erased when system abnormally shutdown

·         By default it stores upto 20 values

·         While exporting and importing the data we may have the chances of skipping sequence values

·         By default min value of the cache is ‘2’

Note:  Cache values has to fit into the cycle by using following formulae we have to specify the cache values

Formula:  Ceil (maxvalue-minvalue)/abs(increment by value)

Eg:         Ceil((10-1)/2)

              (9/2)

            Ceil(4.5)=5

Order:-  We have to use tis order parameter only in RAC applications (real application clusters)

Usage of sequence: We use sequence with the help of sequence pseudo columns They are:

1.   Next lavel

2.   Curr level

Next level:  displays the next value of sequence

Curr value:  displays the current status of the sequence or currently

generated value

    Note:  We have to use currval only after nextval

Syntax: Sequencename.nextval;

             Sequencename.currval;

SQL> create sequence sq;

SQL> select sq.nextval from dual;

SQL> select sq.nextval from dual;

SQL> select sq.currval from dual;

SQL> create table t(sno number(5));

SQL> insert into t values(sq.nextval);

SQL> select * from t;

SQL> Update t set sno=sq.nextval;

SQL> create synonym  st for t;

SQL> insert into st values(sq.nextval);

SQL> select * from st;

SQL> create sequence sq1 start with 5 increment by 2 minvalue 1 maxvalue 10 cycle cache 5

Output: sequence created

SQL> alter sequence sq1 nocycle;

SQL> alter sequence sq1 maxvalue 20;

Note: We can alter all other parameters except start with value

SQL>alter sequence sq1 increment by 1 maxvalue 10;

SQL>select sq1.currval, sq.nextval from dual;

Note: If you use the currval just beside of nextval in the same select statement, firast nextvalue will be consider and also currval displays the nextval values or currently displayed value.

SQL> select * from user_squences where sequence_name=’seq’;

Usage of sequence:

Select list	Insert value clause
To assign pl/sql variables(11g)	Update set clause

 Eg:

Declare

V number(5):=sq.nextval;

Begin

DOPL(v);

End;

We won’t use sequence in the following cases:

• ·         Sub queries
• 
  
• ·         View query (create or replace view sview as select * from..) in this select list won’t use.
• 
  
• ·         Order by
• 
  
• ·         Group by
• 
  
• ·         Select list where clause and distinct clause
• 
  
• ·         Delete statement
• 
  
• ·         Set operators
• 
  
• ·         Materialized view
• 
  
• ·         Check constraints
• 
  
• ·         Default value(create and alter statement)

•   Write a query to display to all child tables for a parent table
• 
  
•     How to get manager name along with employee name in a single column
•    Why we use concatenation operator in DBMS_output.
•    put_line

  

Analytical functions:

·         Rank()

·         Dense-rank()

·         Parent-rank()

·         Cumu-dist()

·         Row-num()

·         Ntile()

·         Log()

·         Lead()

Rank():  Rank provides the ranking values for each of the table records rank skips the sequential ranking values when there is a duplicate records or values

Dense-rank():   It works as same to that of rank but it won’t skip the sequential ranking.

Percent-rank():  Percent rank work based on rank values by using

following formulaeRecord rank-1/total rank-1

Cumu-rank():  Calculate the cumulative distribution by using following

Formulae

Row-num():  Provides the row numbers for each of the record this row

number allows partition unlike pseudo column row numbers

Ntile():  Segments the records into given number of partitions

                            (or)

divides the records into n number of partitions

  

Log(), lead():  Displays logging and leading values with respect to current record

          Eg:  Select deptno,sal ,

rank() over(partitions by deptno order by sal desc) rnk,

dense-rank()over(partitions by deptno order by sal desc) drnk,

perent-rank()over(partitions by deptno order by sal desc) prnk,

cumu-rank()over(partitions by deptno order by sal desc) cd,

row-number()over(partitions by deptno order by sal desc) rn,

ntile(2) over(partitions by deptno order by sal desc) nt,

log(sal,1) over(partitions by deptno order by sal desc) lg,

lead(sal,1) over(partitions by deptno order by sal desc) ld

From emp;

Note: Some of the group functions also works as analytical functions as in the following case

SQL> select deptno,sal,sum(sal) over(partition by deptno order by sal desc) ssal from emp;

Flashback:  (DDL commands 10g)

Hear flashback won’t applicable to rollback because flashback is DDL command, commit and rollback are DML commands. And DDL are permanent and DML are temporary.

It retrives DB objects from ‘recycle-bin’

Eg:

SQL> select * from ftab;

SQL> drop table ftab;

    Note: From 10g onwards if drops an object that will be placed in recycle bin

SQL> select * from ftab;        //table/view doesn’t exist

SQL> flashback table ftab to before drop;    //flashback completed

         SQL> select * from ftab;

Sno

10 10

Purge: (DDL command 10g):  

          Purge eliminates the objects from recycle-bin permanently

Note: Purged objects are not possible for flashback

Purge table ftab;

While dropping we can bypass the flashback by usi g following

    EG:

              SQL> create table ptab(sno number);

              SQL> drop table ptab purge;

              SQL> flashback table ptab to before drop; //objects not in recycle bin

We can completely empty the recycle bin by using purge command as shown in blow.

EG: SQL> purge recycle bin;      //total tables will be deleted permanently

SQL> desc recycle bin;

Recycle bin metadata:  Here we can store all the dropped tables,

SCN, timestamp

SQL> create table stab(sno number(5));

SQL> drop table stab;

SQL> desc recyclebin;

SQL> select * from recyclebin where original_name=’stab’;

Eg for SCN_to_timestamp and timestamp_to_SCN:

SQL> Select original_name,dropSCN,SCN_to_timestamp(DROPSCN) tmst,

timestamp_to_SCN(SCN_to_timestamp(DropSCAN)) SCN from recyclebin

where original_name=’stab’;

Merge:  Merge perform insert, update, delete, in a single statement

Syntax:

          Merge into tablename using tablename|subquery|view on (condition)

          When matched then update set col=val, col1=val1…[where condition]

 [delete where condition]

 When notmathed then

Insert[(col1,col2)] values(val1,val2);

[where condition];

SQL> select * from dept;

SQL> create table mdept as select * from dept where 1=2;

  

SQL> merge into mdept x using dept

on (x.deptno=y.deptno)

when matched then update set x.loc=’hyd’

when not matched then

insert values(y.deptno,y.dname,y.loc)

·         which rows are involving in condition that column should not use in update

·         In matched condition we have to write update and delete in table whatever the rows has updated that rows only will be affected for deletion. In not matched only we need to write insert.

SQL> select * from mdept;

SQL> update set x.loc=’bang’;

SQL> delete where x.loc=’bang’

Deletion happens to the records which are affected by update statement.

We can also specify merge with out matched condition:

SQL> merge into mdept x using dept y on (x.deptno=y.deptno)

        When not matched then

          insert values(y.deptno,y.dname,y.loc);

       We can also specify merge without not matched condition:

SQL> merge into mdept x using dept y on (x.deptno=y.deptno)

when matched then update set x.loc=’bang’ delete where x.loc=’bang’

    We can write like this also

SQL> merge into mdept x using dept y