Simple View
select * from employee
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create view vwemployee as
select fname,salary from employee;
create view vwemployee as select fname,salary from employee
*
ERROR at line 1:
ORA-00942: table or view does not exist
create force view vwemployee as select fname,salary from employee;
SQL> create force view vwemployee as select fname,salary from employee;
Warning: View created with compilation errors.
SQL> create table employee as select * from employ;
Table created.
SQL> select * from vwemployee;
FNAME SALARY
--------------- ----------
Rahul 20000
Anil 24000
Samir 20000
Jaya 23000
Suresh 25000
Karthik 24000
Bhavana 20000
Steven 24000
Abdul 28000
Manoj 30000
10 rows selected.
SQL> create table temploy as select * from employ;
Table created.
SQL> select * from temploy;
EMPI FNAME LNAME SALARY DEPT
---- --------------- --------------- ---------- ----
E01 Rahul Raj 20000 D02
E02 Anil Babu 24000 D01
E03 Samir Thahir 20000 D03
E04 Jaya Krishnan 23000 D01
E05 Suresh Peters 20000 D01
E06 Karthik Kumar 24000 D02
E07 Bhavana Aravind 20000 D02
E08 Steven Devassy 24000 D03
E09 Abdul Rahim 28000
E10 Manoj Kumar 30000
10 rows selected.
SQL> create or replace view vwtemploy as
select empid,lname,salary from temploy;
View created.
select * from vwtemploy
EMPI FNAME SALARY
---- --------------- ----------
E01 Rahul 20000
E02 Anil 24000
E03 Samir 20000
E04 Jaya 23000
E05 Suresh 20000
E06 Karthik 24000
E07 Bhavana 20000
E08 Steven 24000
E09 Abdul 28000
E10 Manoj 30000
10 rows selected.
update vwtemploy
set salary= 25000
where empid='E05'
SQL> select * from temploy;
EMPI FNAME LNAME SALARY DEPT
---- --------------- --------------- ---------- ----
E01 Rahul Raj 20000 D02
E02 Anil Babu 24000 D01
E03 Samir Thahir 20000 D03
E04 Jaya Krishnan 23000 D01
E05 Suresh Peters 25000 D01
E06 Karthik Kumar 24000 D02
E07 Bhavana Aravind 20000 D02
E08 Steven Devassy 24000 D03
E09 Abdul Rahim 28000
E10 Manoj Kumar 30000
create or replace view vwtemploy
as select empid,fname,salary from temploy
with read only;
View created.
update vwtemploy
set salary= 25560
where empid='E05'
ERROR at line 2:
ORA-01733: virtual column not allowed here
insert into vwtemploy
values('E15','Jeevan',34567);
SQL> insert into vwtemploy
2 values('E15','Jeevan',34567);
insert into vwtemploy
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> select * from temploy;
EMPI FNAME LNAME SALARY DEPT
---- --------------- --------------- ---------- ----
E01 Rahul Raj 20000 D02
E02 Anil Babu 24000 D01
E03 Samir Thahir 20000 D03
E04 Jaya Krishnan 23000 D01
E05 Suresh Peters 20000 D01
E06 Karthik Kumar 24000 D02
E07 Bhavana Aravind 20000 D02
E08 Steven Devassy 24000 D03
E09 Abdul Rahim 28000
E10 Manoj Kumar 30000
10 rows selected.
Complex view
create view vwsummrytemply as
select deptid,sum(salary) "dept salary" from temploy
group by deptid
View created.
SQL> select * from vwsummrytemply;
DEPT dept salary
---- -----------
58000
D01 67000
D03 44000
D02 64000
SQL> select fname,deptname,depthead from employ e join department d on e.deptid=d.deptid;
FNAME DEPTNAME DEPTHEAD
--------------- --------------- ---------------
Rahul Sales Kavitha
Anil HR Anjan
Samir Finance Suresh
Jaya HR Anjan
Suresh HR Anjan
Karthik Sales Kavitha
Bhavana Sales Kavitha
Steven Finance Suresh
8 rows selected.
create view vwempdept as
select fname,deptname,depthead from employ e
join department d
on e.deptid=d.deptid;
SQL> select * from vwempdept;
FNAME DEPTNAME DEPTHEAD
--------------- --------------- --------------
Rahul Sales Kavitha
Anil HR Anjan
Samir Finance Suresh
Jaya HR Anjan
Suresh HR Anjan
Karthik Sales Kavitha
Bhavana Sales Kavitha
Steven Finance Suresh
8 rows selected.
Inline view
select fname,d.deptname,d.depthead
from employ e,
(select deptid,deptname,depthead from department ) d
where e.deptid=d.deptid;
FNAME DEPTNAME DEPTHEAD
--------------- --------------- ---------------
Rahul Sales Kavitha
Anil HR Anjan
Samir Finance Suresh
Jaya HR Anjan
Suresh HR Anjan
Karthik Sales Kavitha
Bhavana Sales Kavitha
Steven Finance Suresh
Materialized view
alter table temploy
add constraint pkempempid primary key(empid);
CREATE MATERIALIZED VIEW empmatview
REFRESH FORCE on commit
AS
select empid,fname,salary from temploy
Materialized view created.
SQL> select * from empmatview;
EMPI FNAME SALARY
---- --------------- ----------
E01 Rahul 20000
E02 Anil 24000
E03 Samir 20000
E04 Jaya 23000
E05 Suresh 20000
E06 Karthik 24000
E07 Bhavana 20000
E08 Steven 24000
E09 Abdul 28000
E10 Manoj 30000
drop materialized view empmatview
create view vwempdepartment as
select fname , deptname from employ e join department d
on e.deptid=d.deptid;
SQL> create view vwempdepartment as
2 select fname , deptname from employ e join department d
3 on e.deptid=d.deptid;
View created.
SQL> select * from vwempdepartment;
FNAME DEPTNAME
--------------- ---------------
Rahul Sales
Anil HR
Samir Finance
Jaya HR
Suresh HR
Karthik Sales
Bhavana Sales
Steven Finance
8 rows selected.
select text from user_views where view_name='VWTEMPLOY';
TEXT
-------------------------------------------------------------------------
select empid,fname,salary from temploy
with read only
SQL> select view_name from user_views;
VIEW_NAME
------------------------------
MVIEW_WORKLOAD
MVIEW_FILTER
MVIEW_LOG
MVIEW_FILTERINSTANCE
MVIEW_RECOMMENDATIONS
MVIEW_EVALUATIONS
MVIEW_EXCEPTIONS
AQ$_DEF$_AQCALL_F
AQ$DEF$_AQCALL
AQ$_DEF$_AQERROR_F
AQ$DEF$_AQERROR
VIEW_NAME
------------------------------
PRODUCT_PRIVS
VWEMPLOYEE
VWTEMPLOY
VWSUMMRYTEMPLY
VWEMPDEPT
VWEMPDEPARTMENT
17 rows selected.
No comments:
Post a Comment
Thanks for your valuable comments