사용자 정의 함수

상위 문서: SQL

User-Defined Function
사용자가 절차형 SQL로 작성하여 DBMS에 저장해 두고, 일련의 연산을 처리한 뒤 결과를 단일 값으로 반환하는 함수

개요

SUM, UPPER처럼 DBMS가 기본 제공하는 내장 함수와 달리, 업무에 필요한 계산을 사용자가 직접 정의한다.
저장 프로시저, 트리거와 함께 절차형 SQL의 대표적인 형태다. 선언부, 실행부(BEGIN ~ END), 예외 처리부로 구성된다.
반드시 RETURN 문으로 하나의 값을 돌려준다.
SELECT, WHERE 등 SQL 문 안에서 내장 함수처럼 호출할 수 있다.

문법 (Oracle PL/SQL)

CREATE [OR REPLACE] FUNCTION 함수명 (매개변수 [IN] 자료형, ...)
RETURN 반환자료형
IS
    변수 선언;
BEGIN
    처리 문장;
    RETURN 반환값;
EXCEPTION
    WHEN 예외명 THEN 예외 처리 문장;
END;
/

예시

급여를 받아 등급을 돌려주는 함수를 만들고 SELECT 문에서 호출한다.

CREATE OR REPLACE FUNCTION fn_grade (p_sal IN NUMBER)
RETURN VARCHAR2
IS
    v_grade VARCHAR2(1);
BEGIN
    IF p_sal >= 500 THEN
        v_grade := 'A';
    ELSIF p_sal >= 400 THEN
        v_grade := 'B';
    ELSE
        v_grade := 'C';
    END IF;
    RETURN v_grade;
END;
/

SELECT 이름, 급여, fn_grade(급여) AS 등급
FROM 사원;

삭제: DROP FUNCTION fn_grade;
MySQL에서는 RETURN 대신 RETURNS 자료형으로 반환형을 선언하고, 본문 안에서 RETURN 값;으로 반환한다.

프로시저·트리거와의 비교

구분	사용자 정의 함수	프로시저	트리거
반환값	반드시 1개(RETURN)	없음. OUT 매개변수로 0개 이상 전달	없음
호출 방식	SQL 문이나 식 안에서 호출	EXECUTE, CALL 또는 다른 PL/SQL 블록에서 호출	직접 호출할 수 없고, 이벤트 발생 시 자동 실행
사용 위치	SELECT 목록, WHERE, 다른 PL/SQL 식	독립 실행, 응용 프로그램	INSERT·UPDATE·DELETE 등 이벤트가 일어난 테이블
DML	SQL 문 안에서 호출될 때는 테이블을 변경할 수 없음	가능	가능
트랜잭션 제어(COMMIT, ROLLBACK)	SQL 문에서 호출될 때 불가	가능	불가(자율 트랜잭션 제외)

Oracle에서 SELECT 문이 호출한 함수 안에서 INSERT·UPDATE·DELETE를 실행하면 ORA-14551 오류가 난다. 교재에서 "사용자 정의 함수는 DML을 쓸 수 없다"고 설명하는 이유다.

시험 포인트

필기: 사용자 정의 함수의 특징(단일 값 반환, RETURN 사용, SQL 문 안에서 호출)과 프로시저·트리거의 구분
실기: PL/SQL 함수 코드에서 반환 결과 구하기, 빈칸(RETURN, IS, BEGIN, END) 채우기
트리거는 직접 호출하지 않고 이벤트로 실행된다는 점

같이 보기

참고 문헌