Tăng/giảm giá trị hàng MySQL bằng Stored Procedure

Ví dụ: Procedure tăng/giảm điểm user

1. Tạo bảng

mysql> CREATE TABLE IncrementAndDecrementValue ( 
-> UserId INT, 
-> UserScores INT 
-> ); 
Query OK, 0 rows affected (0.60 sec)

2. Chèn dữ liệu

mysql> INSERT INTO IncrementAndDecrementValue VALUES(101,20000); 
Query OK, 1 row affected (0.13 sec) 
mysql> INSERT INTO IncrementAndDecrementValue VALUES(102,30000); 
Query OK, 1 row affected (0.20 sec) 
mysql> INSERT INTO IncrementAndDecrementValue VALUES(103,40000); 
Query OK, 1 row affected (0.11 sec)

3. Dữ liệu gốc

mysql> SELECT * FROM IncrementAndDecrementValue;

+--------+------------+ 
| UserId | UserScores | 
+--------+------------+ 
| 101    | 20000      | 
| 102    | 30000      | 
| 103    | 40000      | 
+--------+------------+ 
3 rows in set (0.00 sec)

4. Tạo Stored Procedure

mysql> DELIMITER // 
mysql> CREATE PROCEDURE IncrementAndDecrementRowValueByOne() 
-> BEGIN 
-> DECLARE first INT; 
-> DECLARE second INT; 
-> SET first = (SELECT UserScores FROM IncrementAndDecrementValue WHERE UserId = 101); 
-> SET second = (SELECT UserScores FROM IncrementAndDecrementValue WHERE UserId = 102); 
-> UPDATE IncrementAndDecrementValue SET UserScores = first - 1 WHERE UserId = 101; 
-> UPDATE IncrementAndDecrementValue SET UserScores = second + 1 WHERE UserId = 102; 
-> END // 
Query OK, 0 rows affected (0.17 sec) 
mysql> DELIMITER ;

5. Gọi Procedure

mysql> CALL IncrementAndDecrementRowValueByOne(); 
Query OK, 1 row affected (0.24 sec)

6. Kết quả

mysql> SELECT * FROM IncrementAndDecrementValue;

+--------+------------+ 
| UserId | UserScores | 
+--------+------------+ 
| 101    | 19999      | 
| 102    | 30001      | 
| 103    | 40000      | 
+--------+------------+ 
3 rows in set (0.00 sec)

Phân tích Procedure

• Lấy giá trị hiện tại của UserId=101 và 102 vào biến
• UserId=101: Giảm 1 (20000 → 19999)
• UserId=102: Tăng 1 (30000 → 30001)
• UserId=103: Không thay đổi

Cách viết gọn hơn (Không cần biến trung gian)

CREATE PROCEDURE IncrementDecrementSimple() 
BEGIN 
    UPDATE IncrementAndDecrementValue SET UserScores = UserScores - 1 WHERE UserId = 101; 
    UPDATE IncrementAndDecrementValue SET UserScores = UserScores + 1 WHERE UserId = 102; 
END;

Procedure có tham số (Linh hoạt hơn)

CREATE PROCEDURE AdjustUserScore(IN p_user_id INT, IN p_delta INT) 
BEGIN 
    UPDATE IncrementAndDecrementValue 
    SET UserScores = UserScores + p_delta 
    WHERE UserId = p_user_id; 
END;

Gọi procedure tham số

CALL AdjustUserScore(101, -1);  -- Giảm 1
CALL AdjustUserScore(102, 5);   -- Tăng 5

Xử lý giao dịch (Transaction)

CREATE PROCEDURE TransferScore(IN from_id INT, IN to_id INT, IN amount INT) 
BEGIN 
    DECLARE from_score INT; 
    
    START TRANSACTION; 
    
    SELECT UserScores INTO from_score FROM IncrementAndDecrementValue WHERE UserId = from_id FOR UPDATE; 
    
    IF from_score >= amount THEN 
        UPDATE IncrementAndDecrementValue SET UserScores = UserScores - amount WHERE UserId = from_id; 
        UPDATE IncrementAndDecrementValue SET UserScores = UserScores + amount WHERE UserId = to_id; 
        COMMIT; 
    ELSE 
        ROLLBACK; 
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Không đủ điểm để chuyển'; 
    END IF; 
END;

Lưu ý

• Procedure phù hợp cho logic nghiệp vụ phức tạp, nhiều bước
• Đơn giản chỉ UPDATE: Nên dùng câu lệnh SQL trực tiếp từ ứng dụng
• Luôn test logic với SELECT trước khi PUT vào procedure
• Tránh logic quá phức tạp trong DB - chuyển sang application layer