Friday 17 April 2015
Types of Views
- Simple View
- Complex View
- Materialized View
- 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_idComplex View ...
FROM employees;
- 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;
