Shulou(Shulou.com)06/01 Report--

How to write the command to modify the stored procedure in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

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

In the actual development process, business requirements change occurs from time to time, so it is inevitable to modify the stored procedures in MySQL.

The stored procedure is modified by ALTER PROCEDURE statement in MySQL. This section explains in detail how to modify stored procedures.

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

ALTER PROCEDURE stored procedure name [feature.]

The feature specifies the properties of the stored procedure, and possible values are:

CONTAINS SQL means that the subroutine contains SQL statements, but not statements that read or write data.

NO SQL indicates that the subroutine does not contain a SQL statement.

READS SQL DATA represents a statement in a subroutine that contains read data.

MODIFIES SQL DATA indicates that the subroutine contains statements to write data.

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

DEFINER says that only the definer can execute it.

INVOKER indicates that the caller can execute.

COMMENT 'string' represents comment information.

Example 1

The following is to modify the definition of the stored procedure showstuscore, change the read and write permissions to MODIFIES SQL DATA, and indicate 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 result is as follows:

Mysql > SHOW CREATE PROCEDURE showstuscore\ gateway * 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 INVOKERBEGINSELECT id,name,score FROM studentinfo ENDcharacter_set_client: gbkcollation_connection: gbk_chinese_ci Database Collation: latin1_swedish_ci1 row in set (0.00 sec)

The results show that the stored procedure was modified successfully. As you can see from the run results, the permission to access the data has become MODIFIES SQL DATA, and the security type has become INVOKE.

Tip: the 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 and then create a new stored procedure with the same name; if you want to change the name of the stored procedure, you can first delete the original stored procedure and then create a new stored procedure with a different name.

Thank you for reading! After reading the above, do you have a general idea of how to write commands to modify stored procedures in mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.