Brisez une ligne par un séparateur. Un peu de CONNECT BY

Bonjour

Je travaille en tant que développeur PL / SQL. Il existe une tâche de collecte de données pour les mesures afin de suivre la charge du système. Il y a une fonction qui est appelée avec un paramètre composé d'une liste d'ID.

1,2,3,4…1001,1002,1003…

La tâche est la suivante. Il est nécessaire de diviser une telle ligne en éléments et de les écrire dans une collection d'entiers.

Commençons.

Vous avez d'abord besoin de données pour fonctionner. Écrivons une fonction qui génère une chaîne avec des nombres séparés par une virgule. Nous passerons un argument entier N à la fonction - le nombre de nombres dans la séquence.

Nous ne serons pas intelligents, nous ferons la séquence avec le type VARCHAR2, pas CLOB. Ensuite, je vais expliquer pourquoi VARCHAR2.

Code de fonction pour générer la séquence:

 CREATE OR REPLACE FUNCTION createNumbers(v_N IN PLS_INTEGER) RETURN VARCHAR2 IS v_str VARCHAR2(4000) := '0'; too_small EXCEPTION; PRAGMA EXCEPTION_INIT(too_small, -6502); BEGIN FOR i IN 1..v_N LOOP BEGIN v_str := v_str || ',' || TO_CHAR(i); EXCEPTION WHEN too_small THEN EXIT; END; END LOOP; RETURN v_str || ','; END; 

Revenons à notre tâche.

La première chose qui vient à l'esprit est de parcourir la ligne en boucle, ce qui réduit la longueur de la ligne à chaque itération. Étant donné que, selon les conditions du problème, le résultat doit être placé dans la collection, nous créerons la collection correspondante.

 DECLARE v_N PLS_INTEGER := 1000; v_str VARCHAR2(4000) := createNumbers(v_N => v_N); TYPE tab_number IS TABLE OF NUMBER(5); t_str tab_number := tab_number(); BEGIN WHILE v_str IS NOT NULL LOOP t_str.EXTEND; t_str(t_str.COUNT) := SUBSTR(v_str, 1, INSTR(v_str, ',', 1) - 1); v_str := SUBSTR(v_str, INSTR(v_str, ',', 1) + 1); END LOOP; FOR i IN t_str.FIRST..t_str.LAST LOOP dbms_output.put_line(t_str(i)); END LOOP; t_str.DELETE; END; 

Résultat:
0
1
2
…
421
422
423
…

La fonction createNumber () prend l'argument v_N = 1000. Dans la fonction createNumber (), vous pouvez voir la gestion du débordement de la variable v_str. Avec un calcul simple, vous pouvez découvrir que 4000 octets suffisent pour 1021 nombres. Notre 1000 s'inscrit dans cette taille sans aucun problème.

Comme vous pouvez le voir, le résultat est celui qui était nécessaire. La chaîne est divisée.

Même si Oracle n'a pas de fonction split () intégrée, comme en Java ou en Python, cette option ne me convient pas, car je pense que trop de code est écrit pour une tâche aussi simple que le fractionnement d'une chaîne.

À ce stade, je pensais, est-il possible de diviser une chaîne uniquement en utilisant SQL? Je ne parle pas du SQL classique, mais du SQL qu'Oracle propose.

Je me suis souvenu de la conception de la création de requêtes CONNECT BY hiérarchiques.



L'instruction facultative START WITH indique à Oracle où commencer la boucle, c'est-à-dire quelle ligne sera la racine. La condition peut être presque n'importe laquelle. La condition après CONNECT BY doit être spécifiée. Ici, il est nécessaire de dire à Oracle combien de temps pour continuer le cycle.

On peut voir que la seule condition importante pour construire une requête hiérarchique est l'opérateur CONNECT BY, le reste est "enfilé" selon les besoins.

En outre, cette construction a un niveau de pseudo-colonne, qui renvoie le niveau d'imbrication à l'itération actuelle.

À première vue, il peut sembler que cette construction ne convient pas pour briser une ligne. Ce n'est pas tout à fait vrai. Si la condition est correctement définie, la traversée récursive peut être transformée en cyclique, comme dans les boucles while ou for.

Avant d'écrire une requête, envisagez un algorithme de traversée de ligne. Il faut, à partir du début de la ligne, couper un certain nombre de caractères, au caractère séparateur. Ci-dessus, j'ai écrit sur le niveau de la pseudo-colonne. Nous l'utiliserons comme numéro d'itération actuel.
Il s'avère quelque chose comme ceci:

 SELECT SUBSTR(str, INSTR(str, ',', 1, level - 1) + 1, INSTR(str, ',', 1, level) - INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

Mais si vous regardez attentivement, vous pouvez voir que cet algorithme ne fonctionnera pas sur la toute première itération, car le troisième argument de la fonction INSTR () ne peut pas être 0.

Par conséquent, nous ajoutons une petite condition à l'aide de la fonction DECODE ().

 SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

Maintenant, la toute première itération fonctionnera correctement.

Il est temps d'appliquer la construction CONNECT BY. De plus, nous prenons notre ligne.

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM TMain CONNECT BY NVL(INSTR(str, ',', 1, level), 0) <> 0; 

J'ai déjà écrit que, dans les bonnes conditions, la construction CONNECT BY peut se comporter comme une boucle. La condition est remplie jusqu'à ce que la fonction INSTR () puisse trouver la nième position du délimiteur, où n est le numéro de l'itération en cours, et comme nous nous en souvenons, le niveau de la pseudo-colonne est responsable du numéro d'itération.

Il semble que le problème soit résolu? Non.

Le code peut fonctionner, mais sa lisibilité est nulle. Je pensais déjà à revenir à l'option de boucle, mais j'ai compris comment améliorer l'option CONNECT BY.

Oracle dispose d'un outil aussi puissant que les expressions régulières. Plus précisément, les fonctions regexp_instr () et regexp_substr ().

regexp_instr(_, [, _ [, ] ]) - la fonction renvoie la position du caractère au début ou à la fin de la correspondance pour le pattern, ainsi que son INSTR () analogique.

regexp_substr(_, [, [, ]]) - la fonction renvoie une sous-chaîne qui correspond au motif.

Réécrivez la requête à l'aide d'expressions régulières:

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT regexp_substr(str, '[^,]+', 1, level) FROM TMain CONNECT BY NVL(regexp_instr(str, '[^,]+', 1, level), 0) <> 0; 

Le code se lit beaucoup mieux que dans l'exemple précédent. Cette option me convient.

Au final, il serait logique de comparer les comparaisons d'analyse d'exécution pour les trois options. Ci-dessus, j'ai promis d'expliquer pourquoi au lieu du type CLOB nous utiliserons le type VARCHAR2. C'est juste pour comparer l'exécution. Comme Oracle gère le type CLOB différemment de VARCHAR2, ce qui peut fausser les résultats.


Les résultats sont tabulés:

Option de rupture / nombre de numéros	10	100	1000
Cycle	0,05 ms	0,15 ms	1,52 ms
CONNECTER PAR	0,18 ms	0,68 ms	18,1 ms
CONNECT BY + expressions régulières	0,25 ms	12,1 ms	1s 137ms

Honnêtement, le résultat est inattendu pour moi. J'ai supposé que la troisième option serait la plus rapide. Eh bien, ce sera une leçon.

Merci d'avoir lu!

Liste des sources utilisées:

1. Requêtes hiérarchiques (récursives)