首页 > 代码库 > 下订单存储过程 - MYSQL

下订单存储过程 - MYSQL

BEGIN    DECLARE smark INT;    DECLARE orderId INT;    /*查询课程是否存在,如果不存在就不执行订单操作了*/    SET @count = (SELECT count(1) FROM t_course WHERE id = courseId);    IF @count = 0 THEN         SELECT "noexist";    ELSE         /*查询某个课程是否已经报名,如果已经报名了就不需要在报名了*/        SET @c1 = (SELECT COUNT(1) FROM t_shoporder sd WHERE sd.user_id = userId AND sd.course_id = courseId AND sd.is_delete = 0); /*我报名的课程*/        IF @c1 = 0 THEN            /*如果没有报名*/            /*订单号的设定*/            SET @orderNum = CONVERT(CONCAT(                            "ms_",DATE_FORMAT(NOW(),%Y%m%d),                            courseId,                            CEIL(RAND() * 99999),                            userId                        ),CHARACTER);            SET @price = (SELECT tprice FROM t_course WHERE id = courseId);            /*保存订单*/            INSERT INTO t_shoporder (                user_id,                is_delete,                num,                price,                description,                ip,                ipAddress,                order_number,                STATUS,                course_id,                type            )VALUES(                userId,                0,                tnum,                @price,                CONCAT("用户【",username,"】,在",DATE_FORMAT(NOW(),%Y-%m-%d),"位于",ip,"/",ipAddress,"提交订单,数量是",tnum,",金额是:¥",@price),                ip,                ipAddress,                @orderNum,                0,                courseId,                NULL            );            /*查询当前订单的最后一条ID*/            SET @orderId = (SELECT id FROM t_shoporder WHERE order_number = @orderNum);            /*拼接订单号,确保唯一*/            SET @onumber = CONVERT(CONCAT(@orderNum,@orderId), CHARACTER);            /*修改订单号*/            UPDATE t_shoporder SET order_number = @onumber WHERE id = @orderId;            /*返回订单和状态*/            SELECT CONCAT(@orderId,"#",0);        ELSE            SELECT ts.id, ts.status INTO orderId, smark FROM t_shoporder ts WHERE ts.is_delete = 0 AND ts.course_id = courseId AND ts.user_id userId;            IF smark = 0 THEN                SELECT CONCAT(orderId,"#",0);        /*已经支付了*/            ELSE                 SELECT CONCAT(orderId,"#",1);        /*已经支付了*/            END IF;        END IF;    END IF;END;

 

下订单存储过程 - MYSQL