Routines in Distributed Operation

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.

In general, use DROP PROCEDURE with an SPL procedure name and DROP FUNCTION with an SPL function name, as Figure 374 shows.

Figure 374.

DROP PROCEDURE raise_prices;
DROP FUNCTION read_address;

You can also use DROP PROCEDURE with a function name to drop an SPL function. However, it is recommended that you use DROP PROCEDURE only with procedure names and DROP FUNCTION only with function names.

However, if the database has other routines of the same name (overloaded routines), you cannot drop the SPL routine by its routine name alone. To drop a routine that has been overloaded, you must specify either its signature or its specific name. Figure 375 shows two ways that you might drop a routine that is overloaded.

Figure 375.

DROP FUNCTION calculate( a INT, b INT, c INT);
   -- this is a signature

DROP SPECIFIC FUNCTION calc1;
   -- this is a specific name

If you do not know the type of a routine (function or procedure), you can use the DROP ROUTINE statement to drop it. DROP ROUTINE works with either functions or procedures. DROP ROUTINE also has a SPECIFIC keyword, as Figure 376 shows.

Figure 376.

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. 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. If the tables or the UDR resides on another database of the same Dynamic Server instance, however, the arguments and returned values of routines written in SPL (or in external languages that Dynamic Server supports) can be the built-in opaque data types BLOB, BOOLEAN, CLOB, and LVARCHAR. They can also be UDTs or DISTINCT data types if the following conditions are true: