opms_backend/opms_parent/schema/function.sql

CREATE TABLE `plat_sequence` (
    `name` varchar(32) NOT NULL COMMENT '序列编码',
    `current_value` int(11) DEFAULT 1000 COMMENT '当前值/初始值',
    `increment` int(11) DEFAULT NULL COMMENT '步增值',
    `remark` text DEFAULT NULL COMMENT '备注',
    `created_by` int(11) NOT NULL COMMENT '创建者',
    `created_name` varchar(90) NOT NULL COMMENT '创建人',
    `created_time` datetime NOT NULL COMMENT '创建时间',
    `updated_by` int(11) DEFAULT NULL COMMENT '更新者',
    `updated_name` varchar(90) DEFAULT NULL COMMENT '更新人',
    `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
    `deleted_time` datetime DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '序列信息表' ;


DROP FUNCTION IF EXISTS nextval;
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN
UPDATE plat_sequence
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END $ 
DELIMITER ;

alter table plat_sequence add `year` int(11) DEFAULT NULL COMMENT '年份' after increment;
update plat_sequence set year=2023,current_value=49  where name = 'contract_code'

DROP FUNCTION IF EXISTS next_year_reset_val;
DELIMITER $
CREATE FUNCTION next_year_reset_val (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN
UPDATE plat_sequence SET current_value=0, `year`=year(now()) WHERE `year` = year(now())-1;
UPDATE plat_sequence SET current_value=current_value+increment WHERE name = seq_name;
RETURN currval(seq_name);
END $
DELIMITER ;

DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
    DETERMINISTIC
BEGIN
    DECLARE VALUE INTEGER;
    SET VALUE = 0;
    SELECT current_value INTO VALUE
        FROM plat_sequence
        WHERE NAME = seq_name;
    RETURN VALUE;
END $
DELIMITER ;

-- 添加日期字段（用于按天重置）
alter table plat_sequence add `day` date DEFAULT NULL COMMENT '日期' after `year`;

-- 创建按天重置序列号的函数
DROP FUNCTION IF EXISTS next_day_reset_val;
DELIMITER $
CREATE FUNCTION next_day_reset_val (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN
    -- 如果日期不是今天，重置序列为0
    UPDATE plat_sequence SET current_value=0, `day`=CURDATE() WHERE `day` IS NULL OR `day` != CURDATE();
    -- 递增序列号
    UPDATE plat_sequence SET current_value=current_value+increment WHERE name = seq_name;
    RETURN currval(seq_name);
END $
DELIMITER ;

-- INSERT INTO plat_sequence VALUES ('customer_code', 1000, 1, '', 1000, '系统管理员', '2023-02-09 10:27:42', null, null, null, null);
-- INSERT INTO plat_sequence VALUES ('contract_code', 1000, 1, '', 1000, '系统管理员', '2023-02-09 10:27:42', null, null, null, null);
-- INSERT INTO plat_sequence VALUES ('consult_code', 1000, 1, null, '', 1000, '系统管理员', '2023-02-09 10:27:42', null, null, null, null);
-- select `nextval`('customer_code');