Oracle:Views
Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Views
Views In Oracle
A view is like a window through which you can view or change information tables. A view allows you to see as a single table parts of one (or more than one) table(s).
Creating a view
To define a view of department 10 of the EMP table:
CREATE VIEW EMP10 AS SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = 10;
Once EMP10 is created, it can be used as any other table. Any updates are automatically applied to the 'base' tables, though there are restrictions covering updates to views, which will be covered in later database modules.
Column names take on the names from the base table; however these can be changed by including the new column names after the View-Name, for example, to create a view for department 20, renaming all the columns:
CREATE VIEW EMP20 (EMPNUMBER, EMPLOYEE, POSITION) AS SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = 20;
Views can be based on more than one table.
To create a view joining EMP to EMPPROJ:
CREATE VIEW PROJSTAFF ( EMPLOYEE, PROJECT, PROJECT_NUMBER) AS SELECT ENAME, PNAME, EMPPROJ.PROJNO FROM EMP, EMPPROJ WHERE EMP.PROJNO = EMPPROJ.PROJNO;
Since views occupy no storage space they are sometimes called "virtual tables".
There are two main reasons to use views:
- Security
- Because they provide a means of hiding sensitive data items form particular user groups
- Convenience
- If enquiries frequently involve joins on tables then it may be easier for the user to formulate the queries on a view based on these joins rather than having to specify the join conditions for each query
Deleting a view
A view can be deleted with the DROP command.
DROP VIEW viewname
For example:
DROP VIEW EMP10;
Updating a view definition
If you want to change a view's definition there is no equivalent of an ALTER TABLE ... command. Instead you either need to drop and recreate the view.
Alternatively you can use the CREATE OR REPLACE VIEW ... defintion.
For example, we want to include the salary details to EMP10:
CREATE OR REPLACE VIEW EMP10 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 10;
Using the view
The view can be queried as if it were a normal table, you do not need to retrieve all the columns or rows that are part of the view.
For example, to show all the data in emp10:
SELECT * FROM emp10;
Or just some of the data:
SELECT empno, ename FROM emp10 WHERE sal > 2000;
Using other data manipulation commands via the view, such as INSERT, UPDATE and DELETE can cause issues depending on how the view was setup and is the subject of the advanced database modules.
If you want to find further information on this, see these links on updateable views:
