Deletes the specified view definition. When the DROP VIEW
statement executes, SQL deletes the view definition from the
database. If you attach to the database using the PATHNAME
qualifier, SQL also deletes the view definition from the
repository.
1 – Environment
You can use the DROP VIEW statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mDROP[m[1m [1;4mVIEW[m[1m <view-name> qqwqwqqqqqqqqqqqqqqqwqwq>[m
[1m [m [1mx tq> [1;4mCASCADE[m[1m [m [1mqqqu x[m
[1m [m [1mx tq> [1;4mRESTRICT[m [1mqqu x[m
[1mx mq>[m [1;4mIF[m[1m [1;4mEXISTS[m [1mqqj[m [1mx[m
[1mmqqqqqqqqqq<qqqqqqqqj[m
3 – Arguments
3.1 – CASCADE
Specifies that you want SQL to delete all other view definitions
that refer to the named view and then delete that view
definition. This is known as a cascading delete. If you delete
a view referenced by a stored routine or trigger with a routine
or language-semantic dependency, SQL also marks the affected
routines and triggers as invalid.
3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
3.3 – RESTRICT
Specifies that you want SQL to delete only the named view
definition. If there are other views, triggers, or routines that
refer to the named view, the deletion fails. RESTRICT is the
default.
3.4 – view-name
Specifies the name of the view definition you want to delete.
4 – Examples
Example 1: Deleting a view definition
The following example deletes the view definition CURRENT_INFO:
SQL> DROP VIEW CURRENT_INFO;
SQL> COMMIT;
Example 2: Deleting a view with dependent views
This example shows that SQL will not automatically delete any
views that refer to the view named in the DROP VIEW statement.
You must use the CASCADE keyword to delete a view with dependent
views.
SQL> DROP VIEW CURRENT_JOB;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-VIEWINVIEW, view CURRENT_JOB is referenced by view CURRENT_INFO
-RDMS-F-VIEWNOTDEL, view CURRENT_JOB has not been deleted
SQL> DROP VIEW CURRENT_JOB CASCADE;
View CURRENT_INFO is also being dropped.
SQL> COMMIT;
Example 3: Adding new definitions to a database
When updating metadata definitions using a predefined SQL script
it sometimes required to remove objects that may not be present
in all databases being maintained. Adding a DROP VIEW, for
instance, will result in an error as shown here.
SQL> drop view CURRENT_INFO;
%SQL-F-RELNOTDEF, Table CURRENT_INFO is not defined in database or schema
SQL> create view CURRENT_INFO
cont> ...etc...
By using the IF EXISTS clause the error message is supressed and
makes for a less confusing execution of the maintance script.
SQL> drop view CURRENT_INFO if exists;
SQL> create view CURRENT_INFO
cont> ...etc...