The ALTER PROCEDURE statement is used to modify 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 |
Note |
|---|---|
| proc_name | The name of the stored procedure to be modified. You can use database_name.proc_name Specify the database. |
| characteristic | The characteristics of the stored procedure to be modified can include: - COMMENT 'string': comments on stored procedures- LANGUAGE SQL: Language to specify (only for SQL statements. This parameter can be omitted.)- CONTAINS SQL \| NO SQL \| READS SQL DATA \| MODIFIES SQL DATA: specifies the data access characteristics of the stored procedure.- SQL SECURITY { DEFINER \| INVOKER }: specifies the execution privilege mode. |
The ALTER PROCEDURE statement cannot be used to modify the parameters or body of a stored procedure. 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 this procedure. By default, this privilege is automatically granted to the procedure creator.
The following table describes the characteristics of data used by routines.
CONTAINS SQL: The sample program does not contain statements for reading or writing data. This is the default value. For example, statements such asSET@x=1orDO RELEASE_LOCK('abc')are executed but do not read from or write to any data.NO SQLindicates that the sample program does not contain SQL statements.The
READS SQL DATAtable example procedure contains statements for reading data (such asSELECT) but does not contain statements for writing data.The
MODIFIES SQL DATAclause example statements include those that may write data (such asINSERTorDELETE).
SQL SECURITY can be set to DEFINER or INVOKER to specify whether the routine executes with the privileges of the specified user (who must have access to the database associated with the routine) or with the privileges of the caller. The default value is DEFINER. The user calling the routine must have the EXECUTE privilege on that routine.
Examples
Modifies 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';
Run the SHOW CREATE PROCEDURE statement to view the definition of the modified stored procedure:
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)