下訂單預存程序 - 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