Sometimes you may need to edit stored procedure or alter stored procedure in MySQL. Here’s how to edit stored procedure using MySQL ALTER PROCEDURE statement.
MySQL Alter Stored Procedure
Here are the steps to alter stored procedure in MySQL using ALTER PROCEDURE statement.
Let’s say you have the following stored procedure in MySQL.
mysql> DELIMITER //
CREATE PROCEDURE get_orders()
BEGIN
SELECT * FROM orders;
END //
DELIMITER ;
mysql> call get_orders;
+----+------------+--------+----------+
| id | order_date | amount | sequence |
+----+------------+--------+----------+
| 1 | 2020-08-01 | 250 | 1 |
| 2 | 2020-08-02 | 125 | 4 |
| 3 | 2020-08-03 | 300 | 9 |
+----+------------+--------+----------+
Bonus Read : MySQL List All Procedures
How to Edit Stored Procedure in MySQL Command Line
Although MySQL provides ALTER PROCEDURE statement, it does not allow you to change the body or parameters of stored procedure. It only allows you to change the characteristics of stored procedure
Here’s the syntax of ALTER PROCEDURE
ALTER PROCEDURE proc_name [characteristic ...]
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
Here’s an example to add a comment using ALTER PROCEDURE query.
ALTER PROCEDURE get_orders COMMENT 'test comment';
Bonus Read : MySQL Reset Root Password
However, if you want to change the query, body or parameters of stored procedure in MySQL command line, then you need to DROP PROCEDURE and then CREATE PROCEDURE with new definition. Here’s an example to update the query of get_orders stored procedure defined above.
mysql> drop procedure get_orders;
mysql> DELIMITER //
CREATE PROCEDURE get_orders()
BEGIN
SELECT order_date, amount FROM orders;
END //
DELIMITER ;
mysql> call get_orders;
+------------+--------+
| order_date | amount |
+------------+--------+
| 2020-08-01 | 250 |
| 2020-08-02 | 125 |
| 2020-08-03 | 300 |
+------------+--------+
Hopefully, now you can alter stored procedure in MySQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
