[ Database - Intermediate ] - stored function

Stored function

stored function이란 사용자가 정의한 함수를 말합니다. 즉 DBMS에서 저장되고 사용되는 함수라는 것이죠. 이는 SQL의 select, insert, update, delete statement에서 사용될 수 있습니다. 

 

바로 예시로 보겠습니다. 만약 임직원의 ID를 열자리 정수로 랜덤하게 발급하고 싶다면 어떻게 해야할까요? ( iD의 맨 앞자리는 1로 고정

 

delimiter $$
CREATE FUNCTION id_generator()
RETURNS int
NO SQL
BEGIN
    RETURN (1000000000 + float(rand() * 1000000000));
END
$$
delimiter ;

바로 이렇게 하면 되는데, delimiter $$ 는 ";" 이걸 바꿔주기 위함입니다. 안그러면 RETURN 문에 있는 세미콜론을 만나면 바로 끝나버리기 때문입니다. 그리고 마지막으로 delimiter ;로 원래대로 돌려 놓습니다.

 

BEGIN END 사이에 우리의 로직을 적는데, rand() 은 [0-1) 의 값을 return합니다. 이를 통해 9자리 수를 얻고 이를 10000000000과 더해주면 우리가 원하는 동작을 만들 수 있습니다. 이제 이 함수를 적용해주면

 

INSERT INTO employee
VALUES (id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);

위 쿼리문을 통해 집어넣으면, 위와같은 결과가 나오게 됩니다!  

 

그 다음 예시입니다. 만약 부서의 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 ;

이렇게 해주면 됩니다. avg_sal이라는 변수를 선언한 예시입니다. 하지만 변수를 사용하지 않고도 이 예제는 작성될 수 있는 아래와 같이 가능합니다.

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 ;

앞에 골뱅이를 붙히면 이를 RETURN에 바로 사용할 수 있습니다. 이를 이제 바로 사용해보면

SELECT *, dept_avg_salary(id)
FROM department;

위와같이 부서별로 결과가 나오게 됩니다. 각 department의 id마다 평균 연봉을 구한 예시였습니다.

 

마지막 예시로 졸업 요건 중 하나인 토익 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 ;

이와같이 작성될 수 있겠죠?? 그리고 이를 "@" 를 통해서 바꿔주면

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_score < 800	THEN 	SET	@pass_fail = 'fail';
    ELSE					SET @pass_fail = 'pass';
    END IF;
    RETURN @pass_fail;
END
$$
delimiter ;

이와같이 바꿀 수 있게 됩니다. 이제 이를 사용해보면

SELECT *, toeic_pass_fail(toeic)
FROM student;

위와같이 결과를 간결하게 가져올 수 있게 됩니다!!

 

이 외에도 loop를 돌면서 반복적인 작업을 수행하거나, case 키워드를 사용해서 값에 따라 분기 처리 하거나 에러를 핸들링하거나 에러를 일으키는 등의 다양한 동작을 정의할 수 있게 됩니다.

 

stored function은 언제 써야할까?

Three-tier architecture

대부분의 기업은 위와같이 Three-tier archietecture를 따르게 됩니다. 이는 직관적이므로 설명은 생략하겠습니다. 그럼 본격적으로 stored function을 써야하는 경우에 대해 설명하곘습니다.

 

stored-function은 util 함수로 쓰기에는 괜찮을거 같습니다. 하지만 비지니스 로직을 stored function에 두는 것은 좋지 않을거 같습니다. 그 이유는 비지니스 로직이 Logic tier가 아니라 Data tier에도 있게 되는 것입니다. 그래서 관리의 비용이 높게 될 수 있다는 것입니다.

 

그리서 우리가 작성했었던 stored-function을 비지니스 로직 측면에서 보면 위와같습니다. 그래서 toeic_pass_fail은 비지니스 로직으로 Logic tier에서 다루는 것이 좋다는 것입니다.