mysql 存储过程循环插入问题

Question

又要麻烦老大了，现在需求有变化，我的jz_business_plan表的描述如下：CREATE TABLE `jz_business_plan` (
  `gp_id` int(10) unsigned NOT NULL auto_increment,
  `g_id` int(11) unsigned default NULL,
  `account_id` int(11) unsigned default NULL,
  `plandate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `re` varchar(255) default NULL,
  `gb_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`gp_id`),
  KEY `g_id` (`g_id`),
  CONSTRAINT `jz_business_plan_ibfk_3` FOREIGN KEY (`g_id`) REFERENCES `jz_gbmes` (`g_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;在另一张表jz_gbmes中包含业务信息，提醒种类，开始时间和截止时间等，如果是按月提醒，我需要判断当前日和起始时间截止时间的大小，如果在其中插入，如果是按年则需要判断月和日，我需要根据jz_business_plan表中的plandate来判断是否是当前月和日，如果不是，按月提醒每个月只生成一条记录，存储过程如下：SELECT max(plandate) into business_date from jz_business_plan;
   set mon = month(business_date);
   set ye =  year(business_date);
   set de =  dayofmonth(business_date);
   if (ye = year(now())) then
   WHILE (mon <= month(now())) do 
    insert into jz_business_plan(g_id,gb_id,account_id) SELECT a.g_id,a.gb_id,a.account_id from jz_gbmes a left join jz_business_plan b on a.g_id = b.g_id where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) or ((a.remindtype = 0) and ((a.stmonth <= mon) and (a.endmonth >= mon) and (a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())))) or ((a.remindtype = 1) and (a.stday <= dayofmonth(now()) and dayofmonth(now())) and (((mon+1)=1) or ((mon+1)=4) or ((mon+1)=7) or ((mon+1)=10)))) and b.g_id is null;   
    set mon = mon+1;
   end WHILE; 现在需要每次插入的时候也要将plandate赋值为当时的年月，即取得mon和当时的年插入，plandate为time_stamp类型，我用insert select形式还可以么？谢谢

Answer 1

可以，
当时的年：YEAR（NOW（））
在SELECT 语句中加入即可，明日再具体看看，现在要出去。
SELECT a.g_id,a.gb_id,a.account_id from jz_gbmes-》
SELECT a.g_id,a.gb_id,a.account_id from jz_gbmes，MON，YEAR（NOW（））.....

Answer 2

mon和year(now())都是整数，不能直接放在insert into jz_business_plan(g_id,gb_id,account_id，plandate)中啊。

Answer 3

每次插入的时候我需要判断根据最大的plandate中的月作为条件来判断在这个月中如果g_id不为空才能插入，如果为空，就不插入，这样每个月按月提醒时只生成一条记录，如果按年需要另外判断。

Answer 4

insert into jz_business_plan(g_id,gb_id,account_id,plandate) SELECT a.g_id,a.gb_id,a.account_id from MON,Year(now()),jz_gbmes a left join jz_business_plan b on a.g_id = b.g_id where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) or ((a.remindtype = 0) and ((a.stmonth <= mon) and (a.endmonth >= mon) and (a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())))) or ((a.remindtype = 1) and (a.stday <= dayofmonth(now()) and dayofmonth(now())) and (((mon+1)=1) or ((mon+1)=4) or ((mon+1)=7) or ((mon+1)=10)))) and b.g_id is null; 
这样不行啊。

Answer 5

1、你要将MON,Year(now())两个字段插入什么表中；
2、如是jz_business_plan表，
insert into jz_business_plan(g_id,gb_id,account_id,plandate,f1,f2) SELECT a.g_id,a.gb_id,a.account_id,MON,Year(now()) from jz_gbmes a

Answer 6

不能把mon,year(now())转换成一个时间类型的放入plandate中么？

Answer 7

plandate:是你的字段吧，你的意思是用mon,year(now())替换此字段？

Answer 8

不是plandate原先就是
`plandate` timestamp NOT NULL default CURRENT_TIMESTAMP,以前插入的时候都是让它默认为当前时间，现在需要将它改为插入时的时间，如果在当月的时候再次调用这个存储过程，如果是该月已经存在g_id了就不再插入了。

Answer 9

现在需要将它改为插入时的时间：什么形式，年月日时间？用NOW不行

Answer 10

对，需要插入时间，比如，今天是12月，如果在九月的时候就没有向里面插入数据，我在12月调用这个存储过程的时候，需要将九月，十月，十一月的数据插入其中，并且plandate要记录2008-9，2008-10，2008-11的日期。

Answer 11

向jz_business_plan的plandate字段插入。

Answer 12

这样：
CAST(
CONCAT(YEAR(NOW()),@MON,DAY(NOW())) AS DATE )

Answer 13

insert into jz_business_plan(g_id,gb_id,account_id,plandate) SELECT a.g_id,a.gb_id,a.account_id,CAST(
CONCAT(YEAR(NOW()),@MON,DAY(NOW())) AS DATE )) from jz_gbmes a

Answer 14

CAST( 
CONCAT(YEAR(NOW()),@MON,DAY(NOW())) AS DATE ) 如果是09年的话，在查08年的业务，这个时间就不对了。

Answer 15

用
set de =  dayofmonth(business_date);
中的ye如何？你可以根据上述示例自行修改一下。

Answer 16

呵呵，
是 set ye =  year(business_date);
中的YE

Answer 17

CAST(
CONCAT(YE,MON,DAY(NOW())) AS DATE )

Answer 18

有一个问题麻烦老大再帮我看一下，就是在判断如果是当月有数据的话就不插入了，如果没有再插入，哪么这个跨年的比如我2009年2月的时候调用2008年业务，假设数据库中只有2008-11的业务，我需要查处2008-12和2009-1和2009-2的数据，哪么这个判断条件：
where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) or ((a.remindtype = 0) and ((a.stmonth <= mon) and (a.endmonth >= mon) and (a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())))) or ((a.remindtype = 1) and (a.stday <= dayofmonth(now()) and dayofmonth(now())) and (((mon+1)=1) or ((mon+1)=4) or ((mon+1)=7) or ((mon+1)=10)))) and b.g_id is null;该怎么修改呢？谢谢！

Answer 19

用DATEDIFF取得2009-2与2008-11相差月数
select round(datediff('2009-02-01','2008-11-01' )/30)
DATE_ADD(date,INTERVAL 1 MONTH), 
进行循环就可以了，直接在WHERE中加入日期=循环日期

Answer 20

示例：
declare ff1 date;
declare ff2 date;
set ff1='2009-01-01';
set ff2='2008-11-01';
while ff2<=ff1 do
INSERT INTO LSBRQ VALUES(FF2);
set ff2=date_add(ff2,INTERVAL 1 month);
END WHILE;

Answer 21

我如果这样写的话：while business_date <= now() do
insert into jz_business_plan(g_id,gb_id,account_id,plandate) SELECT a.g_id,a.gb_id,a.account_id,CAST( 
   CONCAT(ye,mon,DAY(NOW())) AS DATE ) from jz_gbmes a left join jz_business_plan b on a.g_id = b.g_id where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) or ((a.remindtype = 0) and ((a.stmonth <= mon) and (a.endmonth >= mon) and (a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())))) or ((a.remindtype = 1) and (a.stday <= dayofmonth(now()) and dayofmonth(now())) and ((mon=1) or (mon=4) or (mon=7) or (mon=10))))  and b.g_id is null;   哪么插入一次后第二个月b.g_id is null就不会再插入了，如果我把他去掉，哪么一个月中将会有好几次插入数据库，而需要只插入一次。

Answer 22

如要考虑有跨年的情况，恐怕你的思路要调整，直接用
declare ff1 date;
declare ff2 date;
set ff1='2009-01-01';
set ff2='2008-11-01';
while ff2 <=ff1 do
INSERT INTO LSBRQ VALUES(FF2);
set ff2=date_add(ff2,INTERVAL 1 month);
END WHILE;
插入，在条件中直接加入年月的判断。

Answer 23

这个是这样，可是如果比如现在是2008-12，数据库中已经存在2008-11的业务，我想调用存储过程生成2008-12的业务insert into jz_business_plan(g_id,gb_id,account_id,plandate) SELECT a.g_id,a.gb_id,a.account_id,CAST( 
   CONCAT(ye,mon,DAY(NOW())) AS DATE ) from jz_gbmes a where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) or ((a.remindtype = 0) and ((a.stmonth <= mon) and (a.endmonth >= mon) and (a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())))) or ((a.remindtype = 1) and (a.stday <= dayofmonth(now()) and dayofmonth(now())) and ((mon=1) or (mon=4) or (mon=7) or (mon=10))));   这样的话会生成很多
(((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now()))这个时间段的重复的数据，我只要生成一条就行了。

Answer 24

哈哈，麻烦老大了，可能我没说清楚，是这样的jz_business_plan是业务计划表，每次点击待办业务的时候都会调用存储过程来将业务产生到表中，业务有三种提醒方式，按年，按季，按月，然后根据他们的起始日，截止日来判断是否将业务放入表中，然后将他们列出，对于按月提示业务每个月只生成一次。

Answer 25

这是我的按月的判断条件：insert into jz_business_plan(g_id,gb_id,account_id) SELECT a.g_id,a.gb_id,a.account_id from jz_gbmes a left join jz_business_plan b on a.g_id = b.g_id where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) and b.g_id is null;      每次产生待办业务以后，在表现层会列出待办业务，点击完成后业务就不会出现在jz_business_plan中。我在这个判断条件中加上b.g_id is null这样如果在表中还有这个业务，哪么他将不再生成了，加上这个条件后就不能生成跨年多个月没有做的业务，如果去掉b.g_id is null,哪么在a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) 将会产生这个时间差的业务，而需要只产生一个。

Answer 26

对我的年，季，月都有如下：insert into jz_business_plan(g_id,gb_id,account_id) SELECT a.g_id,a.gb_id,a.account_id from jz_gbmes a left join jz_business_plan b on a.g_id = b.g_id where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) or ((a.remindtype = 0) and ((a.stmonth <= mon) and (a.endmonth >= mon) and (a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())))) or ((a.remindtype = 1) and (a.stday <= dayofmonth(now()) and dayofmonth(now())) and (((mon+1)=1) or ((mon+1)=4) or ((mon+1)=7) or ((mon+1)=10)))) and b.g_id is null;   关键还是我上述说的问题。