«Back to Home

Oracle Jump Start
Topics

How To Create, Update and Drop Views In Oracle

VIEW

Description

A view is the representation of a SQL statement, which is stored in the memory, so that it can be reused easily.
A view is also called a virtual table, stored query and the Window.
  • View is used to restrict an access to the data of a table.
  • View is used to simplify the query.
  • View is used to increase the data independence.
  • It is not possible to modify the structure of a table, using view.
  • All views are stored in view.
These are the advanced view of synonyms.

Types of view
  • Simple view
  • Complex view
  • Force view
  • Vertical view
  • Horizontal view
  • Functional view
  • Partition view
  • Materialized view
  • Inline view
Create simple view

Simple view is used to define a view on a single table.

Syntax
  1. Create view view_name  
  2. AS  
  3. Select * from Table_name;  
Example
  1. Create View Employee  
  2. AS  
  3. Select * from Employee;  
1
  1. Select * from Employees;  
2

Complex View

Complex View is used to define a view on the multiple tables.

Syntax
  1. Create view view_name  
  2. AS  
  3. Select * from Emp, Dept  
  4. Where Emp.Dept_no = Dept.Dept_no;  
Example
  1. Create view Employees  
  2. AS  
  3. Select * from Emp, Dept  
  4. Where Emp.Dept_no = Dept.Dept_no;  
Force View

Force View is used to define a view without a base table.

Syntax
  1. Create Force view view_name  
  2. AS  
  3. Select * from Non Existing table;  
Example
  1. Create Force View Emp4  
  2. AS  
  3. Select * from Employee;   
3
 
4

Vertical View

Vertical view is used to define a view on the specific columns in a table.

Syntax
  1. Create view view_name  
  2. AS   
  3. Select Col1, col2..coln  
  4. From Table_name;  
Example
  1. Create view Emp5  
  2. AS  
  3. select Emp_id, Emp_name, Salary  
  4. from Employee;  
5

Horizontal View

Horizontal view is used to define a view on the specific column in the table.

Syntax
  1. Create view view_name  
  2. AS  
  3. select * from Table_name  
  4. Where  
  5. Dept_no=10;  
Example
  1. Create view Emp6  
  2. AS  
  3. Select * from Employee  
  4. Where Dept_no= 104;  
6

Functional View

Functional view is used to define a view function on the table.

Syntax
  1. Create view view_name(col1, col2)  
  2. AS  
  3. Select Fun1, Fun2  
  4. from Employee;  
Example
  1. create view Emp7(Emp_name, Salary)  
  2. AS  
  3. Select Min(Salary), Max(Salary)  
  4. From Employee;  
7

Partition View

Partition view is used to define a view on the compound query.

Syntax
  1. Create view view_name  
  2. AS  
  3. Query1 Union Query2;  
Example
  1. Create view Emp10  
  2. AS  
  3. Select * from Emp6  
  4. Union  
  5. Select * from Emp6;  
8

Materialized View

Materialized view is used to define a view, which is having the own structure.
it is used to store the historical data.

Syntax
  1. Create materialized view view_name  
  2. AS  
  3. Select * from Table_name;  
Example
  1. Create materialized view Emp13  
  2. AS  
  3. Select * from Employee;  
9

Inline view

Inline view works like a query.

Syntax
  1. Select * from(select * from Table_name);  
Example
  1. Select * from(Select * from employee)  
  2. where Emp_id<=7001;  
10

Update view

You can modify the view without dropping in Oracle.

Syntax
  1. Create or replace view view_name  
  2. AS  
  3. Select Columns  
  4. From Table  
  5. where conditions; 
Example
  1. Create or replace view Emp13  
  2. AS  
  3. Select Emp_ID, Emp_name  
  4. from Employee  
  5. where Employee.Emp_name= ‘King’;  
11

Drop View

In this view, you want to drop a view, which is called drop view.

Syntax
  1. Drop view view_name;  

Example

  1. Drop view Emp7;  
12

Summary

Thus, we learnt, a view is the representation of a SQL statement, which is stored in the memory, so that it can be reused easily. A view is also called the virtual table, stored query and the Window. We learnt, how to use view with the examples.