NALAM Oracle Apps Technical : VIEWS:

VIEWS:

******

-->why the people is introduced in views.

-->We have some drawbacks in synonyms.The drawbacks will be overwrite while using views.

Drawbacks of Synonyms:

**********************

-->We can't posible to create synonym on multiple tables.

-->We can't posible to create synonym on particular columns.

-->We can't posible to create synonym on particular record.

The above drawbacks will be overwrite by views.

VIEW:

*****

-->View is nothing but an image table or virtual table,which is created for a base table.

-->It is a virtual table to hide the base table and it works like a mirror image of the table.

-->It doesn't have own structure.

-->It is not posible to modify the structure of the table by using views.

-->We can define view on synonym and synonyms on views.

-->View can be created by taking all values from the base table or by taking only selected values from base table.

-->Any modifications made through a view automatically updates the base table and vice versa.

-->View is a database object , which will store a query in compiled format, hence it is called "Stored Query".

-->If any changes in the view automatically reflected into table and vice versa.

-->View supports only DML And DQL.

-->All views are stored by all_views,user_views

Advantages of views:

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

1. Provides high security

2. Improves performance

3. Network traffic gets reduces

4. Shortens SQL queries

5. Supports to perform summarized calculation fastly

6. Supports to INSERT/UPDATE/DELETE related data

7. We can retrieve the data from many tables (joins)

QUERY EXECUTION PROCESS:

*************************

QUERY-->ORACLE ENGINE-->SQL ENGINE-->GO BACK TO ORACLE ENGINE-->ORACLE DB-->DATA DICTIONARY-->META DATA-->ACTUAL DATA-->

Types of Views:

****************

-->Simple View / Read write view (select,insert,update,delete)

-->Read only views (select)

-->Check option views

-->Complex View

-->Force View

-->Materialized Views

SIMPLE VIEW:

************

-->It is used to define a view on single table that views are called as simple view.

-->It can perform all DML operations like Insert,Update and Delete.

NOTE:

*****

1. When base table is dropped, view will stop its working.

2. When base table is back in database, view will start its working.

SYNTAX:

******

CREATE VIEW <VIEW NAME> AS SELECT * FROM <TN> [ WHERE <CONDITION>];

CREATE [ OR REPLACE ] [ FORCE / NOFORCE ] VIEW view_name

AS SELECT query

[WITH READ ONLY / WITH CHECK OPTION];

EX:

***

CREATE TABLE EMP1 AS SELECT * FROM EMP;

CREATE TABLE EMP_T

SELECT *

FROM EMP1;

CREATE OR REPLACE VIEW SV

SELECT *

FROM EMP_T;

SELECT * FROM SV;

--DML OPERATIONS

--INSERT DATA INTO VIEW

INSERT INTO SV(EMPNO,ENAME,JOB) VALUES (100,'MOHAN','SOFTWARE');

SELECT * FROM SV; --CHECK WHETHER DATA IS INSERTED IN VIEW OR NOT

SELECT * FROM EMP_T; --CHECK WHETHER DATA IS INSERTED IN TABLE OR NOT

UPDATE SV SET ENAME='MADAN' WHERE ENAME='MOHAN';

SELECT * FROM SV;

SELECT * FROM EMP_T;

DELETE FROM SV WHERE EMPNO=100;

SELECT * FROM SV;

SELECT * FROM EMP_T;

DROP TABLE EMP_T;

SELECT * FROM EMP_T;

SELECT * FROM SV; --VIEW HAS ERRORS

SELECT OWNER,VIEW_NAME FROM ALL_VIEWS WHERE VIEW_NAME='SV';

SELECT *

FROM USER_VIEWS

WHERE 1=1

AND VIEW_NAME='SV'

SELECT *

FROM USER_OBJECTS

WHERE 1=1

AND OBJECT_NAME='SV';

CREATE TABLE EMP_T AS SELECT * FROM EMP;

SELECT * FROM EMP_T;

SELECT * FROM SV;

-WE CAN POSIBLE TO CREATE VIEW ON SYNONYM

CREATE SYNONYM EMP_SYN FOR EMP1;

SELECT * FROM EMP_SYN;

--CREATE VIEW ON ABOVE SYNONYM

CREATE VIEW EMP_SYNV AS SELECT * FROM EMP_SYN;

SELECT * FROM EMP_SYNV;

-- I WANT TO DROP THE SYNONYM

DROP SYNONYM EMP_SYN;

SELECT * FROM EMP_SYNV;

-- I WANT TO CREATE THE SYNONYM

CREATE SYNONYM EMP_SYN FOR DEPT1;

SELECT * FROM EMP_SYNV;

DROP SYNONYM EMP_SYN;

CREATE SYNONYM EMP_SYN FOR EMP;

SELECT * FROM EMP_SYN;

SELECT * FROM EMP_SYNV;

CREATE OR REPLACE VIEW SV1 AS SELECT ENAME,EMPNO,SAL FROM EMP;

SELECT * FROM SV1;

CREATE OR REPLACE VIEW SV2 AS SELECT * FROM EMP WHERE DEPTNO=20;

SELECT * FROM SV2;

--WE CAN CREATE SYNONYM ON VIEW

SELECT * FROM SV;

CREATE SYNONYM EMP_SYN1 FOR SV;

SELECT * FROM EMP_SYN1;

CREATE VIEW SV2 AS SELECT EMPNO,ENAME,JOB,SAL FROM EMP;

S ELECT * FROM SV2;

--Interview question

A View was created by selecting all the columns of tables.

CREATE OR REPLACE VIEW EMP_VIEW

SELECT *

FROM EMP1;

Q)After created a view,If we add a new column to the base table,will that column added to view.

ALTER TABLE EMP1 ADD ADDRESS VARCHAR2(400);

SELECT * FROM EMP1;

SELECT * FROM EMP_VIEW;

SELECT *

FROM USER_VIEWS

WHERE 1=1

AND VIEW_NAME='EMP_VIEW';

SELECT *

FROM USER_OBJECTS

WHERE 1=1

AND OBJECT_NAME='EMP_VIEW';

READ ONLY VIEWS:

****************

-->When a view is created as Read only view, it will not allow a user to perform DML operations through a view.

A keyword called "WITH READ ONLY" should be associated.

-->It will allow select and desc commands.

EX:

***

CREATE OR REPLACE VIEW EMP_RO

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP

WITH READ ONLY;

SELECT * FROM EMP_RO;

NOTE:

*****

-->We cannot perform DML operations through a view on base table.

EX:

****

DELETE FROM EMP_RO;

--View based on Arithematic Expressions

create view pay_info as select empno ecode, sal basic, round(sal * .25) da, round(sal * .35) hra ,

round(sal * .15) pf,

round(sal + sal * .25 + sal * .35 - sal * .15 ) gross

from emp where sal >= 2000;

desc pay_info

select * from pay_info;

CHECK OPTION VIEWS:

*******************

-->These views will support to insert the data into base table only for those records that get match with the condition mentioned at SELECT query.

-->An option called "WITH CHECK OPTION" is used.

-->It is a constraint which is used to restrict rows on base table through a view while performing DML operations.

EX:

***

CREATE OR REPLACE VIEW V4

SELECT EMPNO,ENAME,DEPTNO FROM EMP1 WHERE DEPTNO=20

WITH CHECK OPTION;

INSERT INTO V4 VALUES(2001,'A',20);

INSERT INTO V4 VALUES(2002,'B',30); INVALID RECORD SINCE DEPTNO IF BEYOND THE CONDITION OF SELECT QUERY.

SELECT * FROM V4;

COMPLEX VIEW:

*************

-->It is used to define a view on multple tables that views are called as complex views.

-->We can not perform DML operatons(INSERT,UPDATE,DELETE) on complex view but by using instead of triggers.

-->A view is created based on single table using the below clauses.'

Group by caluse

Order by clause

Group function/Aggregate function

Joins

Subquery

NOTE:

*****

By default complex view is non updatable view.

EX:

***

CREATE VIEW EMPDEPT_CV

SELECT E.EMPNO,

E.ENAME,

E.JOB,

E.SAL,

E.DEPTNO,

D.DEPTNO,

D.DNAME,

D.LOC

FROM EMP E,

DEPT D

WHERE E.DEPTNO=D.DEPTNO;

--THE ABOVE QUERY RETURNS THE ERROR(DUPLICATE COLUMN NAME)

CREATE VIEW EMPDEPT_CV

SELECT E.EMPNO,

E.ENAME,

E.JOB,

E.SAL,

D.DEPTNO,

D.DNAME,

D.LOC

FROM EMP E,

DEPT D

WHERE E.DEPTNO=D.DEPTNO;

;

CREATE OR REPLACE VIEW EMPDEPT_CV

SELECT E.EMPNO,

E.ENAME,

E.JOB,

E.MGR,

E.HIREDATE,

E.SAL,

E.COMM,

E.DEPTNO EMP_DEPTNO,

D.DEPTNO DEPT_DEPTNO,

D.DNAME,

D.LOC

FROM EMP1 E,

DEPT1 D

WHERE 1=1

AND E.DEPTNO=D.DEPTNO ;

SELECT * FROM EMPDEPT_CV;

--DML OPERATIONS ARE NOT POSIBLE IN COMPLEX VIEW But its update in child table other than deptno.

INSERT INTO EMPDEPT_CV(EMPNO,ENAME,JOB,SAL,EMP_DEPTNO,DNAME,LOC) VALUES(11,'MOHAN','SOFTWARE',4000,30,'PO','HYD');

--ORA-01779: cannot modify a column which maps to a non key-preserved table

DELETE FROM EMPDEPT_CV WHERE DEPT_DEPTNO=10;

--ORA-01752: cannot delete from view without exactly one key-preserved table

UPDATE EMPDEPT_CV SET EMP_DEPTNO=40 WHERE EMP_DEPTNO=10;

--ORA-01779: cannot modify a column which maps to a non key-preserved table

CREATE OR REPLACE VIEW CV2 AS SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO;

ORA-00998: must name this expression with a column alias

NOTE:

*****

-->WHEN WE CREATE A VIEW WITH FUNCTION THEN WE MUST CREATE ALIAS NAME FOR THOSE FUNCTIONS OTHERWISE ORACLE RETURNS AN ERROR.

CREATE VIEW CV2 AS SELECT DEPTNO,SUM(SAL) AS SUMSAL

FROM EMP

GROUP BY DEPTNO;

SELECT * FROM CV2;

DELETE FROM CV2 WHERE DEPTNO=10; --IT WILL THROW THE ERROR(DML OPERATIONS ARE NOT ALLOWED)

INSERT INTO CV2 VALUES(50,20000); --IT WILL THROW THE ERROR(DML OPERATIONS ARE NOT ALLOWED)

UPDATE CV2 SET DEPTNO=60 WHERE DEPTNO=20; --IT WILL THROW THE ERROR(DML OPERATIONS ARE NOT ALLOWED)

CREATE VIEW CV3

SELECT EMPNO,ENAME,SAL,SAL*12 A_SAL

FROM EMP;

SELECT * FROM CV3;

-->Create a view with DISTINCT and try to execute DML operations to that view.

EX:

***

CREATE OR REPLACE VIEW CV4

SELECT DISTINCT DEPTNO

FROM EMP;

SELECT * FROM CV4;

DELETE FROM CV4 WHERE DEPTNO=10; -- IT WILL THROW THE ERROR(DML OPERATIONS ARE NOT ALLOWED)

UPDATE CV4 SET DEPTNO=50 WHERE DEPTNO=10; -- IT WILL THROW THE ERROR(DML OPERATIONS ARE NOT ALLOWED)

INSERT INTO CV4 VALUES(60); ---- IT WILL THROW THE ERROR(DML OPERATIONS ARE NOT ALLOWED)

CREATE OR REPLACE VIEW V1

SELECT DEPTNO,SUM(SAL) SUMSAL,AVG(SAL)AVGSAL,MAX(SAL) MAXSAL,MIN(SAL)MINSAL

FROM EMP

WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='RESEARCH')

GROUP BY DEPTNO;

select * from v1;

FORCE VIEW:

***********

-->It is used to define a view without base table.

-->Generally views are created based on tables but force views are created without base tables.

SYNTAX:

*******

CREATE FORCE VIEW <VIEW NAME> AS SELECT * FROM <TN>;

EX:

***

SELECT * FROM EMP_BT; --TABLE OR VIEW DOESNOT EXIST

--CREATE FORCE VIEW

CREATE FORCE VIEW EMP_FV AS SELECT * FROM EMP_BT;

ERROR:Warning: View created with compilation errors.

SELECT * FROM EMP_FV;

DESC EMP_FV;

ERROR:ORA-24372: invalid object for describe

-->To activate a force view then we should create a table with name as EMP_BT.

CREATE TABLE EMP_BT AS SELECT * FROM EMP;

SELECT * FROM EMP_FV;

MATERIALIZED VIEW:

******************

-->It is one of the view which is having the own structure.

-->It doesnot allow the DML operations on mviews.

-->It is used to store the historical data

-->We can define a view on table which is having the primary key.

-->Views doesn't store any data where as materialized views are storing data.

-->When we drop a base table then materialized view can be accesible.

SYNTAX:

*******

CREATE MATERIALIZED VIEW <VIEW NAME> AS SELECT * FROM <TABLENAME>;

EX:

***

DROP TABLE XX_BASE;

CREATE TABLE XX_BASE

(

SNO NUMBER PRIMARY KEY,

SNAME VARCHAR2(20)

);

INSERT INTO XX_BASE VALUES (1,'A');

INSERT INTO XX_BASE VALUES (2,'B');

INSERT INTO XX_BASE VALUES (3,'C')

INSERT INTO XX_BASE VALUES (4,'D');

COMMIT;

SELECT * FROM XX_BASE;

CREATE OR REPLACE VIEW V1

SELECT *

FROM XX_BASE;

CREATE MATERIALIZED VIEW MV1

SELECT *

FROM XX_BASE;

--table

SELECT ROWID ROW_ID,SNO,SNAME

FROM XX_BASE;

OUTPUT:

*******

ROW_ID SNO SNAME

AABqppABfAAAwDmAAA 1 A

AABqppABfAAAwDmAAB 2 B

AABqppABfAAAwDmAAC 3 C

AABqppABfAAAwDmAAD 4 D

--view

SELECT ROWID ROW_ID,SNO,SNAME

FROM V1;

OUTPUT:

*******

ROW_ID SNO SNAME

AABqppABfAAAwDmAAA 1 A

AABqppABfAAAwDmAAB 2 B

AABqppABfAAAwDmAAC 3 C

AABqppABfAAAwDmAAD 4 D

-->Here view rowid's are same as base table rowid's that's why view does not store data.

--materialized view

SELECT ROWID ROW_ID,SNO,SNAME

FROM MV1;

OUTPUT:

********

ROW_ID SNO SNAME

AABqprABfAAAwD8AAA 1 A

AABqprABfAAAwD8AAB 2 B

AABqprABfAAAwD8AAC 3 C

AABqprABfAAAwD8AAD 4 D

-->Here materialized view rowid's are different from base table rowid's that's why materialized view store own data

EX:

***

CREATE TABLE STU200

(

SNO NUMBER PRIMARY KEY,

SNAME VARCHAR2(20)

);

INSERT INTO STU200 VALUES (100,'MOHAN');

INSERT INTO STU200 VALUES (101,'MADAN');

INSERT INTO STU200 VALUES (102,'VINOD');

COMMIT;

SELECT * FROM STU200;

CREATE VIEW V10 AS SELECT * FROM STU200;

--View created.

CREATE MATERIALIZED VIEW STU200_MV AS SELECT * FROM STU200;

--Materialized view created.

INSERT INTO STU200_MV VALUES(100,'MOHAN');

DELETE FROM STU200_MV;

UPDATE STU200_MV SET SNO=10 WHERE SNO=100;

UPDATE STU200 SET SNAME=LOWER (SNAME);

-->we have updated data in base tabe the data will be reflected in view but materialized view does not update.

INSERT INTO STU200 VALUES (103,'A');

UPDATE STU200

SET SNAME='AAA'

WHERE SNO=103;

DELETE FROM STU200 WHERE SNO=103;

-->If we want perform DML operations on materialized view we can use the follwing syntax.

ON DEMAND / ON COMMIT:

**********************

-->In oracle we are refreshing materialized view in two ways.

ON DEMAND:

**********

-->It is a default refreshing method.

-->In this method we are refreshing materialized view by using "DBMS_MVIEW" procedure.

SYNTAX:

*******

EXECUTE DBMS_MVIEW.REFRESH ('MVIEW NAME');

EX:

****

EXEC DBMS MVIEW.REFRESH('STU200_MV');

ON COMMIT:

**********

-->We can refresh a materalized view with out using "DBMS_MVIEW" but using "ON COMMIT" method.

SYNTAX:

*********

CREATE MATERIALIZED VIEW <VIEW NAME>

REFRESH ON COMMIT

AS SELECT * FROM <TN>;

EX:

****

CREATE TABLE STU300

(

SNO NUMBER PRIMARY KEY,

SNAME VARCHAR2(20)

);

INSERT INTO STU300 VALUES (100,'MOHAN');

INSERT INTO STU300 VALUES (101,'MADAN');

INSERT INTO STU300 VALUES (102,'VINOD');

COMMIT;

SELECT * FROM STU300;

CREATE MATERIALIZED VIEW STU300_MV

REFRESH ON COMMIT

AS SELECT * FROM STU300;

SELECT * FROM STU300_MV;

--Add new record into table

INSERT INTO STU300 VALUES(103,'A');

select * from stu300;

--new record will not reflected into materialized view.

select * from stu300_mv;

commit;

--After apply the commit then only we will be able to see the new record.

select * from stu300_mv;

NOTE:

*****

-->To view all views details in oracle database then we use the following table.

"USER_VIEWS".

DESC USER_VIEWS

SELECT VIEW_NAME FROM USER_VIEWS;

HOW TO DROP A VIEW:

********************

-->DROP VIEW <VIEWNAME>;

EX:

DROP VIEW CV;

NOTE:

*****

-->if we want to view materialized views Then we are using the following data dictionary is “user_mviews”

EX:

***

DESC USER_MVIEWS;

SELECT MVIEW_NAME FROM USER_MVIEWS;

-->SYNTAX TO DROP MATERIALIZED VIEW:

DROP MATERIALIZED VIEW <MVIEW NAME>;

DROP MATERIALIZED VIEW MV1;

Materialized view dropped.

DIFFERENCES BETWEEN VIEW AND MATERIALIZED VIEW:

***********************************************

VIEW MATERIALIZED VIEW

******* *******************************

1)View does not store any Data. 1)Materialized view store Data.

2)When we dropping base Table then view cannot be accessible 2)When we dropping base Table then materialized View can be accessible.

3)We can perform DML operations on view 3)We cannot perform DML Operations on materialized view

4)Structure it should be same for table and view. 4)It's having own structure.It will not depends in table.

NALAM Oracle Apps Technical

Monday, 4 March 2024

VIEWS:

No comments:

Post a Comment