Learn Oracle PL/SQL - Create & Use View

Do you know the type of views ,how to create them and their uses. In this blog I will tell you about views , how you can use them practically. Before that I want to tell you that this is my third blog on Learn Oracle pl/sql if you have not read earlier blog on VARIABLES & tABLES kindly read them.

Do you know the types of views, how to create them, and their uses? In this blog I will tell you about views, and how you can use them practically. Before that I want to tell you that this is my third blog on learning Oracle pl/sql. If you have not read my earlier blog posts on Variables & Tables kindly read them first.

What is View?

View is a virtual table that does not exist physically but can be created by joining one or more tables.

Create View

Create View View_emp as select LASTNAME,FIRSTNAME from employee_master where city='DEHRDUN'

IN THIS View_emp is a view name and we can give the query for the view

select * from View_emp;

Now you can see view by selecting statement on view, it will show the data with given conditions in query.

Above view is for single table; let’s make it for more than one table.

Create View View_more as select LASTNAME,FIRSTNAME,city,basic_sal from employee_master emp_mast inner join employee_entitlement emp_ent on emp_mast.PERSONID=emp_ent.PERSONID where emp_mast.STATE='UTTARAKHAND' and emp_ent.basic_sal>10000

In this view we are joining two tables, it will show the data which has state Uttarakhand & basic salary greater than 10000.

Now you understood how to create a view. If at any point of time you have another requirement and you have to change the view than you have to alter it. Let's see how we can alter our view.

Alter View

Create or replace View View_emp as  select * from employee_master where city='DEHRADUN'and lastname = 'GOSWAMI'

For updating view we use Create or replace. For changing existing view View_emp I added one more where condition in my view.

Drop View

DROP VIEW View_emp;

This query statement is used to drop the existing view.

Type of view

1. Simple view
2. Materialized view

Simple View

In the below examples we have created a simple view which can be created using CREATE VIEW statement.

Create View View_emp as  select LASTNAME,FIRSTNAME <u>from</u> employee_master where city='DEHRDAUN'

Materialized view

These are the views which are generally used in warehouses. They can be created CREATE MATERIALIZED VIEW statement . You can use views to increase speed of queries in very large databases.

CREATE MATERIALIZED VIEW EMP_mv  ENABLE QUERY REWRITE AS  select LASTNAME,FIRSTNAME from employee_master WHERE STATE='UTTARAKHAND’

The difference between both views are a simple view is used like a query to pull the data from the underlying table. But Materialized View is a table on a disk that contains the result set of query and it is updated periodically.

So now we understood the whole concept of view. I hope you liked my content. If you can give me some suggestions or responses please comment . In my next blog I will teach you about FUNCTIONS.