[Database] (쉬운 코드) Stored function
쉬운 코드 강의 「데이터베이스」 내용을 바탕으로, SQL에서 stored function의 특징과 사용법을 정리한 포스트 입니다.
테이블 구조
우선 시작하기에 앞서 내용 이해에 필요한 테이블 구조입니다.
Stored function
Stored function은 사용자가 정의한 함수로 DBMS에 저장되고 사용되는 함수이다. 또한, SQL의 select, insert, update, delete statement에서 사용할 수 있다.
ex. 임직원의 ID를 열자리 정수로 랜덤하게 발급하고 싶다. 이때, ID의 맨 앞자리는 1로 고정이다.
1
2
3
4
5
6
7
8
9
delimiter $$ -- ;로 하면 함수의 끝을 잘못 인식하는 우류가 발생 --
CREATE FUNCTION id_generator()
RETURNS int
NO SQL
BEGIN
RETURN (1000000000 * floor(rand() * 1000000000));
END
$$
delimiter ;
위의 함수를 사용해서 employee 테이블에 새로운 임직원 정보를 추가하는 코드는 아래와 같다.
1
2
INSERT INTO employee
VALUES (id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);
ex. 부서의 ID를 파라미터로 받으면 해당 부서의 평균 연봉을 알려주는 함수를 작성하자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--- 방법 1 ---
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;
--- 방법 2 ---
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 ;
사용 방법은 다음과 같다.
1
2
SELECT *, dept_avg_salary(id)
FROM department;
ex. 졸업 요건 중 하나인 토익 800 이상을 충족했는지를 알려주는 함수를 작성하자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 방법 1 --
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 ;
-- 방법 2 --
delimiter ##
CREATE FUNCTION toeic_pass_fail(toeic_score int)
RETURNS char(4)
NO SQL
BEGIN
IF toeic_score is null THEN SET @pass_fail = 'fail';
ELSEIF toeic_scure < 800 THEN SET @pass_fail = 'fail';
ELSE SET @pass_fail = 'pass';
END IF;
END
$$
delimiter ;
위의 함수를 사용하는 방법은 다음과 같다.
1
2
SELECT *, toeic_pass_fail(toeic)
FROM student;
이외에서 stored function에서는 다음과 같은 작업을 할 수 있다.
- loop를 돌면서 반복적인 작업을 수행할 수 있다.
- case 키워드를 사용해서 값에 따라 분기 처리 할 수 있다.
- 에러를 핸들링하거나 에러를 일으키는 등의 다양한 동작을 정의할 수 있다.
stored function 삭제
stored function을 삭제하는 방법은 다음과 같다.
1
DROP FUNCTION stored_function_name;
등록된 stored function 파악
등록된 stored function을 파악하는 방법은 다음과 같다.
1
SHOW FUNCTION STATUS where DB = 'company';
함수의 코드를 보고 싶으면 다음과 같은 코드를 실행하면 된다.
1
SHOW CREATE FUNCTION id_generator;
Stored function 사용 시점
아래의 내용은 절대적인 기준이 아닙니다. 참고용으로만 봐주세요.
대부분의 기업에서는 Three-tier architecture를 사용한다.
Presentation tier- 사용자에게 보여지는 부분을 담당하는 tier
- HTML, javascript, CSS, native app, desktop app
Logic tier- 서비스와 관련된 기능과 정책 등등 비즈니스 로직을 담당하는 tier
- application tier, middle tier라고도 부름
- Java + Spring, Python + Django, etc..
Data tier- 데이터를 저장하고 관리하고 제공하는 역할을 하는 tier
- MySQL, Oracle, SQL Server, PostgreSQL, MongoDB
이러한 구조 속에서 stored function는 util 함수로 쓰는 것은 괜찮지만 비즈니스 로직(Logic tier)을 stored function에 두는 것은 좋지 않을 것 같다. 왜냐하면 stored function이 저장되는 곳은 data tier이기 때문에 효율적이지 않다.