ALTER PROCEDURE

You can use the ALTER PROCEDURE statement to change one or more characteristics of a stored procedure.

Syntax:

ALTER PROCEDURE proc_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

The ALTER PROCEDURE statement cannot change the parameters or body of a stored procedure. To make such changes, you must use the DROP PROCEDURE statement to drop the procedure and then use the CREATE PROCEDURE statement to recreate the procedure.

You must have the ALTER PROCEDURE privilege on the procedure. By default, OceanBase Database automatically grants the privilege to the creator of the procedure.

The following characteristics provide information about how the routine uses data:

• CONTAINS SQL indicates that the routine does not contain data read/write statements. This is the default value. For example, SET@x=1 or DO RELEASE_LOCK('abc') will be executed but it does not read or write data.

• NO SQL indicates that the routine does not contain SQL statements.

• READS SQL DATA indicates that the routine contains data read statements such as SELECT but does not contain data write statements.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data, such as INSERT or DELETE.

SQL SECURITY can be set to DEFINER or INVOKER to specify whether the routine is executed by using the privileges of the specified account that has the privileges to access the database associated with the routine, or by using the privileges of the invoker. The default value is DEFINER. To call a routine, you must have the EXECUTE privilege on the routine.

Example:

obclient> ALTER PROCEDURE proc_name LANGUAGE SQL READS SQL DATA  SQL SECURITY INVOKER COMMENT 'Example';
Query OK, 0 rows affected