Comment mieux split csv chaînes dans oracle 9i

Je veux être en mesure de séparer csv chaînes dans Oracle 9i

J'ai lu l'article suivant
http://www.oappssurd.com/2009/03/string-split-in-oracle.html

Mais je n'ai pas à comprendre comment faire ce travail.
Voici quelques unes de mes questions s'y rapportant

Serait-ce de travailler dans Oracle 9i, si non, pourquoi pas?
Est-il une meilleure façon d'aller sur le fractionnement du csv cordes puis la solution présentée ci-dessus?
Dois-je créer un nouveau type? Si oui, ai-je besoin d'privilages pour qui?
Puis-je déclarer le type w/dans la fonction?

OriginalL'auteur Joyce | 2009-07-06

Voici une chaîne tokenizer pour Oracle qui est un peu plus simple que celui de la page, mais aucune idée si c'est aussi rapide:

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;

Vous pouvez l'utiliser comme ceci:

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

C'est une très bonne méthode, toujours en vigueur, même jusqu'à 11g au moins. Une telle méthode est rapide parce que vous n'avez pas de redémarrer votre analyse à chaque fois à partir de la position zéro, mais de poursuivre l'analyse à partir d'où vous l'avez laissé. J'ai développé un code de le manipuler un peu différent, publié sur mon blog sur l'Analyse d'une chaîne de caractères avec un CSV en plusieurs colonnes. Il est quelque peu similaire, mais je suis allé la route avec un pipeline de fonction, et d'une drôle de façon de à l'aide d'une jointure cartésiens. J'ai des résultats dans les différentes colonnes.

OriginalL'auteur Michael Sofaer

Joyce,

Voici trois exemples:

1) à l'Aide de dbms_utility.comma_to_table. Ce n'est pas un objectif général de routine, parce que les éléments doivent être des identificateurs valides. Avec quelques trucs sales, nous pouvons le faire fonctionner plus universelle:

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) à l'Aide de SQL connect par niveau. Si vous êtes sur 10g ou plus, vous pouvez utiliser la connexion-par-niveau de l'approche en combinaison avec des expressions régulières, comme ceci:

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) de Nouveau à l'aide de SQL connect par niveau, mais maintenant, en combinaison avec la bonne vieille SUBSTR/INSTR dans le cas où vous êtes sur la version 9, comme vous êtes:

    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.

Vous pouvez voir une partie de plus en plus techniques comme celles-ci, dans ce billet de blog: http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

Espère que cette aide.

Ce qui concerne,
Rob.

À l'adresse de votre commentaire:

Un exemple de l'insertion de valeurs séparées dans une table normalisée.

D'abord créer les tables:

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.

Parce que vous semblez intéressé par le dbms_utility.comma_to_table approche, je le mentionne ici. Cependant, je ne recommande pas cette variante, en raison de l'identificateur de bizarreries et en raison de la lenteur ligne par ligne de traitement.

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.

Je ne recommande cet unique SQL variante:

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.

Ce qui concerne,
Rob.

Je pense que l'utilisation dbms_utility.comma_to_table est bon. C'est un peu hors sujet. Comment pourrais-je faire cette course sur une colonne qui est plein de ces csv valeurs et de les mettre tous dans une nouvelle table? Désolé, je suis très très nouveau pour Oracle. L'apprécier! Joyce
J'ai ajouté une section de la réponse à l'adresse de votre commentaire.
Salut Rob, la solution # 2 [à l'Aide de SQL connect par niveau] fonctionne très bien pour moi.Mais maintenant,j'ai une exigence de passer 2 CSV cordes[de longueur égale] comme entrée PL/SQL stockées proc.Et, j'ai besoin d'insérer des valeurs de ces deux CSV chaînes de caractères dans les deux colonnes de la table.Pourriez-vous s'il vous plaît laissez-moi savoir comment vous y prendre?
Le numéro 2 est excellent

OriginalL'auteur Rob van Wijk

Vous voudrez peut-être un peu plus clair sur ce que vous voulez faire, alors nous pouvons vous donner une réponse précise. Montrant une partie de votre code est toujours utile 🙂

Si vous utilisez des paramètres, de découper une chaîne de csv nombres (par exemple: 1,2,3,4) utilisez-la dans un IN instruction ont un look à la fonction str2tbl() dans Question 670922. Avec quelques modifications, vous pouvez le changer pour un VARCHAR2 ou tout ce que vous avez besoin.

Dans le suivant, vous pouvez définir :sMyCatagories égal à '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;

et de l'utiliser dans une instruction select....

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

C'est vraiment utile que si vous utilisez les paramètres. Si vous êtes munging ensemble SQL dans votre application, alors il suffit d'utiliser un normal DANS l'état.

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

En gros, je voulais juste unpivot des données au format csv, et que vous voulez voir si je peux le faire sans avoir besoin de créer un nouveau type, puisque je n'ai pas le privilège.

OriginalL'auteur Mark Nold

J'ai utilisé ce en fin de compte

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 ;

OriginalL'auteur Joyce

Il semble que vous ne voulez pas ajouter de schéma (types, fonction). SQL seul moyen pour analyser le texte délimité par est de "devenir fou" avec instr et substr appels.

    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;

Si vous cherchez à formaliser une structure et approprié d'ajouter le code de l'application (fonctions, vues, etc...) je voudrais prendre un coup d'oeil à Tom Kyte est l'écriture sur ce sujet.

Oui, j'ai lu son article, asktom.oracle.com/pls/asktom/...
Ce n'est pas la seule façon...

OriginalL'auteur Brian

Vous devez vous connecter pour publier un commentaire.