저장 함수(stored function)는 기본적으로 결과값을 반환할 때 가장 유용하게 사용됩니다. 하지만 테이블에 값을 삽입(INSERT)하거나 업데이트(UPDATE)하는 것처럼 데이터를 조작하는 작업을 수행하는 저장 함수를 만들 수도 있습니다. 이 경우 함수의 동작 방식은 저장 프로시저(stored procedure)와 크게 다르지 않습니다.
테이블을 업데이트하는 저장 함수의 개념
일반적으로 저장 함수는 SELECT 문처럼 값을 계산하여 반환하는 용도로 쓰이지만, 함수 내부에 UPDATE 문을 포함시키면 특정 조건에 맞는 행의 값을 수정하고, 그 결과로 성공 여부를 나타내는 값을 반환하도록 만들 수 있습니다. 아래 예제에서는 student_marks 테이블의 점수를 업데이트하는 저장 함수 tbl_Update를 생성하는 과정을 살펴보겠습니다.
1단계: 현재 테이블 데이터 확인
먼저 student_marks 테이블의 현재 상태를 조회합니다. Saurabh 학생의 점수가 모두 NULL로 되어 있는 것을 확인할 수 있습니다.
mysql> Select * from student_marks// +---------+------+---------+---------+---------+ | Name | Math | English | Science | History | +---------+------+---------+---------+---------+ | Raman | 95 | 89 | 85 | 81 | | Rahul | 90 | 87 | 86 | 81 | | Mohit | 90 | 85 | 86 | 81 | | Saurabh | NULL | NULL | NULL | NULL | +---------+------+---------+---------+---------+ 4 rows in set (0.00 sec)
2단계: 저장 함수 tbl_Update 생성
다음으로 학생 이름과 네 과목의 점수를 매개변수로 받아 해당 학생의 레코드를 업데이트하는 저장 함수를 생성합니다. 함수는 성공 시 1을 반환하며, DETERMINISTIC 옵션을 지정하여 이진 로깅 환경에서도 안전하게 사용할 수 있도록 합니다.
mysql> Create Function tbl_Update(S_name Varchar(50),M1 INT,M2 INT,M3 INT,M4 INT)
-> RETURNS INT
-> DETERMINISTIC
-> BEGIN
-> UPDATE student_marks SET Math = M1,English = M2, Science = M3, History =M4 WHERE Name = S_name;
-> RETURN 1;
-> END //
Query OK, 0 rows affected (0.03 sec)3단계: 함수 호출로 값 업데이트 실행
생성된 함수를 호출하여 Saurabh 학생의 점수를 새로운 값으로 업데이트합니다. 함수가 정상적으로 실행되면 반환값 1이 출력됩니다.
mysql> Select tbl_update('Saurabh',85,69,75,82);
+------------------------------------+
| tbl_update('Saurabh',85,69,75,82) |
+------------------------------------+
| 1 |
+------------------------------------+
1 row in set (0.07 sec)4단계: 업데이트 결과 확인
다시 테이블을 조회하면 Saurabh 학생의 점수가 NULL에서 각각 85, 69, 75, 82로 변경된 것을 확인할 수 있습니다.
mysql> Select * from Student_marks; +---------+------+---------+---------+---------+ | Name | Math | English | Science | History | +---------+------+---------+---------+---------+ | Raman | 95 | 89 | 85 | 81 | | Rahul | 90 | 87 | 86 | 81 | | Mohit | 90 | 85 | 86 | 81 | | Saurabh | 85 | 69 | 75 | 82 | +---------+------+---------+---------+---------+ 4 rows in set (0.00 sec)
정리
이처럼 MySQL 저장 함수 내부에 UPDATE 문을 작성하면, 함수 호출 한 번으로 특정 조건의 데이터를 손쉽게 수정할 수 있습니다. 다만 저장 함수는 본래 값을 반환하는 용도로 설계되었기 때문에, 단순히 데이터만 조작하고 반환값이 필요 없는 경우에는 저장 프로시저를 사용하는 것이 더 적합합니다. 또한 함수 내에서 DML 문을 사용할 때는 바이너리 로깅 설정에 따라 DETERMINISTIC 또는 READS SQL DATA 같은 특성 선언이 필요할 수 있으므로 참고하시기 바랍니다.