오라클에서 csv를 테이블로 변환하는 방법

오라클에서 csv를 테이블로 변환하는 방법

csv 값으로 전달되었을 때 테이블 형식으로 결과를 반환하는 패키지를 어떻게 만들 수 있습니까?

select * from table(schema.mypackage.myfunction('one, two, three'))

돌아와야 합니다

one
two
three

나는 asktom에서 무언가를 시도했지만 그것은 오직 sql 유형에서만 작동합니다.

저는 오라클 11g를 사용하고 있습니다.뭔가 붙박이가 있습니까?

다음 작업에서는 테이블(분할기('a,b,c,d'))에서 선택 *로 호출합니다.

create or replace function splitter(p_str in varchar2) return  sys.odcivarchar2list
is
v_tab sys.odcivarchar2list:=new sys.odcivarchar2list();
begin
with cte as (select level  ind from dual
connect by 
level <=regexp_count(p_str,',') +1
)
select regexp_substr(p_str,'[^,]+',1,ind)
bulk collect into v_tab
from cte;
return v_tab;
end;
/

아아, 11g에서는 여전히 SQL 유형을 사용하여 자체 PL/SQL 토큰화기를 수동으로 제어해야 합니다.11gR2에서 Oracle은 결과를 CSV 문자열로 연결하는 집계 기능을 제공했으므로 12i에서는 역기능을 제공할 것입니다.

특히 SQL 유형을 생성하지 않으려면 기본 제공 SYS를 사용할 수 있습니다.DBMS_DEBUG_VC2COLL은 다음과 같습니다.

create or replace function string_tokenizer
    (p_string in varchar2
        , p_separator in varchar2 := ',')
    return sys.dbms_debug_vc2coll
is
    return_value SYS.DBMS_DEBUG_VC2COLL;
    pattern varchar2(250);
begin

    pattern := '[^('''||p_separator||''')]+' ;

    select trim(regexp_substr (p_string, pattern, 1, level)) token
    bulk collect into return_value
    from dual
    where regexp_substr (p_string, pattern, 1, level) is not null
    connect by regexp_instr (p_string, pattern, 1, level) > 0;

    return return_value;

end string_tokenizer;
/

다음과 같이 작동합니다.

SQL> select * from table (string_tokenizer('one, two, three'))
  2  /

COLUMN_VALUE
----------------------------------------------------------------
one
two
three

SQL>

승인: 이 코드는 Tanel Poder의 블로그에서 찾은 일부 코드의 변형입니다.

다음은 전체적으로 sql에서 정규식 일치기를 사용하는 다른 솔루션입니다.

SELECT regexp_substr('one,two,three','[^,]+', 1, level) abc
FROM dual 
CONNECT BY regexp_substr('one,two,three', '[^,]+', 1, level) IS NOT NULL

최적의 성능을 위해 분할기 기능에서 계층형(CONNECT BY) 쿼리를 사용하지 않는 것이 가장 좋습니다.

다음 스플리터 기능은 더 큰 데이터 볼륨에 적용할 때 훨씬 더 나은 성능을 발휘합니다.

CREATE OR REPLACE FUNCTION row2col(p_clob_text IN VARCHAR2) 
   RETURN sys.dbms_debug_vc2coll PIPELINED 
IS
     next_new_line_indx PLS_INTEGER;
     remaining_text VARCHAR2(20000);
     next_piece_for_piping VARCHAR2(20000);
  BEGIN

    remaining_text := p_clob_text;
    LOOP
       next_new_line_indx := instr(remaining_text, ','); 
       next_piece_for_piping :=
          CASE
             WHEN next_new_line_indx <> 0 THEN
                TRIM(SUBSTR(remaining_text, 1, next_new_line_indx-1))
             ELSE
                TRIM(SUBSTR(remaining_text, 1))
          END;

       remaining_text := SUBSTR(remaining_text, next_new_line_indx+1 );
       PIPE ROW(next_piece_for_piping);
       EXIT WHEN next_new_line_indx = 0 OR remaining_text IS NULL;
    END LOOP;
    RETURN;
  END row2col;
/

이 성능 차이는 아래에서 확인할 수 있습니다(이 논의의 앞부분에서 설명한 대로 기능 분할기를 사용했습니다).

SQL> SET TIMING ON
SQL>
SQL> WITH SRC AS (
  2  SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt
  3  FROM DUAL
  4  CONNECT BY LEVEL <=10000
  5  )
  6  SELECT  NULL
  7  FROM SRC, TABLE(SYSTEM.row2col(txt)) t
  8  HAVING MAX(t.column_value) > 'zzz'
  9  ;

no rows selected

Elapsed: 00:00:00.93
SQL>
SQL> WITH SRC AS (
  2  SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt
  3  FROM DUAL
  4  CONNECT BY LEVEL <=10000
  5  )
  6  SELECT  NULL
  7  FROM SRC, TABLE(splitter(txt)) t
  8  HAVING MAX(t.column_value) > 'zzz'
  9  ;

no rows selected

Elapsed: 00:00:14.90
SQL>
SQL> SET TIMING OFF
SQL>

나는 가지고 놀 11g이 설치되어 있지 않지만 열을 행으로/행을 열로 변환하는 피벗 및 UNPIVOT 작업이 있어 좋은 출발점이 될 수 있습니다.

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html

(실제로 몇 가지 추가 조사를 해본 결과, 이 경우에는 적합하지 않은 것으로 보입니다. 실제 행/열에는 적용되지만 열에는 데이터 집합이 적용되지 않습니다.)

CSV 목록을 pl/sql 테이블로 변환하기 위한 DBMS_UTILITY.comma_to_table 및 table_to_comma도 있습니다.몇 가지 제한 사항(라인 피드 처리 등)이 있지만 좋은 출발점이 될 수 있습니다.

제 성향은 콤마_to_table을 수행하는 간단한 함수와 함께 TYPE 접근 방식을 사용한 다음 comma_to_table의 결과에서 각 항목에 대해 PIPE ROW를 사용하는 것입니다(불행하게도 DBMS_UTILITY.comma_to_table은 프로시저이므로 SQL에서 호출할 수 없습니다).

언급URL : https://stackoverflow.com/questions/3142665/how-to-convert-csv-to-table-in-oracle