一个存储过程 游标循环结果集

在查询表名使用变量时只能使用concat 拼接 哭哭哭。。。。

drop procedure if exists proc_tmp;
create procedure proc_tmp()
    BEGIN
    declare done int default 0;
    declare tablename varchar(255);

    declare idCur cursor for select table_name from information_schema.tables where table_schema='record' and table_name like 'user_%';
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 

    open idCur; 
    REPEAT
          fetch idCur INTO tablename; 
     if not done THEN
       SET @a = tablename;       
       SET @sql_texta =concat('SELECT max(unix_timestamp(time)) into @maxtime from ',@a);
       PREPARE stmta FROM @sql_texta;       
       EXECUTE stmta;
       DEALLOCATE PREPARE stmta;       

       SET @sql_textb =concat('SELECT * FROM ',@a,' where unix_timestamp(time) <= ',@maxtime,' and unix_timestamp(time) >= ',@maxtime,'- 5');
       PREPARE stmtb FROM @sql_textb;       
       EXECUTE stmtb;
       DEALLOCATE PREPARE stmtb;

      end if;
    until done end repeat;
    close idCur;
END;

call proc_tmp();

在使用mysql.exe 命令行执行时 直接 传递编写好的过程sql文件 会出错

cmd脚本如下

rem @echo off
mysql.exe -h 10.80.1.74 -u root -p < query.sql > result.txt
echo "!!!!!!!!!!!!Complete!!!!!!!!!!!!!!!!"
pause

使用 query.sql文件 执行时 需要做如此修改

delimiter //
create procedure proc_tmp()
....
END //