oracle oracle中如何将csv转换为表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3142665/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to convert csv to table in oracle
提问by Mehur
How can I make a package that returns results in table format when passed in csv values.
如何制作一个包，当传入 csv 值时以表格格式返回结果。
select * from table(schema.mypackage.myfunction('one, two, three'))
should return
应该回来
one
two
three
I tried something from ask tombut that only works with sql types.
我尝试了ask tom 的一些东西，但这只适用于 sql 类型。
I am using oracle 11g. Is there something built-in?
我正在使用 oracle 11g。有什么内置的吗？
回答by josephj1989
The following works invoke it as select * from table(splitter('a,b,c,d'))
以下作品将其调用为 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;
/
回答by APC
Alas, in 11g we still have to handroll our own PL/SQL tokenizers, using SQL types. In 11gR2 Oracle gave us a aggregating function to concatenate results into a CSV string, so perhaps in 12i they will provide the reverse capability.
唉，在 11g 中，我们仍然需要使用 SQL 类型来处理我们自己的 PL/SQL 标记器。在 11gR2 中，Oracle 给了我们一个聚合函数来将结果连接成一个 CSV 字符串，所以也许在 12i 中它们将提供反向功能。
If you don't want to create a SQL type especially you can use the built-in SYS.DBMS_DEBUG_VC2COLL, like this:
如果您不想创建 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;
/
Here it is in action:
这是在行动：
SQL> select * from table (string_tokenizer('one, two, three'))
2 /
COLUMN_VALUE
----------------------------------------------------------------
one
two
three
SQL>
Acknowledgement: this code is a variant of some code I found on Tanel Poder's blog.
致谢：这段代码是我在 Tanel Poder 的博客上找到的一些代码的变体。
回答by Shailesh
Here is another solution using a regular expression matcher entirely in sql.
这是另一个完全在 sql 中使用正则表达式匹配器的解决方案。
SELECT regexp_substr('one,two,three','[^,]+', 1, level) abc
FROM dual
CONNECT BY regexp_substr('one,two,three', '[^,]+', 1, level) IS NOT NULL
回答by Bobby Kenny
For optimal performance, it is best to avoid using hierarchical (CONNECT BY) queries in the splitter function.
为了获得最佳性能，最好避免在拆分器函数中使用分层 (CONNECT BY) 查询。
The following splitter function performs a good deal better when applied to greater data volumes
当应用于更大的数据量时，以下拆分器功能会表现得更好
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;
/
This performance difference can be observed below (I used the function splitter as was given earlier in this discussion).
这种性能差异可以在下面观察到（我使用了本讨论前面给出的函数拆分器）。
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>
回答by JulesLt
I don't have 11g installed to play with, but there is a PIVOT and UNPIVOT operation for converting columns to rows / rows to columns, that may be a good starting point.
我没有安装 11g 来使用，但是有一个 PIVOT 和 UNPIVOT 操作可以将列转换为行/行转换为列，这可能是一个很好的起点。
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html
(Having actually done some further investigation, this doesn't look suitable for this case - it works with actual rows / columns, but not sets of data in a column).
（实际上做了一些进一步的调查，这看起来不适合这种情况 - 它适用于实际的行/列，但不适用于列中的数据集）。
There is also DBMS_UTILITY.comma_to_table and table_to_comma for converting CSV lists into pl/sql tables. There are some limitations (handling linefeeds, etc) but may be a good starting point.
还有 DBMS_UTILITY.comma_to_table 和 table_to_comma 用于将 CSV 列表转换为 pl/sql 表。有一些限制（处理换行等），但可能是一个很好的起点。
My inclination would be to use the TYPE approach, with a simple function that does comma_to_table, then PIPE ROW for each entry in the result of comma_to_table (unfortunately, DBMS_UTILITY.comma_to_table is a procedure so cannot call from SQL).
我的倾向是使用 TYPE 方法，使用一个简单的函数执行comma_to_table，然后为comma_to_table 的结果中的每个条目执行PIPE ROW（不幸的是，DBMS_UTILITY.comma_to_table 是一个过程，因此不能从SQL 调用）。
