개요
저장 프로시저 안에서 세션 변수(@변수)를 사용해 값을 할당하면, 이후 레코드가 업데이트될 때 변수를 다시 조회·할당하는 순간 값이 함께 바뀌어 버립니다. 이번 글에서는 이러한 현상을 예제로 확인하고, 변수 값이 변경되지 않도록 만드는 방법까지 살펴보겠습니다.
1. 테이블 생성
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Value int
);
Query OK, 0 rows affected (0.63 sec)
2. 데이터 삽입 및 조회
INSERT 명령으로 레코드를 추가합니다.
mysql> insert into DemoTable(Value) values(100); Query OK, 1 row affected (0.13 sec)
SELECT 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select *from DemoTable;
출력 결과
+----+-------+ | Id | Value | +----+-------+ | 1 | 100 | +----+-------+ 1 row in set (0.00 sec)
3. 저장 프로시저 작성
다음은 업데이트 전후의 값을 조회하는 저장 프로시저입니다. 여기서는 세션 변수 @myValue에 ':=' 연산자로 값을 반복해서 할당하고 있습니다.
mysql> DELIMITER //
mysql> CREATE PROCEDURE updateValue100()
BEGIN
DECLARE myValue int;
select @myValue :=(select Value from DemoTable where Id=1);
select @myValue;
update DemoTable set Value=200 where Id=1;
select @myValue :=(select Value from DemoTable where Id=1);
select @myValue;
END
//
Query OK, 0 rows affected (0.21 sec)
mysql> DELIMITER ;
4. 프로시저 호출
CALL 명령으로 저장 프로시저를 실행합니다.
mysql> call updateValue100();
출력 결과
+-------------------------------------------------------+ | @myValue :=(select Value from DemoTable where Id=1) | +-------------------------------------------------------+ | 100 | +-------------------------------------------------------+ 1 row in set (0.00 sec) +----------+ | @myValue | +----------+ | 100 | +----------+ 1 row in set (0.01 sec) +-------------------------------------------------------+ | @myValue :=(select Value from DemoTable where Id=1) | +-------------------------------------------------------+ | 200 | +-------------------------------------------------------+ 1 row in set (0.16 sec) +----------+ | @myValue | +----------+ | 200 | +----------+ 1 row in set (0.17 sec) Query OK, 0 rows affected (0.18 sec)
5. 원인 분석과 해결 방법
실행 결과를 보면, UPDATE 이후 @myValue가 100에서 200으로 바뀐 것을 확인할 수 있습니다. 그 이유는 '@'가 붙은 변수는 세션(사용자 정의) 변수이며, 프로시저 안에서 ':=' 연산자로 SELECT 결과를 다시 할당했기 때문입니다. 즉, 테이블 값이 200으로 변경된 뒤 동일한 할당문이 한 번 더 실행되면서 변수 값마저 덮어써진 것입니다.
레코드가 업데이트되더라도 변수 값이 변경되지 않게 하려면 다음 두 가지 방법을 사용할 수 있습니다.
- DECLARE로 선언한 지역 변수 사용: 프로시저 내부에서 DECLARE로 선언한 변수는 SELECT ... INTO 구문으로 한 번만 값을 담아두면, 이후 테이블이 변경되어도 그 값이 그대로 유지됩니다.
- 업데이트 후 재할당하지 않기: UPDATE 문 이후에 변수를 다시 할당하는 SELECT 문을 제거하면 기존 값이 보존됩니다.
예를 들어 다음과 같이 지역 변수를 활용하면 업데이트 전의 값(100)이 끝까지 유지됩니다.
mysql> DELIMITER //
mysql> CREATE PROCEDURE keepOldValue()
BEGIN
DECLARE myValue int;
select Value into myValue from DemoTable where Id=1;
select myValue; -- 100 출력
update DemoTable set Value=200 where Id=1;
select myValue; -- 여전히 100 출력
END
//
Query OK, 0 rows affected (0.21 sec)
mysql> DELIMITER ;
이처럼 세션 변수(@변수)와 ':=' 재할당 방식 대신 DECLARE 지역 변수와 SELECT ... INTO 구문을 사용하면, 레코드가 업데이트되더라도 프로시저 안의 변수 값은 처음 읽어 온 상태 그대로 유지할 수 있습니다.