我的第一个完整存储过程 /散花

根据日历计算两个时间段之间的请假天数。涉及游标,循环的基本使用,记录下。

调用:

CALL pro_get_usedtime_bycalendar('日历1','2019-09-01 9:00:00','2019-09-27 17:30:00',@total)

DELIMITER $$

USE `s2cloud`$$

DROP PROCEDURE IF EXISTS `pro_get_usedtime_bycalendar`$$

CREATE DEFINER=`sqluser`@`%` PROCEDURE `pro_get_usedtime_bycalendar`(IN _calendar_name VARCHAR(50),
IN _startDate DATETIME,
IN _endDate DATETIME,
OUT _totalUsedTime DOUBLE )
BEGIN

    ### -- 总用(总用天数+总用小时/一天的工作时长)
    DECLARE totalUsedDays DOUBLE DEFAULT 0.0 ; -- 总用天数
    DECLARE totalUsedHours DOUBLE DEFAULT 0.0 ; -- 总用小时
    DECLARE oneDayNeedWorkHours DOUBLE DEFAULT 0.0 ; -- 一天的工作时长(小时)

    ##定义用到得变量
    ######WorkingDay的变量
    DECLARE workingDayId VARCHAR(50) DEFAULT '' ;    -- 工作日objectId,对应t_working_timespan的parentObjectId
    DECLARE workingDayCurrentDate DATETIME;    -- 工作日当前日期
    DECLARE wkDayIndex INT DEFAULT 0;    -- 定义workingDay循环判断变量

    ######WorkingTimespan的变量
    DECLARE startTimeHour INT DEFAULT 0;    -- 开始时间(小时)
    DECLARE startTimeMinute INT DEFAULT 0;    -- 开始时间(分)
    DECLARE endTimeHour INT DEFAULT 0;    -- 结束时间(小时)
    DECLARE endTimeMinute INT DEFAULT 0;    -- 结束时间(分)
    DECLARE startTimeDate DATETIME;    -- 开始日期(年-月-日 时:分) 
    DECLARE endTimeDate DATETIME;    -- 结束日期(年-月-日 时:分)
    DECLARE wkTiemSpanIndex INT DEFAULT 0;    -- 定义WorkingTimespan循环判断变量

    ## 定义workingDay游标:工作日表
    DECLARE cur_workingday CURSOR FOR
        SELECT ObjectID,CurrentDate FROM t_working_day
        WHERE CalendarId IN (SELECT ObjectID FROM t_working_calendar WHERE DisplayName=_calendar_name)
        AND isworkingDay=1
        AND IF(IFNULL(_startDate,'')='' OR IFNULL(_endDate,'')='' , 1=0 ,(CurrentDate>=DATE_FORMAT(_startDate,'%Y-%m-%d') AND CurrentDate <=_endDate))
        ORDER BY CurrentDate ASC;
    ### 循环赋初始值
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET wkDayIndex=1;

    ##前提_startDate,_endDate不能为空
    IF IFNULL(_startDate,'')<>'' AND IFNULL(_endDate,'')<>'' THEN
        BEGIN
            ### 打开
            OPEN cur_workingday;
            ## 赋值
            FETCH cur_workingday INTO workingDayId,workingDayCurrentDate;
            ###循环判断
            WHILE wkDayIndex <> 1 DO
                BEGIN
                    ##重置变量
                    SET startTimeDate=NULL;
                    SET endTimeDate=NULL;

                    SET @startDiff=DATEDIFF(_startDate,workingDayCurrentDate);
                    SET @endDiff=DATEDIFF(_endDate,workingDayCurrentDate);

                    ## 请假开始  OR 请假结束那天
                    IF @startDiff = 0 OR  (@startDiff<0 AND  @endDiff=0) THEN

                        BEGIN
                            -- 遍历 t_working_day 工作日
                            -- 定义游标WorkingTimespan以及结束标识
                            DECLARE cur_workingTimeSpan CURSOR FOR
                                SELECT ts.StartTimeHour,ts.StartTimeMinute,ts.EndTimeHour,ts.EndTimeMinute FROM t_working_timespan ts WHERE ts.ParentObjectID=workingDayId;
                            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET wkTiemSpanIndex=1;    #结束标识
                            ## 打开游标
                            OPEN cur_workingTimeSpan;
                            ## 赋值
                            FETCH cur_workingTimeSpan INTO startTimeHour,startTimeMinute,endTimeHour,endTimeMinute;

                            SET wkTiemSpanIndex=0;    -- 重置timeSpan循环标识
                            -- set @oneDayRealWorkHours=0;    -- 工作日当天实际工作的小时数   (小时数/天工作总小时 =工作天数)
                            SET oneDayNeedWorkHours=0;    -- 工作日当天需要工作的小时数   (小时数/天工作总小时 =工作天数)

                            WHILE wkTiemSpanIndex <> 1 DO
                                BEGIN
                                    -- 遍历 t_working_timeSpan 时间段
                                    -- SELECT startTimeHour,startTimeMinute,endTimeHour,endTimeMinute;
                                    ##
                                    SET startTimeDate=DATE_ADD(DATE_ADD(workingDayCurrentDate, INTERVAL startTimeHour HOUR), INTERVAL startTimeMinute MINUTE);
                                    SET endTimeDate=DATE_ADD(DATE_ADD(workingDayCurrentDate, INTERVAL endTimeHour HOUR), INTERVAL endTimeMinute MINUTE);

                                    SET @workTimeSpanDiff=TIMEDIFF(endTimeDate,startTimeDate);  -- 12:00-9:30 =03:30 3小时30分钟=3.5小时
                                    SET @workTimeSpanHours=HOUR(@workTimeSpanDiff)+ MINUTE(@workTimeSpanDiff)/60;    -- 转成小时小时数
                                    SET oneDayNeedWorkHours=oneDayNeedWorkHours+@workTimeSpanHours;
                                    ##请假开始那天
                                    IF @startDiff = 0 THEN
                                        BEGIN
                                            IF DATEDIFF(_endDate,_startDate)<>0 THEN
                                                ##开始和结束不为同一天
                                                BEGIN
                                                    IF TIMEDIFF(_startDate,startTimeDate)<=0 THEN
                                                        ##开始时间前请假
                                                        SET totalUsedHours=totalUsedHours+@workTimeSpanHours;
                                                    ELSEIF  TIMEDIFF(_startDate,startTimeDate)>0 AND TIMEDIFF(_startDate,endTimeDate)<0 THEN
                                                        ##开始时间后请假
                                                        SET @tempWorkDiff=TIMEDIFF(endTimeDate,_startDate);  -- 12:00-9:30 =03:30 3小时30分钟=3.5小时
                                                        SET @tempWorkDiffHours=HOUR(@tempWorkDiff)+ MINUTE(@tempWorkDiff)/60;    -- 转成小时小时数
                                                        SET totalUsedHours=totalUsedHours+@tempWorkDiffHours;
                                                    END IF;
                                                END;
                                            ELSEIF DATEDIFF(_endDate,_startDate)=0 THEN
                                                ##开始和结束为同一天
                                                BEGIN
                                                    SET @tempWorkDiff=NULL;
                                                    IF TIMEDIFF(_startDate,startTimeDate)<=0 AND  TIMEDIFF(_endDate,startTimeDate)>=0 AND  TIMEDIFF(_endDate,endTimeDate)<=0 THEN
                                                        SET @tempWorkDiff=TIMEDIFF(_endDate,startTimeDate);  -- 12:00-9:30 =03:30 3小时30分钟=3.5小时
                                                    ELSEIF   TIMEDIFF(_startDate,startTimeDate)<=0 AND  TIMEDIFF(_endDate,startTimeDate)>=0 AND  TIMEDIFF(_endDate,endTimeDate)>=0 THEN
                                                        SET @tempWorkDiff=TIMEDIFF(endTimeDate,startTimeDate);  -- 12:00-9:30 =03:30 3小时30分钟=3.5小时
                                                    ELSEIF   TIMEDIFF(_startDate,startTimeDate)>=0 AND  TIMEDIFF(_endDate,startTimeDate)>=0 AND  TIMEDIFF(_endDate,endTimeDate)<=0 THEN
                                                        SET @tempWorkDiff=TIMEDIFF(_endDate,_startDate);  -- 12:00-9:30 =03:30 3小时30分钟=3.5小时
                                                    ELSEIF   TIMEDIFF(_startDate,startTimeDate)>=0 AND  TIMEDIFF(_startDate,endTimeDate)<=0 AND  TIMEDIFF(_endDate,endTimeDate)>=0 THEN
                                                        SET @tempWorkDiff=TIMEDIFF(endTimeDate,_startDate);  -- 12:00-9:30 =03:30 3小时30分钟=3.5小时
                                                    END IF;

                                                    IF @tempWorkDiff<>NULL THEN
                                                        SET @tempWorkDiffHours=HOUR(@tempWorkDiff)+ MINUTE(@tempWorkDiff)/60;    -- 转成小时小时数
                                                        SET totalUsedHours=totalUsedHours+@tempWorkDiffHours;
                                                    END IF;
                                                END;
                                            END IF;

                                        END;
                                    ##请假结束那天
                                    ELSEIF (@startDiff<0 AND  @endDiff=0) THEN
                                        BEGIN
                                            IF TIMEDIFF(_endDate,startTimeDate)>=0 AND TIMEDIFF(_endDate,endTimeDate)<=0 THEN
                                                ##请假结束在下班前,加中间的小时数
                                                SET @tempWorkDiff=TIMEDIFF(_endDate,startTimeDate);  -- 12:00-9:30 =03:30 3小时30分钟=3.5小时
                                                SET @tempWorkDiffHours=HOUR(@tempWorkDiff)+ MINUTE(@tempWorkDiff)/60;    -- 转成小时小时数
                                                SET totalUsedHours=totalUsedHours+@tempWorkDiffHours;
                                            ELSEIF TIMEDIFF(_endDate,startTimeDate)>0 THEN
                                                ##请假结束在下班后,加这个时间段的workTimeSpan
                                                SET totalUsedHours=totalUsedHours+@workTimeSpanHours;
                                            END IF;
                                        END;
                                    END IF;

                                END;
                            FETCH cur_workingTimeSpan INTO startTimeHour,startTimeMinute,endTimeHour,endTimeMinute;
                            END WHILE;

                            CLOSE cur_workingTimeSpan;-- 关闭游标
                        END;

                    ELSEIF @startDiff<0  AND @endDiff>0 THEN  ##跨整天,天数+1
                        BEGIN
                         SET totalUsedDays=totalUsedDays+1;
                        END;
                    END IF;

                END;
            ## 赋值下一个游标
            FETCH cur_workingday INTO workingDayId,workingDayCurrentDate;
            END WHILE;
            ## 关闭
            CLOSE cur_workingday;

        END;
    END IF;

    ## 结果:
    SET @totalUsed=totalUsedDays;
    ###将工作小时数折算成天
    IF oneDayNeedWorkHours>0 AND totalUsedHours>0    THEN
        SET @totalUsed=@totalUsed+(totalUsedHours/oneDayNeedWorkHours);
    END IF;
    ##  精度为0.5 :不足0.5进到0.5,大于0.5进1
    SET  @totalUsed=CEILING(@totalUsed / 0.5) * 0.5;

    ## for log
    SELECT oneDayNeedWorkHours , totalUsedDays AS DAY,totalUsedHours AS HOUR, @totalUsed AS total;
    SET _totalUsedTime=@totalUsed;
END$$

DELIMITER ;
01-10 02:37