DB Objects / SCHEMA objects / ORACLE objects :-
---------------------------------------------
1.TABLES
2. VIEWS
3. SYNONYMS
4. SEQUENCES
5. MATERIALIZED VIEWS
6. INDEXES
7.CLUSTERS
---------------------
VIEWS
A view is a subset of a table i.e. part of the table.
A view is a virtual table because it doesn't store data and doesn't occupy memory.
It always derives data from base table.
A view is representation of a query.
views are created
To provide security
To reduce complexity
A view provides another level of security called row & column level security.
With the help of views we can grant specific rows & columns to users.
Views are Two Types:
1.Simple views.
2.Complex views.
1.Simple views.
If view created on single table then it is called Simple View.
Syntax:
CREATE VIEW <NAME>
AS
SELECT column_Names
FROM TABNAME
[WHERE COND];
select *from emp;
select ename,mgr,sal from emp;
CREATE VIEW V7
AS
SELECT ename,mgr,sal from emp;
select *from V7;
CREATE VIEW V1
AS
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP;
Oracle creates view "V1" and stores query but not query output
SELECT * FROM V1 ;
When above query is submitted to Oracle , it execute the query as follows
SELECT * FROM (SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP);
Login as administrator:
Granting permission to create View in DB:
GRANT CREATE VIEW TO <User_Name>;
Granting permissions on view to users :
GRANT ALL ON V1 TO <user name> ;
GRANT ALL ON V1 TO HR;
select *from emp;
Row-Level Security
=================
CREATE VIEW V2
AS
SELECT EMPNO,ENAME,JOB,DEPTNO
FROM EMP
WHERE DEPTNO = 20;
GRANT ALL ON V2 TO HR;
select *from V2;
CREATE VIEW V3
AS
SELECT EMPNO,ENAME,JOB,DEPTNO
FROM EMP
WHERE DEPTNO =20
WITH CHECK OPTION;
GRANT ALL ON V3 TO HR;
-------------------------------
2.Complex Views
A View said to be complex view
If it is based on multiple tables
If it is based on query contains
group by clause
having clause
distinct clause
aggregate functions
sub-queries
set operators
Views reduces complexity i.e. with the help of views complex queries can be converted into simple queries which is useful for non-DB developers.
CREATE VIEW CV1
AS
SELECT
E.EMPNO,
E.ENAME,
E.SAL,
D.DEPTNO,
D.DNAME,
D.LOC
FROM
EMP E ,
DEPT D
Where
E.DEPTNO = D.DEPTNO;
select *from CV1;
After creating view, whenever we want data from emp & dept tables instead of writing complex join query write the simple query as follows
select *from CV1;
CREATE VIEW CV2
AS
SELECT
D.DNAME,
SUM(E.SAL) AS TOTSAL
FROM
EMP E,
DEPT D
Where
E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME;
After creating view, whenever we want dept wise total sal execute the following simple query
SELECT * FROM CV2;
==================================================
Difference bewteen Simple and Complex Views?
Simple Views
Based on single table.
Query performs simple operations
DML operations are allowed here
VS
Complex Views
Based on Multiple Tables
Query performs complex operations like joins, group by ....etc
DML operations are not allowed here
======================================================
Table: User_Views
It is a table which stores the list of views created by an user.
select *from user_views;
select view_name from user_views;
select text from user_views where view_name='V1';
========================================================
What are the differences between Table and View?
Table
It stores data and occupy memory
Changes made to one table are not visiable to another table.
Vs
View
Does not store the data or occupy the memory
Changes made to one table are visiable to another table.
========================================================
Q:How to add a column to view?
CREATE VIEW V4
AS
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP;
To add column (hiredate) to V4 it must be recreated.
Example:
CREATE OR REPLACE VIEW V4
AS
SELECT EMPNO,ENAME,JOB,DEPTNO,HIREDATE FROM EMP;
select *from V4;
Q:How to drop a View
DROP VIEW V4;
============================================IMP====
Q:If we drop table what about views created on table ?
Views are not dropped but views cannot be queried.
=========================================
------------
USER_VIEWS :-
-------------
Stores list of views created by user list of views ?
SELECT VIEW_NAME FROM USER_VIEWS ;
Select query associated with v1 ?
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME='V1' ;
Droping view :-
-----------------V.IMP------------------------
DROP VIEW V1;
Q:If we drop table what about views created on table ?
ans :-views are not dropped but views cannot be queried
------------------------------------------
Difference between table & view ?
TABLE
1.Stores data and occupies memory
2.Changes made to one table
Vs
VIEW
Doesn't store data and doesn't occupy memory.
Changes are visible are not visilble inanother table.
------------------------------------
Q:How to add a column to view ?
ex :-
CREATE VIEW V1
AS
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP;
=>To add column (hiredate) to view it must be recreated
CREATE OR REPLACE VIEW V1
AS
SELECT EMPNO,ENAME,JOB,DEPTNO,HIREDATE FROM EMP;
No comments:
Post a Comment