테이블의 동적 데이터를 활용하는 MySQL 저장 함수 만드는 방법

MySQL 저장 함수와 테이블 데이터 활용

MySQL의 저장 함수(Stored Function)는 테이블을 참조할 수 있지만, 결과 집합(result set)을 반환하는 문장은 사용할 수 없습니다. 즉, 결과 집합을 그대로 반환하는 SELECT 쿼리는 함수 내에서 사용할 수 없다는 의미입니다.

하지만 SELECT INTO 구문을 사용하면 이러한 제한을 우회할 수 있습니다. SELECT INTO는 조회한 값을 결과 집합으로 반환하는 대신, 변수에 직접 저장해 주기 때문입니다.

예제: 학생 평균 점수 계산 함수 만들기

다음 예제에서는 'Student_marks' 테이블의 동적 데이터를 활용하여 학생별 과목 평균 점수를 계산하는 함수 'Avg_marks'를 만들어 보겠습니다. 먼저 테이블에 저장된 데이터는 다음과 같습니다.

mysql> Select * from Student_marks;
+-------+------+---------+---------+---------+
| Name  | Math | English | Science | History |
+-------+------+---------+---------+---------+
| Raman |   95 |      89 |      85 |      81 |
| Rahul |   90 |      87 |      86 |      81 |
+-------+------+---------+---------+---------+
2 rows in set (0.00 sec)

저장 함수 생성

이제 SELECT INTO를 사용하여 특정 학생의 네 과목 점수를 변수에 담고, 그 평균을 반환하는 함수를 생성합니다.

mysql> DELIMITER //
mysql> Create Function Avg_marks(S_name Varchar(50))
    -> RETURNS INT
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE M1,M2,M3,M4,avg INT;
    -> SELECT Math,English,Science,History INTO M1,M2,M3,M4 FROM Student_marks WHERE Name = S_name;
    -> SET avg = (M1+M2+M3+M4)/4;
    -> RETURN avg;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

함수의 주요 구성 요소를 살펴보면 다음과 같습니다.

RETURNS INT: 함수가 정수형 값을 반환함을 선언합니다.
DETERMINISTIC: 동일한 입력값에 대해 항상 동일한 결과를 반환하는 함수임을 명시합니다. 바이너리 로깅이 활성화된 환경에서 함수 생성 시 필요할 수 있습니다.
SELECT ... INTO: 조회한 네 과목의 점수를 각각 M1, M2, M3, M4 변수에 저장합니다.
RETURN avg: 계산된 평균값을 반환합니다.

함수 호출 및 결과 확인

생성한 함수를 호출하여 각 학생의 평균 점수를 확인해 보겠습니다.

mysql> Select Avg_marks('Raman') AS 'Raman_Marks';
+-------------+
| Raman_Marks |
+-------------+
|          88 |
+-------------+
1 row in set (0.07 sec)

mysql> Select Avg_marks('Rahul') AS 'Rahul_Marks';
+-------------+
| Rahul_Marks |
+-------------+
|          86 |
+-------------+
1 row in set (0.00 sec)

이처럼 SELECT INTO 구문을 활용하면 저장 함수 내에서도 테이블의 동적 데이터를 참조하여 원하는 값을 계산하고 반환할 수 있습니다. 이 방법은 특정 조건에 따라 데이터를 조회한 뒤 가공된 결과를 반환해야 하는 다양한 상황에서 유용하게 사용됩니다.