做月初始化,不管user_info的第二条数据是啥,只能增加第一条记录,谁帮我看看代码
CREATE PROCEDURE `ycsh`(in cshrq varchar(10))
BEGIN
declare rowcount int default 0;
declare currentid varchar(20);
declare currentname varchar(100);
declare currentld varchar(80);
declare currentzhlx varchar(20);
declare currentdfdj, currentsfdj decimal(7,2) default 0;
declare currentdf, currentsf decimal(10,2) default 0;
declare currentmj, currentwyf, currentwsf, currentqtfy decimal(7,2) default 0;
DECLARE Done INT DEFAULT 0;
/* 声明游标 */
DECLARE rs CURSOR FOR SELECT c_id, c_name, i_ld , i_zhlx, i_dfdj, i_sfdj, i_mj, i_wyf, i_wsf, i_qtfy FROM user_info;
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
select count(*) into rowcount from expenses where year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date));
if rowcount=0 then
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录 */
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj,currentmj,currentwyf, currentwsf, currentqtfy;
/* 遍历数据表 */
REPEAT
select n_dfbybs, n_sfsybs into currentdf, currentsf from expenses where c_id=currentid and year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date))-1;/*insert into */
insert into expenses(d_rq, c_id, c_name, i_zhlx, i_ld, n_dfsybs, n_sfsybs, n_dfbybs, n_sfbybs, n_dfdj, n_sfdj) values (convert(cshrq,date), currentid, currentname, currentzhlx, currentld, currentdf,currentsf, currentdf, currentsf, currentdfdj, currentsfdj);insert into expenses1(d_rq, c_id, c_name, i_zhlx, i_ld, i_mj, i_wyf, i_wsf, i_qtfy) values(convert(cshrq,date),currentid, currentname, currentzhlx, currentld, currentmj, currentwyf, currentwsf,currentqtfy);
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj, currentmj, currentwyf, currentwsf, currentqtfy;UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
end if;
END
CREATE PROCEDURE `ycsh`(in cshrq varchar(10))
BEGIN
declare rowcount int default 0;
declare currentid varchar(20);
declare currentname varchar(100);
declare currentld varchar(80);
declare currentzhlx varchar(20);
declare currentdfdj, currentsfdj decimal(7,2) default 0;
declare currentdf, currentsf decimal(10,2) default 0;
declare currentmj, currentwyf, currentwsf, currentqtfy decimal(7,2) default 0;
DECLARE Done INT DEFAULT 0;
/* 声明游标 */
DECLARE rs CURSOR FOR SELECT c_id, c_name, i_ld , i_zhlx, i_dfdj, i_sfdj, i_mj, i_wyf, i_wsf, i_qtfy FROM user_info;
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
select count(*) into rowcount from expenses where year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date));
if rowcount=0 then
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录 */
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj,currentmj,currentwyf, currentwsf, currentqtfy;
/* 遍历数据表 */
REPEAT
select n_dfbybs, n_sfsybs into currentdf, currentsf from expenses where c_id=currentid and year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date))-1;/*insert into */
insert into expenses(d_rq, c_id, c_name, i_zhlx, i_ld, n_dfsybs, n_sfsybs, n_dfbybs, n_sfbybs, n_dfdj, n_sfdj) values (convert(cshrq,date), currentid, currentname, currentzhlx, currentld, currentdf,currentsf, currentdf, currentsf, currentdfdj, currentsfdj);insert into expenses1(d_rq, c_id, c_name, i_zhlx, i_ld, i_mj, i_wyf, i_wsf, i_qtfy) values(convert(cshrq,date),currentid, currentname, currentzhlx, currentld, currentmj, currentwyf, currentwsf,currentqtfy);
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj, currentmj, currentwyf, currentwsf, currentqtfy;UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
end if;
END
BEGIN
declare rowcount int default 0;
declare currentid varchar(20);
declare currentname varchar(100);
declare currentld varchar(80);
declare currentzhlx varchar(20);
declare currentdfdj, currentsfdj decimal(7,2) default 0;
declare currentdf, currentsf decimal(10,2) default 0;
declare currentmj, currentwyf, currentwsf, currentqtfy decimal(7,2) default 0;
DECLARE Done INT DEFAULT 0;
/* 声明游标 */
DECLARE rs CURSOR FOR SELECT c_id, c_name, i_ld , i_zhlx, i_dfdj, i_sfdj, i_mj, i_wyf, i_wsf, i_qtfy FROM user_info;
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;select count(*) into rowcount from expenses where year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date));
Set Done=0;
if rowcount=0 then
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录 */
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj,currentmj,currentwyf, currentwsf, currentqtfy;
/* 遍历数据表 */
REPEAT
select n_dfbybs, n_sfsybs into currentdf, currentsf from expenses where c_id=currentid and year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date))-1;/*insert into */
insert into expenses(d_rq, c_id, c_name, i_zhlx, i_ld, n_dfsybs, n_sfsybs, n_dfbybs, n_sfbybs, n_dfdj, n_sfdj) values (convert(cshrq,date), currentid, currentname, currentzhlx, currentld, currentdf,currentsf, currentdf, currentsf, currentdfdj, currentsfdj);insert into expenses1(d_rq, c_id, c_name, i_zhlx, i_ld, i_mj, i_wyf, i_wsf, i_qtfy) values(convert(cshrq,date),currentid, currentname, currentzhlx, currentld, currentmj, currentwyf, currentwsf,currentqtfy);
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj, currentmj, currentwyf, currentwsf, currentqtfy;UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
end if;
END
我在前面已经定义了DECLARE Done INT DEFAULT 0;
select count(*) into rowcount from expenses where year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date));
以及
select n_dfbybs, n_sfsybs into currentdf, currentsf from expenses where c_id=currentid and year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date))-1;是否有结果
估计是这个第2条语句没有结果的问题
第二条没有满足条件的记录n_dfbybs, n_sfsybs等的值是不是应该为我定义的default 0呢?
这样的话下面的insert是不是也应该执行?
BEGIN
declare rowcount int default 0;
declare currentid varchar(20);
declare currentname varchar(100);
declare currentld varchar(80);
declare currentzhlx varchar(20);
declare currentdfdj, currentsfdj decimal(7,2) default 0;
declare currentdf, currentsf decimal(10,2) default 0;
declare currentmj, currentwyf, currentwsf, currentqtfy decimal(7,2) default 0;
DECLARE Done INT DEFAULT 0;
/* 声明游标 */
DECLARE rs CURSOR FOR SELECT c_id, c_name, i_ld , i_zhlx, i_dfdj, i_sfdj, i_mj, i_wyf, i_wsf, i_qtfy FROM user_info;
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;select count(*) into rowcount from expenses where year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date));
if rowcount=0 then
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录 */
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj,currentmj,currentwyf, currentwsf, currentqtfy;
/* 遍历数据表 */
REPEAT
select n_dfbybs, n_sfsybs into currentdf, currentsf from expenses where c_id=currentid and year(d_rq)=year(convert(cshrq,date)) and month(d_rq)=month(convert(cshrq,date))-1;Set Done=0;
/*insert into */
insert into expenses(d_rq, c_id, c_name, i_zhlx, i_ld, n_dfsybs, n_sfsybs, n_dfbybs, n_sfbybs, n_dfdj, n_sfdj) values (convert(cshrq,date), currentid, currentname, currentzhlx, currentld, currentdf,currentsf, currentdf, currentsf, currentdfdj, currentsfdj);insert into expenses1(d_rq, c_id, c_name, i_zhlx, i_ld, i_mj, i_wyf, i_wsf, i_qtfy) values(convert(cshrq,date),currentid, currentname, currentzhlx, currentld, currentmj, currentwyf, currentwsf,currentqtfy);
FETCH NEXT FROM rs INTO currentid, currentname, currentld, currentzhlx, currentdfdj, currentsfdj, currentmj, currentwyf, currentwsf, currentqtfy;UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
end if;
END
ACMAIN_CHM说的显然也是正确的,不知道为什么我调试没有通过
真心谢谢二位!