Views

Views can be changed or dropped using the ALTER VIEW and DROP VIEW statements correspondingly.

ALTER VIEW statement

Oracle 9i, DB2 UDB, and MS SQL Server all have an ALTER VIEW statement, but its syntax and functionality is quite different.

Oracle 9i

The two main uses of ALTER VIEW in Oracle are either to recompile a view invalidated because of underlying table(s) DDL changes or to add, modify, or drop view constraints. The syntax is

ALTER VIEW [<qualifier>.]<view_name> {[COMPILE] |  [ADD CONSTRAINT <view_constraint_definition>] |  [MODIFY CONSTRAINT <constraint_name>] |  [DROP {CONSTRAINT <constraint_name> | PRIMARY KEY}] };

For example, you might want to recompile view V_FAX_NUMBER because its underlying table (PHONE) has been modified:

ALTER VIEW v_fax_number COMPILE;

Tip 

You don't actually have to recompile Oracle views invalidated when their underlying tables have been changed. The invalidated view will be automatically recompiled when referred by SQL code; the main reason to recompile views manually is for performance improvement.

The next example adds the UNIQUE constraint to the view V_CONTACT_LIST:

ALTER VIEW v_contact_list ADD CONSTRAINT vpk_contactlist UNIQUE (name, phone_number) DISABLE;

To drop this constraint, issue the following statements:

ALTER VIEW v_contact_list DROP CONSTRAINT vpk_contactlist;

Note 

Oracle's ALTER VIEW statement cannot alter the view definition; if you need to add or drop view columns, change data types or the underlying query, etc., you have to use CREATE OR REPLACE VIEW statement (or, alternatively, use DROP VIEW and CREATE VIEW statements).

DB2 UDB 8.1

DB2 allows you to use the ALTER VIEW statement to alter a reference type column to add a scope. The functionality is an advanced topic and is not covered in this book.

MS SQL Server 2000

The ALTER VIEW in MS SQL Server enables you to change view columns, underlying select statements, and other view options without affecting dependent database objects or changing permissions (which would be different had you used DROP VIEW and CREATE VIEW statements). The syntax is practically the same as for CREATE VIEW:

ALTER VIEW [<qualifier>.]<view_name> [(<column_name>,...)] [WITH {ENCRYPTION|SCHEMABINDING|VIEW_METADATA}] AS <select_statement> [WITH CHECK OPTION]

Cross-References 

See Chapter 4 examples for CREATE VIEW.

Note 

SQL99 does not specify any rules for altering views.

DROP VIEW statement

DROP VIEW statement removes view definition from the system catalog. The dependent objects become invalid.

Oracle 9i

The syntax is

DROP VIEW view_name [CASCADE CONSTRAINTS];

For example

DROP VIEW v_phone_number;

The optional CASCADE CONSTRAINTS clause removes all referential integrity constraints that refer to primary and unique keys in the view to be dropped. See Oracle's DROP TABLE statement described previously.

DB2 UDB 8.1

DB2 uses syntax as follows:

DROP VIEW [<qualifier>.]<view_name>

MS SQL Server

You can drop multiple views using the MS SQL Server DROP VIEW statement:

DROP VIEW <view_name> [,...]

The following statement drops three views (V_PHONE_NUMBER, V_CUSTOMER_TOTALS, and V_WILE_BESS_ORDERS) at once:

1> DROP VIEW v_phone_number, v_customer_totals, v_wile_bess_orders 2> Go 1>