Scission de chaîne et de réitérer pour chaque valeur dans une procédure stockée

J'ai une exigence d'Oracle, où je dois passer une chaîne séparée par des virgules des codes de pays à une procédure stockée Oracle.

À l'intérieur d'une procédure stockée je veux diviser la chaîne de virgule et de réitérer pour chaque code de pays.

Je veux un SP qui est similaire comme suit:

PROCEDURE Get_Query
(
  v_company IN VARCHAR2,

) IS
sqlstr VARCHAR2(32767);
BEGIN
/*
split the v_company on comma (e.g. IN,US,...etc)
iterate for each country code  

*/
END;

C'est un répandues demande (essayer de Googler, "stackoverflow oracle analyser csv chaîne"). Il n'y a pas de pénurie de documentation sur ce sujet.

OriginalL'auteur Mandar Patil | 2015-04-10

7

Vous pouvez utiliser DBMS_UTILITY.COMMA_TO_TABLE.

La DBMS_UTILITY paquet fournit divers de l'utilitaire de sous-programmes. L'un de ces utilitaires est COMMA_TO_TABLE procédure, qui convertit une liste délimitée par des virgules de noms dans un PL/SQL de la table des noms.

Par exemple,
```
SQL> set serveroutput on;
SQL> DECLARE
  2    l_tablen BINARY_INTEGER;
  3    l_tab DBMS_UTILITY.uncl_array;
  4    CURSOR cur
  5    IS
  6      SELECT 'word1, word2, word3, word4, word5, word6' val FROM dual;
  7    rec cur%rowtype;
  8  BEGIN
  9    OPEN cur;
 10    LOOP
 11      FETCH cur INTO rec;
 12      EXIT
 13    WHEN cur%notfound;
 14      DBMS_UTILITY.comma_to_table (
 15      list => rec.val, tablen => l_tablen, tab => l_tab);
 16      FOR i IN 1 .. l_tablen
 17      LOOP
 18        DBMS_OUTPUT.put_line(i || ' : ' || trim(l_tab(i)));
 19      END LOOP;
 20    END LOOP;
 21    CLOSE cur;
 22  END;
 23  /
1 : word1
2 : word2
3 : word3
4 : word4
5 : word5
6 : word6

PL/SQL procedure successfully completed.

SQL>
```
Mise à JOUR @ruudvan, il y a des restrictions pour l'utilisation de COMMA_TO_TABLE comme cela ne fonctionnera pas si vous avez mots-clés en tant que chaîne délimitée comme IS,AS etc.

De surmonter les restrictions de COMMA_TO_TABLE, il existe de nombreuses autres façons de le faire, voir Comment diviser délimité par des virgules chaîne en lignes dans Oracle

Par exemple, vous pouvez utiliser une EXPRESSION RÉGULIÈRE comme indiqué ci-dessous:

Cas de Test
```
SQL> CREATE OR REPLACE
  2  PROCEDURE Get_Query(
  3      v_company IN VARCHAR2 )
  4  IS
  5  BEGIN
  6    
  7    FOR i IN
  8    (SELECT level,
  9      trim(regexp_substr(v_company, '[^,]+', 1, LEVEL)) str
 10    FROM dual
 11      CONNECT BY regexp_substr(v_company , '[^,]+', 1, LEVEL) IS NOT NULL
 12    )
 13    LOOP
 14      -- do something
 15      dbms_output.put_line('Company code no.'||i.level||' = '||i.str);
 16    END LOOP;
 17  END;
 18  /

Procedure created.

SQL> sho err
No errors.
```
Nous allons vérifier:
```
SQL> set serveroutput on
SQL> EXEC get_query('COMP1,COMP2,COMP3,COMP4');
Company code no.1 = COMP1
Company code no.2 = COMP2
Company code no.3 = COMP3
Company code no.4 = COMP4

PL/SQL procedure successfully completed.

SQL>
```
Je pense que vous devriez plutôt mettre l'regexp_substr méthode de comma_to_table. Chaque fois que vous recommander quelqu'un comma_to_table, j'ai envie de vous doit aussi les informer au sujet de ses limites. Tom Kyte, il résume ici. Il pourrait ne pas vous donner des résultats corrects, même dans cet exemple spécifique de codes de pays, parce que les OP peuvent avoir des codes de pays comme "tant QUE" et "EST", qui sont des mots réservés et comma_to_table ne serez pas en mesure de les traiter.
Bien sûr il y a des limite avec l'intégré dans le paquet. C'est la raison pour laquelle j'ai fourni le lien vers mon article où j'ai démontré diverses méthodes. Je vais le laisser jusqu'à l'OP de revenir et de lui demander s'il a besoin de plus d'assistance. Si je dois aller plus loin dans les détails et de fournir à tous les micro-détails ici, ce serait comme écrire un livre. De toute façon, merci pour le commentaire. Je serai plus qu'heureux de vous aider OP si il revient avec d'autres préoccupations.
Vous avez suffisamment de rep savoir comment StackOverflow œuvres. Nous sommes censés fournir des réponses sur ce site. Offrant une sous-solution par - et comme @ruudvan points, COMMA_TO_TABLE() en est que plus un lien vers votre blog n'est tout simplement pas assez bon.
et ruudvan, juste des mecs assez. Ajout d'une complète solution de travail. Merci pour me pousser 😉

OriginalL'auteur Lalit Kumar B

Cette utilisation des expressions régulières convertit la chaîne d'entrée dans un flux de jetons qui peuvent être traités comme l'ensemble de résultats de toute autre instruction SELECT.

PROCEDURE Get_Query
(
  v_company IN VARCHAR2,

) IS
    sqlstr VARCHAR2(32767);
BEGIN

    for rec in (select distinct regexp_substr(v_company, '[^,]+', 1, level) as ctry
    from dual
    connect by level <= regexp_count (v_company, '[,]')  +1
   loop
        do_something ( rec.ctry );

    end loop;
END;

OriginalL'auteur APC

Vous devez vous connecter pour publier un commentaire.