DROP [PUBLIC] FUNCTION (Delete function, delete public function)
Function
DROP FUNCTION deletes a function.
All users can delete these functions (public functions) without having to modify the routine identifier with the authorization identifier.
(1) DROP FUNCTION (Delete function)
Privileges
- Owner of a function
The owner of a function can delete that function.
- Users with the DBA privilege
These users can delete functions that are owned by other users.
Format
DROP FUNCTION [authorization-identifier.]routine-identifier
([data-type [,data-type]...])
[WITH PROGRAM]
Operands
- [authorization-identifier.]routine-identifier
- authorization-identifier
- Specifies the authorization identifier of the owner of the function that is to be deleted.
- routine-identifier
- Specifies the name of the function that is to be deleted.
Specifies the data type that was specified in the parameter for the function that is to be deleted. If the data type specified in a parameter of the function to be deleted is fixed-length or variable-length character data type and the character set specification is specified, specify the character set specification here.
When deleting a function, this option is specified to nullify an SQL object for which any of the following functions, procedures, and triggers are in affect:
- SQL objects for which functions, procedures, and triggers are in effect that use the function being deleted
- SQL objects for which functions, procedures, and triggers are in effect that use a function that has the same owner, routine identifier, and number of parameters as the function being deleted
If WITH PROGRAM is omitted, and if there is an SQL object for which any of the following functions, procedures, and triggers are in effect, that function cannot be deleted:
- SQL objects for which functions, procedures, and triggers are in effect that use the function being deleted
- SQL objects for which functions, procedures, and triggers are in effect that use a function that has the same owner, routine identifier, and number of parameters as the function being deleted
Common rules
- DROP FUNCTION cannot be executed from within a Java procedure if the deletion results in invalidation of the SQL object being executed.
- A function that is defined in an abstract data type cannot be deleted.
- If there are multiple functions with the same name, specify the data type parameters so that the functions to be deleted can be identified uniquely.
- If there are view tables (including public views) that use a specified function, the function cannot be deleted.
- Of the functions invalidated by the WITH PROGRAM specification, if a function that meets either of the following conditions is used in the view definition, the function cannot be deleted:
- Abstract data type is used in the data type of an argument.
- Abstract data type is used in the data type of a return value.
Notes
- DROP FUNCTION 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; however, if the trigger was using a function having the same owner, routine identifier, and number of parameters as the function that was deleted, you can re-create the trigger SQL object by executing either ALTER TRIGGER or ALTER ROUTINE:
- Redefine the function, and re-create the trigger SQL object by executing either ALTER TRIGGER or ALTER ROUTINE.
- Delete the nullified trigger by using DROP TRIGGER, and then redefine the trigger by using CREATE TRIGGER so that the deleted functions 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.
- If the function that was being used in the trigger action conditions was deleted by specifying WITH PROGRAM, an error occurs, not only during the execution of the SQL object associated with the nullified trigger, but also during the preprocessing of the SQL statement that induces the trigger.
- To access a view table that uses a function invalidated by specifying WITH PROGRAM, you must execute ALTER ROUTINE to re-create the SQL object of the function.
(2) DROP PUBLIC FUNCTION (Delete public function)
Privileges
- Owner of a public function
- The owner of a (defined) public function can delete that function.
- Users with the DBA privilege
- These users can delete public functions that are owned by other users.
Format
DROP PUBLIC FUNCTION routine-identifier([data-type [,data-type]...])
[WITH PROGRAM] |
Operands
For descriptions of operands other than PUBLIC, routine-identifier, and WITH PROGRAM, see (1) DROP FUNCTION (Delete function) under DROP [PUBLIC] FUNCTION (Delete function, delete public function) in this chapter.
Specifies that a public function is to be deleted.
Specifies the name of the public function that is to be deleted.
Specifies the following valid SQL objects of a function, procedure, or trigger when deleting a public function:
- A valid SQL object of a function, procedure, or trigger that uses the public function being deleted
- A valid SQL object of a function, procedure, or trigger that uses a public function with the same number of routine identifiers and parameters as the public function being deleted
If WITH PROGRAM is omitted and there exists one of the following SQL objects of a function, procedure, or trigger, that function cannot be deleted:
- A valid SQL object of a function, procedure, or trigger that uses the public function being deleted
- A valid SQL object of a function, procedure, or trigger that uses a function with the same number of routine identifiers and parameters as the public function being deleted
Common rules
- If the SQL object being executed is invalidated, DROP PUBLIC FUNCTION cannot be executed from within a Java procedure.
- For details about other common rules, see (1) DROP FUNCTION (Delete function) under DROP [PUBLIC] FUNCTION (Delete function, delete public function) in this chapter.
Notes
- DROP PUBLIC FUNCTION cannot be specified from an X/Open-compliant UAP running under OLTP.
- For other notes, see (1) DROP FUNCTION (Delete function) under DROP [PUBLIC] FUNCTION (Delete function, delete public function) in this chapter.