Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


ALTER ROUTINE


Alter an existing routine.



where routine-type is:



Usage

Embedded/Interactive/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, procedure or method.

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, procedure or method 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, CREATE METHOD on page 234 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 procedure 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 procedure 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 procedure being altered, the alter operation is not allowed.

In addition, all restrictions for create routine also applies.

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

Standard
Compliance
Comments

Mimer SQL extension
The possibility to change the routine body of a routine is a Mimer SQL extension.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX