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
(B)0[m[1m [m [1m [m [1m [m [1m [m [1m [m [1m [m [1m [m [1m [m
[1;4mALTER[m[1m [1;4mVIEW[m[1m <view-name>[m [1mqwqwqq> [1;4mAS[m[1m <select-expr> qqqqqqqqqqqqqqqqqwqwq> [m
[1mx[m [1mtqq>[m [1m<check-option-clause> qqqqqqqqqqqqu[m [1mx[m
[1m x[m [1mtqq>[m [1;4mCOMMENT[m[1m IS qwq> 'text-literal'qqwqu[m [1mx[m
[1mx[m [1mx[m [1mmqqqqqqqqq[m [1m/[m [1m<qqqqqqj[m [1mx[m [1mx[m
[1m [m [1m [m [1m [m [1m x[m [1mtqq> [1;4mRENAME[m[1m [1;4mTO[m[1m <new-view-name> qqqqqqqu x[m
[1m [m [1m [m [1m [m [1m x[m [1mmqq> [1;4mWITH[m[1m [1;4mNO[m[1m [1;4mCHECK[m[1m [1;4mOPTION[m[1m qqqqqqqqqqqqj[m [1mx[m
[1m [m [1m [m [1m [m [1mmqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqj[m
[1m [m [1m [m
[1m [m
[1m [m
[1m [m
[1m [m
[1m [m
(B)0[m[1mselect-expr = [m [1m [m
[1m [m [1m [m
[1mqwqwq> select-clause qqqqqqqqqqqqwqwqqqqqqk[m [1m [m
[1m x tq> ( select-expr ) qqqqqqqqqqqu x [m [1mx[m [1m [m [1m [m
[1m x mq> [1;4mTABLE[m[1m table-ref qqqqqqqqqqj x x [m [1m [m
[1m mqqqqqq select-merge-clause <qqqqqqqj [m [1m x[m
[1m lqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqj[m [1m [m
[1mmqwqqqqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqqqqqqqqqqwq>[m
[1m [m [1m mq> order-by-clause qj[m [1mmq> offset-clause qj [m [1mmq> limit-to-clause qj[m
(B)0[m[1mcheck-option-clause = [m
[1m [m
[1;4mWITH[m[1m [1;4mCHECK[m[1m [1;4mOPTION[m[1m qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>[m
[1m mq> [1;4mCONSTRAINT[m[1m <check-option-name> j [m
[1m [m
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>