我们如何改变 MySQL 存储过程？

如果我们对过程具有 ALTER ROUTINE 权限，那么在 ALTER PROCEDURE 语句的帮助下，可以改变 MySQL 存储过程。为了证明这一点，我们使用一个名为“delete_studentinfo”的存储过程示例，它具有如下创建语句：

mysql> SHOW CREATE PROCEDURE Delete_studentinfo\G
*************************** 1. row ***************************
           Procedure: Delete_studentinfo
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_
ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_studentinfo`( IN p_id INT)
BEGIN
DELETE FROM student_info
WHERE ID=p_id;
END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

在上述结果集中，显然存储过程“delete_studentinfo”中没有注释。现在，在 ALTER PROCEDURE 语句的帮助下，我们可以添加注释，如下所示：

mysql> ALTER PROCEDURE Delete_studentinfo
   -> COMMENT 'deleting the record'//
Query OK, 0 rows affected (0.03 sec)

现在，借助以下查询的结果集，可以确认已在存储过程中添加注释：

mysql> SHOW CREATE PROCEDURE Delete_studentinfo\G
*************************** 1. row ***************************
           Procedure: Delete_studentinfo
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_
ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_studen
tinfo`( IN p_id INT)
   COMMENT 'deleting the record'
BEGIN
DELETE FROM student_info
WHERE ID=p_id;
END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

更新于：22-6 月-2020

719 次查看

启动你的 职业

完成课程即可获得认证

开始
广告