MySQL 저장 프로시저로 행 값을 1씩 증가·감소시키는 방법

MySQL에서는 저장 프로시저(Stored Procedure)를 활용하면 여러 UPDATE 작업을 하나의 로직으로 묶어 손쉽게 실행할 수 있습니다. 이번 글에서는 저장 프로시저를 사용해 특정 행의 값을 1씩 늘리거나 줄이는 방법을 테이블 생성부터 결과 확인까지 단계별로 살펴보겠습니다.

1. 테이블 생성하기

먼저 행 값을 증가·감소시킬 테이블을 생성합니다. 아래 쿼리를 실행해 보세요.

mysql> create table IncrementAndDecrementValue
    -> (
    -> UserId int,
    -> UserScores int
    -> );
Query OK, 0 rows affected (0.60 sec)

위 테이블은 사용자 ID(UserId)와 점수(UserScores) 두 개의 컬럼으로 구성되어 있습니다.

2. 샘플 데이터 삽입하기

INSERT 명령어를 사용해 테이블에 레코드를 추가합니다.

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. 데이터 조회하기

SELECT 문으로 테이블에 저장된 모든 레코드를 확인합니다.

mysql> select *from IncrementAndDecrementValue;

실행 결과는 다음과 같습니다.

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

4. 저장 프로시저 생성하기

이제 한 행의 값은 1 감소시키고, 다른 행의 값은 1 증가시키는 저장 프로시저를 만들어 보겠습니다.

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 ;

프로시저의 동작 순서는 다음과 같습니다.

DECLARE 문으로 정수형 변수 first와 second를 선언합니다.
SET 문으로 UserId가 101인 행의 현재 점수와 102인 행의 현재 점수를 각각 변수에 저장합니다.
UPDATE 문으로 UserId 101의 점수에서 1을 빼고, UserId 102의 점수에 1을 더합니다.

참고로 DELIMITER //는 프로시저 본문 내부의 세미콜론(;)이 일반 쿼리 종료 기호로 해석되지 않도록 구분 기호를 임시로 변경하는 명령입니다. 프로시저 생성이 끝나면 반드시 DELIMITER ;로 원래대로 되돌려야 합니다.

5. 저장 프로시저 호출하기

CALL 명령어로 생성한 프로시저를 실행합니다.

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

6. 결과 확인하기

SELECT 문으로 행 값이 정상적으로 업데이트되었는지 확인합니다.

mysql> select *from IncrementAndDecrementValue;

실행 결과는 다음과 같습니다.

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

정리

실행 결과를 보면 UserId 101의 점수는 20000에서 19999로 1 감소했고, UserId 102의 점수는 30000에서 30001로 1 증가했습니다. UserId 103의 값은 프로시저에서 다루지 않았기 때문에 그대로 유지됩니다.

이처럼 저장 프로시저를 사용하면 여러 UPDATE 문을 하나의 이름으로 묶어 재사용할 수 있으며, 애플리케이션 코드 없이도 데이터베이스 내부에서 복잡한 값 변경 로직을 처리할 수 있다는 장점이 있습니다.