oracle 사용자 정의 함수 function 생성 및 실행 방법

Oracle 사용자 정의 함수(User-Defined Function) 생성 및 실행 방법

oracle 사용자 정의 함수 function 생성 및 실행 방법

오라클 데이터베이스에는 숫자 함수, 문자 함수, 날짜 처리 함수, 데이터 변환 함수 등 다양한 표준 함수가 존재하며, 이러한 표준 함수 외에도 사용자가 정의하여 사용하는 함수인 '사용자 정의 함수(User-Defined Function)'가 존재하는데요.

 

최근 oracle 사용자 정의 함수를 사용해 보게 되면서 함수의 생성 방법과 세부적인 내용을 정리하였으며, 참고할 수 있는 간단한 사용 예시도 함께 기록하였습니다.

 

---

Function 특징

먼저 사용자 정의 함수(User-Defined Function)의 몇 가지 주요 특징을 살펴보면 아래와 같습니다.

(프로시저와의 차이점에 대한 내용도 일부 포함되어 있습니다.)

 

 

- 함수(Function)는 데이터를 가공 또는 계산하여 특정 값을 반환하는 데 사용됩니다. 프로시저(Procedure)가 값을 반환하지 않고 특정 작업을 절차적으로 처리하는 것과 차이점이 있습니다.

 

- 프로시저가 PL/SQL 문으로 실행되는 반면, 함수는 SQL 쿼리나 PL/SQL Block에서 호출하여 식의 일부로서 사용할 수 있습니다.

 

- 함수는 사용자가 원하는 특정 기능을 모듈화 하여 재사용할 수 있기 때문에 식의 일부로서 사용 시 복잡한 쿼리문을 간결하게 만들 수 있습니다.

 

- 함수는 단일 값을 반환하며, 반환될 데이터 타입을 RETURN 문에 반드시 선언해야 합니다.

 

- 함수는 내부적으로 INSERT, UPDATE, DELETE를 통한 데이터 조작은 할 수 없으며, SELECT를 통한 조회만 가능합니다.

(기본적으로는 SELECT만 사용할 수 있지만, 'PRAGMA AUTONOMOUS_TRANSACTION'을 사용하면 INSERT, UPDATE, DELETE도 사용할 수 있습니다.)

 

 

***

다중 컬럼, 다중 로우를 반환하려면 'Table Function' 또는 'Pipelined Table Function'을 사용해야 합니다.

 

 

---

생성 기본 형식

CREATE [OR REPLACE] FUNCTION 함수명
[(매개변수)]
RETURN 반환 데이터 타입
IS
	[선언부 - 변수 선언]
BEGIN
	[실행부 - PL/SQL Block]
[EXCEPTION]
	[EXCEPTION 처리 구문]
	RETURN 반환 데이터;
END 함수명;

 

함수를 생성하는 기본적인 구문은 다음과 같으며, 세부적인 내용은 아래와 같습니다.

 

 

- '[ ]' 대괄호 안의 구문은 생략 가능한 부분입니다.

 

- CREATE 뒤에 사용되는 'OR REPLACE'는 해당 함수가 이미 존재할 경우 기존의 내용을 지우고 재생성하는 구문으로, 생략 가능하지만 CREATE만 사용 시 해당 함수가 존재할 경우 오류가 발생하기 때문에 대부분 생략하지 않고 사용합니다.

 

- 사용할 매개변수의 선언은 '매개변수명 [매개변수타입] 데이터타입'으로 사용하며, 데이터의 크기를 설정해주지 않아도 됩니다. 매개변수 타입으로는 'IN', 'OUT', 'IN OUT'이 있는데, 프로시저와 다르게 함수에서는 기본적으로 'IN' 파라미터만 허용됩니다. 타입을 생략할 경우 기본 타입은 'IN'이 적용됩니다.

 

/*

IN: 값을 전달할 때 사용됩니다. 읽을 수는 있지만 변경할 수는 없는 값입니다.

OUT: 값을 반환할 때 사용됩니다. 함수나 프로시저 내에서 값을 할당하고 호출자는 그 값을 받아 사용할 수 있습니다. 호출자는 OUT 매개변수를 초기화한 후 함수나 프로시저를 호출해야 합니다.

IN OUT: 값을 읽고 쓸 때 사용됩니다. 호출자가 전달한 값에 대한 변경을 함수나 프로시저에서 직접 반영할 수 있습니다.

*/

 

- 함수 내에서 사용할 변수 선언은 '변수명 데이터타입(크기)'로 사용합니다.

 

- 앞서 이야기한 것처럼 함수는 RETURN 문에 반환될 데이터 타입을 필수로 명시해야 합니다. 또한 RETURN 문에 선언된 반환 데이터 타입과 실행부에서 반환하는 데이터 타입이 일치해야 합니다.

 

- 'BEGIN'과 'END'는 함수 내에서 실행할 핵심 내용이 들어가는 실행부의 시작과 종료를 표현하는 데 필수적입니다. IF, WHILE, FOR, LOOP 등의 제어문을 사용하거나, 특정 테이블에 대한 조회를 통해 필요한 값을 반환할 수 있습니다. 실행부가 끝나기 전에 RETURN을 통해 함수의 결과 데이터를 반환해야 한다는 특징이 있습니다.

 

 

---

간단한 생성 및 실행 예시

// 사원 번호를 통해 부서 번호를 반환하는 함수 생성 예시
CREATE OR REPLACE FUNCTION FN_EMPNO_TO_DEPTNO
(
    P_EMPNO IN NUMBER
)
RETURN NUMBER
IS
    V_DEPTNO NUMBER(2);
BEGIN
    SELECT emp.deptno
    INTO V_DEPTNO
    FROM emp
    WHERE emp.empno = P_EMPNO;
    
    RETURN V_DEPTNO;
END FN_EMPNO_TO_DEPTNO;

(사용자 정의 함수 생성 예시)

 

// 함수 호출 예시1
SELECT FN_EMPNO_TO_DEPTNO(1001) 
FROM dual;

// 함수 호출 예시2
SELECT * 
FROM emp 
WHERE emp.deptno = FN_EMPNO_TO_DEPTNO(1001);

(생성된 함수 호출 예시, 2번 예시의 경우 where 절에서도 사용할 수 있다는 것을 보여주기 위한 쿼리입니다.)

 

생성한 사원 번호를 받아 부서 번호를 반환하는 간단한 함수의 생성 예시 및 호출 예시입니다.

 실행부(BEGIN/END)의 내용을 살펴보면 SELECT 문에 대한 결과를 'INTO' 절을 사용하여 위에서 선언한 변수인 'V_DEPTNO'에 저장하고 RETURN 문을 통해 반환하는 것을 확인할 수 있는데요.

 

추가적으로 오라클에서 대입 연산자는 ':='가 사용되기 때문에 변수에 대한 초기 값을 세팅하거나 하는 경우 'V_DEPTNO NUMBER(2) := 0;' 처럼 값을 대입할 수 있습니다.

 

 

 

 

< 참고 자료 >

https://benggri.tistory.com/77