SELECT workflow_id INTO workflowId FROM orders WHERE id=orderId;
INSERT INTO order_detail(order_id,step_id,user_id,valid,submit_time)VALUES(orderId,stepId,userId,1,CURRENT_TIMESTAMP); DELETE FROM next_step WHERE order_id=orderId AND step_id=stepId; INSERT INTO next_step(order_id,step_id,valid)SELECT orderId,end_step_id,1 FROM step_line WHERE start_step_id=stepId; SELECT id ,order_number INTO stepLineId,orderNumber FROM step_line WHERE start_step_id=stepId;
INSERT INTO next_step(order_id,step_id,valid)SELECT orderId,start_step_id,1 FROM step_line WHERE rely_id=(stepLineId);
l:BEGIN DECLARE cursor_step CURSOR FOR SELECT start_step_id FROM step_line WHERE order_number<=orderNumber; DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1; OPEN cursor_step; REPEAT FETCH cursor_step INTO tempStepId; BEGIN DECLARE tempOrderDetailId INT; SELECT id INTO tempOrderDetailId FROM order_detail WHERE order_id=orderId AND step_id=tempStepId; IF tempOrderDetailId IS NULL THEN CLOSE cursor_step; LEAVE l; END IF; END; UNTIL stopFlag = 1 END REPEAT; CLOSE cursor_step; END l;
INSERT INTO next_step(order_id,step_id,valid)SELECT orderId,start_step_id,1 FROM step_line WHERE step_line.order_number=(SELECT MIN(order_number) FROM step_line WHERE order_number>orderNumber AND workflow_id=workflowId) AND workflow_id=workflowId;
2 修改业务逻辑 不要再一个事务中有两个以上sql
SELECT workflow_id INTO workflowId FROM orders WHERE id=orderId;
INSERT INTO order_detail(order_id,step_id,user_id,valid,submit_time)VALUES(orderId,stepId,userId,1,CURRENT_TIMESTAMP);
DELETE FROM next_step WHERE order_id=orderId AND step_id=stepId;
INSERT INTO next_step(order_id,step_id,valid)SELECT orderId,end_step_id,1 FROM step_line WHERE start_step_id=stepId;
SELECT id ,order_number INTO stepLineId,orderNumber FROM step_line WHERE start_step_id=stepId;
INSERT INTO next_step(order_id,step_id,valid)SELECT orderId,start_step_id,1 FROM step_line WHERE rely_id=(stepLineId);
l:BEGIN
DECLARE cursor_step CURSOR FOR SELECT start_step_id FROM step_line WHERE order_number<=orderNumber;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;
OPEN cursor_step;
REPEAT
FETCH cursor_step INTO tempStepId;
BEGIN
DECLARE tempOrderDetailId INT;
SELECT id INTO tempOrderDetailId FROM order_detail WHERE order_id=orderId AND step_id=tempStepId;
IF tempOrderDetailId IS NULL THEN
CLOSE cursor_step;
LEAVE l;
END IF;
END;
UNTIL stopFlag = 1
END REPEAT;
CLOSE cursor_step;
END l;
INSERT INTO next_step(order_id,step_id,valid)SELECT orderId,start_step_id,1 FROM step_line WHERE step_line.order_number=(SELECT MIN(order_number) FROM step_line WHERE order_number>orderNumber AND workflow_id=workflowId) AND workflow_id=workflowId;
COMMIT;next_step表被锁了,只能查询,不能进行增删改操作。