사용자 정의 함수와 집계 함수

Oracle 사용자 정의 함수 및 집계 함수 완벽 가이드

Oracle 사용자 정의 함수 및 집계 함수

Oracle 데이터베이스는 내장 함수 외에도 사용자 정의 함수(User-Defined Functions, UDF)와 사용자 정의 집계 함수(User-Defined Aggregate Functions)를 통해 데이터 처리 능력을 확장할 수 있는 강력한 기능을 제공합니다. 이번 가이드에서는 이러한 함수들을 자세히 알아보고, 실무에 적용 가능한 다양한 예제와 함께 설명하겠습니다.

사용자 정의 함수 (User-Defined Functions, UDF)

사용자 정의 함수는 특정 작업을 수행하기 위해 사용자가 직접 정의하는 함수입니다. PL/SQL, Java, C 등 다양한 언어로 작성할 수 있으며, SQL 쿼리 내에서 호출하여 사용할 수 있습니다.

사용자 정의 함수의 장점

  • 코드 재사용성: 반복되는 로직을 함수로 캡슐화하여 코드 중복을 줄이고 유지보수성을 향상시킵니다.
  • SQL 확장성: 내장 함수만으로는 구현하기 어려운 복잡한 로직을 구현할 수 있습니다.
  • 모듈화: 복잡한 쿼리를 여러 개의 함수로 분할하여 가독성을 높입니다.

사용자 정의 함수의 종류

사용자 정의 함수는 크게 스칼라 함수(Scalar Functions)와 테이블 함수(Table Functions)로 나눌 수 있습니다.

  • 스칼라 함수: 단일 값을 반환하는 함수로, SQL 표현식 내에서 사용됩니다.
  • 테이블 함수: 테이블 형태의 결과 집합을 반환하는 함수로, FROM 절에서 테이블처럼 사용됩니다.

스칼라 함수 예제 (PL/SQL)

다음은 PL/SQL로 작성된 스칼라 함수의 예제입니다. 이 함수는 주어진 부서 번호에 해당하는 부서명을 반환합니다.

CREATE OR REPLACE FUNCTION get_dept_name (p_deptno IN NUMBER) RETURN VARCHAR2 IS  v_dname VARCHAR2(30); BEGIN  SELECT dname  INTO v_dname  FROM dept  WHERE deptno = p_deptno;  RETURN v_dname; EXCEPTION  WHEN NO_DATA_FOUND THEN  RETURN NULL; END; /

실행 결과:

SELECT empno, ename, get_dept_name(deptno) AS department_name FROM emp WHERE deptno IN (10, 20);

위 쿼리를 실행하면 각 사원의 사원번호, 이름, 그리고 해당 부서의 부서명이 함께 출력됩니다.

또 다른 예시로, 문자열을 자르는 사용자 정의 함수를 살펴보겠습니다.

CREATE OR REPLACE FUNCTION truncate_string (  p_string IN VARCHAR2,  p_length IN NUMBER ) RETURN VARCHAR2 IS BEGIN  IF p_string IS NULL THEN  RETURN NULL;  END IF;  IF LENGTH(p_string) <= p_length THEN  RETURN p_string;  END IF;  RETURN SUBSTR(p_string, 1, p_length) || '...'; END; /

실행 결과:

SELECT ename, truncate_string(ename, 5) AS truncated_name FROM emp;

이 쿼리를 실행하면 사원 이름이 5글자를 초과할 경우 뒤에 “…”이 붙어 출력됩니다.

테이블 함수 예제 (PL/SQL)

테이블 함수는 테이블 형태의 결과 집합을 반환합니다. 다음은 주어진 부서 번호에 해당하는 사원 목록을 반환하는 테이블 함수 예제입니다.

CREATE OR REPLACE TYPE emp_record AS OBJECT (  empno NUMBER,  ename VARCHAR2(10),  job VARCHAR2(9),  sal NUMBER ); / CREATE OR REPLACE TYPE emp_table AS TABLE OF emp_record; / CREATE OR REPLACE FUNCTION get_emps_by_dept (p_deptno IN NUMBER) RETURN emp_table PIPELINED IS  v_emps emp_table; BEGIN  FOR rec IN (SELECT empno, ename, job, sal FROM emp WHERE deptno = p_deptno) LOOP  PIPE ROW(emp_record(rec.empno, rec.ename, rec.job, rec.sal));  END LOOP;  RETURN; END; /

실행 결과:

SELECT * FROM TABLE(get_emps_by_dept(20));

위 쿼리를 실행하면 부서 번호 20번에 해당하는 사원 목록이 테이블 형태로 출력됩니다.

사용자 정의 집계 함수 (User-Defined Aggregate Functions)

사용자 정의 집계 함수는 여러 행의 데이터를 기반으로 계산된 단일 값을 반환하는 함수입니다. 예를 들어, 평균, 합계, 최댓값, 최솟값 등이 있습니다. Oracle은 기본적으로 다양한 집계 함수를 제공하지만, 사용자는 특정 요구사항에 맞춰 사용자 정의 집계 함수를 만들 수 있습니다.

사용자 정의 집계 함수의 장점

  • 특정 요구사항 충족: Oracle의 내장 집계 함수만으로는 처리하기 어려운 특별한 계산을 수행할 수 있습니다.
  • 성능 향상: 복잡한 계산 로직을 데이터베이스 서버 내에서 처리하여 클라이언트-서버 간 데이터 전송량을 줄입니다.

사용자 정의 집계 함수의 구현

사용자 정의 집계 함수는 사용자 정의 타입(User-Defined Type)과 함께 구현해야 합니다. 다음은 사용자 정의 집계 함수 구현의 일반적인 단계입니다.

  1. 사용자 정의 타입 생성: 집계 함수의 상태를 저장할 사용자 정의 타입을 생성합니다. 이 타입은 집계 함수의 상태를 초기화, 갱신, 병합, 종료하는 데 사용되는 메서드를 포함해야 합니다.
  2. 집계 함수 생성: 사용자 정의 타입을 기반으로 집계 함수를 생성합니다.

사용자 정의 집계 함수 예제 (PL/SQL)

다음은 사용자 정의 집계 함수의 예제입니다. 이 함수는 문자열 값들을 연결하여 하나의 문자열로 반환합니다.

CREATE OR REPLACE TYPE string_concat_type AS OBJECT (  result VARCHAR2(32767),  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_concat_type) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_concat_type, value IN VARCHAR2) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateTerminate(self IN string_concat_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_concat_type, ctx2 IN string_concat_type) RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY string_concat_type AS STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_concat_type) RETURN NUMBER IS BEGIN  sctx := string_concat_type(NULL);  RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_concat_type, value IN VARCHAR2) RETURN NUMBER IS BEGIN  IF self.result IS NULL THEN  self.result := value;  ELSE  self.result := self.result || ',' || value;  END IF;  RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateTerminate(self IN string_concat_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS BEGIN  returnValue := self.result;  RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_concat_type, ctx2 IN string_concat_type) RETURN NUMBER IS BEGIN  IF ctx2.result IS NOT NULL THEN  IF self.result IS NULL THEN  self.result := ctx2.result;  ELSE  self.result := self.result || ',' || ctx2.result;  END IF;  END IF;  RETURN ODCIConst.Success; END; END; / CREATE OR REPLACE FUNCTION string_concat (input VARCHAR2) RETURN VARCHAR2 AGGREGATE USING string_concat_type; /

실행 결과:

SELECT string_concat(ename) AS concatenated_names FROM emp WHERE deptno = 10;

위 쿼리를 실행하면 부서 번호 10번에 해당하는 사원 이름들이 쉼표(,)로 구분되어 하나의 문자열로 출력됩니다.

결론

Oracle 데이터베이스의 사용자 정의 함수와 사용자 정의 집계 함수는 데이터 처리 능력을 확장하고, 특정 요구사항을 충족하는 데 유용한 기능입니다. 이번 가이드를 통해 이러한 함수들을 이해하고, 실제 개발 환경에서 활용하여 데이터베이스 성능을 향상시키고 효율적인 데이터 처리를 구현할 수 있습니다.

위로 스크롤