View and Base Tables (Dropping of View)

What is a View?

A view is a logical table based on one or more tables or another view.View can be thought as a virtual table which takes the output of a query and stores it.

Syntax:

create [or replace] [force|noforce] view

<view_name> [column_alias_names_separated_commas]

as <subquery>[with [check option] [constraint constraint_name] [with read only]];

A View can be based on a table or another view.  Dropping of a view has no effects on the base-table. The child view, based on a parent view will become invalid if the parent view is dropped. The view becomes invalid if the base table is altered, renamed or dropped (Although invalid the definition of view still remains in the data dictionary)

For Example:

SQL> create view PARENT_VIEW_EMP as select * from EMP;

View created.

SQL> create view CHILD_VIEW_EMP as select * from PARENT_VIEW_EMP;

View created.

SQL> SELECT EMPNO, JOB, MGR, HIREDATE FROM EMP WHERE ENAME='SMITH';

     EMPNO JOB        MGR    HIREDATE

  ---------- --------- ---------- ---------

      7369 CLERK     7902    17-DEC-80

SQL> SELECT EMPNO, JOB, MGR, HIREDATE FROM PARENT_VIEW_EMP where ENAME='SMITH';

     EMPNO JOB        MGR    HIREDATE

  ---------- --------- ---------- ---------

      7369 CLERK     7902    17-DEC-80

SQL> SELECT EMPNO, JOB, MGR, HIREDATE FROM CHILD_VIEW_EMP where ENAME='SMITH';

     EMPNO JOB        MGR    HIREDATE

  ---------- --------- ---------- ---------

      7369 CLERK     7902    17-DEC-80

SQL> DROP VIEW PARENT_VIEW_EMP;

View dropped.

SQL> select * from CHILD_VIEW_EMP where ENAME='SMITH';              *

ERROR at line 1:

ORA-04063: view "SCOTT.CHILD_VIEW_EMP" has errors