Creating And Using Views in A Database
Views are logical subset of data based on one or more tables.
A view is used primarily to hide the table structure from unauthorised users. The table based on which a VIEW is created is called as base table. Since the view behaves like a table, it is also called as virtual table.
Depending on the base table a view can be of two types:
- Simple View: Based on one base table.
- Complex View: Based on multiple base table.
Creating a View
Syntax: CREATE [OR REPLACE][FORCE|NO FORCE] VIEW <VIEW_NAME> AS <SELECT STATEMENT> [WITH CHECK OPTION] [WITH READ ONLY];
Example: CREATE OR REPLACE VIEW VEMP AS SELECT * FROM EMP;
Above example will create a simple view based on a single table. Through the above view any record can be inserted, updated, deleted, selected from EMP base table but no structure change is allowed.
Operations on a View
Shown below are some operations on the view we just created:
SELECT * FROM VEMP; INSERT INTO VEMP(NAME, EMPNO) VALUES('ROB',4); ALTER TABLE VEMP ADD(DEPTNO NUMBER(4)); DESC VEMP;
The statement marked in red tried to add a new column, this is an invalid statement since structure change is not allowed.
DESC command will also work on a VIEW, since a view is considered a virtual table. The desc command displays the structure of the VIEW.
View based on a condition
In the view created below any record can be inserted through the view to the base table but only those records can be updated, deleted and selected, the condition based on which the view has been created i.e. only the records where job is manager.
CREATE OR REPLACE VIEW VEMP AS SELECT * FROM EMP WHERE JOB='MANAGER'
A view can also be based on another view:
CREATE OR REPLACE VIEW FVIEW AS SELECT * FROM AVIEW;
With Check Option
With Check Option is used to restrict insertion of records into the base table, when a view is created based on a conditions.
Example: CREATE OR REPLACE VIEW VEMP AS SELECT * FROM EMP WHERE JOB = ‘SALESMAN’ WITH CHECK OPTION;
In the above view created only those records can be inserted, updated, deleted or selected where job is salesman.
INSERT INTO VEMP VALUES (3, ‘CC’, ‘MANAGER’, 4500); INSERT INTO VEMP VALUES (3, ‘CC’, ‘SALESMAN’, 4500);
The statement in red above is invalid, since only SALESMAN records can be inserted.
With Read Only
With Read Only is used to create read only view, no update, insert or delete is allowed in such views, only data selection is allowed.
Example: CREATE OR REPLACE VIEW REMP AS SELECT * FROM SYSEMP8 WITH READ ONLY;
Normally a view can only be created if its base table exists. Force option is used to create a view where the base table does not exist. This is particularly useful in cases when a lot of tables and views are needed to be created and we do not want to bother about the sequence.
Example: CREATE FORCE VIEW FVIEW AS SELECT * FROM EMP;
Note: The VIEW without a base table can be functional only when the base table is available.
A complex view contains a join statement, as it is based on multiple tables.
Example: CREATE VIEW COMPVIEW AS SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
Complex views are mostly meant for reporting purposes. It is not advisable to perform DML operations on these kind of views.
Deleting a View
We can use the DROP statement to delete a view.
Syntax: DROP <VIEW_NAME>;