VIEWS IN PL/SQL
By soniacharan
A view is an object that contains no data of its own.
- It is a kind of table whose contents are taken from other tables through the execution of a query.
- As the values in the base tables change, the changes are automatically reflected in the views.
- Views are easy to use and implement as they are not tables. View definition is stored in data dictionary. Hence the changes are automatically reflected in the views.
Views are read-only (not modifiable) if the CREATE VIEW statement:
Contains SET and DISTINCT operators.
Contains a group function or computed columns or expressions.
Includes a GROUP BY or HAVING clause.
References to more than one table either through a join or a sub-query.
References to a non-updatable view.
Does not include NOT NULL column that does not have a DEFAULT clause.
Database objects based on one or more tables are called Views.
They allow us to create imaginary table that has no data.
View consists of sole SQL query that retrieves rows and columns.That Data is presented in a tabular format.
Views can provide Security making certain rows and columns only visible and read only.
Materialized Views are treated like tables:
- Most DML and query ccommands such as insert,delete,update and select can be performed.
- Partiton is possible.
- Compression is possible.
- They can be parallelized.
- Indexes can be created on them.
Uses for Materialized Views:
- Performing data summarization.
- Prejoining Tables.
- Replicating and Distributing data
Using Materialized views have an advantage as they are transparent to the users.The users always query the tables with detail data they usually dont query the materializes views directly.This results in hugh performance gains without the user having to do anything special just the query detail data.
There is a maintenance benefit of this feature for the user as well.
--Syntax
CREATE VIEW <viewname>
AS < select query>;
SQL> CREATE VIEW emp_vw
AS SELECT empno, ename, job FROM emp;
--Create view containing all the employees in dept 20
SQL> CREATE VIEW emp_vw1
AS SELECT empno, ename, job FROM emp
WHERE deptno = 20;
--To find out the base table on which a view is dependent
SQL > SELECT * FROM user_views;
LISTEN TO UR FAV MUSIC NOW!!!
 | Amazon Price: $49.99 List Price: $79.95 |
 | Amazon Price: $39.99 List Price: $59.99 |
 | Amazon Price: $79.00 |
 | Amazon Price: Too low to display List Price: $99.99 |
TEST FOR YOU GUYS...
IN ORDER TO ACCESS THE DATA IN MATERALIZED VIEWS, A USER OR APPLICATION MUST QUERY THE MATERIALIZED VIEWS DIRECTLY?
See results without votingComments
No comments yet.
