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.

Learn Oracle PL/SQL : Create & Use View

What is View?

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

Create View

Create View View_emp as

select LASTNAME,FIRSTNAME from employee_master where city='DEHRDUN'

IN THIS View_emp is a view name & after as we can give the query for the view

select * from View_emp;

Now you can see view by select 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 table, it will show the data which has state uttarakhand & basic salary greater than 10000.

Now you understood how to create a view in Oracle pl/sql .If at any point of time you have another requirement and you have to change the view than you have to alter it. Lets 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 in Oracle pl/sql.

 

Also read :

Learn Oracle PL/SQL : Create & Use Tables
Learn Oracle PL/SQL : Variables
Top 10 ORACLE PL/SQL Interview Questions

 

Type of view

  1. Simple view
  2. Materialized view

Simple View

In 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, It can be created CREATE MATERIALIZED VIEW statement in Oracle pl/sql .  You can used views to increase speed of queries  in very large database.

 

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 & it is updated periodically.

 

So now we understood the whole concept of view in Oracle pl/sql, I have given this best of my knowledge. I hope you liked my content. If you can give me some suggestion or response please comment . In our next blog I will teach you about FUNCTIONS.

  •  
    11
    Shares
  • 11
  •  
  •  
  •  
  •  
  •  

About the Author

Prashant Goswami

I am the blog writer who will teach how to love programming. My experience in programming is about 6 years. And i am the lover of programming , so i will make you people.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: