DBMS_TF 패키지로 구현하는 Polymorphic Table Functions 활용법

 

 

SQL을 다루는 개발자라면 한번쯤 이런 고민을

해보셨을 겁니다.

"이 함수, 어떤 테이블을 넣어도 자동으로 처리할 수는

없을까?" 혹은 "컬럼 이름조차 모르는 상태에서

데이터를 가공할 방법은 없을까?"

 

바로 이 지점에서 Oracle Database 18c가 내놓은

해답이 Polymorphic Table Functions(PTF)

입니다.

 

기존 테이블 함수는 컴파일 시점에 반환할 구조가 고정돼

있었습니다.

하지만 PTF는 런타임, 즉 쿼리 실행 순간에 입력 테이블

을 보고 출력 구조를 결정합니다.

마치 변신 로봇처럼 상황에 맞춰 형태를 바꾸는 셈

입니다.

---

 

. PTF란 무엇인가? 개념부터 차근차근

 

Polymorphic Table Function은 직역하면

'다형성 테이블 함수'입니다.

쉽게 말해 입력에 따라 모양이 바뀌는 함수라고 생각하면

됩니다.

 

. 기존 테이블 함수와의 결정적 차이

구분	기존 테이블 함수	PTF
구조 결정 시점	컴파일 타임	런타임 (실행 시점)
입력 유연성	특정 타입만 가능	어떤 테이블이든 가능
컬럼 추가/삭제	불가능	자유롭게 가능
활용 범위	고정된 데이터 가공	ETL, 마스킹, 통계 등 범용 처리

 

. 핵심 특징 4가지

 

1. 입력 테이블 구조를 몰라도 작동합니다. 

   제네릭(Generic) 방식으로 설계돼 있어 어떤 테이블이

   들어와도 메타데이터를 읽어 처리합니다.

 

2. 행 단위 또는 집합 단위 처리가 가능합니다. 

   ROW Semantics와 SET Semantics를 모두 지원해

   데이터 처리 전략을 유연하게 가져갈 수 있습니다.

 

3. 컬럼을 통과시키거나 새로 추가할 수 있습니다. 

   Pass-through 옵션으로 기존 컬럼을 그대로 유지하면

   서 계산된 컬럼을 덧붙이는 방식이 가능합니다.

 

4. SQL 레벨에서 복잡한 로직을 처리합니다. 

   PL/SQL 절차적 로직과 SQL 선언적 처리의 장점을

   결합해 고성능 데이터 가공이 가능합니다.

---

 

. PTF는 어떻게 동작할까?

 

PTF는 크게 선언부와 구현부로 나뉩니다.

데이터베이스 엔진은 쿼리 실행 시 구현 패키지의 메서드

를 순서대로 호출하며 데이터를 처리합니다.

 

 

. 실행 흐름 4단계 ( 필수 / 선택 옵션 )

 

1단계 - DESCRIBE (필수)

  쿼리 파싱 시점에 호출됩니다. 입력으로 들어온 테이블

  정보를 받아서 출력될 테이블의 모양(Shape)을 정의

  합니다.

  "이 함수는 이런 컬럼을 내보낼 거예요"라고 선언하는

  단계입니다.

 

2단계 - OPEN (선택)

  실행 계획 수립 후 실제 처리가 시작되기 전 초기화 작업

  을 수행합니다. 상태값 설정이나 변수 초기화 등이

  이뤄집니다.

 

3단계 - FETCH_ROWS (선택)

  실제 데이터를 청크(Chunk) 단위로 읽어와 가공합니다.

  컬럼 값 계산, 필터링, 변환 등 핵심 로직이 여기서 실행

  됩니다.

 

4단계 - CLOSE (선택)

  실행 종료 후 리소스를 정리합니다. 메모리 해제나

  임시 객체 삭제 등을 처리합니다.

---

 

. DBMS_TF 패키지 핵심 구성 요소

 

DBMS_TF는 PTF를 만들기 위한 도구 상자입니다.

데이터 타입부터 유틸리티 프로시저까지 필요한 모든

것이 들어 있습니다.

 

• 주요 데이터 타입

. TABLE_T

  입력 테이블의 메타데이터를 담는 레코드입니다.

  스키마 이름, 테이블명, 컬럼 목록 등이 포함됩니다.

. COLUMNS_T

  특정 컬럼들의 정보를 담는 컬렉션입니다. 컬럼 이름과

  데이터 타입 정보를 배열 형태로 관리합니다.

. COLUMN_METADATA_T

  개별 컬럼의 속성을 정의합니다. 타입, 길이, 정밀도 등

  세부 정보를 설정할 때 사용합니다.

. DESCRIBE_T

  DESCRIBE 함수가 최종적으로 반환하는 타입입니다.

  새로운 컬럼 정의나 출력 구조 정보가 담깁니다.

. ROW_SET_T

  FETCH_ROWS 단계에서 처리할 실제 데이터 행들의

  집합입니다.

 

• 핵심 서브프로그램

. GET_COL_VAL

  현재 처리 중인 Row Set에서 특정 컬럼의 값들을 배열

  로 가져옵니다. 숫자, 문자, 날짜 등 타입별로 오버로딩

  돼 있습니다.

. PUT_COL_VAL

  계산된 값을 결과 Row Set의 특정 컬럼에 씁니다.

  주로 새로 추가한 컬럼에 값을 채울 때 활용합니다.

. TRACE

  디버깅용 메시지를 출력합니다. 개발 단계에서 로직

  흐름을 추적할 때 유용합니다.

---

 

. STATUS 컬럼을 동적으로 추가하기

 

입력받은 테이블 데이터는 그대로 두고,

STATUS_FLAG라는 새 컬럼을 추가해 값을 채워 넣는

예제입니다.

 

Q. PTF를 만들려면 무엇이 필요한가요?

구현 패키지와 SQL 인터페이스 함수 두 가지가 필요

합니다. 구현 패키지에는 실제 로직(DESCRIBE,

FETCH_ROWS)을 작성하고, 함수는 SQL에서

호출할 수 있는 진입점 역할을 합니다.

 

Q. DESCRIBE 함수에서는 무엇을 정의하나요?

출력될 테이블의 구조를 정의합니다. 기존 컬럼을 통과

시킬지, 새 컬럼을 추가할지, 추가할 컬럼의 이름과

타입은 무엇인지를 결정합니다.

 

Q. FETCH_ROWS에서는 어떤 작업을 하나요?

실제 데이터를 읽어와 가공합니다. GET_COL_VAL로

값을 읽고, 로직을 수행한 뒤, PUT_COL_VAL로 결과

컬럼에 값을 씁니다.

---

 

. PTF 활용 시나리오

 

. 동적 데이터 마스킹

  사용자 권한이나 파라미터에 따라 특정 컬럼 값을 별표

  처리하여 출력합니다. 개인정보 보호가 필요한 시스템

  에서 유용합니다.

 

. 범용 CSV or JSON Parser

  BLOB이나 CLOB 컬럼 하나를 입력받아 파싱한 뒤,

  여러 개의 컬럼으로 펼쳐 보여주는 함수를 만들 수

  있습니다.

 

. 복잡한 통계 계산

  윈도우 함수로 처리하기 어려운 절차적 로직을 SQL

  흐름 내에서 고성능으로 처리합니다.

  Bulk Processing 방식으로 대용량 처리에 강점을

  보입니다.

 

. Top-N 필터링

  FETCH_ROWS 단계에서 불필요한 행을 제외하고

  원하는 데이터만 골라 결과셋을 구성합니다.

---

 

. 알아두면 좋은 제약 사항

 

. PL/SQL Context Overhead

  PTF 구현부는 PL/SQL 엔진에서 실행되므로 Context

  Switching 비용이 발생할 수 있습니다.

  Native Compilation을 활용하면 일부 완화됩니다.

 

. 병렬 처리 전략

  PARALLEL_ENABLE을 지원하지만,

  ROW Semantics 방식일 경우 파티셔닝 전략을 신중

  하게 설계해야 합니다.

 

. DML 제약

  PTF 내부에서 자율 트랜잭션 없이는 INSERT나

  UPDATE 같은 DML을 직접 수행할 수 없습니다.

---

 

 

Oracle PTF는 SQL과 PL/SQL의 경계를 허무는 강력한

도구입니다.

처음엔 복잡해 보이지만, DESCRIBE와 FETCH_ROWS

두 가지 메서드만 이해하면 금방 감을 잡을 수 있습니다.

여러분의 데이터베이스 환경에서 어떤 방식으로 활용할

수 있을지 고민해 보시고, 댓글로 경험을 공유해 주세요.

함께 배우고 성장하는 개발자 커뮤니티가 되길 바래

봅니다.