This statement allows the named view to be modified.
1 – Environment
You can use the ALTER VIEW statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in a SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ALTER VIEW <view-name> -+-+--> AS <select-expr> -----------------+-+->
| +--> <check-option-clause> ------------+ |
| +--> COMMENT IS -+-> 'text-literal'--+-+ |
| | +--------- / <------+ | |
| +--> RENAME TO <new-view-name> -------+ |
| +--> WITH NO CHECK OPTION ------------+ |
+------------------- <---------------------+
select-expr =
-+-+-> select-clause ------------+-+------+
| +-> ( select-expr ) -----------+ | |
| +-> TABLE table-ref ----------+ | |
+------ select-merge-clause <-------+ |
+------------------- <-------------------+
+-+--------------------+--+------------------+--+--------------------+->
+-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+
check-option-clause =
WITH CHECK OPTION --+----------------------------------+-->
+-> CONSTRAINT <check-option-name> +
3 – Arguments
3.1 – AS
Replaces the view select expression and the definitions of the
columns. The number of expressions in the select list must match
the original CREATE VIEW column list.
3.2 – COMMENT_IS
Replaces the comment currently defined for the view (if any).
The comment will be displayed by the SHOW VIEW statement in
Interactive SQL.
3.3 – CONSTRAINT
Specifies a name for the WITH CHECK OPTION constraint. If you
omit the name, SQL creates a name. However, Oracle Rdb recommends
that you always name constraints. If you supply a name for the
WITH CHECK OPTION constraint, the name must be unique in the
schema.
The name for the WITH CHECK OPTION constraint is used by the
INTEG_FAIL error message when an INSERT or UPDATE statement
violates the constraint.
3.4 – RENAME_TO
Renames the current view. The new view name must not exist as the
name of an existing view, table, sequence, or synonym.
3.5 – WITH_CHECK_OPTION
A constraint that places restrictions on update operations made
to a view. The check option clause ensures that any rows that are
inserted or updated in a view conform to the definition of the
view. Do not specify the WITH CHECK OPTION clause with views that
are read-only.
3.6 – WITH_NO_CHECK_OPTION
Removes any check option constraint currently defined for the
view.
4 – Examples
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>