✅ stored function 의 뜻과 예제
: 사용자가 정의한 함수
: DBMS에 저장되고 사용되는 함수
: SQL의 select, insert, update, delete statement에서 사용할 수 있다.
stored function을 만들 때, delimiter 라는 키워드를 사용한다.
delimiter 는 구분자를 의미하는데, 기본적으로 SQL 에서 사용되는 delimiter는 (;)세미콜론이다.
하지만, stored program의 정의를 서버로 보내기 위해서는 delimiter를 일시적으로 재정의 해주어야만 한다.
이 때, 주의해야 할 점은 DELIMITER를 재정의하고 stored program을 정의한 후에 delimiter를 기존의 세미콜론(;)으로 바꿔주는 것이 좋다. (계속해서 DELIMITER를 사용자가 재정의한 것으로 사용할게 아니라면 말이다.)
또한, 사용자가 위 코드처럼 DELIMITER를 $$ 가 아닌 어떤 문자나 문자열을 한글자 혹은 여러글자로 구성해서 선언할 수도 있다. 하지만, 백슬래시(\)만큼은 피해야 한다. 왜나하면 백슬래시(\)는 MySQL에서 escape문이기 때문이다.
만약 stored program 내부에 ;(세미콜론)이 사용되지 않는다면 굳이 불필요하게 DELIMETER를 사용하지 않아도 된다.
대략적인 구문은 이렇다.
delimiter 를 지정해주고 시작한다.
CREATE FUNCTION 키워드를 통해 함수를 만들고 함수 이름을 지정한다. 파라미터가 있다면, (파라미터 자료형) 순으로 적어줘야한다.
RETURNS 키워드 뒤에는 반환하는 타입을 작성해줘야 한다.(s 붙인다)
body 부분은 BEGIN 으로 시작하고 END 로 끝난다. ( 이 때는 RETURN 문으로 값을 반환한다.-s 안 붙인다)
지정했던 delimiter 로 함수생성완료를 닫아주고, 다시 세미콜론(;)으로 바꿔준다.
※ 그럼 이제, stored function 을 작성하고 각 예제를 보면서 차근차근 살펴보도록 하자.
stored function 예제 1)
- 임직원의 ID 를 열자리 정수로 랜덤하게 발급하고 싶다.
- ID의 맨 앞자리는 1로 고정이다.
delimiter $$
CREATE FUNCTION id_generator()
RETURNS int
NO SQL
BEGIN
RETURN (1000000000 + floor(rand() * 1000000000));
END
$$
delimiter ;
rand 함수는 0부터 1미만까지의 수를 랜덤해서 뽑아내는 함수이다. 이 수에 10억을 곱해주면 9자리의 정수부를 가진 숫자가 반환될 것 이다.
그 후, floor 라는 내장함수를 통해 전체 이 값을 내림시켜줘서 정수만 가져올 수 있도록 한다. 그리고 1000000000 과 더해서 그 값을 리턴하면 우리가 원하는 1로 고정된 열자리 정수를 뽑아낼 수 있다.
※ 여기서 NO SQL 은 뭘까?
NO SQL
→ MySQL의 stored program을 정의할 때 사용되는 키워드로, 해당 stored program이 SQL 데이터를 읽거나 변경하지 않음을 명시하는 것이다. 즉, 이 함수나 프로시저 내에서는 데이터베이스의 데이터를 조회, 삽입, 수정, 삭제하는 어떤 SQL 문도 실행되지 않는다는 것을 데이터베이스 시스템에 알려주는 것이다.
그럼 우린 이제 id_generator() 라는 함수를 만든 것이다.
이제, 이 함수를 통해 EMPLOYEE 테이블에 값을 넣어보자. 현재 EMPLOYEE 테이블은 아래와 같다.
'JENNY' 라는 이름의 직원을 EMPLOYEE 테이블에 추가한다. 이 때, id_generator() 를 사용하면, 어떻게 값이 들어갈지 살펴보자.
INSERT INTO EMPLOYEE
VALUES (id_generator(),'JENNY','1991-08-04','F','PO',160000000,1005);
id 값이 1로 시작하는 랜덤한 열자리 정수로 발급된 것을 확인할 수 있다.
다른 예제를 살펴보자. stored function 예제 2)
- 부서의 ID를 파라미터로 받으면 해당 부서의 평균 연봉을 알려주는 함수를 작성하자.
delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA
BEGIN
DECLARE avg_sal int;
SELECT AVG(salary) INTO avg_sal
FROM EMPLOYEE
WHERE dept_id = d_id;
RETURN avg_sal;
END
$$
delimiter ;
dept_avg_salary 함수는 매개변수로 부서ID(d_id) 에 해당하는 직원들의 평균 연봉을 계산한다.
'DECLARE' 문을 사용해서 로컬 변수 'avg_sal' 와 타입을 선언하고,
'SELECT ... INTO' 구문을 사용해 평균 연봉을 계산하여 이 변수에 할당한 후, 이 값을 반환하는 로직이다.
'RETURN' 문을 사용해 BEGIN END 사이에서 정해진 값을 반환한다.
물론, DECLARE를 안쓰고 한 번에 값을 할당하고 싶으면 '@'를 사용하면 된다. 하지만, 이 때는 스코프가 확장되어서 해당 함수가 아닌 외부 쿼리에서도 참조가 가능하다.
delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA
BEGIN
SELECT AVG(salary) INTO @avg_sal
FROM EMPLOYEE
WHERE dept_id = d_id;
RETURN @avg_sal;
END
$$
delimiter ;
여기서 사용된 '@avg_sal' 은 사용자 변수를 의미한다. 사용자 변수는 세션 레벨에서 설정되며, 함수 내부 뿐만 아니라, 해당 세션의 다른 쿼리에서도 참조할 수 있다.
로컬 변수를 사용하는 대신 사용자 변수를 사용하는 것은 함수나 프로시저 내부에서만 유효한 값을 저장하는 것보다 더 넓은 스코프에서 값을 유지하고 싶을 때 유용하다.
그러나 일반적인 경우에는 stored program 내에서만 유효한 로컬변수를 사용하는 것이 더 바람직할 수 있다.
※ 여기서 READS SQL DATA 는 뭘까?
READS SQL DATA
→ MySQL의 stored program을 정의할 때 사용되는 키워드로, 해당 함수가 데이터베이스의 데이터를 읽을 수 있지만 변경하지 않는다는 것을 명시한다. 즉, 함수가 데이터를 조회('SELECT')할 수 있으나 데이터를 삽입('INSERT'), 갱신('UPDATE'), 삭제('DELETE')하는 작업은 수행하지 않는다는 것을 나타낸다. 이는 함수의 side effect를 관리하고, 데이터베이스의 무결성을 유지하는 데 도움이 된다.
그럼 우린 이제 dept_avg_salary () 라는 함수를 만든 것이다.
이제, 이 함수를 통해 DEPARTMENT 테이블에서의 부서마다의 평균연봉을 가져와보자. 현재 EMPLOYEE 테이블과 DEPARTMENT 테이블은 아래와 같다.
dept_avg_salary의 인자값에 dept_id 를 넣고 SELECT를 해보면 아래와 같다.
SELECT *,dept_avg_salary(dept_id)
FROM DEPARTMENT;
각 부서의 평균연봉이 나온 것을 확인 할 수 있다. 현재 legal 부서에는 어떠한 직원도 속해있지 않기 때문에 NULL 이 나온다.
다른 예제를 살펴보자. stored function 예제 3)
- 졸업 요건 중 하나인 토익 800 이상을 충족했는지를 알려주는 함수를 작성하자.
delimiter $$
CREATE FUNCTION toeic_pass_fail(toeic_score int)
RETURNS char(4)
NO SQL
BEGIN
DECLARE pass_fail char(4);
IF toeic_score is NULL THEN SET pass_fail = 'fail';
ELSEIF toeic_score < 800 THEN SET pass_fail = 'fail';
ELSE SET pass_fail = 'pass';
END IF;
RETURN pass_fail;
END
$$
delimiter ;
toeic_pass_fail 함수는 정수타입의 매개변수 'toeic_score'를 받는다. 이 매개변수는 TOEIC 점수를 나타낸다.
'RETURNS char(4)' 는 함수가 문자열을 반환할 것임을 명시하는데, 여기서는 4자리 문자열을 의미한다.
NO SQL 은 이 함수가 데이터베이스에 저장된 데이터를 읽거나 변경하지 않음을 나타낸다.
BEGIN END에 본문을 짜는데, 'DECLARE' 문을 사용해서 로컬 변수 'pass_fail' 과 타입을 선언한다.
'IF ... THEN SET ... ELSEIF ... THEN SET ... ELSE SET ... END IF;' 는 입력받은 'toeic_score'의 값에 따라 'pass_fail' 변수에 값을 할당하는 조건문이다.
'RETURN' 문을 사용해 BEGIN END 사이에서 정해진 값 'pass_fail'을 반환한다.
student table을 만들고 학생들을 테이블에 추가해보자.
CREATE TABLE student
(
id CHAR(9),
name VARCHAR(48) NOT NULL,
major VARCHAR(10),
phone VARCHAR(14),
toeic INTEGER,
gender CHAR(1),
status INTEGER DEFAULT 1
);
INSERT INTO student VALUES('20230905','김알루','CS','01030564321',795,'M',1);
INSERT INTO student VALUES('20231102','박민희','MT','01020498472',820,'F',1);
INSERT INTO student VALUES('20220101','유희석','AT','01053242317',835,'M',2);
INSERT INTO student VALUES('20221101','오나미','MC','01023447732',590,'F',2);
INSERT INTO student VALUES('20210141','최예인','BS','01030564323',905,'F',3);
INSERT INTO student VALUES('20200231','이나라','FS','01048146561',690,'F',3);
INSERT INTO student VALUES('20222201','곽시온','PE','01079986542',780,'M',2);
INSERT INTO student VALUES('20210101','조혜연','AB','01096984130',800,'F',3);
INSERT INTO student VALUES('20180101','정태식','AB','01082322615',920,'M',4);
INSERT INTO student VALUES('20171601','김정엽','CS','01014589322',890,'M',4);
INSERT INTO student VALUES('20202301','성시아','PE','01074230331',620,'F',2);
INSERT INTO student VALUES('20185211','마성민','CS','01056208443',770,'M',3);
INSERT INTO student VALUES('20170231','차진교','BS','01022319985',980,'M',4);
INSERT INTO student VALUES('20231794','고하연','MT','01023221124',610,'F',1);
INSERT INTO student VALUES('20232001','여진우','FS','01087989910',950,'M',1);
INSERT INTO student VALUES('20223141','임예린','AT','01087905235',920,'F',2);
INSERT INTO student VALUES('20230665','송현','MC','01042959455',555,'M',1);
이제 만들었던 toeic_pass_fail 함수를 사용해서 toeic 점수 조건을 충족했는지 여부를 파악해보면 아래와 같다.
SELECT *, toeic_pass_fail(toeic)
FROM STUDENT;
✅ 이외에 stored function 이 할 수 있는 일
MySQL 에서는 stored function 은
- 루프를 돌면서 반복적인 작업을 수행하거나
- case 키워드를 사용해서 값에 따라 분기처리를 하거나
- 에러를 핸들링하거나 에러를 일으키는 등의 다양한 동작을 정의할 수 있다.
stored 함수의 사용은 데이터베이스의 로직을 중앙화하고 표준화하는 데 유용하며, 다양한 상황에서 코드의 재사용성과 효율성을 높일 수 있다. 그러나 복잡한 함수는 데이터베이스 서버에 부하를 줄 수 있으므로, 성능과 리소스 사용을 고려하여 적절히 사용해야 하는 것도 중요하다.
✅ stored function 삭제하기
생성했던 stored 함수를 삭제하고 싶다면, 아래와 같은 구문을 작성하면 된다.
DROP FUNCTION stored_function_name;
위에서 toeic_pass_fail 함수를 삭제한다고 하면, DROP FUNCTION toeic_pass_fail; 이라고 할 수 있다.
✅ 등록된 stored function 파악하기
보통 실무에 가면, stored function 이 문서나 파일등에 잘 정리되어 있겠지만, 그게 아니라면 직접 파악해야 할 수도 있다.
그럼, 등록된 stored function이 어떤것이 있는지 파악하는 방법에 대해 한 번 알아보자.
.
..
...
가장 쉽게 알 수 있는 방법은 SHOW FUNCTIONS STATUS를 사용하고 WHERE 조건에 DB 이름을 걸어주면 된다.
SHOW FUNCTIONS STATUS WHERE DB = 'company';
company 라는 이름의 DB에 어떤 stored_function 이 있는지 파악해보면 아래와 같다.
결과의 일부를 캡쳐한 것인데, 우선 현재 company 라는 DB 에는 dept_avg_salary() 와 id_generator() 함수가 있는 것을 알 수 있다.
그럼, school DB를 확인해보자.
SHOW FUNCTIONS STATUS WHERE DB = 'school';
school DB 에는 toeic_pass_fail() 함수가 있는 것을 알 수 있다.
※ 우리가 stored_function을 만들 때, 뒤에 따로 DB 이름을 붙여주지 않으면, 그 stored_function 은 현재 활성화 되어 있는 DB 에 만들어진다.
아까 예제에서는 각 DB 안에서 함수를 만들었기 때문에, 따로 DB 이름을 뒤에 붙여주지 않았어도 각 해당 DB 내에 stored_function이 잘 작성된 것을 볼 수 있다.
만약 현재 DB와는 다른 DB에 함수를 만들어주고 싶다면, CREATE FUNCTION 뒤에 DB 이름을 먼저 적어주고 (.)점을 붙이고 function 이름을 적어주면 된다.
CREATE FUNCTION [DBName].[FunctionName]
예를 들어, 아까 company DB 로 이동하지 않고 school DB 안에서 dept_avg_salary 함수를 만든다면, 아래와 같은 형식으로 함수를 생성해야 한다.
delimiter $$
CREATE FUNCTION company.dept_avg_salary(d_id int)
RETURNS ...
...
...$$
그럼, 이제 특정 stored_function 이 어떤 함수인지 살펴보려면 어떻게 하면 되는지 알아보자.
예를 들어, 아까 company DB에서의 두 개의 함수 중에 id_generator 라는 함수를 알고 싶다고 해보자.
.
..
...
키워드는 간단하다. 우리가 DATABASES 를 볼 때, show 키워드를 쓰는 것처럼, SHOW 키워드와 함께 CREATE FUNCTION 을 써주고 그 뒤에 원하는 함수이름을 적어주면 된다.
(물론 이 때도, 해당 DB 내에 있는 stored_function 을 확인하고 싶다면, 굳이 DB이름을 적지 않고 바로 FunctionName 만 써줘도 된다.)
SHOW CREATE FUNCTION [DBName].[FunctionName]
SHOW CREATE FUNCTION [FunctionName]
SHOW CREATE FUNCTION company.id_generator;
위 사진은 SHOW CREATE FUNCTION company.id_generator 을 했을 때, 일부를 캡처한 것이다.
중간 부분을 보면, CREATE FUNCTION 부분이 있고, 이 함수가 어떻게 선언되어 있는지 볼 수 있다.
CREATE DEFINER 라는 정보도 나와있는데, 이는 이 함수를 정의한 사람에 대한 정보이다. 우리는 함수를 작성할 때, 따로 명시하지 않았기 때문에 디폴트 값인 localhost 가 들어가 있는 것을 확인할 수 있다. (=root 계정으로 localhost 가 작성했다)
💡위 내용은 MySQL 기준이다. 다른 RDBMS 의 stored_function 에 관한 문법이 조금씩 다를 수 있다.
✅ stored function 은 언제 써야 할까?
💡로마에 가면 로마법을 따르라
아래 내용은 개인적인 생각이다. 실무에 가면, stored function 에 대해 어떻게 쓸건지에 대해서는 회사사람들끼리 내부적으로 고민과 회의를 거쳐 결정했을 것이므로, 그대로 내부규약을 따르는게 좋다.(생각이 달라지면 팀 안에서 회의를 또 하면 된다.)
💡Three - tier - architecture
우선, 대부분의 IT 회사는 three-tier-architecture 로 나뉘어져 있고 이걸 따른다.
💡그건 그렇고, 그럼 stored function 은 언제 써야 할까?
(유튜브에서는 개인적인 의견이 포함되어 있음)
→ util 함수로 쓰기에는 괜찮을 것 같다.
→ 비즈니스 로직을 stored function 에 두는 것은 좋지 않을 것 같다.
(왜냐하면, 비즈니스 로직은 Logic Tier 에서 담당하고 있는 것인데, 비즈니스 로직의 일부를 stored function 에 두게 되면 로직의 일부가 Data Tier 에 있게 되는 셈이다.)
그럼, 원래 비즈니스 로직이 Logic Tier에 있었다가, 일부는 Data Tier 에 있게 되는 것이므로, 서비스의 규모가 커지면 커질수록, 관리 비용이 점점 더 늘어날 것이다. 양쪽을 다 봐야 하는 상황이 발생한다!
💡 그럼, 우리가 이번 장에서 생성한 stored function을 각각 판별해보자.
| stored function | 비즈니스 로직을 가지는가? |
| dept_avg_salary | X |
| id_generator | △ |
| toeic_pass_fail | O |
dept_avg_salary : 단순하게 계산을 편하게 하기 위한 함수였다.
id_generator : 얘를 로직으로 보기에는 애매하다. 로직으로 보기에는 너무 간단하기도 하고, 중요해보이지도 않지만, 한편으로는 1로 시작하는 10자리 정수라고 정책을 정했기 때문에 비즈니스 로직을 가지는 것이 아닐까도 싶다.
toeic_pass_fail : 비즈니스 로직을 가진다고 생각한다. 매우 중요해보이기도 하고, 800 이라는 점수 자체가 바뀔 수도 있는 부분이기 서비스의 방향이 달라질 수 있다. → 그래서 이런 부분은 stored_function 으로 만들기보다는 logic-tier 에서(소스코드 상에서) 구현하는 것이 더 낫지 않을까 싶다.
참고자료
- 유튜브 쉬운코드
'컴퓨터 공부 > 📗 CS' 카테고리의 다른 글
| [데이터베이스] 12장 - stored procedure를 백엔드 실무에서 쓰기에 조심스러운 이유! (2) | 2024.01.15 |
|---|---|
| [데이터베이스] 11장 - stored procedure + 프로시저의 동작 방식과 특징, stored function과의 차이 (0) | 2024.01.15 |
| [데이터베이스] 9장 - SQL 로 데이터 조회하기! Group by, Aggregation function, Order by (0) | 2024.01.11 |
| [데이터베이스] 8장 - SQL 로 데이터 조회하기! JOIN (4) | 2024.01.09 |
| [데이터베이스] 7장 - SQL 로 데이터 조회하기! NULL 의 의미와 three-valued logic (0) | 2024.01.09 |