![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3XsIqTjNrA-378m5AWv59oK3-2tZ07-6LfUp_O1A-Y2E91d9qtAtDSY4yH6ZR-EYqWiG3kOYKXUcYTJe5Bj-czHY7h2qsnlI21bTm_zzRIFLRLO4Ikp3vNrX6CgRTvwyABzwN/s400/mysql.jpg)
I needed to delete multiple tables with name like 'TMP_%' the following procedure do this job in an effective way !
DROP PROCEDURE IF EXISTS curdemo;
CREATE PROCEDURE curdemo()
BEGIN
DECLARE name_val VARCHAR(255);
DECLARE no_more_rows boolean;
DECLARE num_rows INT;
DECLARE friends_cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_name like 'TMP_%';
-- Declare 'handlers' for exceptions
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
-- 'open' the cursor and capture the number of rows returned
-- (the 'select' gets invoked when the cursor is 'opened')
OPEN friends_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH friends_cur
INTO name_val;
IF no_more_rows THEN
CLOSE friends_cur;
LEAVE the_loop;
END IF;
SET @d=concat('DROP TABLE IF EXISTS ',name_val);
PREPARE prst1 FROM @d;
EXECUTE prst1;
END LOOP the_loop;
END;
CALL curdemo();
DELIMITER $$
DROP PROCEDURE IF EXISTS curdemo;
CREATE PROCEDURE curdemo()
BEGIN
DECLARE name_val VARCHAR(255);
DECLARE no_more_rows boolean;
DECLARE num_rows INT;
DECLARE friends_cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_name like 'TMP_%';
-- Declare 'handlers' for exceptions
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
-- 'open' the cursor and capture the number of rows returned
-- (the 'select' gets invoked when the cursor is 'opened')
OPEN friends_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH friends_cur
INTO name_val;
IF no_more_rows THEN
CLOSE friends_cur;
LEAVE the_loop;
END IF;
SET @d=concat('DROP TABLE IF EXISTS ',name_val);
PREPARE prst1 FROM @d;
EXECUTE prst1;
END LOOP the_loop;
END;
CALL curdemo();
0 commenti:
Post a Comment