Example 1: Changing the comment on a view

    A comment can be added or changed on a view using the COMMENT IS
    clause as shown in this example.

    SQL> show view (comment) current_job
    Information for table CURRENT_JOB

    SQL> alter view CURRENT_JOB
    cont>   comment is 'Select the most recent job for the employee';
    SQL> show view (comment) current_job
    Information for table CURRENT_JOB

    Comment on table CURRENT_JOB:
    Select the most recent job for the employee

    SQL>

    Example 2: Changing the column's results of a view definition

    The following view uses a derived table and join to collect
    the count of employees in each department. The view is used in
    several reporting programs used by the department and company
    managers.

    SQL> create view DEPARTMENTS_SUMMARY
    cont> as
    cont> select department_code, d.department_name,
    cont>        d.manager_id, jh.employee_count
    cont> from departments d inner join
    cont>      (select department_code, count (*)
    cont>         from job_history
    cont>         where job_end is null
    cont>         group by department_code)
    cont>           as jh (department_code, employee_count)
    cont>      using (department_code);
    SQL>
    SQL> show view DEPARTMENTS_SUMMARY;
    Information for table DEPARTMENTS_SUMMARY

    Columns for view DEPARTMENTS_SUMMARY:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    DEPARTMENT_CODE                 CHAR(4)
    DEPARTMENT_NAME                 CHAR(30)
     Missing Value: None
    MANAGER_ID                      CHAR(5)
     Missing Value:
    EMPLOYEE_COUNT                  INTEGER
     Source:
    select department_code, d.department_name,
           d.manager_id, jh.employee_count
    from departments d inner join
         (select department_code, count (*)
            from job_history
            where job_end is null
            group by department_code) as jh (department_code, employee_count)
         using (department_code)

    SQL>

    The database administrator decides to create a column in the
    DEPARTMENTS table to hold the count of employees (rather than
    using a query to gather the total) and to maintain the value
    through triggers on EMPLOYEES and JOB_HISTORY (not shown here).
    Now the view can be simplified without resorting to a DROP
    VIEW and CREATE VIEW. The ALTER VIEW statement preserves the
    dependencies on the view from other views, triggers, and routines
    and so minimizes the work required to implement such a change.

    SQL> alter table DEPARTMENTS
    cont>     add column EMPLOYEE_COUNT integer;
    SQL>
    SQL> alter view DEPARTMENTS_SUMMARY
    cont> as
    cont> select department_code, d.department_name,
    cont>        d.manager_id, d.employee_count
    cont> from departments d;
    SQL>
    SQL> show view DEPARTMENTS_SUMMARY;
    Information for table DEPARTMENTS_SUMMARY

    Columns for view DEPARTMENTS_SUMMARY:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    DEPARTMENT_CODE                 CHAR(4)
     Missing Value: None
    DEPARTMENT_NAME                 CHAR(30)
     Missing Value: None
    MANAGER_ID                      CHAR(5)
     Missing Value:
    EMPLOYEE_COUNT                  INTEGER
     Source:
    select department_code, d.department_name,
           d.manager_id, d.employee_count
    from departments d

    SQL>

    Example 3: Changing the WITH CHECK OPTION constraint of a view
    definition

    This example shows that a WITH CHECK OPTION constraint restricts
    the inserted data to the view's WHERE clause. Once the constraint
    is removed, the INSERT is no longer constrained.

    SQL> create view TOLIVER_EMPLOYEE
    cont> as select * from EMPLOYEES where employee_id = '00164'
    cont> with check option;
    SQL> insert into TOLIVER_EMPLOYEE (employee_id) value ('00000');
    %RDB-E-INTEG_FAIL, violation of constraint TOLIVER_EMPLOYEE_CHECKOPT1 caused operation to fail
    -RDB-F-ON_DB, on database DISK1:[DATABASES]MF_PERSONNEL.RDB;1
    SQL>
    SQL> alter view TOLIVER_EMPLOYEE with no check option;
    SQL>
    SQL> insert into TOLIVER_EMPLOYEE (employee_id) value ('00000');
    1 row inserted
    SQL>