下订单存储过程

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;