如何在oracle 9i中最佳拆分csv字符串

Question

我希望能够在Oracle 9i中拆分csv字符串

我已阅读以下文章 http://www.oappssurd.com/2009/03/string-split-in-oracle.html

但我不知道如何使这项工作。这是我有关的一些问题

1. 如果没有的话，这在Oracle 9i中行得通吗，为什么不呢？

2. 是否有比上面介绍的解决方案更好的拆分csv字符串的方法？

3. 我需要创建一个新类型吗？如果是这样，我是否需要特定特权？

4. 我可以在函数中声明w /类型吗？

Answer 1

这是Oracle的字符串令牌生成器，它比该页面要简单一些，但不知道它是否这么快：create or replace function splitter_count(str in varchar2, delim in char) return int asval int;begin&nbsp; val := length(replace(str, delim, delim || ' '));&nbsp; return val - length(str);&nbsp;end;create type token_list is varray(100) of varchar2(200);CREATE or replace function tokenize (str varchar2, delim char) return token_list asret token_list;target int;i int;this_delim int;last_delim int;BEGIN&nbsp; ret := token_list();&nbsp; i := 1;&nbsp; last_delim := 0;&nbsp; target := splitter_count(str, delim);&nbsp; while i <= target&nbsp; loop&nbsp; &nbsp; ret.extend();&nbsp; &nbsp; this_delim := instr(str, delim, 1, i);&nbsp; &nbsp; ret(i):= substr(str, last_delim + 1, this_delim - last_delim -1);&nbsp; &nbsp; i := i + 1;&nbsp; &nbsp; last_delim := this_delim;&nbsp; end loop;&nbsp; ret.extend();&nbsp; ret(i):= substr(str, last_delim + 1);&nbsp; return ret;end;您可以像这样使用它：select tokenize('hi you person', ' ') from dual;VARCHAR(hi,you,person)

Answer 2

我最后用这个create or replace function split(&nbsp; &nbsp;p_list varchar2) return sys.dbms_debug_vc2coll pipelinedis&nbsp; &nbsp;l_idx&nbsp; &nbsp; pls_integer;&nbsp; &nbsp;l_list&nbsp; &nbsp; varchar2(32767) := p_list;&nbsp; &nbsp;l_value&nbsp; &nbsp; varchar2(32767);begin&nbsp; &nbsp;loop&nbsp; &nbsp; &nbsp; &nbsp;l_idx := instr(l_list,',');&nbsp; &nbsp; &nbsp; &nbsp;if l_idx > 0 then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pipe row(substr(l_list,1,l_idx-1));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;l_list := substr(l_list,l_idx+length(','));&nbsp; &nbsp; &nbsp; &nbsp;else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pipe row(l_list);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exit;&nbsp; &nbsp; &nbsp; &nbsp;end if;&nbsp; &nbsp;end loop;&nbsp; &nbsp;return;end split;declareCURSOR c IS&nbsp; select occurrence_num, graphics from supp where graphics is not null and graphics not like ' %';begin&nbsp; FOR r IN c LOOP&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; insert into image (photo_id,report_id, filename)&nbsp;&nbsp; &nbsp; &nbsp; select image_key_seq.nextval&nbsp; &nbsp;photo_id, r.occurrence_num report_id,&nbsp;&nbsp; &nbsp; &nbsp; t.column_value&nbsp; filename from table(split(cast(r.graphics as varchar2(1000)))) t where t.column_value is not null;&nbsp; &nbsp;END LOOP;&nbsp;&nbsp;end ;