Dropping a routine in a local or remote database
After you create an SPL routine, you cannot change the body of the routine. Instead, you need to drop the routine and re-create it. Before you drop the routine, however, make sure that you have a copy of its text somewhere outside the database.
DROP PROCEDURE raise_prices;
DROP FUNCTION read_address;
DROP FUNCTION calculate(INT, INT, INT);
-- this is a signature
DROP SPECIFIC FUNCTION calc1;
-- this is a specific name
DROP ROUTINE calculate;
DROP SPECIFIC ROUTINE calc1;
Before you drop an SPL routine stored on a remote database server, be aware of the following restriction. You can drop an SPL routine with a fully qualified routine name in the form database@dbservername:owner.routinename only if the routine name alone, without its arguments, is enough to identify the routine.
Restrictions on data types in distributed operations
SPL routines that access tables in databases of non-local database servers, or that are invoked as UDRs of a database of another database server, can only have non-opaque built-in data types as their arguments or returned values.
- The remote database has the same server as the current database.
- The UDT arguments are explicitly cast to a built-in data type.
- The DISTINCT types are based on built-in types and are explicitly cast to built-in types.
- The SPL routine and all the casts are defined in all participating databases.