Posted by : Akshay Patil Friday, 17 April 2015

What is View?
  • A view is a named and validated SQL query which is stored in the Oracle data dictionary. 
  • View does not have storage of its own (i.e. View does not hold any data) .
  • It is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables.
  • A view is a logical representation of the data that can be used just like a table in SELECT statement.
  • They represent the data of one of more tables and derives its data from the tables on which it is based.
  • DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based. These tables are called base tables. Views can be based on actual tables or another view also.
  • Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
Views are useful for security and information hiding, but can cause problems if nested too deeply. 
Some of the advantages of using views:
  1. Reduce the complexity of SQL statements
  2. Share only specific rows in a table with other users
  3. Hide the NAME and OWNER of the base table
Types of Views
  1. Simple View
  2. Complex View
  3. Materialized View
  4. Force View
Simple View ...
- Simple View is the view that is created based on a single  table
- Derives data from only one table.
- Contains no functions or groups of data.
- Can perform DML operations through the view.

Syntax :
CREATE [OR REPLACE] VIEW <view name>
AS
<select statement using single table>;
/*View that fetches data from single database table is called Simple View.*/

Dropping A View :
Syntax :
Drop View  View-name ;

Example :
        CREATE VIEW staff AS  SELECT employee_id, last_name, job_id, manager_id,department_id
FROM employees;
 Complex View ...
- Complex view is the view that is created on multiple tables.
- We can not perform DML operations directly on complex View.
- Derives data from many tables.
- Contains functions or groups of data.
Syntax :  
CREATE [OR REPLACE] VIEW <view name> AS  <select ,.., from table1,table2 .. Table n where ..>;
Examples :
- With Join
CREATE OR REPALCE VIEW na_emp_v
AS SELECT e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
FROM emp e,dept d WHERE e.deptno = d.deptno;

- With Group By
CREATE OR REPLACE VIEW na_emp_v
AS SELECT deptno, COUNT (*) total
FROM emp GROUP BY deptno;  
DML Operations …
On Simple Views :
CREATE TABLE na_dept (deptno NUMBER (10), dname VARCHAR2(10), loc VARCHAR2(10));
        CREATE VIEW na_dept_view AS SELECT *FROM na_dept;
INSERT INTO na_dept_view VALUES     (10,'ACCOUNTING','ENGLAND');
SELECT *FROM na_dept;
SELECT * FROM na_dept_view;

On Complex Views :
CREATE VIEW na_complex_v AS SELECT e.empno, e.ename, e.sal ,d.deptno,d.dname, d.loc  FROM emp e, dept d WHERE e.deptno = .deptno;
INSERT INTO na_complex_v VALUES (7625,'BLAKE', 12000,20,'TESTING','CHICAGO');
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view 

USING INSTEAD OF TRIGGERS:-
-Instead of triggers can be used only with views.
-Effective for views in which there are multiple tables involved.
-Let’s create INSERTED OF TRIGGER on ‘na_complex_v’. Inside trigger, we will write a plsql code to insert data into EMP and DEPT separately as our view is based on these two tables. 
BEGIN
INSERT INTO EMP (empno, ename, Sal) VALUES (:NEW.empno, NEW.ename,:NEW.sal);  
INSERT INTO DEPT (deptno,dname,loc) VALUES (:NEW.deptno, :NEW.dname, :NEW.LOC);
END;

INSERT INTO na_complex_v VALUES (7625,’BLAKE’, 12000,20,’TESTING’,’CHICAGO’);
SELECT *FROM dept;

SELECT * FROM EMP;
 
Materialized View…
-Merialized views are query results that have been stored or "materialized" in advance as schema objects. The FROM clause of the query can name tables, views, and materialized views. 
-Materialized View Replication
-They contain actual data and consume storage space.
-They can be refreshed when the data in their master tables changes.
Example :
CREATE MATERIALIZED VIEW sales_mv AS 
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales 
FROM times t, products p, sales s 
WHERE t.time_id = s.time_id 
AND p.prod_id = s.prod_id 
GROUP BY t.calendar_year, p.prod_id;

DROP TABLE sales;
SELECT * FROM sales_mv WHERE ROWNUM < 4; 

    CALENDAR_YEAR    PROD_ID    SUM_SALES
    ------------------------     ------------   ---------------- 
    1998                           13                 936197.53 
    1998                           26                 567533.83 
          
Built Options :
- BUILD IMMEDIATE: view is built at creation time
- BUILD DEFFERED: view is built at a later time
- ON PREBUILT TABLE: use an existing table as view source
Refresh Options : 
- COMPLETE 
- FAST
- FORCE 

Force View…
We can create views (i.e. view with errors) by using the FORCE option in the CREATE VIEW command:

CREATE [OR REPLACE] [FORCE] VIEW <view name> AS <select statement>;
When Force command is used in the view syntax then even if select statement is invalid VIEW gets created successfully.
Example :
CREATE  OR  REPLACE  FORCE  VIEW na_view AS SELECT * FROM dummy_table;

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Welcome to My Blog

Study Basics

Oracle APEX Oracle SQL Oracle PL/SQL

Popular Post

Blogger templates

Total Pageviews

Powered by Blogger.

Unordered List

Follow us on Facebook!

- Copyright © TechnicalBits -Robotic Notes- Powered by Blogger - Designed by Johanes Djogan -