Posted by : Akshay Patil Friday, 17 April 2015

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 REPLACEVIEW <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 -