订单支付成功后存储过程 - MYSQL
时间：2021-07-01 10:21:17
帮助过：4人阅读
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
标签：
