[Oracle] 프로시저에 배열을 활용한 MAP 만들기

Oracle내 프로시저가 너무 많아진 관계로 프로시저를 줄일 방법을 고민하던 와중에

매개변수로 각 table column 을 받는게 아니라 Varchar2 한줄로 받아 MAP처리하여 

Case문으로 상황에 맞는 Table처리를 하도록 하면 각 Table CRUD에 따른 프로시저를 줄일 수 있다 생각했습니다.

예를 들어 학생의 정보를 넣고 싶다하면 Column에 name, address, phone등이 들어갈 것입니다.

그리고 프로시저 매개변수를 한줄 'name:hong, address:seoul, phone:010-1234-5679'로 받아

이것을 INDEX처리한 VARRAY변수에 LOOP로 MAP처리 한 후 CRUD에 불러와 쓰는 것입니다.

 

그리하여 완성된 Procedure

create or replace 
PROCEDURE ARRAY_DATA  
( 
  ACTION IN VARCHAR2,
  ARRAY_DATA IN VARCHAR2
) AS 
  TYPE ARRAY_V IS TABLE OF VARCHAR2(4096) INDEX BY VARCHAR2(4096); 
   
  L_STRING LONG DEFAULT ARRAY_DATA || ','; 
  L_DATA ARRAY_V;
  N_VALUE NUMBER;
  N_KEY NUMBER;
BEGIN 
 -- ARRAY에 Data 저장 
  LOOP
    EXIT WHEN L_STRING IS NULL; 
    N_KEY := INSTR( L_STRING, ':' ); 
    N_VALUE := INSTR( L_STRING, ',' ); 
    L_DATA(LTRIM(RTRIM(SUBSTR(L_STRING, 1, N_KEY-1)))) 
        := LTRIM(RTRIM(SUBSTR(L_STRING, N_KEY+1, N_VALUE-N_KEY-1))); 
    L_STRING := SUBSTR( L_STRING, N_VALUE+1 ); 
  END LOOP;
  /* CASE문 써도 무방 */  
  IF(ACTION = '???') THEN 
 -- 처리 ( L_DATA('name') --> Key값으로 value값 불러오기 )
  END IF; 
END ARRAY_DATA;

좀더 자세히 살펴봅시다. 주요 핵심 부분은

LOOP 
    EXIT WHEN L_STRING IS NULL; 
    N_KEY := INSTR( L_STRING, ':' ); 
    N_VALUE := INSTR( L_STRING, ',' ); 
    L_DATA(LTRIM(RTRIM(SUBSTR(L_STRING, 1, N_KEY-1)))) 
        := LTRIM(RTRIM(SUBSTR(L_STRING, N_KEY+1, N_VALUE-N_KEY-1))); 
    L_STRING := SUBSTR( L_STRING, N_VALUE+1 ); 
END LOOP;

여기는 CRUD작업의 기초가 될 정보를 가진 L_STRING, 예를 들어 'name:hong, address:seoul, phone:010-1234-5679' 으로 매개변수를 받아와 L_DATA에 초기화 하는 작업입니다. ':' 과 ',' 으로 key값과 value 정보를 split하고 L_DATA(key) = value의 Map형식으로 맵핑하는 것 입니다.

L_DATA는 ARRAY_V라는 INDEX BY로 정렬된 UDT(User Defined Type) == index by tables 를 활용해 정의하였습니다.

INDEX BY TABLES의 경우 타입이 indexing되어 선언되었기에 값이 연속적일 필요가 없어 add를 이용해 순차적으로 value를 추가하지 않아도 됩니다.

Oracle 배열별 설명 참조 : https://oracle-base.com/articles/8i/collections-8i#index-by-tables

Collections in Oracle PL/SQL

 

다만 위 PL/SQL에서 아쉬운 점은 여러 프로시저에서 공통으로 사용될 LOOP 코드를 Function으로 빼서 사용할 수 없다는 점입니다. 적어도 제가 알아보고 시도해본 결과 할 수 없었습니다. 이것때문에 내 시간 이틀이...(아시는 분 댓글좀...)

실패한 사례로 다음과 같습니다.

1. 배열이 아닌 MAP으로 활용하기 위해선 UDT(User Defined Type)가 Index 처리 되어야합니다. 하지만 SQL단에서 Type 선언 시 Index처리가 안됩니다. 이유는 index by가 pl/sql 이기 때문.

2. Package에서 Function 및 UDT를 선언할 시 컴파일은 되나 외부에서 해당 패키지에 대한 UDT를 불러오기도 안되고 동일하게 프로시저 단에서 Type 선언 후 cast해도 안되었습니다. Package에서 UDT가 안되는 이유는 아래 URL 답변에 설명되어 있습니다.

//PACKAGE에서 PACKAGE로 type cast가 안되는 이유.

https://stackoverflow.com/questions/17033160/table-function-with-bulk-collection-throws-invalid-datatype

Table function with bulk collection throws invalid datatype

PIPELINED 이용하라는 글도 있었으나 PIPELINED의 경우 Collection 타입은 안된다 하여 적용하지 못하였습니다.

 

이러저러한 이유로 LOOP를 밖으로 뺄 수 없으나 만약 MAP이 아닌 배열 활용하고자 한다면 문제없이 Function으로 빼 배열을 리턴할 수 있으니 LOOP를 밖으로 빼도록 합시다.

 

ps. Oracle 12c부턴 json type을 지원한다하니 json을 이용하는 방법 또한 좋은 방향이라 생각합니다.