10. DBMS에 저장해서 쓰는 함수 Stored Function

본 내용은 유튜버 쉬운코드의 강의 "데이터베이스"를 참고하여 작성하였습니다.

 

stored function의 뜻

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

---

예제 1) id_generator

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

-- id_gernerator() 함수 정의
delimiter $$
CREATE FUNCTION id_gernerator()
RETURNS int  -- 리턴 타입 지정
NO SQL
BEGIN  -- 함수의 body 부분
  RETURN (1000000000 + floor(rand() * 1000000000))
END
$$
delimiter ;

-- 정의한 함수를 사용해서 employee 테이블에 임직원 정보 추가
INSERT INTO employee VALUES(id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);

• delimiter : SQL 명령어의 끝을 나타내는 구분자를 변경하는 명령어

---

예제 2) dept_avg_salary

예제 : 부서의 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을 선언하는 문장을 생략할 수 있음.
-- BEGIN
--   select avg(salary) into @avg_sal from employee where dept_id = d_id;
--   RETURN @avg_sal;
-- END


-- 사용 예제
SELECT *, dept_avg_salary(id) FROM department;

---

예제 3) toeic_pass_fail

예제 : 졸업 요건 중 하나인 토익 800 이상을 충족했는지를 알려주는 함수를 작성하자.

delimiter $$
CREATE FUNCTION toeic_pass_fail(score int)
RETURNS char(4)
NO SQL
BEGIN
  DECLARE pass_fail char(4);
  IF     score is null THEN SET pass_fail = 'fail';
  ELSEIF score < 800   THEN SET pass_fail = 'fail';
  ELSE                      SET pass_fail = 'pass';
  END IF;
  RETURN pass_fail ;
END
$$
delimiter ;

-- pass_fail 선언 없이 하려면
-- BEGIN
--   IF     score is null THEN SET @pass_fail = 'fail';
--   ELSEIF score < 800   THEN SET @pass_fail = 'fail';
--   ELSE                      SET @pass_fail = 'pass';
--   END IF;
--   RETURN @pass_fail ;
-- END


-- 사용 예제
SELECT *, toeic_pass_fail(toeic) FROM student;

---

이외에 stored function이 할 수 있는 일

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

---

stored function 삭제하기

DROP FUNCTION stored_fct_name;

---

등록된 stored function 파악하기

-- 정의되어 있는 함수 찾기
SHOW FUNCTION STATUS where DB = 'company';

SHOW FUNCTION STATUS 실행 결과

• 따로 설정하지 않으면 함수를 정의할 때 활성화되어 있었던 DB로 저장이 됨.
• CREATE FUNCTION db_name.fct_name; 으로 DB를 지정해줄 수 있음.

 

-- 함수 코드 보기
SHOW CREATE FUNCTION id_generator;

SHOW CREATE FUNCTION 실행 결과

• DEFINER=`root`@`localhost` : 함수를 정의한 사용자 정보

 

-- 참고 : DB 이름 확인하는 방법
SHOW DATABASES;

SHOW DATABASES 실행 결과

• SHOW DATABASES; 를 했을 때 나오는 information_schema, mysql, performance_schema, sys는 기본으로 저장되어 있음(MySQL에서 자체적으로 사용).

---

중요 참고 사항

• MySQL 기준이기 때문에 RDBMS에 따라 다를 수 있음.

---

(참고) stored function은 언제 써야 할까?

본 챕터는 강의자 개인적인 생각으로, 사용하는 곳마다 다를 수 있음.

 

Three-tier architecture

• util 함수로 쓰기에는 괜찮을 것 같다.
• 비즈니스 로직을 stored fct에 두는 것은 좋지 않을 것 같다. -> 그러면 비즈니스 로직을 확인하다가 stored fct도 확인하기 위해 Data tier 부분도 봐야 하기 때문