Database 8. stored function

• MySQL 문법을 기준으로 서술한다.

stored function

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

stored function 예제1

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

delimiter $$ -- 함수 내부에서 세미 콜론을 써도 커맨드가 종료되지 않게 $$로 delimiter를 바꿔줌
CREATE FUNCTION id_generator() -- 함수명 선언
RETURNS int -- 반환 타입 선언
NO SQL -- SQL문을 함수 내에서 사용하지 않는다는 명시를 통해 성능 향상
BEGIN
  RETURN (1000000000+floor(rand() + 1000000000));
END
$$
delimiter ; -- delimiter 복원

• 함수 선언 이후 활용 예시

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

stored function 예제 2

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

delimiter $$ -- 함수 내부에서 세미 콜론을 써도 커맨드가 종료되지 않게 $$로 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 ; -- delimiter 복원

• 혹은 아래와 같이도 가능함.

delimiter $$ -- 함수 내부에서 세미 콜론을 써도 커맨드가 종료되지 않게 $$로 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 ; -- delimiter 복원

• 함수 선언 이후 활용 예시

SELECT *, dept_avg_salary(id) FROM department;

stored function 예제 3

delimiter $$ -- 함수 내부에서 세미 콜론을 써도 커맨드가 종료되지 않게 $$로 delimiter를 바꿔줌
CREATE FUNCTION toeic_pass_fail(toeic_score int) -- 함수명 선언
RETURNS char(4) -- 반환 타입 선언
NO SQL -- 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 복원

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

stored function 삭제하기

DROP FUNCTION stored_function_name;

등록된 stored function 파악하기

SHOW FUNCTION STATUS where DB = 'company';

• CREATE FUNCTION DB.FUNCITON 과 같이 현재 활성화 되어 있는 DB가 아닌 다른 DB에 정의할 수도 있다.

stored function은 언제 써야할까?

• Three-tier architecture |Tier|description| |—|—| |Presentation tier|사용자에게 보여지는 부분을 담당하는 tier(HTML, javascript, CSS, native app)| |Logic tier|서비스와 관련된 기능과 정책 등등 비즈니스 로직을 담당하는 tier(Java+Spring, Python+Django)| |Data tier|데이터를 저장하고 관리하고 제공하는 역할을 하는 tier(MySQL, SQL Server, PostgreSQL, MongoDB)|
• 해당 stored function은 util 함수로 쓰기에 적합하다고 생각한다.
• 비즈니스 로직에 stored function을 두는 것은 좋지 않다고 생각한다.
• 위의 예시들에 사용된 로직들 |stored function|비즈니스 로직을 가지는가?| |—|—| |dept_avg_salary|갖지 않음| |id_generator|모호| |toeic_pass_fail|갖음|