1CREATE TABLE t1 (id INT);
2DELIMITER $$;
3CREATE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment1' BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$
4DELIMITER ;$$
5CALL proc1(@cnt);
6SELECT @cnt;
7INSERT INTO t1 VALUES (1), (2), (3);
8CALL proc1(@cnt);
9SELECT @cnt;
10
11DELIMITER $$;
12--error ER_SP_ALREADY_EXISTS
13CREATE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment2'
14  BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$
15SELECT comment FROM mysql.proc WHERE name='proc1'$$
16
17CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) COMMENT 'comment3'
18  BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$
19SELECT comment FROM mysql.proc WHERE name='proc1'$$
20
21--error ER_WRONG_USAGE
22CREATE OR REPLACE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) COMMENT 'comment4'
23  BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$
24SELECT comment FROM mysql.proc WHERE name='proc1'$$
25
26CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment5'
27  BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$
28SELECT comment FROM mysql.proc WHERE name='proc1'$$
29
30DELIMITER ;$$
31DROP PROCEDURE proc1;
32DELIMITER $$;
33CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$
34DELIMITER ;$$
35INSERT INTO t1 VALUES (1), (2), (3);
36CALL proc1(@cnt);
37SELECT @cnt;
38
39DROP TABLE IF EXISTS t1;
40DROP PROCEDURE IF EXISTS proc1;
41DROP PROCEDURE IF EXISTS proc1;
42