What is the command to modify stored procedures in mysql?

The command to modify the stored procedure in mysql is "ALTER PROCEDURE", the syntax format is "ALTER PROCEDURE stored procedure name [characteristic...]", the possible values ​​of the characteristic are CONTAINS SQL, NO SQL, INVOKER, DEFINER and so on.

(Recommended tutorial: mysql video tutorial)

In the actual development process, when business requirements are modified has occurred, so modifying stored procedures in MySQL is inevitable.

In MySQL, the stored procedure is modified through the ALTER PROCEDURE statement. This section will explain in detail how to modify the stored procedure.

The syntax format for modifying stored procedures in MySQL is as follows:

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

Characteristics specify the characteristics of the stored procedure. Possible values ​​are:

• CONTAINS SQL means that the subroutine contains SQL statements, but does not contain statements for reading or writing data.

• NO SQL means the subroutine does not contain SQL statements.

• READS SQL DATA indicates that the subroutine contains statements for reading data.

• MODIFIES SQL DATA indicates that the subroutine contains statements for writing data.

• SQL SECURITY { DEFINER |INVOKER } Specifies who has permission to execute.

• DEFINER means that only the definer can execute it.

• INVOKER indicates that the caller can execute.

• COMMENT 'string' represents comment information.

Example 1

The following changes the definition of the stored procedure showstuscore, changes the read and write permissions to MODIFIES SQL DATA, and indicates that the caller can execute , the code is as follows:

mysql> ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;
Query OK, 0 rows affected (0.01 sec)

Execute the code and view the modified information. The running results are as follows:

mysql> SHOW CREATE PROCEDURE showstuscore \G
*************************** 1. row ***************************
           Procedure: showstuscore
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `showstuscore`()
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
SELECT id,name,score FROM studentinfo;
END
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

The results show that the stored procedure was modified successfully. From the running results, we can see that the permission to access the data has changed to MODIFIES SQL DATA, and the security type has also changed to INVOKE.

Tip: The ALTER PROCEDURE statement is used to modify certain characteristics of the stored procedure. If you want to modify the content of a stored procedure, you can delete the original stored procedure first, and then create a new stored procedure with the same name; if you want to modify the name of the stored procedure, you can delete the original stored procedure first, and then create a new stored procedure with a different name. process.

Related recommendations: php training

The above is the detailed content of What is the command to modify stored procedures in mysql?. For more information, please follow other related articles on the PHP Chinese website!