表 如下:
create table aaa(a timestamp);create table lzp20121129(a timestamp);procedure如下:
delimiter $$
DROP PROCEDURE IF exists lzp_insert_by_date;
create procedure lzp_insert_by_date()
begin
declare str_date date;
declare find_time date;
declare lzp_b int default 0;
declare table_name date;
DECLARE cur_lzp CURSOR FOR select date(timeline) from aaa;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lzp_b = 1;
set str_date=DATE_ADD(curdate(), INTERVAL -1 DAY);
SET table_name = date_format(DATE_ADD(curdate(), INTERVAL -1 DAY),"%Y%m%d");
OPEN cur_lzp;
set lzp_b=0;
FETCH cur_lzp INTO find_time ;
while (lzp_b <> 1) do
if (find_time=str_date) then
set @sqlcmd_lzp="insert into lzp20121129 values('2012-11-15')";
PREPARE p1 FROM @sqlcmd_lzp;
EXECUTE p1;
DEALLOCATE PREPARE p1;
end if;
FETCH cur_lzp INTO find_time;
end while;
close cur_lzp;
end;
$$
delimiter ;帮忙看下那里出错了 找了两天了,还是找不到那里错了。
create table aaa(a timestamp);create table lzp20121129(a timestamp);procedure如下:
delimiter $$
DROP PROCEDURE IF exists lzp_insert_by_date;
create procedure lzp_insert_by_date()
begin
declare str_date date;
declare find_time date;
declare lzp_b int default 0;
declare table_name date;
DECLARE cur_lzp CURSOR FOR select date(timeline) from aaa;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lzp_b = 1;
set str_date=DATE_ADD(curdate(), INTERVAL -1 DAY);
SET table_name = date_format(DATE_ADD(curdate(), INTERVAL -1 DAY),"%Y%m%d");
OPEN cur_lzp;
set lzp_b=0;
FETCH cur_lzp INTO find_time ;
while (lzp_b <> 1) do
if (find_time=str_date) then
set @sqlcmd_lzp="insert into lzp20121129 values('2012-11-15')";
PREPARE p1 FROM @sqlcmd_lzp;
EXECUTE p1;
DEALLOCATE PREPARE p1;
end if;
FETCH cur_lzp INTO find_time;
end while;
close cur_lzp;
end;
$$
delimiter ;帮忙看下那里出错了 找了两天了,还是找不到那里错了。
再就是 set @sqlcmd_lzp="insert into lzp20121129 values('2012-11-15')";为了测试用的 ,就写成字符串了。
' select * from aaa where date(timeline)=\'',find_time,'\'');
DROP PROCEDURE IF EXISTS lzp_insert_by_date$$
CREATE PROCEDURE lzp_insert_by_date()
BEGIN
DECLARE str_date DATE;
DECLARE find_time DATE;
DECLARE lzp_b INT DEFAULT 0;
DECLARE table_name DATE;
DECLARE cur_lzp CURSOR FOR SELECT DATE(timeline) FROM aaa;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lzp_b = 1;
SET str_date=DATE_ADD(CURDATE(), INTERVAL -1 DAY);
SET table_name = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 DAY),"%Y%m%d");
OPEN cur_lzp;
SET lzp_b=0;
FETCH cur_lzp INTO find_time ;
WHILE (lzp_b <> 1) DO
IF (find_time=str_date) THEN
SET @sqlcmd_lzp="insert into lzp20121129 values('2012-11-15')";
PREPARE p1 FROM @sqlcmd_lzp;
EXECUTE p1;
DEALLOCATE PREPARE p1;
END IF;
FETCH cur_lzp INTO find_time;
END WHILE;
CLOSE cur_lzp;
END$$
DELIMITER ;
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.49-community |
+------------------+
1 row in set (0.00 sec)
DROP PROCEDURE IF EXISTS lzp_insert_by_date$$
DROP PROCEDURE IF exists lzp_insert_by_date$$
create procedure lzp_insert_by_date()
begin
declare str_date date;
declare find_time date;
declare lzp_b int default 0;
declare table_name date;
DECLARE cur_lzp CURSOR FOR select date(timeline) from aaa;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lzp_b = 1;
set str_date=DATE_ADD(curdate(), INTERVAL -1 DAY);
SET table_name = date_format(DATE_ADD(curdate(), INTERVAL -1 DAY),"%Y%m%d");
OPEN cur_lzp;
set lzp_b=0;
FETCH cur_lzp INTO find_time ;
while (lzp_b <> 1) do
if (find_time=str_date) then
SET @sqlcmd_lzp=CONCAT("insert into lzp",find_time,' select * from aaa where date(timeline)=\'',find_time,'\'');
PREPARE p1 FROM @sqlcmd_lzp;
EXECUTE p1;
DEALLOCATE PREPARE p1;
end if;
FETCH cur_lzp INTO find_time;
end while;
close cur_lzp;
end$$
delimiter ;是这样的 啊 我都改了 , 错误 还是一样的。
mysql> DROP PROCEDURE IF exists lzp_insert_by_date$$
Query OK, 0 rows affected, 1 warning (0.10 sec)mysql> create procedure lzp_insert_by_date()
-> begin
-> declare str_date date;
-> declare find_time date;
-> declare lzp_b int default 0;
-> declare table_name date;
-> DECLARE cur_lzp CURSOR FOR select date(timeline) from aaa;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET lzp_b = 1;
->
-> set str_date=DATE_ADD(curdate(), INTERVAL -1 DAY);
-> SET table_name = date_format(DATE_ADD(curdate(), INTERVAL -1 DAY),"%Y%m%d");
-> OPEN cur_lzp;
-> set lzp_b=0;
-> FETCH cur_lzp INTO find_time ;
->
-> while (lzp_b <> 1) do
-> if (find_time=str_date) then
-> SET @sqlcmd_lzp=CONCAT("insert into lzp",find_time,' select * from aaa where date(timeline)=\'',find_time,'\'');
-> PREPARE p1 FROM @sqlcmd_lzp;
-> EXECUTE p1;
-> DEALLOCATE PREPARE p1;
-> end if;
-> FETCH cur_lzp INTO find_time;
-> end while;
-> close cur_lzp;
-> end$$
Query OK, 0 rows affected (0.01 sec)mysql> delimiter ;
mysql>
mysql>
mysql>
mysql>
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.49-community |
+------------------+
1 row in set (0.00 sec)mysql> 难道是我的版本有问题? 请指导下。