Saturday, March 12, 2016

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>;



No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...