15 сент. 2010 г., 11:08 отAPCJoyce

Как лучше разделить строки CSV в Oracle 9i

Я хочу иметь возможность разбивать строки CSV в Oracle 9i

Я прочитал следующую статью http://www.oappssurd.com/2009/03/string-split-in-oracle.html

Но я не понял, как заставить это работать. Вот некоторые из моих вопросов, касающихся этого

Would this work in Oracle 9i, if not, why not? Is there a better way of going about splitting csv strings then the solution presented above? Do I need to create a new type? If so, do I need specific privilages for that? Can I declare the type w/in the function?

Ответы на вопрос(5)

07 июл. 2009 г., 19:29 отRob van Wijk

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

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

SQL> declare
  2    cn_non_occuring_prefix constan,t 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-соединений по уровням. Если у вас 10g или выше, вы можете использовать поэтапный подход в сочетании с регулярными выражениями, например:

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.

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

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

Regards, Роб.

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

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

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

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.

Regards, Роб.

07 июл. 2009 г., 08:31 отBrian

вы не хотите добавлять схему (типы, функции). Единственный 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;

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

07 июл. 2009 г., 11:10 отCommunity

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

Если вы используете параметры, чтобы разбить строку чисел 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 
  *
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);
08 июл. 2009 г., 01:28 отJoyce

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

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 ;
07 июл. 2009 г., 12:11 отMichael Sofaer

который немного проще, чем эта страница, но не знаю, насколько он быстр:

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)

ВАШ ОТВЕТ НА ВОПРОС