28_ Oracle 사용자 정의 함수

Oracle에서 사용자 정의 함수는 개발자가 직접 작성해서 재사용할 수 있는 프로그램 단위입니다. 자주 쓰는 로직이나 계산을 함수로 만들어 두면, SQL문 안에서도 바로 활용할 수 있어 편리합니다.

 

1. 사용자 정의 함수 특징

• 반드시 하나의 값 반환: RETURN 문이 필수입니다.
• SELECT 문에서 컬럼처럼 사용 가능: SQL 안에서 직접 호출할 수 있다.
• PL/SQL, Java, C 등으로 작성 가능: Oracle은 다양한 언어 지원.

 

2. 함수 생성 문법

CREATE [OR REPLACE] FUNCTION 함수명 
    (파라미터1 [IN] 데이터타입, 
     파라미터2 [IN] 데이터타입, ...)
RETURN 리턴타입
IS [AS]
    -- 변수 선언부
BEGIN
    -- 실행부
    RETURN 반환값;
EXCEPTION
    -- 예외 처리부
END;
/

 

• OR REPLACE: 기존 함수를 덮어쓰기
• IN 파라미터: 함수 입력값, 함수에서는 OUT이나 INOUT 불가
• EXCEPTION 블록: 오류 발생 시 처리

 

3. 함수 사용 예제

예제 1: 사원의 연봉 계산

CREATE OR REPLACE FUNCTION calc_annual_salary 
    (p_salary IN NUMBER, 
     p_commission IN NUMBER)
RETURN NUMBER
IS
    v_annual_salary NUMBER;
BEGIN
    v_annual_salary := (p_salary * 12) + (p_salary * NVL(p_commission, 0));
    RETURN v_annual_salary;
END;
/

 

사용 방법

SELECT emp_name, 
       salary,
       calc_annual_salary(salary, commission_pct) AS annual_salary
FROM employees;

 

4. 주의사항

• DML 문장(INSERT, UPDATE, DELETE) 사용 불가
• 트랜잭션 제어(COMMIT, ROLLBACK) 사용 불가
• 함수 내에서 다른 함수 호출은 가능

 

5. 함수 관리

• 삭제:

DROP FUNCTION 함수명;

권한 부여:

GRANT EXECUTE ON 함수명 TO 사용자명;

 

6. 함수의 장점

• 코드 재사용성 향상
• 비즈니스 로직 모듈화
• 유지보수 용이
• 자주 쓰이는 로직의 경우 성능 향상

 

7. 프로시저 vs 함수 비교

항목                                      프로시저(Procedure)                                                함수(Function)

반환값	없음 또는 OUT 파라미터 가능	반드시 하나 반환
호출 위치	독립 PL/SQL 문장으로 실행	SQL 문 일부로 사용 가능
DML 사용	가능	불가
트랜잭션 제어	COMMIT, ROLLBACK 가능	불가
주요 용도	데이터 처리 작업	값 계산 및 반환
파라미터 모드	IN, OUT, INOUT	IN만 사용 가능

 

8. 디버깅 및 오류 처리 예제

0으로 나누기 등 예외 상황을 처리하는 함수 예시:

CREATE OR REPLACE FUNCTION safe_divide 
    (p_numerator IN NUMBER, 
     p_denominator IN NUMBER)
RETURN NUMBER
IS
    v_result NUMBER;
BEGIN
    IF p_denominator = 0 THEN
        RETURN NULL;
    END IF;

    v_result := p_numerator / p_denominator;
    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
/

 

• 0으로 나눌 경우 NULL 반환
• 예상치 못한 오류도 EXCEPTION 블록으로 안전하게 처리