[MySQL] 사용자 정의 함수(UDF), 데이터 타입

1. 사용자 정의 함수(User Defined Function)

1.1. UDF 기본 개념

• MySQL 내장함수 이외에 사용자가 직접 만들 수 있는 함수
  • 반복해서 쓰는 복잡한 로직이 있을 경우, 직접 함수를 정의해서 재사용하면 유용함
  • ex) 특정 패턴의 문자열 처리, 특정 규칙에 따른 계산, 데이터 정제 등
• ❇️기본 쿼리 형식
  
  • 매개변수 : 함수 사용 시 입력받는 변수 (여러 개 가능)
  • 반환타입 : 함수가 어떤 타입의 값을 반환하는지 명시
    • 아래 2장 참고
  • DETERMINISTIC : 입력한 파라미터가 같으면 출력도 동일하게 해주는 설정 (아닐 경우 NOT DETERMINISTIC)
    • NOW, RAND 처럼 매번 실행 결과 달라지는 함수 사용 시, 일관된 출력을 하도록 쓸 수 있음.
  • BEGIN과 END 사이에 함수 내부 로직을 작성하면 됨
    • 구문마다 종료 기호(;) 필수!
  • RETURN : 반환할 값을 설정 (필수⭐)
  • DECLARE : 지역 변수를 선언 (선택사항)
  • SET : 지역변수에 값을 할당하는 함수로, DECLARE와 함께 쓰임 (선택사항)

DELIMITER $$ -- (함수 내부에 세미콜론을 쓰기 위해) 세미콜론 대신 $$를 구문 종료 기호로 잠깐 바꾸는 것

CREATE FUNCTION 함수이름(매개변수 데이터타입, ...)
	RETURNS 반환타입 DETERMINISTIC
BEGIN
    -- 함수 내부 로직
    DECLARE 변수명 데이터타입;
    SET;
    RETURN 결과값;
END$$

DELIMITER ; -- 다시 세미콜론을 구문 종료 기호로 설정

 

1.2. UDF 쿼리 예시

📌예시1) 이름의 이니셜을 추출해주는 함수

-- 함수 정의
CREATE FUNCTION get_initials(fullname VARCHAR(100))
	RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
    RETURN CONCAT(UPPER(LEFT(fullname, 1)), '.');
END

-- 사용 예시 (1) ➡️결과: 'J.'
SELECT get_initials('john');

-- 사용 예시 (2) ➡️결과: initial 컬럼 추가로 출력됨
SELECT
    fullname,
    get_initials(fullname) AS initial
FROM employees;

 

📌예시2) 점수에 따라 학점(A, B, C, F)을 계산해주는 함수

-- 함수 정의
CREATE FUNCTION get_grade(score INT)
	RETURNS CHAR(1) DETERMINISTIC
BEGIN
    RETURN CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'F'
    END;
END

-- 사용 예시 (1) ➡️ 결과: 'A'
SELECT get_grade(95);

-- 사용 예시 (2) ➡️결과: grade 컬럼 추가로 출력됨
SELECT
    name, score,
    get_grade(score) AS grade
FROM exam_results;

 

📌예시3) 입력된 가격에 세금을 계산해서 더해주는 함수

• 예시1,2와 같은 간단한 함수에서는 변수 선언이 필요 없지만, 지금처럼 중간 계산이 필요하거나 일시적으로 값을 저장하는 복잡한 로직에서는 DECLARE & SET이 자주 쓰임!

-- 함수 정의
CREATE FUNCTION get_taxed_price(price DECIMAL(10,2))
	RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    DECLARE tax_rate DECIMAL(4,2);
    DECLARE taxed_price DECIMAL(10,2);

    SET tax_rate = 0.1; -- 세율 설정
    SET taxed_price = price * (1 + tax_rate); -- 입력된 price에 세금 더해줌

    RETURN taxed_price; -- 최종 결과 반환
END

-- 사용 예시 (1) ➡️결과: 110.00
SELECT get_taxed_price(100.00);

-- 사용 예시 (2) ➡️결과: taxed_price 컬럼 추가로 출력됨
SELECT
    name, price,
    get_taxed_price(price) AS taxed_price
FROM products;

 

1.3. IF THEN 구문

• 사용자 정의 함수를 만들 때 조건 분기를 위해 IF THEN구문을 쓸 수 있는데, 이게 SELECT 절에서 사용하는 IF 함수 또는 CASE WHEN 구문과 헷갈릴 수 있어서 정리해둔다.

📌① IF 함수

• SELECT, WHERE, ORDER BY 등 SQL 문 안에서 사용 가능한 함수
• IF(조건,a,b) : 조건이 참이면 a를 반환하고, 거짓이면 b를 반환함

SELECT name,
	IF(score >= 90, 'A', 'B') AS grade
FROM student;

📌② IF THEN 구문

• CREATE FUNCTION, CREATE PROCEDURE 등 BEGIN~END 블록 내에서 사용하는 흐름 제어문 (일반 SELECT문에서는 사용 불가!)
• IF (조건) THEN ... ELSE ... END IF : 조건이 참이면, THEN 뒤의 코드블록을 실행함.
• THEN 뒤에 명령문 여러 개도 실행 가능함! (RETURN, SELECT INTO, LEAVE 등)

CREATE FUNCTION getGrade(score INT)
	RETURNS CHAR(1)
BEGIN
    DECLARE result CHAR(1);

    IF score >= 90 THEN -- 이렇게 함수 내에서 코드 흐름을 제어하는 역할!
        SET result = 'A';
    ELSEIF score >= 80 THEN -- 중간 조건은 ELSEIF로 처리!
        SET result = 'B';
        -- (밑에 명령문 더 넣어도 됨)
    ELSE
        SET result = 'C';
    END IF; --⭐반드시 END IF로 끝내줘야 함!

    RETURN result;
END;

📌③ CASE WHEN 구문

• SQL문, BEGIN~END 블록 등 어디서든 자유롭게 사용하는 흐름 제어문 (IF함수보다 복잡한 여러 조건을 순차적으로 처리할 때 유용함)
• CASE WHEN(조건) THEN ... ELSE ... END : 조건이 참이면, THEN 뒤의 값을 반환함
• IF 구문과 달리, THEN 뒤에 반드시 '값'이 와야 함! (SET, RETURN 등 명령문 불가능)

SELECT name,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END AS grade --
FROM student;

 

✅차이점 정리

	IF(a,b,c) 함수	IF THEN 구문	CASE WHEN 구문
사용 위치	SQL문 내부	BEGIN~END 블록 내부	어디서든 가능
종료 방식	없음	END IF; 필수	END 필수
다중 조건 처리	불가능	가능	가능
실행 결과	값 자체를 반환	코드블록을 실행	값 자체를 반환

 

2. 데이터 타입 정리

• 사용자 정의 함수를 만들 때 지정해줄 수 있는 데이터 타입에 관해서도 정리해둔다.

2.1. 숫자(numeric) 타입

• 범위가 작을수록 저장공간이 절약, 성능 향상됨
  • 사용할 숫자의 최대값을 예측해서 적당한 타입을 사용! (ex. 나이는 많아도 200 안 넘으니까 TINYINT 쓰자)
  • BIGINT는 연산 속도가 느려지므로 정말 필요할 때만 사용
• UNSIGNED 옵션을 쓰면 0부터 시작하는 양의 정수 범위로 두 배 확장됨. (ex. UNSIGNED TINYINT = 0~255)

데이터 타입	크기 (Byte)	범위 (SIGNED 기준)	특징 및 사용 예시
TINYINT	1	-128 ~ 127	작은 정수 (ex. 나이, 플래그 값(0/1) 등)
SMALLINT	2	-32,768 ~ 32,767	좀 더 큰 수 (ex. 점수, 재고수량 등)
INT	4	약 ±21억	기본 정수형, 거의 모든 경우 사용 가능
BIGINT	8	약 ±900경	매우 큰 수 (ex. 누적금액, 유튜브 조회수 등)
DECIMAL(p,s)	가변	정확한 소수 표현 전체 p, 소수점 s 자리 (60,35까지 가능)	정밀 계산이 필요한 경우 (ex. 금액, 통화 등) DECIMAL(6,2) = -9999.99 ~ 9999.99
FLOAT(p)	4	약 ±3.4E+38 (극도로 큰 실수)	빠르지만 부정확 (반올림 오차 생김) (ex. 측정값, 과학 계산 등)
DOUBLE(p)	8	약 ±1.8E+308 (극도로 작은 실수)	빠르지만 부정확 (반올림 오차 생김) (ex. 측정값, 과학 계산 등)

 

2.2. 문자열(string) 타입

• 간단한 문자열은 대부분 VARCHAR를 사용
• TEXT 계열(TINYTEXT, LONGTEXT 등)은 인덱싱 제약이 있으므로 검색이 필요한 필드에는 사용 주의

데이터 타입	크기 제한	특징	사용 예시
CHAR(n)	고정 길이(n byte)	항상 n글자 저장 (부족하면 공백 채움)	성별('M', 'F'), 우편번호 등
VARCHAR(n)	가변 길이(n byte)	실제 글자 수만큼 저장 (공간 효율적)	이름, 이메일, 주소 등
TEXT	최대 65,535자	대용량 텍스트 저장 (검색 제한 있음)	게시글, 메모 등
ENUM('a','b')	내부적으로 숫자로 저장	성별, 상태 등 정해진 값만 저장	성별, 플래그값 ('활성', '중지') 등

 

2.3. 날짜 및 시간(Date/Time) 타입

• TIMESTAMP는 서버의 로컬 시간 차이에 주의해야 함. (로컬 시간 보존이 필요할 땐 오히려 DATETIME이 적합)

데이터 타입	저장 범위	특징	사용 예시
DATE	'1000-01-01' ~ '9999-12-31'	날짜만 저장 (시간 불필요한 경우)	생일, 등록일 등
DATETIME	'1000-01-01 00:00:00' ~	날짜+시간 저장 (타임존 X)	주문 시간, 예약 시간 등
TIMESTAMP	'1970-01-01 00:00:01 UTC' ~	UTC 타임존 기준으로 저장, 자동 업데이트/기록에 최적화됨	로그 기록, 최신 수정 시간 등
TIME	'-838:59:59' ~ '838:59:59'	시간의 양을 나타냄	소요 시간, 근무 시간, 시간 차이 등
YEAR	1901 ~ 2155	INT(4)로도 대체 가능	졸업 연도, 회계 연도 등

 

 

3. 연습 문제

3.1. Nth Highest Salary (리트코드 177번)

📌문제

• Write a solution to find the n-th highest distinct salary from the Employee table.
• If there are less than n distinct salaries, return null.

📌풀이 1

• nth_salary, total_count 변수 선언 후 SELECT ...  INTO 사용해서 값 할당
• nth_salary는 Window 함수(DESNE_RANK) 활용해서 계산
• 최종 결과 RETURN 시 CASE WHEN 구문으로 조건 분기 (또는 IF 사용해도 됨!)

CREATE FUNCTION getNthHighestSalary(N INT)
    RETURNS INT
BEGIN
    DECLARE nth_salary INT;
    DECLARE total_count INT;

    SELECT COUNT(*) INTO total_count
    FROM employee;

    SELECT DISTINCT(salary) INTO nth_salary --⚠️DISTINCT 필수!
    FROM(
        SELECT
            *,
            DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
        FROM employee
    ) e
    WHERE salary_rank = N;

    RETURN CASE -- N이 행 개수보다 큰 경우 null 처리
        WHEN n > total_count THEN NULL
        ELSE nth_salary
    END;
END

-- [참고] CASE 대신 IF 사용한 풀이
CREATE FUNCTION getNthHighestSalary(N INT)
    RETURNS INT
BEGIN
    DECLARE nth_salary INT;
    DECLARE total_count INT;

    SELECT COUNT(*) INTO total_count
    FROM employee;

    SELECT DISTINCT(salary) INTO nth_salary 
    FROM(
        SELECT
            *,
            DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
        FROM employee
    ) e
    WHERE salary_rank = N;

    RETURN( -- 여기만 다름!
        SELECT
            IF(n > total_count, NULL, nth_salary) as r
    );
END

📌풀이 2

• 변수 선언 없이 서브쿼리 활용 (SELECT의 결과값이 하나만 나오게 만들어두고, 그걸 통째로 RETURN에 집어넣음👨🏻‍🏫)
• SELECT절 내에서 CASE WHEN으로 조건 분기 (또는 IF 사용해도 됨!)

CREATE FUNCTION getNthHighestSalary(N INT)
    RETURNS INT
BEGIN
    RETURN ( -- (3) SELECT 결과를 RETURN에 통째로 집어넣기
        SELECT
            CASE
                WHEN N > COUNT(*) THEN NULL
                ELSE MIN(e.salary) -- (2) 그걸 from절에 둔 뒤, MIN 출력하기
            END
        FROM (
            SELECT DISTINCT(salary) -- (1) 일단 N개 중복 없이 뽑아두고
            FROM employee
            ORDER BY salary DESC
            LIMIT N
        ) e
    );
END

-- [참고] CASE 대신 IF 사용한 풀이
CREATE FUNCTION getNthHighestSalary(N INT)
    RETURNS INT
BEGIN
    RETURN (
            SELECT
                IF(N > COUNT(*), NULL, MIN(salary)) AS result -- 여기만 다름!
            FROM(
                SELECT DISTINCT(salary)
                FROM employee
                ORDER BY salary DESC
                LIMIT N
                ) e
    );
END

📌풀이 3

• nth_salary, total_count, offset_val 변수 선언 후 SELECT ...  INTO 사용해서 값 할당
• nth_salary는 LIMIT & OFFSET 활용해서 계산 (= 풀이1과의 차이)

CREATE FUNCTION getNthHighestSalary(N INT)
    RETURNS INT
BEGIN
    DECLARE nth_salary INT;
    DECLARE total_count INT;
    DECLARE offset_val INT;

    SET offset_val = N - 1; --⚠️연산식을 LIMIT 절에 바로 넣을 수 없어서, 별도로 변수 할당

    SELECT COUNT(id) INTO total_count
    FROM employee;

    SELECT salary INTO nth_salary
    FROM(
        SELECT DISTINCT(salary)
        FROM employee
        ORDER BY salary DESC -- 내림차순 정렬한 뒤,
        LIMIT 1 OFFSET offset_val -- N-1개만큼 패스하고 1개 선택하기➡️Nth salary!
    ) e;

    RETURN CASE -- N이 행 개수보다 큰 경우 null 처리
            WHEN N > total_count THEN NULL
            ELSE nth_salary
        END;
END

📌풀이 3-1

• (근데 알고보니) offset_val 가 실제 행 개수보다 클 경우, SELECT 결과가 아무것도 없어서 RETURN은 자동으로 NULL을 반환하게 됨!
• ➡️ 풀이3처럼 LIMIT & OFFSET 쓰되, 조건 분기 안 해줘도 작동함! (물론 풀이3처럼 명시적으로 짠 쿼리가 더 좋다고 함.. by GPT)

CREATE FUNCTION getNthHighestSalary(N INT)
    RETURNS INT
BEGIN
    DECLARE offset_val INT;
    SET offset_val = N - 1; -- N-1로 바꾸기 위한 변수만 선언해주고,

    RETURN( -- 그냥 CASE문 없이 이렇게만 해도 통과됨!
        SELECT DISTINCT(salary)
        FROM employee
        ORDER BY salary DESC
        LIMIT 1 OFFSET offset_val
    );
END

---