DROP Statements

Syntax

DROP FUNCTION function-name

Identifies the particular function to be dropped, and is valid only if there is exactly one function instance with the function-name in the schema. The identified function can have any number of parameters defined for it. If no function with the indicated name in the named or implied schema, an error (SQLSTATE 42704) will occur. An error will also occur if there is more than one specific instance of the function in the named or implied schema.

DROP INDEX removes the specified index.

Syntax

DROP INDEX index-Name

Examples

DROP INDEX OrigIndex
 
DROP INDEX DestIndex

Statement Dependency System

If there is an open cursor on the table from which the index is dropped, the DROP INDEX statement generates an error and does not drop the index. Otherwise, statements that depend on the index's table are invalidated.

Syntax

DROP PROCEDURE procedure-name

Identifies the particular procedure to be dropped, and is valid only if there is exactly one procedure instance with the procedure-name in the schema. The identified procedure can have any number of parameters defined for it. If no procedure with the indicated name in the named or implied schema, an error (SQLSTATE 42704) will occur. An error will also occur if there is more than one specific instance of the procedure in the named or implied schema.

The DROP SCHEMA statement drops a schema. The target schema must be empty for the drop to succeed.

Neither the APP schema (the default user schema) nor the SYS schema can be dropped.

Syntax

DROP SCHEMA schema-Name RESTRICT

The RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database. The RESTRICT keyword is required

Examples

-- Drop the SAMP schema
-- The SAMP schema may only be deleted from the database
-- if no objects are defined in the SAMP schema. 
 
DROP SCHEMA SAMP RESTRICT

DROP TABLE removes the specified table.

Syntax

DROP TABLE table-Name

Statement Dependency System

Triggers, constraints (primary, unique, check and references from the table being dropped) and indexes on the table are silently dropped. The existence of an open cursor that references table being dropped cause the DROP TABLE statement to generate an error, and the table is not dropped.

Dropping a table invalidates statements that depend on the table. (Invalidating a statement causes it to be recompiled upon the next execution. See Interaction with the Dependency System.)

DROP TRIGGER removes the specified trigger.

Syntax

DROP TRIGGER TriggerName

Examples

DROP TRIGGER TRIG1

Statement Dependency System

When a table is dropped, all triggers on that table are automatically dropped. (You don't have to drop a table's triggers before dropping the table.)

Drops the specified view.

Syntax

DROP VIEW view-Name

Examples

DROP VIEW AnIdentifier

Statement Dependency System

Any statements referencing the view are invalidated on a DROP VIEW statement. DROP VIEW is disallowed if there are any views or open cursors dependent on the view. The view must be dropped before any objects that it is dependent on can be dropped.