[Database] Stored Function & Stored Procedure

stored function

• 사용자가 정의한 함수
• DBMS에 저장되고 사용되는 함수
• SQL의 select, insert, update, delete statement에서 사용할 수 있다.

예제 1

• 임직원의 ID를 열자리 정수로 랜덤하게 발급하고 싶다.
• ID의 맨 앞자리는 1로 고정이다.

delimiter $$
CREATE FUNCTION id_generator()
RETURNS int
NO SQL -- 오직 MySQL에만 있는 부분
BEGIN
    RETURN (1000000000 + floor(rand() * 1000000000));
END
$$
delimiter;

• 일반적으로 SQL에서의 delimiter는 세미콜론(;) 이다. 하지만, 함수 내부에서 세미콜론을 사용할 일이 있으므로 function을 작성하기 전에 미리 delimiter를 바꿔주어야 한다. 그리고 함수 작성 이후에 원복.

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

예제 2

• 부서의 ID를 파라미터로 받으면 해당 부서의 평균 연봉을 알려주는 함수를 작성하자

delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA -- MySQL과 관련된 부분
BEGIN
    DECLARE avg_sal int;
    select avg(salary) into avg_sal
                                         from employee
                                         where dept_id = d_id;
    RETURN avg_sal;
END
$$
delimiter ;

-- 개선

delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA -- MySQL과 관련된 부분
BEGIN
    select avg(salary) into @avg_sal
                                         from employee
                                         where dept_id = d_id;
    RETURN @avg_sal;
END
$$
delimiter ;

• DECLARE를 통해 변수를 선언할 수 있음.
• select avg(salary) into avg_sal …: 구한 평균 연봉을 avg_sal에 저장

SELECT *, dept_avg_salary(id) FROM department;

예제 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 ;

-- 

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(student.toeic) FROM student;

이외에 stored function이 할 수 있는 일

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

stored function 삭제하기

DROP FUNCTION stored_function_name;

등록된 stored function 파악하기

SHOW FUNCTION STATUS where DB = db_name;

create function 할 때 따로 db이름을 명시하지 않으면 현재 활성화 되어 있는 데이터베이스에 stored function을 만듦.

특정 db에 stored function을 만들고 싶으면 CREATE FUNCTION db.function과 같은 형태로 만들면 됨.

SHOW CREATE FUNCTION id_generator;

정의된 stored function의 정의를 확인할 수 있음.

stored function은 언제 써야할까?

물론 상황마다 다르니 참고만 하자

• util 함수로 쓰기에는 괜찮을 것 같다
• 하지만, 비즈니스 로직을 stored function에 두는 것은 좋지 않을 것 같다.

stored procedure

• 사용자가 정의한 프로시저
• RDBMS에 저장되고 사용되는 프로시저
• 구체적인 하나의 테스크(task)를 수행한다

예제 1

두 정수의 곱셈 결과를 가져오는 프로시저

delimiter $$
CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
BEGIN
    SET result = a * b;
END
$$    
delimiter ;

call product(5, 7, @result);
select @result; -- 35

• input paramter에는 IN이라는 키워드 (생략 가능)
• 결과를 반환할 output paramter에는 OUT이라는 키워드
• IN / OUT 키워드를 적지 않으면 기본값으로 IN 사용

예제 2

두 정수를 맞바꾸는 프로시저 (swap)

delimiter $$
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
BEGIN
    SET @temp = a;
    SET a = b;
    SET b = @temp;
END
$$    
delimiter ;

set @a = 5, @b = 7;
call swap(@a, @b);
select @a, @b;

• INOUT: 호출한 값을 저장함과 동시에 반환값으로도 사용 가능.

예제 3

각 부서별 평균 연봉을 가져오는 프로시저

delimiter $$
CREATE PROCEDURE get_dept_avg_salary()
BEGIN
    SET dept_id, avg(salary)
    FROM employee
    GROUP BY dept_id;
END
$$    
delimiter ;

call get_dept_avg_salary();

예제 4

사용자가 프로필 닉네임을 바꾸면 이전 닉네임을 로그에 저장하고 새 닉네임으로 업데이트 하는 프로시저를 작성하자

delimiter $$
CREATE PROCEDURE change_nickname(user_id INT, new_nick varchar(30))
BEGIN
    INSERT INTO nickname_logs (
        SELECT id, nickname, now() FROM users WHERE id = user_id
    );
    UPDATE users SET nickname = new_nick WHERE id = user_id;
END
$$    
delimiter ;

call change_nickname(1, 'ZIDANE');

이외에도 procedure로 할 수 있는 것들

• 조건문을 통해 분기처리를 하거나
• 반복문을 수행하거나
• 에러를 핸들링하거나 에러를 일으키는 등의 다양한 로직을 정의할 수 있다.

stored procedure vs stored function

이 외에도

• 다른 function/procedure를 호출할 수 있는지
• resultset(= table)을 반환할 수 있는지
• precompiled execution plan을 만드는지
• try-catch를 사용할 수 있는지

3-tier architecture에서 stored procedure의 의미

stored procedure의 주된 사용 목적은 비즈니스 로직인데 이는 즉, 3-tier architecture에서 data tier에 비즈니스 로직이 존재할 수 있다는 의미

stored procedure의 장단점

장점

• application에 transparent하다
• network traffic을 줄여서 응답 속도를 향상시킬 수 있다.
• 여러 서비스에서 재사용 가능하다
• 민감한 정보에 대한 접근을 제한할 수 있다.
  • 데이터베이스에 간접적으로만 접근 가능하게 함.

단점

• 유지 관리 보수 비용이 커진다.
  • 비즈니스 로직이 data tier에도 존재해서 왔다갔다 하기 불편하고 DB 쪽 버전 관리가 어려움.
  • Database의 procedure 관련된 문법까지 잘 알아야 함.
  • 새로운 로직을 프로시저로 구현한다 치면, DB도 구현 후 배포하고 서버도 구현 후 배포 후 재시작 해야 함.
• DB 서버를 추가하는 것은 간단한 작업이 아니다.
  • 프로시저를 통해 비즈니스 로직을 처리하게 되면 DB 서버의 자원 사용량이 증가하게 되어 트래픽이 몰렸을 때 대응이 어려움.. DB를 추가만 하면 끝이 아니라 추가하고 복제까지 해야하는데 이미 혹사 중인 DB의 데이터를 긴급하게 복제하기에는 어려움이 있음.
  • 반면 애플리케이션 서버를 늘리는 것은 쉬운 일임. (오토스케일링) → 트래픽 대응 쉬움
• stored procedure가 언제나 transparent인건 아니다.
  • DB의 프로시저의 이름을 변경한다면 기존 서버의 코드도 모두 바꿔주고 빌드 및 재시작 해야 함.
• transparent 하다고 무조건 좋은 것도 아니다
  • 새로운 비즈니스 로직을 반영한 프로시저 내에서 에러가 생긴다면 이에 의존하고 있는 운영 중인 모든 애플리케이션에서 문제가 생길 수 있음.
  • 하지만 비즈니스 로직을 서버에서 관리하고, 새로운 비즈니스 로직을 운영 중인 서버 중 1대에 먼저 테스트로 띄워본 후 에러가 발생하면 빠르게 롤백할 수 있고 영향을 최소화할 수 있음. → 번거롭긴 해도 예상치 못한 문제의 영향을 최소화 할 수 있다.
• 재사용 가능하다는 것이 양날의 검이 될 수도..
  • 이 역시 여러 서버가 하나의 DB에 의존하고 있는데 DB가 터지면 의도치 않게 다른 서버들도 모두 터짐.
• 비즈니스 로직을 소스코드에 두고도 응답 속도를 향상 시킬 수 있다.
  • 순차적일 필요 없이 동시에 진행 가능한 경우? → 쓰레드 풀이나 논블락 IO 등을 사용
  • 서로 다른 테이블에 대해 순차적으로 select를 해야하는 경우 → 캐시를 사용해서 속도 향상
• stored procedure가 민감한 정보에 대한 접근을 완벽히 제한할 수는 없다.
  • stored procedure에 민감한 정보를 반환하도록 로직을 작성하면 됨.
• DB 혹은 테이블 접근을 막으면 개발 및 CS 업무의 신속함이 떨어진다..
  • 담당자나 개발자에게만 DB 혹은 테이블 권한을 부여하자
  • 민감한 정보는 암호화해서 저장하자
  • 보안 서약서 등을 통해 정책적으로 보안을 강화하자
• procedure로는 복잡하고 유연한 코드를 작성하기 어렵다.
• 오늘날의 프로그래밍 언어는 훨씬 다양하고 강력한 기능들을 제공한다.
• procedure는 가독성이 떨어진다.
• procedure는 디버깅이 어렵다.