56,687
社区成员
发帖
与我相关
我的任务
分享
DELIMITER //
-- 这个地方:因为怕有SP重名现象,所以我create sp之前.怎么检测sp是否存在,如果存在则select 'output message ' 并且不执行下面的create sp语句.
CREATE PROCEDURE XX()
BEGIN
SELECT 'AAA' AS message;
END//
DELIMITER ;
mysql> show procedure status like 'XX';
+------+------+-----------+----------------+---------------------+--------------
-------+---------------+---------+----------------------+----------------------+
--------------------+
| Db | Name | Type | Definer | Modified | Created
| Security_type | Comment | character_set_client | collation_connection |
Database Collation |
+------+------+-----------+----------------+---------------------+--------------
-------+---------------+---------+----------------------+----------------------+
--------------------+
| csdn | XX | PROCEDURE | root@localhost | 2010-06-24 10:49:20 | 2010-06-24 10
:49:20 | DEFINER | | latin1 | latin1_swedish_ci |
latin1_swedish_ci |
+------+------+-----------+----------------+---------------------+--------------
-------+---------------+---------+----------------------+----------------------+
--------------------+
1 row in set (0.04 sec)
mysql>
CREATE PROCEDURE B()
BEGIN
IF EXISTS (SELECT 1 FROM mysql.proc WHERE name ='XX') THEN
SELECT 'already exists';
ELSE
PREPARE stmt FROM 'CREATE PROCEDURE XX() SELECT 1 AS message';
EXECUTE stmt;
END IF;
END ;