Как лучше всего разбить строки csv в oracle 9i

Вот строковый токенизатор для Oracle, который немного более прямолинейный, чем эта страница, но не знаю, если это так быстро:

create or replace function splitter_count(str in varchar2, delim in char) return int as val int; begin val := length(replace(str, delim, delim || ' ')); return val - length(str); end; create type token_list is varray(100) of varchar2(200); CREATE or replace function tokenize (str varchar2, delim char) return token_list as ret token_list; target int; i int; this_delim int; last_delim int; BEGIN ret := token_list(); i := 1; last_delim := 0; target := splitter_count(str, delim); while i <= target loop ret.extend(); this_delim := instr(str, delim, 1, i); ret(i):= substr(str, last_delim + 1, this_delim - last_delim -1); i := i + 1; last_delim := this_delim; end loop; ret.extend(); ret(i):= substr(str, last_delim + 1); return ret; end; 

Вы можете использовать его следующим образом:

 select tokenize('hi you person', ' ') from dual; VARCHAR(hi,you,person) 

Джойс,

Вот три примера:

1) Использование dbms_utility.comma_to_table. Это не обычная процедура, потому что элементы должны быть действительными идентификаторами. С некоторыми грязными трюками мы можем сделать его более универсальным:

 SQL> declare 2 cn_non_occuring_prefix constant varchar2(4) := 'zzzz'; 3 mystring varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example 4 l_tablen binary_integer; 5 l_tab dbms_utility.uncl_array; 6 begin 7 dbms_utility.comma_to_table 8 ( list => cn_non_occuring_prefix || replace(mystring,':',','||cn_non_occuring_prefix) 9 , tablen => l_tablen 10 , tab => l_tab 11 ); 12 for i in 1..l_tablen 13 loop 14 dbms_output.put_line(substr(l_tab(i),1+length(cn_non_occuring_prefix))); 15 end loop; 16 end; 17 / a sd dfg 31456 dasd sdfsdf PL/SQL-procedure is geslaagd. 

2) Использование SQL-соединения по уровню. Если вы находитесь на 10 г или выше, вы можете использовать подход «по отдельности» в сочетании с регулярными выражениями, например:

 SQL> declare 2 mystring varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example 3 begin 4 for r in 5 ( select regexp_substr(mystring,'[^:]+',1,level) element 6 from dual 7 connect by level <= length(regexp_replace(mystring,'[^:]+')) + 1 8 ) 9 loop 10 dbms_output.put_line(r.element); 11 end loop; 12 end; 13 / a sd dfg 31456 dasd sdfsdf PL/SQL-procedure is geslaagd. 

3) Снова используя соединение SQL по уровню, но теперь в сочетании с хорошим старым SUBSTR / INSTR в случае, если вы на версии 9, например, вы:

  SQL> declare 2 mystring varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example 3 begin 4 for r in 5 ( select substr 6 ( str 7 , instr(str,':',1,level) + 1 8 , instr(str,':',1,level+1) - instr(str,':',1,level) - 1 9 ) element 10 from (select ':' || mystring || ':' str from dual) 11 connect by level <= length(str) - length(replace(str,':')) - 1 12 ) 13 loop 14 dbms_output.put_line(r.element); 15 end loop; 16 end; 17 / a sd dfg 31456 dasd sdfsdf PL/SQL-procedure is geslaagd. 

В этом blogpost вы можете увидеть еще несколько таких методов: http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

Надеюсь это поможет.

С уважением, Роб.

Чтобы ответить на ваш комментарий:

Пример вставки разделенных значений в нормализованную таблицу.

Сначала создайте таблицы:

 SQL> create table csv_table (col) 2 as 3 select 'a,sd,dfg,31456,dasd,,sdfsdf' from dual union all 4 select 'a,bb,ccc,dddd' from dual union all 5 select 'zz,yy,' from dual 6 / Table created. SQL> create table normalized_table (value varchar2(10)) 2 / Table created. 

Поскольку вы, похоже, заинтересованы в подходе dbms_utility.comma_to_table, я упоминаю его здесь. Тем не менее, я, конечно, не рекомендую этот вариант из-за недостатков идентификатора и из-за медленной обработки строк за строкой.

 SQL> declare 2 cn_non_occuring_prefix constant varchar2(4) := 'zzzz'; 3 l_tablen binary_integer; 4 l_tab dbms_utility.uncl_array; 5 begin 6 for r in (select col from csv_table) 7 loop 8 dbms_utility.comma_to_table 9 ( list => cn_non_occuring_prefix || replace(r.col,',',','||cn_non_occuring_prefix) 10 , tablen => l_tablen 11 , tab => l_tab 12 ); 13 forall i in 1..l_tablen 14 insert into normalized_table (value) 15 values (substr(l_tab(i),length(cn_non_occuring_prefix)+1)) 16 ; 17 end loop; 18 end; 19 / PL/SQL procedure successfully completed. SQL> select * from normalized_table 2 / VALUE ---------- a sd dfg 31456 dasd sdfsdf a bb ccc dddd zz yy 14 rows selected. 

Я рекомендую этот единственный вариант SQL:

 SQL> truncate table normalized_table 2 / Table truncated. SQL> insert into normalized_table (value) 2 select substr 3 ( col 4 , instr(col,',',1,l) + 1 5 , instr(col,',',1,l+1) - instr(col,',',1,l) - 1 6 ) 7 from ( select ',' || col || ',' col from csv_table ) 8 , ( select level l from dual connect by level <= 100 ) 9 where l <= length(col) - length(replace(col,',')) - 1 10 / 14 rows created. SQL> select * from normalized_table 2 / VALUE ---------- a a zz sd bb yy dfg ccc 31456 dddd dasd sdfsdf 14 rows selected. 

С уважением, Роб.

Возможно, вам захочется немного разобраться в том, что вы хотите сделать, тогда мы сможем дать вам конкретный ответ. Показывать некоторые из вашего кода всегда полезно 🙂

Если вы используете параметры, чтобы разбить строку чисел csv (например: 1,2,3,4), то используйте это в инструкции IN , посмотрите на функцию str2tbl() в вопросе 670922 . С некоторыми изменениями вы можете изменить его на VARCHAR2 или все, что вам нужно.

Ниже вы можете установить :sMyCatagories равный '1,2,3,4'

 create or replace type myTableType as table of number; create or replace function str2tbl( p_str in varchar2 ) return myTableType as l_str long default p_str || ','; l_n number; l_data myTableType := myTabletype(); begin loop l_n := instr( l_str, ',' ); exit when (nvl(l_n,0) = 0); l_data.extend; l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1))); l_str := substr( l_str, l_n+1 ); end loop; return l_data; end; 

и используя его в select statement ….

 SELECT * FROM atable a WHERE a.category in ( select * from INLIST ( select cast(str2tbl(:sMyCatagories) as mytableType) from dual ) ); 

Это действительно полезно, если вы используете параметры. Если вы собираете SQL в своем приложении, просто используйте обычный оператор IN.

 SELECT * FROM atable a WHERE a.category in (1,2,3,4); 

Я использовал это в конце

 create or replace function split ( p_list varchar2 ) return sys.dbms_debug_vc2coll pipelined is l_idx pls_integer; l_list varchar2(32767) := p_list; l_value varchar2(32767); begin loop l_idx := instr(l_list,','); if l_idx > 0 then pipe row(substr(l_list,1,l_idx-1)); l_list := substr(l_list,l_idx+length(',')); else pipe row(l_list); exit; end if; end loop; return; end split; 

 declare CURSOR c IS select occurrence_num, graphics from supp where graphics is not null and graphics not like ' %'; begin FOR r IN c LOOP insert into image (photo_id,report_id, filename) select image_key_seq.nextval photo_id, r.occurrence_num report_id, t.column_value filename from table(split(cast(r.graphics as varchar2(1000)))) t where t.column_value is not null; END LOOP; end ; 

Похоже, вы не хотите добавлять схему (типы, функции). Один метод SQL только для синтаксического разграничения текста заключается в том, чтобы «сходить с ума» с вызовами instr и substr.

  DECLARE V_CSV_STRING VARCHAR2(100); BEGIN --Create a test delimited list of first_name, last_name, middle_init V_CSV_STRING := 'Brian,Hart,M'; select substr( V_CSV_STRING||',', 1, instr(V_CSV_STRING,',')-1 ) FIRST_NAME, substr( V_CSV_STRING||',,', instr( V_CSV_STRING||',,', ',') +1, instr( V_CSV_STRING||',,', ',', 1, 2 )-instr(V_CSV_STRING||',,',',')-1 ) LAST_NAME, rtrim(substr( V_CSV_STRING||',,', instr( V_CSV_STRING||',,',',',1,2)+1),',') MIDDLE_INIT from dual; END; 

Если вы хотите формализовать структуру и добавить соответствующий код приложения (функции, представления, типы и т. Д.), Я хотел бы взглянуть на запись Тома Ките по этому вопросу .