Restrictions

You cannot drop an SPL routine from within the same SPL routine.

You cannot use the DROP ROUTINE, DROP FUNCTION, or DROP PROCEDURE statement to drop a protected routine. For more information about protected routines, see the description of the sysprocedures system catalog table in the HCL OneDB™ Guide to SQL: Reference.

To use the DROP ROUTINE statement to unregister a UDR, the type of UDR cannot be ambiguous. The name of the UDR that you specify must refer to either a user-defined function or a user-defined procedure. If either of the following conditions exist, the database server returns an error:
  • The name (and parameters) that you specify apply to both a user-defined procedure and a user-defined function,
  • The specific name that you specify applies to both a user-defined procedure and a user-defined function.

If the routine name is not unique within the database, you must specify enough parameter_type information to disambiguate the name. If the database server cannot resolve an ambiguous routine name whose signature differs from that of another routine only in an unnamed ROW type parameter, an error is returned. (This error cannot be anticipated by the database server when the ambiguous routine is defined.)

If you use parameter data types to identify a UDR, they follow the UDR name, as in the following example: 
DROP ROUTINE compare(INT, INT);
If you use the specific name for the UDR, you must also include the keyword SPECIFIC, as in the following example: 
DROP SPECIFIC ROUTINE compare_point;

If you include the optional IF EXISTS keywords, the database server takes no action (rather than issues an error) if the database server cannot find in the current database any routine that matches what your DROP ROUTINE statement specifies.

Determining Whether a Routine Exists

Before you attempt to drop a user-defined routine, you can check for its existence in the database by querying the system catalog. In the following example, the SELECT statement retrieves from the sysprodures table any routines whose identifier is MyRoutine: 
SELECT * FROM sysprocedures WHERE procname = MyRoutine;
If this query returns a single row, then a UDR called MyRoutine is registered in the current database.

If this query returns no rows, you do not need to issue the DROP ROUTINE statement, but you might wish to verify that the WHERE clause specified the correct name, and that you are connected to the correct database.

If the query returns more than one row, then the routine name MyRoutine is overloaded in the current database, and you need to examine the attributes of the MyRoutine routines to determine which of them, if any, need to be unregistered by the DROP ROUTINE statement.