VIEWS
View is a database object.
It doesnot contain any data within the view itsel;f. It always brig the data from base tables.
View also known as Stoed select statement.
Simple view
Complex view
Force view
Read only view
View with check options
Materialised view is a different object.
Grant create view to user ----It will grant the acces to user to create a view.
View name cannot be duplicate
Object should also be different.
If view doesnot store any data then how it bring the data.
Select * from view1;
Select * from ( select text from user_views where view_name=’view1’)
Select * from ( select * from employee_table)
This is how it works internally. It check the view in Data dictionary user_views then change the exaxt slect statemtn that used while creating view.]
View always bring current data from base tables.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
APEX$_ACL TABLE
APEX$_WS_FILES TABLE
APEX$_WS_HISTORY TABLE
APEX$_WS_LINKS TABLE
APEX$_WS_NOTES TABLE
APEX$_WS_ROWS TABLE
APEX$_WS_TAGS TABLE
APEX$_WS_WEBPG_SECTIONS TABLE
APEX$_WS_WEBPG_SECTION_HISTORY TABLE
DEMO_CUSTOMERS TABLE
DEMO_ORDERS TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEMO_ORDER_ITEMS TABLE
DEMO_PRODUCT_INFO TABLE
DEMO_STATES TABLE
DEMO_USERS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE TABLE
EMPLOYEE_V VIEW
EMPLOYEE_V1 VIEW
20 rows selected.
SQL> select distinct view_name from user_views;
VIEW_NAME
------------------------------
EMPLOYEE_V1
EMPLOYEE_V
SQL> select distinct table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
DEMO_USERS
APEX$_ACL
APEX$_WS_ROWS
APEX$_WS_NOTES
APEX$_WS_LINKS
EMP
DEMO_CUSTOMERS
DEMO_PRODUCT_INFO
APEX$_WS_WEBPG_SECTIONS
APEX$_WS_TAGS
TABLE_NAME
------------------------------
EMPLOYEE
APEX$_WS_WEBPG_SECTION_HISTORY
DEMO_ORDER_ITEMS
DEMO_ORDERS
DEMO_STATES
APEX$_WS_HISTORY
APEX$_WS_FILES
18 rows selected.
-------------------------
-- Simple View
--------------------------
View created based on single table. Without using the following.
----GROUP By clause
----ORDER By clause
-----Joins
-----Aggregate functions
-----subquery
select, insert, update and merge we can all mperform in simple view.
-------------------------
-- Complex View
--------------------------
Using following then complex view
----GROUP By clause
----ORDER By clause
-----Joins
-----Aggregate functions
-----subquery
Select is always possible but DML operation may or maynot be possible.
SQL> Create view employee_v as select * from employee;
SQL> insert into employee_v values( 'sdkn',22);
1 row created.
SQL> select * from employee;
NAME ID
-------------------- ----------
sdkn 22
peeyush 11
SQL> commit;
Commit complete.
-------------------------
-- Force View
--------------------------
: We are creating view when base table not available. After creating view , if we create base table then this view become valid and return the data.
SQL> select * from emp5;
select * from emp5
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create view v_emp as select * from emp5;
create view v_emp as select * from emp5
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create force view v_emp as select * from emp5;
Warning: View created with compilation errors.
-------------------------
-- Read Only View
--------------------------
DML operation cannot perform. Insert, updte, delete.
SQL> create view employee_vread as select * from emplpoyee with read only;
create view employee_vread as select * from emplpoyee with read only
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create view employee_vread as select * from employee with read only;
View created.
SQL> insert into employee_vread values('sfs',111);
insert into employee_vread values('sfs',111)
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
-------------------------
-- With Check Option
--------------------------
Comments
Post a Comment