Stored Function이란?
MySQL에서의 Stored Function, 보통 Function이라 불리는 기능은 SQL의 모음으로 서버에 저장한 후 함수 호출을 통해 해당 SQL 문들을 실행시키는 기능입니다. 프로그래밍을 해본 사람들은 쉽게 이해할 수 있는 이름이자 기능으로, 자신이 사용하는 언어에 존재하는 function이나 method의 SQL 버전이라고 이해하면 쉬울 것입니다.
조금 더 자세히 알아보자면, MySQL에는 Stored Routine이라는 기능을 제공합니다. 이는 앞서 설명했던 SQL의 모음(set of SQL Statements)으로 서버에 저장되어 사용됩니다. Stored Routine의 하위 개념으로 많이 들어봤을 Stored Procedure와 이 글에서 다루는 Stored Function이 있다. 공식 문서에 따르면, 다음과 같은 장점이 있다고 합니다.
- 복수의 이용자들이 같은 DB를 이용하는 경우 동일한 SQL구문을 이용할 수 있음
- SQL의 내부 구현을 모르게 되어 보안적 측면의 강화
- 클라이언트에서 DB로 많은 요청을 보내지 않아도 되어 성능 향상 가능성. (단, DB의 자원을 더 많이 사용)
이런 특징들은 Stored Function 역시 동일하게 가지고 있습니다. Stored Function은 이후 특징 부분에서 자세히 살펴보겠지만, Stored Procedure에 비해서 좀 더 간단한 작업을 진행합니다. MySQL에서 제공하는 built-in function(`now()`, `if()`)와 비슷한 역할을 한다고 보시면 될 것 같습니다.
Stored Function에 대한 개념이 얼추 이해가 됐다고 생각하며, Stored Function에 대한 사용법을 알아봅시다.
이 글에서 다루는 Stored Function에 대해서 다수의 사람들이 Function이라고 표현합니다. 하지만 제가 조사한 바로는 MySQL에는 3가지의 Function(Built-in Function, Stored Function, Loadable Function)이 존재하기 때문에, 이 글에서는 혼동을 방지하기 위해 Stored Function임을 명시하도록 하겠습니다.
Stored Function 사용법
Stored Function에 대해서 알아보기 이전에, 이 글에서 사용될 예시 테이블에 대해서 설명드리겠습니다.
우선 `학생(student)` 테이블을 이용할 것이고, 이 학생은, 학생번호, 이름, 출생일, 입학 연도, 성별이 저장되어 있습니다. 학생의 속성들을 이용해 원하는 값을 리턴하는데 stored function을 이용해서 진행해 봅시다.
전체 ddl은 다음과 같습니다.
create table student(
student_id int auto_increment not null,
name varchar(25),
birth_year int,
register_year int,
gender char(6),
primary key (student_id)
);
그리고 아래 insert문을 이용해 3개의 데이터를 넣어놨으니, 이후 있을 함수의 결과에서 기억해 주세요.
insert into student(name, birth_year, register_year, gender) values ('손흥민' , 1992, 2022, 'male'),
('김연아', 1990, 2020, 'female'),
('박지성' , 1981, 2024, 'male');
생성
Stored Function을 생성하는 방법은, DB의 모든 기능들과 마찬가지로 `CREATE`로 시작합니다. `Create Function`으로 시작하며 옵션들을 입력합니다. 전체 옵션은 아래와 같습니다.
CREATE
[DEFINER = user]
FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
SQL routine
위의 코드 중에서 `func_parameter` 윗부분이 함수 생성을 위한 SQL이고, 아래는 옵션 값에 대한 설명입니다. 각 옵션에 대한 세부 설명은 아래와 같습니다.
- func_paramter : 함수에서 사용할 파라미터입니다. 파라미터의 이름과 데이터 타입을 입력하시면 됩니다. 예를 들면 age int 이런 느낌입니다.
- type : 앞서서 봤던 func_parameter나 RETURNS에서 사용되는 것으로, 데이터 타입을 명시합니다. 당연히 파라미터나, 리턴 값이 지정한 타입과 다르다면 오류가 발생합니다.
- RETURNS : 이 stored function에서 리턴할 값의 타입을 명시합니다.
- characteristic : 이 함수의 특성에 대해서 설명합니다. 각각의 세부 내용은 다음과 같습니다.
- COMMENT : 이 함수를 설명하는 문구입니다. 주석과 같이 생각하시면 될 것 같네요
- LANGUAGE : 이 함수를 정의하는 언어를 표기합니다. 이는 서버에서 무시하며, mysql의 경우 SQL만을 사용하기 때문입니다. PostgreSQL의 경우 PL/JAVA, PL/Python 등을 이용해 루틴을 생성할 수 있는 것과 비교되는 부분입니다.
- DETERMINISTIC : 해당 함수가 결정론적인지에 대한 옵션입니다. 동일한 파라미터가 전달되었을 때, 같은 결과를 보장하는지에 대해서 다룹니다. 기본은 NOT DEMTERMINISIC이며, 실제 결정론 적일 때는 DETERMINISTIC을 입력해야 합니다. 함수의 실제 결과가 결정론 적인지에 대해서는 MySQL이 판단하지는 않습니다. 하지만 이 옵션에 따라서 옵티마이저가 성능 최적화를 동작시키기 때문에, 결정론적이지 않는데 DETERMINISTIC을 입력한 경우 예상치 못한 결과가 발생할 수 있으며, 결정론 적인데 DETERMINISTIC를 입력하지 않는 경우 성능적 손해가 발생할 수 있다고 합니다.
- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } : 이 루틴에서 데이터를 사용하는 성격을 말합니다.
- CONTAINS SQL : 기본 값으로 루틴이 데이터를 읽거나 쓰는 문을 포함하지 않는다는 것을 말합니다.
- NO SQL : 이 루틴에 SQL값이 없는 것을 말합니다.
- READS SQL DATA : 데이터를 읽는 작업은 포함되지만, 데이터를 쓰는(insert, update) 문은 포함되지 않는 것을 말합니다.
- MODIFIES SQL DATA : 데이터를 쓰는 문이 포함될 수 있음을 나타냅니다. 하지만 이 설정은 데이터를 쓸 수 없는 function에서는 이용되지 않는, procedure를 위한 옵션입니다.
- CONTAINS SQL : 기본 값으로 루틴이 데이터를 읽거나 쓰는 문을 포함하지 않는다는 것을 말합니다.
- SQL SECURITY : 보안과 관련된 특성으로, DEFINER or INVOKER를 통해 해당 루틴을 실행할 권한을 체크합니다.
- routine_body : 해당 루틴의 내용이 될 SQL문들을 작성합니다. 단일 SQL문을 작성하거나 BEGIN - END 구문내에 복합적인 SQL문을 작성할 수 있습니다.
예시를 통해 Stored Function을 생성하는 과정에 대해서 알아봅시다.
먼저 Gender를 한글로 변환하는 stored function을 만들어 보겠습니다.
아래 코드를 보면, 파라미터로 `gender`라는 값을 받고, 조건문을 이용해서 `male` 인 경우 '남'으로, `female`인 경우 '여'로 표기하고, 그 외의 값, 예를 들면 `null`과 같은 값이 오면 '알수없음'으로 표기하는 함수를 제작했습니다.
delimiter //
create function convertGenderToKorean(gender char(6))
returns char(4)
reads sql data
begin
declare gender_korean char(4);
if gender = 'male' then
SET @gender_korean = '남';
elseif gender ='female' then
SET @gender_korean ='여';
else
SET @gender_korean ='알수없음';
end if;
return @gender_korean;
end//
delimiter ;
이 함수를 이용해서 조회를 하면 아래와 같은 결과가 나타납니다.
select name as '이름' ,
birth_year as '출생년도' ,
register_year as '입학년도' ,
convertGenderToKorean(gender) as '성별'
from student;
이렇게 번거로운 작업에 대해서 함수를 이용하는 것으로 코드의 재사용성이 늘어나고, 가독성 또한 좋아지는 것을 확인할 수 있습니다.
추가로 함수를 생성할 때엔, 기본적으로 현재 use로 설정된 스키마를 기준으로 생성되고, 다른 스키마에 생성하고자 한다면 `create function other_schema.function_name()`과 같은 형태로 입력해 주시면 됩니다.
조회
`select * from information_schema.routines;` 이 명령어를 통해 DB에 저장된 모든 루틴(procedure와 function)에 대한 정보를 확인할 수 있습니다.
해당 쿼리를 입력하면 아래 사진과 같이, 가장 마지막 줄에 제가 위에서 생성한 함수가 있다는 것을 확인할 수 있습니다.
평소 다루던 테이블 형태로 저장되어 있기 때문에 조건문을 이용해 루틴의 타입이나 저장된 스키마만으로도 조회가 가능합니다. 아래 사진처럼 말이죠
위의 명령어 외에 더 간단하게 조회하는 방법으로는
`show function status` 를 입력하여 stored function 들의 정보만을 조회하는 방법이 있고
`show create function {함수 이름}` 을 입력해 함수를 생성할 때 주었던 옵션들에 대해서 확인할 수 있습니다.
삭제
함수를 삭제하는 명령어는 다른 모든 명령어들과 동일하게 `drop` 명령어를 이용합니다. `drop function 함수이름`을 이용해 삭제할 수 있습니다. Stored Function에는 함수 B가 함수 A를 호출할 수 있습니다. 이런 경우에서 함수 A를 삭제하게 된다면, 함수 B를 정상적으로 이용할 수 없는 문제가 있습니다. 일반 row를 삭제할 때는 참조무결성의 원칙에 따라서 삭제를 할 때, 오류가 발생하겠지만 Stored Function의 경우 다른 함수를 호출하는 시점에서 문제가 발생해 이 부분을 주의해야 합니다.
아래 코드처럼 함수 B가 함수 A를 호출하는 상황에서 A를 삭제한 후 B를 호출하면 주석과 같이 testA가 존재하지 않는다는 오류가 발생하는 것을 볼 수 있을 것입니다.
delimiter //
create function testA()
returns int
deterministic
return 1;
end//
delimiter ;
delimiter //
create function testB()
returns int
deterministic
return testA();
end//
delimiter ;
drop function testA;
select testB(); #Error Code: 1305. FUNCTION study.testA does not exist호출
이미 여러 예시들을 보면서 확인하셨겠지만, `select` 문에서 built-in function과 같이 사용해 주시면 됩니다.
`select testB()`와 `select convertGenderToKorean(gender)`처럼요.
Stored Function의 특징
지금까지 Stored Function을 사용하는 방법에 대해서 알아봤으니, Stored Function이 가진 특징에 대해서 알아보겠습니다. 하지만 대부분의 내용은 이미 위의 예제에 녹아있는 내용이기 때문에 가볍게 보실 수 있을 겁니다.
SQL의 집합이다.
Stored Function의 상위 개념인 Routine에 대한 정의와 동일하죠. `Set Of SQL Statements`라는 말처럼 Stored Function은 1개 혹은 그 이상의 SQL로 표현할 수 있습니다.
그리고 1개의 경우 즉시 sql문에 return을 해서 표현할 수 있고, 복합적인 케이스에서는 begin-end 문 내에서 SQL들을 입력해서 사용할 수 있습니다.
다른 함수에서 사용 가능하다.
Stored Function은 다른 Stored Function 혹은 Stored Procedure에서도 사용할 수 있습니다. 앞선 삭제 예시에서 본 것처럼 `testA()` 함수 값을 그대로 리턴하는 `testB()`도 존재했고, 복합문 형태에서도 함수를 이용해 값을 리턴할 수 있습니다.
반드시 1개의 리턴 값을 가진다.
Stored Function의 경우 반드시 1개의 속성을 가진 단일 행의 리턴 값을 반환합니다. 이는 리턴되는 값이 없어서도(return을 안 하는 경우), 여러 개의 속성을 가져서도, 다중 행이어서도 안됩니다.
이 점이 Stored Procedure와의 차이점 중 하나가 되며, CREATE FUNCTION의 명령어 중에 `RETURNS`가 필수적인 이유가 됩니다. 공식문서에서도 아래와 같이 표현하며, 리턴 값이 필수적이라는 것을 표현합니다.
The RETURNS clause may be specified only for a FUNCTION, for which it is mandatory. It indicates the return type of the function, and the function body must contain a RETURN value statement.
함수를 생성하는 시점에서는 이런 부분들을 검사하지는 않지만, 이 조건을 위반하는 경우 오류가 발생합니다.
단일 속성을 무시하는 경우 `Error Code: 1241. Operand should contain 1 column(s)` 이 오류가 발생하고, 단일 행을 무시하는 경우 `Error Code: 1242. Subquery returns more than 1 row` 이런 오류를 만나보실 수 있습니다.
SQL에서 이용 가능하다.
Stored Procedure는 SQL문 내에서 이용할 수 있습니다. 지금껏 사용된 예시들은 `select`문에서 속성값으로만 이용했지만, built-in Function과 마찬가지로 조건문 등에서도 이용이 가능합니다.
where 절에서 이용하는 예시를 들어보겠습니다.
아래와 같이 가장 많은 인원이 있는 출생 연도를 리턴하는 함수를 만든 후 1992년이 제일 많도록 새로운 데이터를 추가하도록 하겠습니다.
delimiter //
create function mostCommonBirthYear()
returns int
reads sql data
return (select birth_year from student group by birth_year order by count(*) desc limit 1);
end//
delimiter ;
insert into student(name, birth_year, register_year, gender) values('이재성', 1992, 2023, 'male');
`select * from student where birth_year = mostCommonBirthYear();` 이처럼 조건절에 해당 함수를 이용해도 정상적으로 결과가 리턴되는 것을 볼 수 있습니다.
트랜잭션에 대해서 commit이나 rollback 할 수 없다.
마지막 특성으로는 함수 내부에서 트랜잭션을 commit이나 rollback 할 수 없습니다. 즉, 테이블이나 row에 대해서 삽입, 수정 작업을 진행하지 못한다는 말입니다. 거꾸로 말하면 조회만 가능하다고 볼 수 있겠네요. 이에 대한 부분은 공식 문서에서 stored function이 포함할 수 없는 구문에 다음과 같이 서술합니다.
Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to permit them.
A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
첫 번째 문장이 살짝 어색하게 들리겠지만, 수행할 수 없는 부분을 말하는 것이기 때문에, 함수 안에서 커밋, 롤백이 불가능하다고 이해하시면 될 것 같습니다.
마무리
지금까지 MySQL의 Stored Function에 대해서 알아보았습니다. 저 개인적으로는 웹 어플리케이션을 개발하면서 스프링에서 JPA나 JDBC를 이용해서 SQL을 이용했기 때문에 DB의 함수에 대한 필요성에 대해서 생각해보지 못했었습니다. 하지만 DB 자체로만 이용하는 경우에는 함수나 프로시저의 기능이 굉장히 유용하다고 생각이 들었습니다.
아직 현업을 경험해보지 못해 DB의 프로시저, 함수등이 자주 사용되는지는 모르겠지만 사용할 일이 된다면, 이번 내용을 토대로 작성해 보면 좋겠습니다.
참고문헌
MySQL 8.4 - Created Procedure & Function
MySQL 8.4 - Restrictions on Stored Programs
Enterprise Architect - Database Functions
