사용자가 정의한 함수 (User-Defined Function, UDF) 사용법 및 활용 사례

이성혁·2024년 8월 29일

목록 보기

9/24

1. 사용자 정의 함수란?

사용자 정의 함수는 개발자가 직접 만든 함수로, DBMS에 저장되어 다양한 SQL 문에서 활용할 수 있습니다. 이 함수들은 SELECT, INSERT, UPDATE, DELETE 등 여러 SQL 문에서 사용 가능하여 코드의 재사용성과 편의성을 크게 향상시킵니다.

예제: 임직원의 ID를 랜덤하게 생성하는 함수

첫 번째 예제로, 임직원의 ID를 10자리 정수로 랜덤하게 생성하는 함수를 만들어 보겠습니다. 단, ID의 첫 자리는 '1'로 고정됩니다.

DELIMITER $$

CREATE FUNCTION id_generator()
RETURNS BIGINT
BEGIN
    DECLARE random_id BIGINT;
    SET random_id = 1 * 1000000000 + FLOOR(RAND() * 1000000000);
    RETURN random_id;
END $$

DELIMITER ;

이 함수는 id_generator()라는 이름으로 정의되며, 임직원의 ID를 10자리 정수로 생성해 반환합니다. 이때, 첫 번째 자리는 항상 '1'입니다.

함수 사용 예제

이제 이 함수를 사용해 임직원의 정보를 테이블에 추가해 보겠습니다.

INSERT INTO employee (id, name, birth_date, position, salary, department_id)
VALUES (id_generator(), 'Jane Doe', '1990-05-21', 'Engineer', 75000, 3);

위의 INSERT 문은 employee 테이블에 새로운 임직원 정보를 추가하면서, id_generator() 함수를 사용해 고유한 ID를 생성합니다.

2. 부서별 평균 연봉을 계산하는 함수

이번에는 특정 부서의 ID를 입력받아 해당 부서의 평균 연봉을 반환하는 함수를 만들어 보겠습니다.

DELIMITER $$

CREATE FUNCTION department_avg_salary(did INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE avg_salary DECIMAL(10,2);
    SELECT AVG(salary) INTO avg_salary
    FROM employee
    WHERE department_id = did;
    RETURN avg_salary;
END $$

DELIMITER ;

이 함수는 부서 ID를 입력받아 해당 부서 소속 임직원들의 평균 연봉을 반환합니다.

함수 사용 예제

각 부서의 평균 연봉을 함께 조회해 보겠습니다.

SELECT department.*, department_avg_salary(id) AS avg_salary
FROM department;

위의 SELECT 문은 각 부서의 정보를 조회하면서, department_avg_salary() 함수를 사용해 부서별 평균 연봉을 함께 가져옵니다.

3. 학생의 토익 성적을 판별하는 함수

마지막 예제로, 학생의 토익 점수가 졸업 기준인 800점을 넘었는지 확인하는 함수를 만들어 보겠습니다.

DELIMITER $$

CREATE FUNCTION toeic_pass_fail(toeic_score INT)
RETURNS CHAR(4)
BEGIN
    DECLARE result CHAR(4);
    IF toeic_score IS NULL THEN
        SET result = 'FAIL';
    ELSEIF toeic_score >= 800 THEN
        SET result = 'PASS';
    ELSE
        SET result = 'FAIL';
    END IF;
    RETURN result;
END $$

DELIMITER ;

이 함수는 학생의 토익 점수를 입력받아 졸업 요건 충족 여부를 'PASS' 또는 'FAIL'로 반환합니다.

함수 사용 예제

학생들의 정보를 조회하면서 토익 성적 결과를 함께 확인해 보겠습니다.

SELECT student.*, toeic_pass_fail(toeic_score) AS result
FROM student;

위의 SELECT 문은 학생 테이블에서 학생 정보를 조회하면서, toeic_pass_fail() 함수를 통해 토익 성적의 졸업 기준 충족 여부를 반환합니다.

4. 사용자 정의 함수의 활용 시기

사용자 정의 함수는 특정 계산이나 반복적인 작업을 수행할 때 매우 유용합니다. 다만, 복잡한 비즈니스 로직을 포함하는 경우에는 함수 내에서 처리하는 것보다 애플리케이션 로직에서 처리하는 것이 더 효율적일 수 있습니다. 특히 비즈니스 로직이 복잡해질 경우 관리가 어려워질 수 있으므로, 이러한 부분은 로직 계층에서 관리하는 것이 좋습니다.

5. 결론

사용자 정의 함수는 SQL 쿼리의 재사용성과 간결성을 크게 향상시키는 강력한 도구입니다. 이 함수들은 복잡한 로직을 캡슐화하고, 반복적인 작업을 단순화하며, 코드의 일관성을 유지하는 데 도움을 줍니다. 그러나 이러한 함수의 적절한 사용 시기와 방법은 프로젝트의 특성, 데이터베이스의 구조, 그리고 전체 시스템 아키텍처에 따라 다양하게 결정됩니다.

예를 들어, 데이터 변환이나 계산이 자주 필요한 경우, 또는 비즈니스 규칙을 데이터베이스 레벨에서 적용해야 하는 경우에 사용자 정의 함수가 특히 유용할 수 있습니다. 반면, 복잡한 트랜잭션 로직이나 대용량 데이터 처리가 필요한 경우에는 저장 프로시저나 애플리케이션 레벨의 로직이 더 적합할 수 있습니다.