CODEWITHSUNDEEP
mysqlsqlstored-procedures
Richard Knop
Richard Knop

Reputation: 83697

Cannot create a MySQL procedure with LOOP

SOLVED:

This works:

DROP PROCEDURE IF EXISTS drop_all_tables_from_specified_database;

DELIMITER $$
CREATE PROCEDURE drop_all_tables_from_specified_database(IN dbname CHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblname CHAR(50);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = dbname COLLATE utf8_general_ci;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET foreign_key_checks = 0;

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO tblname;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @database_name = dbname;
        SET @table_name = tblname;
        SET @sql_text = CONCAT('DROP TABLE ', @database_name, '.' , @table_name, ';');      

        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur1;

    SET foreign_key_checks = 1;
END$$
DELIMITER ;

CALL drop_all_tables_from_specified_database('test');

DROP PROCEDURE drop_all_tables_from_specified_database;

I am trying to create a procedure that deletes all tables inside a database. I have saved this code inside a file tear-down.sql:

CREATE PROCEDURE drop_all_tables_from_specified_database()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_to_drop VARCHAR(255);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema='test';

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO table_to_drop;
        IF done:
            LEAVE read_loop;
        END IF;
        DROP TABLE `test`.table_to_drop;
    END LOOP;

    CLOSE cur1;
END;

When I then do inside MySQL command line tool:

mysql> source tear-down.sql

I get a massive error:

enter image description here

I don't understand the error I get and I cannot figure out what's wrong. Anybody can spot an error in this procedure?

EDIT:

My current code:

DROP PROCEDURE IF EXISTS drop_all_tables_from_specified_database;

DELIMITER $$
CREATE PROCEDURE drop_all_tables_from_specified_database(IN dbname CHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblname CHAR(50);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = dbname;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO tblname;
        IF done THEN
            LEAVE read_loop;
        END IF;
        DROP TABLE dbname.tblname;
    END LOOP;

    CLOSE cur1;
END$$
DELIMITER ;

CALL drop_all_tables_from_specified_database('test');

DROP PROCEDURE drop_all_tables_from_specified_database;

Now it runs ok but it doesn't delete the tables. I get this response:

enter image description here

Upvotes: 1

Views: 1994

Answers (2)

vimdude
vimdude

Reputation: 4585

It should be IF done THEN not IF done: . Also done variable does not get updated so you might get an infinite loop in there. Take a look at fetch cursor into.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270607

It looks like you will need to set an alternative DELIMITER:

DELIMITER $$
CREATE PROCEDURE drop_all_tables_from_specified_database()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_to_drop VARCHAR(255);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema='test';

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO table_to_drop;
        /* This might need to be 'IF done <> FALSE THEN' */
        IF done THEN
            LEAVE read_loop;
        END IF;
        DROP TABLE `test`.table_to_drop;
    END LOOP;

    CLOSE cur1;
END$$
DELIMITER ;

Upvotes: 1

Related Questions