ORACLE VIEW

In Oracle, view is a virtual table that does not physically exist.View does not store any data and It is stored in Oracle data dictionary.

A view is created by a query joining one or multiple tables.

Syntax :

CREATE  VIEW <view_name> as

SELECT  columns

FROM    tables

WHERE   conditions;

Example

let's take an example to create a view.In this example,we are creating two table emp and dept.

EMP table:

   

CREATE TABLE  EMP  

   (    EMP_ID NUMBER,   

EMP_NAME VARCHAR2(4000),   

DEPTNO NUMBER 

   ) ; 

/

DEPT table:

CREATE TABLE  DEPT   

   (   DEPTNO NUMBER ,   

       DNAME VARCHAR2(100) 

   )  ;

/  

Execute the following query to create a view name EMP_DET.

Create View Query:

CREATE VIEW EMP_DET AS  

SELECT EMP.EMP_ID ,EMP.EMP_NAME,DEPT.DNAME

FROM   EMP,DEPT

WHERE  EMP.DEPTNO=DEPT.DEPTNO;

Output:

View created.

You can now check the Oracle VIEW by this query:

SELECT * FROM EMP_DET; 

Oracle Update VIEW

In Oracle, the CREATE OR REPLACE VIEW statement is used to modify the definition of an Oracle VIEW without dropping it.

Syntax:

CREATE OR REPLACE VIEW view_name AS  

  SELECT columns  

  FROM   table  

  WHERE  conditions;  

Oracle DROP VIEW

The DROP VIEW statement is used to remove or delete the VIEW completely.

Syntax:

DROP VIEW view_name;  

Example:

DROP VIEW EMP_DET;