ALTER ROUTINE

Alter an existing routine.

 

where routine-type is:

 

Usage

Interactive, Embedded, Module, ODBC, JDBC.

Description

With the ALTER ROUTINE statement it is possible to change attributes or the procedural sql statement used in the routine body for a routine. A routine can either be a function or procedure.

The routine to be altered is either identified by the specific name for the routine or the name of the routine. If the form alter routine is used there can only be one function or procedure having that name.

The specific name for a routine is either given or generated when the routine is created and is unique within a schema. As the name is unique it is not necessary to specify the type for the routine but the generic qualifier ROUTINE can be used. However, if an explicit type is given in the alter statement, the routine identified by the specific name must match the routine type.

The routine-name and the specific-name should follow the normal rules for naming database objects, see Naming Objects.

If no schema name is given, it is assumed that the routine is defined in a schema with the same name as the current ident.

If only the routine attributes are altered, it is not necessary to provide a parameter list. If a parameter list is given, the names and the data types must match the routine identified by the specific name.

The parameter-name should follow the normal rules for naming SQL identifiers, see SQL Identifiers.

The routine attributes that can be altered are: DETERMINISTIC, ACCESS MODE, IS NULL CALL and SPECIFIC. If a routine attribute is not present in the ALTER ROUTINE statement, the attribute will keep the value it had prior to the statement.

The meaning of the routine attributes are the same as when creating a routine (see CREATE FUNCTION and CREATE PROCEDURE.)

It is possible to change the data type in the returns clause, with some restrictions (see below).

Restrictions

It is only the creator of the schema in which the routine is defined, that is allowed to alter the routine.

It is not possible to alter the data type of a parameter.

If the routine body is altered, a complete parameter list with names must also be given.

It is possible to change the data type in the returns clause if there are no other objects referencing this routine or if the new data types are comparable with the old data type (see Comparisons for more details.)

If the altered routine body contains references to objects on which the current ident does not have the applicable privilege with grant option and there are other objects referencing the routine being altered, the alter operation is not allowed.

In addition, all restrictions for CREATE PROCEDURE and CREATE FUNCTION apply.

Notes

Any privilege on the routine granted to other idents are retained.

It is possible to alter a routine that is part of a module.

Examples

Alter the specific name for a routine:

CREATE PROCEDURE INSERT_AUTHOR

    (IN FIRST_NAME NCHAR VARYING(30),IN LAST_NAME NCHAR VARYING(30))

   SPECIFIC INS_AUTH

BEGIN

   ...

END

 

ALTER SPECIFIC ROUTINE INS_AUTH SPECIFIC INSERT_AUTHOR

 

Example of altering the routine body:

ALTER ROUTINE INSERT_AUTHOR

    (IN FIRST_NAME NCHAR VARYING(30),IN LAST_NAME NCHAR VARYING(30))

BEGIN

    ...

END

Standard Compliance