存储过程--数据整合

BEGIN
  DECLARE param_name VARCHAR(50) CHARACTER SET utf8 DEFAULT 'xx';         
  DECLARE param_day  INT DEFAULT 1;                                      -- 默认统计一天前的数据  
  DECLARE startTimePos,endTimePos,startTimeWeb,endTimeWeb TIMESTAMP DEFAULT '2000-12-12 00:00:00';  


  DECLARE i,done,done2,done3 INT DEFAULT 0;
DECLARE NEW_START_TIME VARCHAR(30) CHARACTER SET utf8 default 'newstarttime';
DECLARE NEW_END_TIME VARCHAR(30) CHARACTER SET utf8 default 'newendtime';

DECLARE DIF_VAL INT DEFAULT 0;


  DECLARE cur_name CURSOR for select DISTINCT t.sta_name from T_INF_STAFF t;
  DECLARE CONTINUE HANDLER FOR NOT FOUND set done=1;
open cur_name; -- 打开游标
cursor_loop:LOOP  -- 开始游标遍历
FETCH cur_name into param_name;   --   处理过程
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND set done2=1;

select startTime,endTime into startTimePos,endTimePos from tmp_sign
where name1=param_name  and date=DATE_SUB(CURRENT_DATE,INTERVAL param_day day);

if done2=0 THEN  -- 如果没有找到记录结束遍历
set i=i+1;
       BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND set done3=1;
select STR_TO_DATE(sign_morningtime,'%Y/%m/%d %H:%i') ,STR_TO_DATE(sign_afternoontime,'%Y/%m/%d %H:%i') 
into startTimeWeb,endTimeWeb 
from T_INF_SIGNIN 
where sign_name=param_name and date(sign_morningtime)=DATE_SUB(CURRENT_DATE,INTERVAL param_day day);

if done3=0 THEN 
set DIF_VAL=TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(startTimePos,'%H:%i:%S'),DATE_FORMAT(startTimeWeb,'%H:%i:%S')));
       if DIF_VAL<0 THEN
set NEW_START_TIME=CONCAT(DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL param_day day),'%Y/%m/%d'),' ',DATE_FORMAT(startTimePos,'%H:%i'));
-- select NEW_START_TIME,1;
UPDATE T_INF_SIGNIN set 
sign_morningtime=NEW_START_TIME
where  sign_name=param_name and date(sign_morningtime)=DATE_SUB(CURRENT_DATE,INTERVAL param_day day);
end if;
set DIF_VAL=TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(endTimePos,'%H:%i:%S'),DATE_FORMAT(endTimeWeb,'%H:%i:%S')));
if DIF_VAL>0 THEN
set NEW_END_TIME=CONCAT(DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL param_day day),'%Y/%m/%d'),' ',DATE_FORMAT(endTimePos,'%H:%i'));
-- select NEW_END_TIME,2;
UPDATE T_INF_SIGNIN set 
sign_afternoontime=NEW_END_TIME 
where sign_name=param_name and date(sign_morningtime)=DATE_SUB(CURRENT_DATE,INTERVAL param_day day);
end if;
UPDATE T_INF_SIGNIN SET 
sign_afternoonip='签到机',
sign_actualtime=TIMEDIFF(STR_TO_DATE(sign_afternoontime,'%Y/%m/%d %H:%i'),STR_TO_DATE(sign_morningtime,'%Y/%m/%d %H:%i'))
where sign_name=param_name and date(sign_morningtime)=DATE_SUB(CURRENT_DATE,INTERVAL param_day day); 
ELSE
set done3=0;
set NEW_START_TIME=CONCAT(DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL param_day day),'%Y/%m/%d'),' ',DATE_FORMAT(startTimePos,'%H:%i'));
set NEW_END_TIME=CONCAT(DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL param_day day),'%Y/%m/%d'),' ',DATE_FORMAT(endTimePos,'%H:%i'));
insert into T_INF_SIGNIN(sign_name,sign_morningtime,sign_afternoontime) 
values(param_name,NEW_START_TIME,NEW_END_TIME) ;
UPDATE T_INF_SIGNIN SET 
sign_morningip='签到机',
sign_afternoonip='签到机',
sign_actualtime=TIMEDIFF(STR_TO_DATE(sign_afternoontime,'%Y/%m/%d %H:%i'),STR_TO_DATE(sign_morningtime,'%Y/%m/%d %H:%i'))
where sign_name=param_name and date(sign_morningtime)=DATE_SUB(CURRENT_DATE,INTERVAL param_day day);
-- select NEW_START_TIME,NEW_END_TIME;
end if;
END; 
ELSE
set done2=0;
end if;
END;
--   处理过程
if done=1 THEN  -- 如果没有找到记录结束遍历
select done,i;
leave cursor_loop; -- 结束遍历
end if;
end loop cursor_loop;  
close cur_name; -- 关闭游标

END