写一个loop存储过程如何写
select * from 表1
inner join 表2
on 表1.id = 表2.oid
and 表1.addtime >= '2010-01-01 00:00:00'
and 表1.addtime< '2010-01-02 00:00:00'
into outfile.....
因为要拿一个月的表数据,用存储过程可以这样实现吗？
while（day <= 31）
select * from 表1
inner join 表2
on 表1.id = 表2.oid
and 表1.addtime >= '2010-01-' +变量day+ '00:00:00'
and 表1.addtime< '2010-01-'+变量day+' 00:00:00'
into outfile.....
day++
还是有别的方法？
[解决办法]
动态执行：
set ff=concat("select * from 表1
inner join 表2
on 表1.id = 表2.oid
and 表1.addtime >= '2010-01-'",变量day,"'00:00:00'
and 表1.addtime< '2010-01-'",变量day,"into outfile.....");
prepare ss from @sql;
execute ss;
[解决办法]
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dowhile`()
BEGIN
DECLARE V1 INT DEFAULT 1;
WHILE V1 < 31 DO
SET @sqlstr=concat("select * from table where table.timed>= '2010-04-",V1," 00:00:00' and
table.timed < '2010-04-",V1+1," 00:00:00'");
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
SET V1 = V1 + 1;
END WHILE;
END$$
DELIMITER ;
