用root账号授权当前账号有创建函数的权限

GRANT CREATE ROUTINE, ALTER ROUTINE, DROP ROUTINE ON `gramess`.* TO 'gramess'@'%';
FLUSH PRIVILEGES;

创建函数：格式化时长

DROP FUNCTION IF EXISTS `format_duration`;

DELIMITER $$

CREATE FUNCTION `format_duration`(seconds INT) RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE years INT;
    DECLARE days INT;
    DECLARE hours INT;
    DECLARE minutes INT;
    DECLARE secs INT;
    DECLARE result VARCHAR(100) DEFAULT '';
    DECLARE remaining_seconds INT DEFAULT seconds;

    -- 计算各时间单位（使用365天为一年）
    SET years = FLOOR(remaining_seconds / 31536000);  -- 365*24*3600
    SET remaining_seconds = remaining_seconds % 31536000;

    SET days = FLOOR(remaining_seconds / 86400);     -- 24*3600
    SET remaining_seconds = remaining_seconds % 86400;

    SET hours = FLOOR(remaining_seconds / 3600);
    SET remaining_seconds = remaining_seconds % 3600;

    SET minutes = FLOOR(remaining_seconds / 60);
    SET secs = remaining_seconds % 60;

    -- 智能拼接时间单位（仅当值>0时添加）
    IF years > 0 THEN
        SET result = CONCAT(result, years, '年');
    END IF;

    IF days > 0 THEN
        SET result = CONCAT(result, days, '天');
    END IF;

    IF hours > 0 THEN
        SET result = CONCAT(result, hours, '小时');
    END IF;

    IF minutes > 0 THEN
        SET result = CONCAT(result, minutes, '分');
    END IF;

    IF secs > 0 OR result = '' THEN  -- 处理全为0的情况
        SET result = CONCAT(result, secs, '秒');
    END IF;

    RETURN result;
END $$

DELIMITER ;

创建存储过程

DROP PROCEDURE IF EXISTS `LGS_INIT_15`;
DELIMITER $$
CREATE PROCEDURE `LGS_INIT_15`(IN begindate VARCHAR(12), IN enddate VARCHAR(12))
BEGIN
    DECLARE arowcount INT DEFAULT 0;
    DECLARE uid INT;

    -- 发货单关系表状态处理
    INSERT INTO `lgs_create_log`(id, begin_date, end_date, message) VALUES(NULL, begindate, enddate, '15.1发货单关系表状态处理开始...');
    SELECT LAST_INSERT_ID() INTO uid;

    UPDATE `lgs_take_delivery_rel` SET is_process = 1 WHERE is_process = 0;

    SELECT ROW_COUNT() INTO arowcount;
    UPDATE `lgs_create_log` SET message = CONCAT('15.1发货单关系表状态处理结束', arowcount) WHERE id = uid;



    -- 临时表数据清除
    INSERT INTO `lgs_create_log`(id, begin_date, end_date, message) VALUES(NULL, begindate, enddate, '15.2临时表[lgs_delivery_info_centre_temp]数据清除开始...');
    SELECT LAST_INSERT_ID() INTO uid;

    TRUNCATE TABLE `lgs_delivery_info_centre_temp`;

    SELECT ROW_COUNT() INTO arowcount;
    UPDATE `lgs_create_log` SET message = CONCAT('15.2临时表[lgs_delivery_info_centre_temp]数据清除结束', arowcount) WHERE id = uid;



    INSERT INTO `lgs_create_log`(id, begin_date, end_date, message) VALUES(NULL, begindate, enddate, '15.3临时表[lgs_delivery_bill_centre_temp]数据清除开始...');
    SELECT LAST_INSERT_ID() INTO uid;

    TRUNCATE TABLE `lgs_delivery_bill_centre_temp`;

    SELECT ROW_COUNT() INTO arowcount;
    UPDATE `lgs_create_log` SET message = CONCAT('15.3临时表[lgs_delivery_bill_centre_temp]数据清除结束', arowcount) WHERE id = uid;



    INSERT INTO `lgs_create_log`(id, begin_date, end_date, message) VALUES(NULL, begindate, enddate, '15.4 All Done');

COMMIT;

END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS `LGS_INIT_10`;

DELIMITER $$
CREATE PROCEDURE `LGS_INIT_10`()
BEGIN

    -- 记录开始时间
    DECLARE starttime INT;

    DECLARE v_id INT;
    DECLARE v_start_date CHAR(8);
    DECLARE v_end_date CHAR(8);

    DECLARE m_id INT;


    -- 2. 声明游标（关联查询）
    DECLARE cur CURSOR FOR SELECT id, start_date, end_date FROM `lgs_create_cursor` WHERE `status` = 1 ORDER BY id ASC;

    -- 3. 声明 NOT FOUND 处理器
    -- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SELECT COALESCE(MAX(id), 0) INTO m_id FROM `lgs_create_cursor` WHERE `status` = 1;

    -- 4. 打开游标
    OPEN cur;

    -- 5. 循环读取数据
    read_loop: LOOP
        FETCH cur INTO v_id, v_start_date, v_end_date;  -- 获取一行数据

        SELECT UNIX_TIMESTAMP() INTO starttime;

        -- 数据执行
        CALL LGS_INIT_11(v_start_date, v_end_date);
        CALL LGS_INIT_12(v_start_date, v_end_date);
        CALL LGS_INIT_13(v_start_date, v_end_date);
        CALL LGS_INIT_14(v_start_date, v_end_date);
        CALL LGS_INIT_15(v_start_date, v_end_date);

        UPDATE `lgs_create_cursor` SET duration = format_duration(UNIX_TIMESTAMP() - starttime), `status` = 2 WHERE id = v_id;

        IF v_id = m_id THEN
            LEAVE read_loop;       -- 退出循环
        END IF;
    END LOOP;

    -- 6. 关闭游标
    CLOSE cur;
END $$
DELIMITER ;