高级篇——删除某个库某张表上的索引 的存储过程

第十章

DELIMITER // 
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200)) BEGIN DECLARE done INT DEFAULT 0; DECLARE ct INT DEFAULT 0; DECLARE _index VARCHAR(200) DEFAULT ''; DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ; 
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束 
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ; 
#若没有数据返回,程序继续,并将变量done设为2 
OPEN _cur; FETCH _cur INTO _index; WHILE _index<>'' DO SET @str = CONCAT("drop index " , _index , " on " , tablename ); PREPARE sql_str FROM @str ; EXECUTE sql_str; DEALLOCATE PREPARE sql_str; SET _index=''; FETCH _cur INTO _index; END WHILE; CLOSE _cur; END //
 DELIMITER ;



CALL proc_drop_index("dbname","tablename");