如何将csv转换为oracle中的表

Question

当传入csv值时，如何制作一个以表格式返回结果的包。select * from table(schema.mypackage.myfunction('one, two, three'))应该回来onetwothree我尝试了来自问汤姆的东西，但仅适用于sql类型。我正在使用oracle 11g。有内置的东西吗？

Answer 1

以下工作将它作为select * from table（splitter（'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;

/

Answer 2

为了获得最佳性能，最好避免在拆分器功能中使用分层（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>