MySQL tutorial 105 MySQL modifying stored procedures

In the actual development process, business requirements are modified from time to time, so they are modified MySQL Stored procedures in are inevitable.

Modify stored procedures through ALTER PROCEDURE statement in MySQL. The method of modifying stored procedures will be explained in detail here.

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

ALTER PROCEDURE Stored procedure name [ features ... ]

Feature specifies the characteristics of the stored procedure. Possible values are:

  • CONTAINS SQL means that the subroutine CONTAINS SQL statements, but does not contain statements to read or write data.
  • NO SQL means that the subroutine does not contain SQL statements.
  • Read SQL data indicates that the subroutine contains statements that read data.
  • Modifications SQL data indicates that the subroutine contains statements to write data.
  • SQL security {determiner | invoker} indicates who has permission to execute.
  • DEFINER means that only the DEFINER can execute it.
  • INVOKER indicates that the caller can execute.
  • COMMENT 'string' indicates comment information.

More specific syntax can be viewed through the mysql command 'help alter procedure', and the help command can also be used to view other commands

Example 1

Next, modify the definition of the stored procedure ShowStuAge, change the read-write permission to modify SQL data, and indicate that the caller can execute. The code is as follows:

mysql> alter procedure ShowStuAge modifies sql data sql security invoker.
Query OK, 0 rows affected (0.18 sec)

Run the modified code and view the following information:

mysql> show create procedure ShowStuAge\G.
*************************** 1. row ***************************
           Procedure: ShowStuAge
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `ShowStuAge`()
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
begin
select age from tb_student_info;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

The results show that the stored procedure is modified successfully. It can be seen from the running results that the permission to access data has changed to modifications SQL data, and the security type has also changed to INVOKE.

Tips:ALTER PROCEDURE Statement is used to modify some characteristics of a stored procedure. If you want to modify the contents 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 a stored procedure, you can delete the original stored procedure first, and then create a new stored procedure with a different name.

Tags: MySQL

Posted by slapdashgrim on Sat, 21 May 2022 09:09:13 +0300