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.
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 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&amp;nbsp; on emp_mast.PERSONID=emp_ent.PERSONID where emp_mast.STATE='UTTARAKHAND' and emp_ent.basic_sal&amp;gt;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.
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 View_emp;
This query statement is used to drop the existing view in Oracle pl/sql.
Also read :
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 &amp;lt;u&amp;gt;from&amp;lt;/u&amp;gt; employee_master where city='DEHRDAUN'
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.