修改库中全表指定字段值的存储过程

参数：字段名称，旧值，新值

DROP PROCEDURE IF EXISTS update_all_table_field;
DELIMITER ?
CREATE PROCEDURE update_all_table_field(IN column_name VARCHAR(50),
                                        IN old_column_value BIGINT,
                                        IN new_colum_value BIGINT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE t_name VARCHAR(100);
    DECLARE rs CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN rs;
        _loop: LOOP
            FETCH NEXT FROM rs INTO t_name;
            IF done THEN
                LEAVE _loop;
            END IF;

            BEGIN
                DECLARE inner_done INT DEFAULT FALSE;
                DECLARE c_exist INT DEFAULT FALSE;
                DECLARE c_name VARCHAR(100);
                DECLARE inner_rs CURSOR FOR SELECT COLUMNS.COLUMN_NAME FROM information_schema.COLUMNS WHERE COLUMNS.TABLE_NAME = t_name AND COLUMNS.TABLE_SCHEMA = DATABASE();
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done = TRUE;

                OPEN inner_rs;
                    inner_loop: LOOP
                        FETCH NEXT FROM inner_rs INTO c_name;
                        IF inner_done THEN
                            LEAVE inner_loop;
                        END IF;
                        insert into tlog select c_name, t_name;
                        
                        IF column_name = c_name THEN
                            SET c_exist = TRUE;
                            LEAVE inner_loop;
                        END IF;
                    END LOOP inner_loop;
                CLOSE inner_rs;

                IF c_exist THEN
                    SET @sql_text = concat('UPDATE ', t_name, ' SET ', column_name, ' = ', new_colum_value, ' WHERE ', column_name, ' = ', old_column_value);
                    PREPARE stmt FROM @sql_text;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                END IF;
            END;
        END LOOP _loop;
    CLOSE rs;
END?
DELIMITER ;