原先有一个存储过程中,把一张表的数据插入到另一张表中,存储过程如下:CREATE DEFINER=`root`@`localhost` PROCEDURE `undobusiness`()
BEGIN
DECLARE business_date timestamp default current_timestamp;
DECLARE no_more_gbmes int;
DECLARE mon int;
DECLARE gid int;
DECLARE gbid int;
DECLARE accountid int;
DECLARE bsid int;
DECLARE gbmes_csr CURSOR FOR SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1;
SELECT max(plandate) into business_date from jz_business_plan;
SET no_more_gbmes=0;
set mon = DATE_FORMAT(business_date,'%m');
WHILE (mon < DATE_FORMAT(Now(),'%m')) do
OPEN gbmes_csr;
REPEAT
FETCH gbmes_csr INTO gid,gbid,accountid,bsid;
insert into jz_business_plan(g_id,gb_id,account_id,bs_id) values(gid,gbid,accountid,bsid);
UNTIL no_more_gbmes
END REPEAT;
CLOSE gbmes_csr;
set mon = mon+1;
end WHILE;
END$$
DELIMITER ;现在需要改变使满足一定条件的数据才能插入,而这个条件需要先从数据库中获得,如:原先:DECLARE gbmes_csr CURSOR FOR SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes;
先需要先从另一张表查处条件:SELECT max(plandate) into business_date from jz_business_plan;
set mon = DATE_FORMAT(business_date,'%m');
然后从SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes where Date_format(date,'%m')= mon;这样怎么使用游标,如果不使用游标,怎样将表中多条记录插入到另一个表中?谢谢!
BEGIN
DECLARE business_date timestamp default current_timestamp;
DECLARE no_more_gbmes int;
DECLARE mon int;
DECLARE gid int;
DECLARE gbid int;
DECLARE accountid int;
DECLARE bsid int;
DECLARE gbmes_csr CURSOR FOR SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1;
SELECT max(plandate) into business_date from jz_business_plan;
SET no_more_gbmes=0;
set mon = DATE_FORMAT(business_date,'%m');
WHILE (mon < DATE_FORMAT(Now(),'%m')) do
OPEN gbmes_csr;
REPEAT
FETCH gbmes_csr INTO gid,gbid,accountid,bsid;
insert into jz_business_plan(g_id,gb_id,account_id,bs_id) values(gid,gbid,accountid,bsid);
UNTIL no_more_gbmes
END REPEAT;
CLOSE gbmes_csr;
set mon = mon+1;
end WHILE;
END$$
DELIMITER ;现在需要改变使满足一定条件的数据才能插入,而这个条件需要先从数据库中获得,如:原先:DECLARE gbmes_csr CURSOR FOR SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes;
先需要先从另一张表查处条件:SELECT max(plandate) into business_date from jz_business_plan;
set mon = DATE_FORMAT(business_date,'%m');
然后从SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes where Date_format(date,'%m')= mon;这样怎么使用游标,如果不使用游标,怎样将表中多条记录插入到另一个表中?谢谢!
http://yueliangdao0608.blog.51cto.com/397025/85145
set @stmt = concat('insert into jz_business_plan(g_id,gb_id,account_id,bs_id)
SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes limit ',@cnt);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = NULL;
SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes where Date_format(date,'%m')=
DATE_FORMAT((SELECT max(plandate) into business_date from jz_business_plan),'%m')
DATE_FORMAT((SELECT max(plandate) into business_date from jz_business_plan),'%m')查询出来不需要给他们赋值?比如set gbid =g_id?而insert语句是不是应该改成这样:insert into jz_business_plan(gid,gbid,accountid,bsid)?
insert into jz_business_plan(g_id,gb_id,account_id,bs_id)
SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes where Date_format(date,'%m')=
DATE_FORMAT((SELECT max(plandate) from jz_business_plan),'%m')查询出来不需要给他们赋值?
不需要
SELECT g_id,gb_id,account_id from jz_gbmes ;
我这样插入的g_id,gb_id,account_id是空的,而且有多条数据的时候也只是插入了这一条。
SELECT g_id,gb_id,account_id from jz_gbmes仔细看看我的代码哦
BEGIN
DECLARE business_date timestamp default current_timestamp;
DECLARE no_more_gbmes int;
DECLARE mon int;
SELECT max(plandate) into business_date from jz_business_plan;
set mon = DATE_FORMAT(business_date,'%m');
WHILE (mon < DATE_FORMAT(Now(),'%m')) do
insert into jz_business_plan(g_id,gb_id,account_id);
SELECT g_id,gb_id,account_id from jz_gbmes;
set mon = mon+1;
end WHILE;
SELECT * from jz_business_plan WHERE DATE_FORMAT(jz_business_plan.plandate,'%m') = DATE_FORMAT(now(),'%m');
END$$DELIMITER ;yueliangdao0608老大,你写的我看不明白,不知道该放在哪里?麻烦你们两位了,哈哈,先谢谢啊。
SELECT g_id,gb_id,account_id from jz_gbmes;
这是一条SQL语句,去掉第一条的;
insert into jz_business_plan(g_id,gb_id,account_id) SELECT g_id,gb_id,account_id from jz_gbmes; 什么错误信息?
insert into jz_business_plan(g_id,gb_id,account_id,bs_id)
SELECT g_id,gb_id,account_id,bs_id FROM jz_gbmes where Date_format(date,'%m')<
DATE_FORMAT((SELECT max(plandate) from jz_business_plan),'%m') 注意是一条SQL语句