oracle 문자열 테이블 리스트 만들기

oracle 문자열 테이블 리스트 만들기

오라클 문자열 테이블 리스트 만들기

• 실행예제 : SELECT * FROM TABLE(fn_to_table('value1,value2,value3'));
• Oracle 데이터베이스에서 문자열을 쉼표로 구분하여 분리하고, 분리된 각 값을 테이블 형태로 반환하는 함수 fn_to_table를 정의하는 SQL 스크립트입니다

CREATE OR REPLACE TYPE vc_array AS TABLE OF VARCHAR2(4000);
/

create or replace NONEDITIONABLE FUNCTION fn_to_table(list VARCHAR2)
RETURN vc_array PIPELINED
AS
  v_column_value VARCHAR2(4000);
BEGIN
  FOR i IN (SELECT REGEXP_SUBSTR(list, '[^,]+', 1, LEVEL) AS val
            FROM dual
            CONNECT BY REGEXP_SUBSTR(list, '[^,]+', 1, LEVEL) IS NOT NULL)
  LOOP
    v_column_value := i.val;
    PIPE ROW (v_column_value);
  END LOOP;
END;

• CREATE OR REPLACE TYPE vc_array AS TABLE OF VARCHAR2(4000); : 이 부분은 vc_array라는 이름의 사용자 정의 데이터 타입을 생성하거나 기존에 있는 타입을 대체합니다. TABLE OF VARCHAR2(4000)은 이 타입이 VARCHAR2(4000) 문자열을 여러 개 담을 수 있는 테이블(컬렉션) 형태임을 나타냅니다. 즉, 문자열 배열과 비슷한 역할을 합니다.
• RETURN vc_array PIPELINED : 이 함수는 vc_array 타입의 테이블을 반환하며, PIPELINED 키워드는 함수가 한 번에 모든 결과를 반환하는 것이 아니라, 각 행을 처리할 때마다 순차적으로 반환한다는 것을 의미합니다.  이 방식을 사용하면 대량의 데이터를 처리할 때 메모리 사용량을 줄일 수 있습니다.
• FOR i IN (SELECT REGEXP_SUBSTR(list, '[^,]+', 1, LEVEL) AS val ... ) :  FOR 루프를 사용하여 문자열을 분리하고 각 값을 처리합니다.
• REGEXP_SUBSTR(list, '[^,]+', 1, LEVEL) : 이 함수는 정규 표현식을 사용하여 문자열 list를 쉼표(,)를 기준으로 분리합니다. [^,]+는 쉼표가 아닌 문자가 하나 이상 연속되는 부분을 매치하는 정규 표현식입니다. 1은 검색 시작 위치, LEVEL은 현재 처리 중인 문자열 조각의 순서를 나타냅니다.
• PIPE ROW (v_column_value); :  v_column_value의 값을 결과 테이블의 한 행으로 반환합니다.  PIPELINED 함수이므로 PIPE ROW를 사용하여 각 행을 순차적으로 반환합니다.