VIEWS AND SEQUENCES

VIEW:

 It is a logical representation of data.

CREATE VIEW <VIEW_NAME> AS (SELECT STATEMENT)

Simple view : view created on a single table

-----------------------

create view emp_vv

as select ename,sal,comm,empno

from emp

select *

from emp_vv

---------------------------------------

Note:

if i update the simple view column values that will refelect on the table as well...

We can update only simple views on upon the privileges provided on the view.

we cannot update the complex views.

update emp_vv

set comm = 22

where comm is null;

commit;

select *

from emp_vv;

comm is updated to 22;

select *

from emp;

even in the above emp table comm is updated to 22 as the update was done on emp_vv view which is based on emp table.

COMPLEX_VIEW

-------------------------

View created on more then one table is called complex view

Ex:

CREATE VIEW EMP_complex_V1 AS

(

SELECT

 E.ENAME

,E.JOB

,E.SAL

,E.EMPNO

,D.DNAME

,E.DEPTNO

,e.comm

FROM EMP E,DEPT D

WHERE D.DEPTNO(+) = E.DEPTNO);

select *

from EMP_complex_V;

even we can apply condition on view

inorder to filter the data to display

which is as below

select *

from EMP_complex_V

where sal > 8000

ex:

update EMP_complex_V

set sal = sal+1000

where deptno = 10

But as per 11g database we are able to do it……

drop view

-------------

DROP VIEW < VIEW_NAME>

1) create table  emp_90

as select * from emp

where 1=1;

2)create a view.

create view emp_v90

as select *

from emp_90

view created....

select *

from emp_v90;

--

shows the data output...

-------------------------------------

now drop a table emp_90 on which a view is dependent.

drop table emp_90;

table dropped.

try to query the view...

select *

from emp_v90;

select * from  emp_v;

says view has some errors bcz table doesnot exists.

even when i drop a table on which a view is dependent still the view will exists but when tryting to quuery the view it says it has some errors....

when i automatically create the table again.The view will become valid...

create table

emp_90

as select *

from emp

where 1=1;

table emp_90 created.

select *

from emp_v90;

shows the output....

-----------------------------------------------------------------------

want to create a table and insert the records  having the same structure of some other table and data in that table which already exists in the data base..

create table dept_test1

as select * from dept where 1=1;

want to create a table only  having the same structure of some other table with no data

which already exists in the data base..

create table dept_test2

as select * from dept where 1=2;

-----------------------------------------------------------------------

Note:

Want to create a view where the tables doesnot exists

create view emp_v80

as select *

from emp_80

it gives error stating table or view doesnot exists....

---------------------------------------------------------------------

FORCE VIEW

----------------------

When ever a base table does not exists in the data base

but still if we want to create a view by using those tables

then we can force a view stucture to be created using a

keyword called FORCE.

Example.. as per the below view is being created the emp_80 table

                does not exists but still the view is created by using the

                keyword called FORCE.

View is created though emp_v80 table doesnot exists

because we have forced the view....

select * from

test_fv ;

it shows view has some errors

because the emp_v80 doesnot exists in the data base.....

now create table emp_v80 with out any records..

create table

emp_80

as select *

from emp

where 1=2;

select *

from emp_80;

o/p --  no rows returned....

select *

from test_fv;

o/p -- no rows returned bcz the query which is being ran on this view

         doesnot how any data.....

-------------

now insert records into emp_80 table and then query the view..

select * from

test_fv;

shows the data.....

-------------------------------------------------------------------------

Case 2:

create force view test_100

as select empno,sal,ename,deptno,comm

from emp_100;

view created...

select *

from test_100;

shows view as some errors bcxz emp_100 table doesnot exists.

Now create the table emp_100 with columns empno,ename

create table emp_100

as select empno,ename

from emp

where 1=1;

table emp_100 is created.

now query the test_100 view...

select *

from test_100;

it again says view has some errors

bcz the query on which the view is dependent has some errors..

like.. we have developed the force

 view with 5 columns on a table

but that table contains only 2 columns...

so, now i need to change the same view..

inorder to do that we use create or replace command..

create or replace force view test_100

as select empno,ename

from emp_100;

vie has been recreated with valid query...

now execute the view..

select *

from test_100;

shows the output...

Sequence...

this is used to generate the numbers automatically...

This uses the Pseudo columns as

NEXTVAL

CURRVAl...

NEXTVAL --> Gives the next generated value of sequence

Systax:  select seq.nextval from dual;

CURRVAL: This igves the current value of the sequence...

Syntax: select seq.currval fro dual;

Note: for each session we need to use seq.nextval for the first time

and at the later we can use seq.curr val any no of times...

 But if we try to use seq.currval with out executing the seq.nextval

 in that session then we get the error as

ORA-08002:

Seq.currval is not yet defined in this session..

----------------------------------------------------------------------------

Syntax:

CREATE SEQUENCE <sequence_name>

[increment by <interger_value>

 start with <interger_value>

 maxvalue <interger_value>

 minvalue  < interger_value>

]

create sequence s3

increment by 1

start with 1

maxvalue 5;

execute a sequence...

select s1.nextval from dual;

 o/p 1,2,3

select s1.currval from dual;

once the max value is reached to 3 for the above seqnece

and when iam trying to execute that seq to get the next value

as the max value for that seq is only 3 it shows the below

error....

ORA-08004: sequence S1.NEXTVAL exceeds MAXVALUE

 and cannot be initiated

=------

create sequence s8

increment by 1

start with 1;

 CREATE SEQUENCE customers_seq

 START WITH     1

 INCREMENT BY   1

   maxvalue 3

 NOCACHE

 CYCLE;

example: create a seq and use a dynamic insertion

               when ever a records is created in the student table

-------------------------------------------

create table xx_std_info_bkup

having the same structure as xx_std_info

with no data

create table xx_std_info_bkup as

select * from xx_std_info where 1=2

--------------------------------------------------------

create table xx_std_info_bkdata

having the same structure as xx_std_info

with data

create table xx_std_info_bkdata as

select * from xx_std_info where 1=1

create sequence student_s90

increment by 1

start with 1000

INSERT INTO xx_std_info_bkup

values(student_s90.nextval,'A','M',20,'HYD');

INSERT INTO xx_std_info_bkup

values(student_s90.nextval,'B','M',20,'HYD');

INSERT INTO xx_std_info_bkup

values(student_s90.nextval,'C','M',20,'HYD');

INSERT INTO xx_std_info_bkup

values(student_s90.nextval,'D','M',20,'HYD');

select *

from xx_std_info_bkup

------------------------------

Drop A Sequence..

----------------------

drop sequence <Sequence _name>;