如何将csv转换为oracle中的表

Question

当传入csv值时，如何制作一个以表格式返回结果的包。

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

应该回来

one

two

three

我尝试了来自问汤姆的东西，但仅适用于sql类型。

我正在使用oracle 11g。有内置的东西吗？

Answer 1

以下工作将它作为select * from table（splitter（'a，b，c，d'））调用它create or replace function splitter(p_str in varchar2) return&nbsp; sys.odcivarchar2listisv_tab sys.odcivarchar2list:=new sys.odcivarchar2list();beginwith cte as (select level&nbsp; ind from dualconnect by&nbsp;level <=regexp_count(p_str,',') +1)select regexp_substr(p_str,'[^,]+',1,ind)bulk collect into v_tabfrom cte;return v_tab;end;/

Answer 2

为了获得最佳性能，最好避免在拆分器功能中使用分层（CONNECT BY）查询。将以下拆分器功能应用于更大的数据量时，其性能会更好CREATE OR REPLACE FUNCTION row2col(p_clob_text IN VARCHAR2)&nbsp;&nbsp; &nbsp;RETURN sys.dbms_debug_vc2coll PIPELINED&nbsp;IS&nbsp; &nbsp; &nbsp;next_new_line_indx PLS_INTEGER;&nbsp; &nbsp; &nbsp;remaining_text VARCHAR2(20000);&nbsp; &nbsp; &nbsp;next_piece_for_piping VARCHAR2(20000);&nbsp; BEGIN&nbsp; &nbsp; remaining_text := p_clob_text;&nbsp; &nbsp; LOOP&nbsp; &nbsp; &nbsp; &nbsp;next_new_line_indx := instr(remaining_text, ',');&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;next_piece_for_piping :=&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHEN next_new_line_indx <> 0 THEN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRIM(SUBSTR(remaining_text, 1, next_new_line_indx-1))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ELSE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRIM(SUBSTR(remaining_text, 1))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END;&nbsp; &nbsp; &nbsp; &nbsp;remaining_text := SUBSTR(remaining_text, next_new_line_indx+1 );&nbsp; &nbsp; &nbsp; &nbsp;PIPE ROW(next_piece_for_piping);&nbsp; &nbsp; &nbsp; &nbsp;EXIT WHEN next_new_line_indx = 0 OR remaining_text IS NULL;&nbsp; &nbsp; END LOOP;&nbsp; &nbsp; RETURN;&nbsp; END row2col;/可以在下面观察到这种性能差异（我使用了本讨论前面给出的功能分配器）。SQL> SET TIMING ONSQL>SQL> WITH SRC AS (&nbsp; 2&nbsp; 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&nbsp; 3&nbsp; FROM DUAL&nbsp; 4&nbsp; CONNECT BY LEVEL <=10000&nbsp; 5&nbsp; )&nbsp; 6&nbsp; SELECT&nbsp; NULL&nbsp; 7&nbsp; FROM SRC, TABLE(SYSTEM.row2col(txt)) t&nbsp; 8&nbsp; HAVING MAX(t.column_value) > 'zzz'&nbsp; 9&nbsp; ;no rows selectedElapsed: 00:00:00.93SQL>SQL> WITH SRC AS (&nbsp; 2&nbsp; 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&nbsp; 3&nbsp; FROM DUAL&nbsp; 4&nbsp; CONNECT BY LEVEL <=10000&nbsp; 5&nbsp; )&nbsp; 6&nbsp; SELECT&nbsp; NULL&nbsp; 7&nbsp; FROM SRC, TABLE(splitter(txt)) t&nbsp; 8&nbsp; HAVING MAX(t.column_value) > 'zzz'&nbsp; 9&nbsp; ;no rows selectedElapsed: 00:00:14.90SQL>SQL> SET TIMING OFFSQL>