Brain187. 사용자 정의 함수 (UDF, User Defined Function)
핵심 인사이트: 오라클이나 MySQL이 기본으로 제공하는
SUM(),SUBSTR()함수만으로는 회사의 복잡한 계산을 다 할 수 없다. "생년월일을 넣으면 만 나이와 띠를 계산해서 리턴해라"처럼 개발자가 직접 로직을 짜서 DB에 등록해 두고,SELECT문 안에서 자유롭게 호출해 쓰는 나만의 맞춤형 함수가 UDF다.
Ⅰ. 사용자 정의 함수 (UDF)의 개념
개발자가 데이터베이스의 절차적 언어(PL/SQL, T-SQL 등)를 사용하여, 특정한 비즈니스 로직(계산, 문자열 조작 등)을 구현한 후 데이터베이스 서버에 컴파일하여 저장해 두는 사용자 제작 함수입니다.
내장 함수(Built-in Function)처럼 SELECT, WHERE 구문 어디서든 자유롭게 재사용할 수 있습니다.
Ⅱ. 스토어드 프로시저(Procedure)와 UDF의 결정적 차이
가장 많이 헷갈리지만, 용도와 리턴 방식이 완전히 다릅니다.
| 구분 | 스토어드 프로시저 (Procedure) | 사용자 정의 함수 (UDF) |
|---|---|---|
| 목적 | 여러 테이블의 INSERT, UPDATE 등 데이터 상태를 변화시키는 작업(작업의 흐름) 에 씁니다. | 복잡한 수식을 계산하여 하나의 결과값을 리턴(계산식 대용) 할 때 씁니다. |
| 반환값(Return) | 리턴값이 없어도 되고, 여러 개여도 됩니다. | 반드시 하나의 스칼라 값(또는 테이블 형태)을 무조건 리턴(RETURN) 해야 합니다. |
| 호출 방식 | CALL 프로시저명(); 이라는 독립적인 명령어로 단독 실행해야 합니다. | SELECT UDF_만나이계산(생일) FROM 직원; 처럼 SQL 문장 내부에 섞여서 자연스럽게 호출됩니다. |
Ⅲ. UDF의 극명한 장단점
[ 장점: 가독성과 재사용성 ]
- 복잡한
CASE WHEN ~ THEN구문이나 중첩된 수학 공식을 쿼리에 수십 줄씩 쓰지 않고, 함수 호출 한 줄로 쿼리를 매우 깔끔하게(가독성 높게) 만들 수 있습니다.
[ 단점: 악마 같은 성능 저하 (Context Switching) ]
- UDF를
SELECT절에서 호출하면, 데이터베이스 엔진은 결과 집합의 행(Row) 수만큼 함수를 반복 호출(Loop) 해야 합니다. - 100만 건의 데이터를 조회할 때 UDF가 들어있으면, DB 엔진(SQL 실행기)과 PL/SQL 엔진(함수 실행기) 사이를 100만 번 왔다 갔다 하는 컨텍스트 스위칭(Context Switching) 오버헤드가 발생하여 쿼리가 끔찍하게 느려집니다.
Ⅳ. UDF 성능 최적화 대책 (스칼라 서브쿼리 캐싱)
이 끔찍한 함수 반복 호출을 막기 위해, UDF를 호출할 때 쿼리를 괄호로 묶어 스칼라 서브쿼리(Scalar Subquery) 로 감싸는 꼼수를 씁니다.
SELECT (SELECT UDF_부서명(부서코드) FROM DUAL) FROM 사원;- 이렇게 감싸면 오라클 옵티마이저가 함수의 입력값과 리턴값을 메모리에 살짝 캐싱(Caching) 해둡니다. 똑같은 부서코드 'A'가 들어오면 함수를 실행하지 않고 캐시에서 결과를 꺼내주므로 성능이 극적으로 향상됩니다.
📢 섹션 요약 비유: 프로시저가 식당 주방장에게 "오늘 들어온 고기 다 다져서 냉동실에 넣어놔!"라고 지시하는 '행동'이라면, 사용자 정의 함수는 홀 서빙 직원이 손님 나이를 계산하기 힘들어 주방장에게 "이 손님 민증이 99년생인데 몇 살이야?"라고 묻고 "25살이야"라는 단일한 정답(리턴값) 하나를 즉시 받아내는 '계산기' 역할입니다.