ALTER PROCEDURE is used 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 }
}
Parameters
Parameter |
Description |
|---|---|
| proc_name | The name of the stored procedure to modify. You can use database_name.proc_name to specify the database. |
| characteristic | The characteristic of the stored procedure to modify, which can include the following: - COMMENT 'string': the comment of the stored procedure- LANGUAGE SQL: the language (SQL only, which can be ignored)- CONTAINS SQL \| NO SQL \| READS SQL DATA \| MODIFIES SQL DATA: the data access characteristics of the stored procedure- SQL SECURITY { DEFINER \| INVOKER }: the execution permission mode |
The ALTER PROCEDURE statement cannot be used to change the parameters or body of a stored procedure. If you need to make such changes, you must use DROP PROCEDURE and CREATE PROCEDURE to drop and recreate the procedure.
You must have the ALTER PROCEDURE privilege on the procedure. By default, this privilege is automatically granted to the procedure creator.
The following table describes the characteristics of routine data usage:
CONTAINS SQLindicates that the routine does not contain statements that read or write data. This is the default value. For example,SET@x=1orDO RELEASE_LOCK('abc')will be executed but will not read or write data.NO SQLindicates that the routine does not contain SQL statements.READS SQL DATAindicates that the routine contains statements that read data (such asSELECT) but does not contain statements that write data.MODIFIES SQL DATAindicates that the routine contains statements that may write data (such asINSERTorDELETE).
SQL SECURITY can be DEFINER or INVOKER, indicating whether the routine is executed with the privileges of the specified user (who must have access to the database associated with the routine) or the caller. The default value is DEFINER. The user who calls the routine must have the EXECUTE privilege on the routine.
Examples
Modify the characteristics of a stored procedure.
obclient> DELIMITER //
obclient> CREATE PROCEDURE proc_name() BEGIN SELECT 1; END //
obclient> DELIMITER ;
obclient> ALTER PROCEDURE proc_name LANGUAGE SQL READS SQL DATA SQL SECURITY INVOKER COMMENT 'demo procedure';
Use SHOW CREATE PROCEDURE to view the modified stored procedure definition:
obclient> SHOW CREATE PROCEDURE proc_name;
The query result is as follows:
+-----------+-------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+-------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| proc_name | STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER | CREATE DEFINER = `root`@`%` PROCEDURE `proc_name`()
READS SQL DATA
SQL SECURITY INVOKER
COMMENT 'demo procedure'
BEGIN SELECT 1; END | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+-----------+-------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.008 sec)