Mysql存储过程订单生成

最新推荐文章于 2022-12-22 23:09:46 发布

奋斗中的老青年

最新推荐文章于 2022-12-22 23:09:46 发布

阅读量780

点赞数

CC 4.0 BY-SA版权

分类专栏： # MySQL

本文链接：https://blog.youkuaiyun.com/weixin_41131531/article/details/90407488

MySQL 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了如何使用MySQL存储过程来生成订单号记录，详细解析了存储过程的实现步骤，帮助读者理解如何在数据库层面高效管理订单编号。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

订单号生成记录表：

drop table if exists other_order_record_sequence;
create table other_order_record_sequence(
	id bigint(20) primary key auto_increment,
	order_record_sequence_type varchar(5) not null comment '订单类型',
	seq_date date not null comment '日期',
	sequence_count int(11)  not null comment '订单数',
	belonged_company bigint(20) not null comment '公司id'
)engine = innodb default charset = utf8mb4 comment '单号记录表';

订单生成存储过程：

drop procedure if exists get_order_number;
DELIMITER ;;
-- order_type_ordinal订单类型标注，order_type_code订单类型在单号中的标识，company_id公司id
create procedure get_order_number(IN order_type_ordinal varchar(5),IN order_type_code varchar(5),IN company_id bigint(20));
begin
	DECLARE exist_sequence int default 1;
    DECLARE sequence_number int(11);
    DECLARE t_error INTEGER DEFAULT 0;  
    DECLARE cur_sequence CURSOR FOR select sequence_count + 1 from other_order_record_sequence where belonged_company = company_id and seq_date = curdate() and order_record_sequence_type = order_type_ordinal limit 1 for update;
    DECLARE continue HANDLER FOR NOT FOUND SET exist_sequence = 0;
    open cur_sequence;
    fetch cur_sequence into sequence_number;
    close cur_sequence;
    if exist_sequence = 0 then
        set sequence_number = 1; 
        insert into other_order_record_sequence(order_record_sequence_type,sequence_count,belonged_company,seq_date) values (order_type_ordinal,sequence_number,company_id,curdate());
    else
        update other_order_record_sequence set sequence_count = sequence_number where belonged_company = company_id and seq_date = curdate() and order_record_sequence_type = order_type_ordinal;
    end if;
    select CONCAT(replace(curdate(),'-',''),order_type_code,LPAD(company_id,5,0),LPAD(sequence_number,5,0)); 
end ;;
DELIMITER ;;

更多文章：
点击跳转优快云博客
 点击跳转简书博客
公众号：代码小搬运
代码小搬运.jpg