Friday 17 April 2015
What is View?
Some of the advantages of using views:
Dropping A View :
Syntax :
Example :
- 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 :
- With Join
- With Group By
On Complex Views :
- 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.
Some of the advantages of using views:
- Reduce the complexity of SQL statements
- Share only specific rows in a table with other users
- Hide the NAME and OWNER of the base table
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;
