DROP PROCEDURE (Delete procedure)
Function
DROP PROCEDURE deletes a procedure.
Privileges
- Owner of the procedure
This user can delete his or her own procedures.
- Users with the DBA privilege
These users can delete procedures belonging to other users.
Format
DROP PROCEDURE [authorization-identifier.] routine-identifier
[WITH PROGRAM]
Operands
- [authorization-identifier.] routine-identifier
- authorization-identifier
- Specifies the authorization identifier of the user who owns the procedure being deleted.
- When this operand is omitted, the authorization identifier of the user who is executing the command is assumed.
- routine-identifier
- Specifies the routine name of the procedure being deleted.
When deleting a procedure, this option is specified if there is an SQL object for which functions, procedures, and triggers are in effect that use the procedure.
If WITH PROGRAM is omitted, and if there is an SQL object associated with functions, procedures, and triggers that use the function, the function cannot be deleted.
Common rules
- If there is an SQL routine, or a routine or a trigger in the abstract data type that calls a specified procedure, the procedure is not deleted.
- Procedures defined in an abstract data type cannot be deleted.
- In the following cases, DROP PROCEDURE cannot be executed from within a Java procedure:
- The SQL object being executed will be invalidated or deleted.
- The Java procedure being executed will be deleted.
Notes
- The DROP PROCEDURE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any rows associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table are deleted.
- Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to recreate the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
- To execute the SQL object associated with the trigger that was nullified by specifying WITH PROGRAM, you need to perform either of the following operations:
- Redefine the procedure, and execute either ALTER TRIGGER or ALTER ROUTINE to recreate the SQL object.
- Delete the nullified trigger by using DROP TRIGGER, and then redefine the trigger by using CREATE TRIGGER so that the deleted procedures are not reused. If triggers satisfying all of the following conditions exist, use DROP TRIGGER to delete them all, and redefine the triggers using CREATE TRIGGER in the order in which they were defined so that there is no change in the sequence of execution of trigger actions:
Conditions:
The defined trigger is later than the nullified trigger.
The nullified trigger is the same as the defined table.
The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification).
The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.
- The identifier specified in the trigger action procedure cannot be specified in routine-identifier. To delete the trigger, you need to execute DROP TRIGGER.