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:

http://www.w3resource.com/sql/update-views/sql-update-views.php

http://docs.oracle.com/database/121/SQLRF/statements_8004.htm#SQLRF01504

Next Step

Indexing Tables