[DB] Stored Function

• 사용자가 정의한 함수로 DBMS에 저장되고 사용된다.
• SQL의 SELECT, INSERT, UPDATE, DELETE 구문에서 사용할 수 있다.
• 반복적인 작업을 처리하기 위해 루프를 돌리거나, CASE 키워드를 사용하여 값에 따라 분기 처리할 수 있다.
• 에러 핸들링이나 에러 발생 같은 다양한 동작을 정의할 수 있다.
• 유틸리티 함수로 사용하는 것이 적합하다.
• 비즈니스 로직을 스토어드 함수에 두는 것은 권장되지 않는다. 비즈니스 로직은 데이터베이스 외부에서 처리하거나 애플리케이션 계층에서 유지 관리하는 것이 바람직하다.

 

예제 1: 임직원 ID 생성 함수

• 임직원의 ID를 10자리 정수로 랜덤 하게 발급하며, ID의 맨 앞자리는 1로 고정된다.

DELIMITER $$
CREATE FUNCTION id_generator()
RETURNS INT
NO SQL
BEGIN
    RETURN (1000000000 + FLOOR(RAND() * 900000000)); -- 1000000000 ~ 1999999999 사이의 값 반환
END$$
DELIMITER ;

• 사용 예시:

  INSERT INTO employee (id, name, birth_date)
  VALUES (id_generator(), 'JHON', '1991-08-04');

 

예제 2: 부서별 평균 연봉 계산 함수

• 부서의 ID를 파라미터로 받아 해당 부서의 평균 연봉을 반환하는 함수이다.

DELIMITER $$
CREATE FUNCTION dept_avg_salary(d_id INT)
RETURNS DECIMAL(10, 2)
READS SQL DATA
BEGIN
    DECLARE avg_sal DECIMAL(10, 2);
    SELECT AVG(salary) INTO avg_sal
    FROM employee
    WHERE dept_id = d_id;
    RETURN avg_sal;
END$$
DELIMITER ;

SELECT name, dept_avg_salary(dept_id) AS average_salary
FROM department;

 

 

예제 3: 토익 점수에 따른 패스/페일 판단 함수

• 토익 점수가 800 이상인지 여부에 따라 'pass' 또는 'fail'을 반환하는 함수이다.

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 ;

SELECT student_name, toeic_pass_fail(toeic_score) AS result
FROM student;

 

스토어드 함수 삭제하기

• 특정 스토어드 함수를 삭제하는 명령어이다.

DROP FUNCTION IF EXISTS 스토어드 함수명;

 

등록된 스토어드 함수 확인하기

• 특정 데이터베이스에 등록된 스토어드 함수의 상태를 확인하는 명령어이다.

SHOW FUNCTION STATUS WHERE Db = '데이터베이스명';

SHOW CREATE FUNCTION 스토어드 함수명;