MySQL에서는 CREATE FUNCTION 명령어를 사용하여 사용자 정의 함수를 직접 만들 수 있습니다. 함수를 활용하면 반복적인 쿼리 로직을 하나의 이름으로 묶어 재사용할 수 있어 코드의 가독성과 유지보수성이 크게 향상됩니다.
1. 기본 문법
함수 생성 시 사용하는 기본 문법은 다음과 같습니다.
delimiter //
DROP FUNCTION if exists yourFunctionName;
CREATE FUNCTION yourFunctionName(Parameter1,...N) returns type
BEGIN
# 변수 선언;
# MySQL 실행문
END //
delimiter ;
delimiter //는 세미콜론(;)과 충돌하지 않도록 구분 기호를 임시로 변경하는 것이며, 함수 작성이 끝나면 delimiter ;로 원래대로 되돌려 주어야 합니다.
2. 예제용 테이블 생성 및 데이터 입력
먼저 실습에 사용할 테이블을 만들고 데이터를 추가하겠습니다. 아래는 테이블을 생성하는 쿼리입니다.
mysql> create table ViewDemo
-> (
-> Id int,
-> Name varchar(200),
-> Age int
-> );
Query OK, 0 rows affected (0.58 sec)
INSERT 명령어로 레코드를 삽입합니다.
mysql> insert into ViewDemo values(1,'John',23);
Query OK, 1 row affected (0.15 sec)
mysql> insert into ViewDemo values(2,'Sam',24);
Query OK, 1 row affected (0.15 sec)
SELECT 문으로 전체 데이터를 확인합니다.
mysql> select *from ViewDemo;
실행 결과는 다음과 같습니다.
+------+------+------+
| Id | Name | Age |
+------+------+------+
| 1 | John | 23 |
| 2 | Sam | 24 |
+------+------+------+
2 rows in set (0.00 sec)
3. 간단한 검색 함수 만들기
이제 정수형 매개변수를 받아 문자열을 반환하는 함수를 만들어 보겠습니다. 이 함수의 목적은 주어진 ID로 레코드를 검색하는 것입니다. ID가 테이블에 존재하면 해당 이름(Name)을 반환하고, 존재하지 않으면 "No Name Found For This Id"라는 메시지를 반환합니다.
참고로 바이너리 로깅이 활성화된 환경에서 함수를 생성하려면 log_bin_trust_function_creators 설정이 필요할 수 있습니다.
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> drop function if exists searchRecord;
->
-> create function searchRecord(yourId int) returns char(100)
-> begin
-> declare Name1 char(100) default "No Name Found For This Id";
-> select Name into Name1 from ViewDemo where Id =yourId;
-> return Name1;
-> end //
Query OK, 0 rows affected (0.21 sec)
Query OK, 0 rows affected (0.33 sec)
mysql> delimiter ;
함수 내부를 살펴보면, 먼저 declare로 기본값이 지정된 변수를 선언한 뒤, SELECT ... INTO 구문으로 조회 결과를 변수에 담아 반환하는 구조입니다. 조건에 맞는 행이 없으면 기본값 그대로 반환되므로 별도의 예외 처리 없이도 '검색 결과 없음' 상황을 자연스럽게 처리할 수 있습니다.
4. 함수 동작 확인
이제 함수가 올바르게 작동하는지 두 가지 경우로 나누어 테스트해 보겠습니다.
Case 1: 존재하는 ID를 입력한 경우
mysql> select searchRecord(2) as Found;
ID 2에 해당하는 이름이 정상적으로 반환됩니다.
+-------+
| Found |
+-------+
| Sam |
+-------+
1 row in set (0.00 sec)
Case 2: 존재하지 않는 ID를 입력한 경우
mysql> select searchRecord(100) as Found;
해당 레코드가 없으므로 기본 메시지가 출력됩니다.
+---------------------------+
| Found |
+---------------------------+
| No Name Found For This Id |
+---------------------------+
1 row in set (0.00 sec)
마무리
이처럼 MySQL의 CREATE FUNCTION을 활용하면 특정 비즈니스 로직을 데이터베이스 차원에서 캡슐화할 수 있습니다. 매개변수 타입, 반환 타입, DECLARE를 통한 변수 선언 등 기본 구조만 익히면 다양한 형태의 함수를 손쉽게 확장해 사용할 수 있습니다.