首页 > 代码库 > 订单支付成功后存储过程 - MYSQL

订单支付成功后存储过程 - MYSQL

BEGIN    SET @userId = (SELECT user_id FROM t_shoporder WHERE id = orderId);    /*修改订单状态,改成已支付*/    UPDATE t_shoporder SET `status` = 1,update_time = NOW() WHERE id = orderId;    /*查询用户是否已经学习改课程*/    SET @count = (SELECT count(1) FROM t_course_user WHERE course_id = courseId AND user_id = @userId);    IF @count = 0 THEN        /*保存用户学习课程的记录*/        INSERT INTO t_course_user (course_id, user_id, STATUS, finished) VALUES(courseId, @userId,1,0);    END IF;    /*检查该课程是否存在对应的班级*/    SET @classId = (SELECT id FROM t_class WHERE course_id = courseId);    /*如果存在就进行学习课程进入班级*/    IF @classId = IS NOT NULL THEN        /*检查学生是否已经加入到该班级了,如果是就不再加入*/        SET @ccount = (SELECT COUNT(1) FROM t_classstudent WHERE class_id = @classId AND user_id = @userId);        IF @ccount = 0 THEN            /**将学生自动加入对应vip教室班级*/            INSERT INTO t_classstudent(class_id, user_id, course_id,stuno) VALUES(@classId, @userId, courseId, CONCAT("ms_",DATE_FORMAT(NOW(),%Y%m%d),"_",courseId, "_"),?);        END IF ;        /*用户升级为vip和用户积分、等级的累加*/        SET @type = (SELECT type FROM t_user WHERE id = @userId);        /*学生升级权限*/        IF @type = 1 THEN             UPDATE t_user SET type = 5,jifen = jifen + 100 WHERE id = @userId;        ELSE            UPDATE t_user SET jifen = jifen + 100 WHERE id = @userId;        END IF;        INSERT INTO t_user_jifen(            user_id,            score,            description,            type,            mark        )VALUES(            @userId,            100,            支付订单积分加+100分,            1,            1        );        SELECT @userId;END

 

订单支付成功后存储过程 - MYSQL