DROP [PUBLIC] PROCEDURE (Delete procedure, delete public procedure)
Function
DROP PROCEDURE deletes a procedure.
All users can delete these procedures (public procedures) without having to modify the routine identifier with the authorization identifier.
(1) DROP PROCEDURE (Delete 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 re-create 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 re-create 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.
(2) DROP PUBLIC PROCEDURE (Delete public procedure)
Privileges
- Owner of the procedure
- The owner of a (defined) public procedure can delete that procedure.
- Users with the DBA privilege
- These users can delete public procedures that are owned by other users.
Format
DROP PUBLIC PROCEDURE routine-identifier
[WITH PROGRAM] |
Operands
For descriptions of operands other than PUBLIC and routine-identifier, see (1) DROP PROCEDURE (Delete procedure) under DROP [PUBLIC] PROCEDURE (Delete procedure, delete public procedure) in this chapter.
Specifies that a public procedure is to be deleted.
Specifies the routine name of the public procedure to be deleted.
Common rules
- In the following cases, DROP PUBLIC 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.
- For details about other common rules, see (1) DROP PROCEDURE (Delete procedure) under DROP [PUBLIC] PROCEDURE (Delete procedure, delete public procedure) in this chapter.
Notes
- DROP PUBLIC PROCEDURE cannot be specified from an X/Open-compliant UAP running under OLTP.
- For other notes, see (1) DROP PROCEDURE (Delete procedure) under DROP [PUBLIC] PROCEDURE (Delete procedure, delete public procedure) in this chapter.