CODEWITHSUNDEEP
mysqlstored-procedures
Ηλίας
Ηλίας

Reputation: 2640

Deleting temporary tables with a stored procedure

I have been trying to make a stored procedure which autodeletes temporary tables.

CREATE PROCEDURE DeleteTemp()
BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;


DECLARE tmptablename VARCHAR(100);
DECLARE tmpTables CURSOR FOR
    SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='myDB' AND TABLE_NAME LIKE 'tmp%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
OPEN tmpTables;
SELECT FOUND_ROWS() into num_rows;
the_loop: LOOP
    FETCH tmpTables INTO tmptablename;
    DROP TABLE tmptablename;
    IF no_more_rows THEN
        CLOSE tmpTables;
        LEAVE the_loop;
    END IF;
END LOOP the_loop;
END

However, all I get is:

Query : call DeleteTemp Error Code : 1051 Unknown table 'tmptablename'

How can I pass the variable tmptablename properly into the "DROP TABLE" command?

Fixed using prepared statements.

CREATE
    PROCEDURE DeleteTemp()
    BEGIN
    DECLARE no_more_rows BOOLEAN;
    DECLARE loop_cntr INT DEFAULT 0;
    DECLARE num_rows INT DEFAULT 0;


    DECLARE tmptablename VARCHAR(100);
    DECLARE tmpTables CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA='MY_SCHEMA'
            AND TABLE_NAME LIKE 'tmp%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
    OPEN tmpTables;
    SELECT FOUND_ROWS() INTO num_rows;

    the_loop: LOOP
        FETCH tmpTables INTO tmptablename;
        IF no_more_rows THEN
            CLOSE tmpTables;
            LEAVE the_loop;
        ELSE
            SET @a:=CONCAT('DROP TABLE ',tmptablename);
            PREPARE stmt1 FROM @a;
            EXECUTE stmt1;
            DEALLOCATE PREPARE stmt1;
        END IF;
    END LOOP the_loop;
    END

Upvotes: 1

Views: 1378

Answers (2)

charliefortune
charliefortune

Reputation: 3200

This may not be what you are after, but are you aware that your temporary tables are only visible to that connection that created them, and will be dropped automatically once that connection is closed?

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

Have you had a look at the PREPARE and EXECUTE commands. This is like executing DYNAMIC SQL

SQL Syntax for Prepared Statements

The following SQL statements can be used in prepared statements: ALTER TABLE, CALL, COMMIT, CREATE INDEX, CREATE TABLE, DELETE, DO, DROP INDEX, DROP TABLE, INSERT, RENAME TABLE, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE are also supported as of MySQL 5.0.23.

Upvotes: 2

Related Questions