Brechen Sie eine Linie durch ein Trennzeichen. Ein wenig über CONNECT BY

Hallo!

Ich arbeite als PL / SQL-Entwickler. Es ist eine Aufgabe, einige Daten für Metriken zu sammeln, um die Systemlast zu verfolgen. Es gibt eine Funktion, die mit einem Parameter aufgerufen wird, der aus einer Liste von IDs besteht.

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

Die Aufgabe ist wie folgt. Es ist notwendig, eine solche Zeile in Elemente zu unterteilen und sie in eine Ganzzahlsammlung zu schreiben.

Fangen wir an.

Zuerst benötigen Sie Daten, um zu arbeiten. Schreiben wir eine Funktion, die eine Zeichenfolge mit durch Komma getrennten Zahlen generiert. Wir werden der Funktion ein ganzzahliges Argument N übergeben - die Anzahl der Zahlen in der Sequenz.

Wir werden nicht schlau sein, wir werden die Sequenz mit dem Typ VARCHAR2 machen, nicht mit CLOB. Als nächstes werde ich erklären, warum VARCHAR2.

Funktionscode zum Generieren der Sequenz:

 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; 

Kommen wir zurück zu unserer Aufgabe.

Das erste, was mir in den Sinn kommt, ist, die Linie zu durchlaufen und die Länge der Linie bei jeder Iteration zu verringern. Da das Ergebnis gemäß den Bedingungen des Problems in die Sammlung aufgenommen werden muss, erstellen wir die entsprechende Sammlung.

 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; 

Ergebnis:
0
1
2
…
421
422
423
…

Die Funktion createNumber () nimmt das Argument v_N = 1000 an. In der Funktion createNumber () können Sie die Überlaufbehandlung der Variablen v_str sehen. Mit einer einfachen Berechnung können Sie herausfinden, dass 4000 Bytes für 1021 Zahlen ausreichen. Unsere 1000 passt problemlos in diese Größe.

Wie Sie sehen können, ist das Ergebnis dasjenige, das benötigt wurde. Die Zeichenfolge ist geteilt.

Obwohl Oracle keine integrierte split () - Funktion wie Java oder Python hat, passt diese Option nicht zu mir, da ich glaube, dass zu viel Code für eine so einfache Aufgabe wie das Teilen einer Zeichenfolge geschrieben wurde.

In diesem Stadium, dachte ich, ist es möglich, einen String nur mit SQL zu teilen? Ich meine nicht klassisches SQL, sondern das SQL, das Oracle anbietet.

Ich erinnerte mich an das Design zum Erstellen hierarchischer CONNECT BY-Abfragen.



Die optionale START WITH-Anweisung teilt Oracle mit, wo die Schleife gestartet werden soll, d. H. Welche Zeile wird die Wurzel sein? Der Zustand kann fast jeder sein. Die Bedingung nach CONNECT BY muss angegeben werden. Hier muss Oracle mitgeteilt werden, wie lange der Zyklus fortgesetzt werden soll.

Es ist ersichtlich, dass die einzige wichtige Bedingung für die Erstellung einer hierarchischen Abfrage der Operator CONNECT BY ist, der Rest wird nach Bedarf "aufgereiht".

Außerdem hat dieses Konstrukt eine Pseudospaltenebene, die die Verschachtelungsebene bei der aktuellen Iteration zurückgibt.

Auf den ersten Blick scheint diese Konstruktion nicht zum Brechen einer Linie geeignet zu sein. Dies ist nicht ganz richtig. Wenn die Bedingung richtig eingestellt ist, kann die rekursive Durchquerung in eine zyklische umgewandelt werden, wie in while- oder for-Schleifen.

Betrachten Sie vor dem Schreiben einer Abfrage einen Zeilenüberquerungsalgorithmus. Ab dem Zeilenanfang muss eine bestimmte Anzahl von Zeichen bis zum Trennzeichen abgeschnitten werden. Oben habe ich über die Pseudospaltenebene geschrieben. Wir werden es als aktuelle Iterationsnummer verwenden.
Es stellt sich so etwas heraus:

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

Wenn Sie genau hinschauen, können Sie feststellen, dass dieser Algorithmus bei der ersten Iteration nicht funktioniert, da das dritte Argument für die Funktion INSTR () nicht 0 sein kann.

Daher fügen wir mit der Funktion DECODE () eine kleine Bedingung hinzu.

 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; 

Jetzt funktioniert die allererste Iteration korrekt.

Es ist Zeit, das Konstrukt CONNECT BY anzuwenden. Außerdem nehmen wir unsere Aufstellung auf.

 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; 

Ich habe bereits geschrieben, dass sich das CONNECT BY-Konstrukt unter der richtigen Bedingung wie eine Schleife verhalten kann. Die Bedingung ist erfüllt, bis die Funktion INSTR () die n-te Position des Begrenzungszeichens finden kann, wobei n die Nummer der aktuellen Iteration ist und, wie wir uns erinnern, die Pseudospaltenebene für die Iterationsnummer verantwortlich ist.

Es scheint, dass das Problem gelöst ist? Nein.

Der Code mag funktionieren, aber seine Lesbarkeit ist Null. Ich habe bereits darüber nachgedacht, zur Loop-Option zurückzukehren, aber ich habe herausgefunden, wie ich die CONNECT BY-Option verbessern kann.

Oracle hat ein so mächtiges Werkzeug wie reguläre Ausdrücke. Insbesondere die Funktionen regexp_instr () und regexp_substr ().

regexp_instr(_, [, _ [, ] ]) - Die Funktion gibt die Position des Zeichens zu Beginn oder am Ende der Übereinstimmung für das Muster sowie dessen analoges INSTR () zurück.

regexp_substr(_, [, [, ]]) - Die Funktion gibt einen Teilstring zurück, der dem Muster entspricht.

Schreiben Sie die Abfrage mit regulären Ausdrücken neu:

 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; 

Der Code liest sich viel besser als im vorherigen Beispiel. Diese Option passt zu mir.

Am Ende wäre es logisch, Laufzeit-Parsing-Vergleiche für drei Optionen zu vergleichen. Oben habe ich versprochen zu erklären, warum wir anstelle des CLOB-Typs den VARCHAR2-Typ verwenden werden. Dies dient nur zum Vergleich der Laufzeit. Da Oracle den CLOB-Typ anders behandelt als VARCHAR2, kann dies die Ergebnisse verfälschen.


Die Ergebnisse sind tabellarisch aufgeführt:

Unterbrechungsoption / Anzahl der Nummern	10	100	1000
Zyklus	0,05 ms	0,15 ms	1,52 ms
VERBINDEN DURCH	0,18 ms	0,68 ms	18,1 ms
CONNECT BY + reguläre Ausdrücke	0,25 ms	12,1 ms	1s 137ms

Ehrlich gesagt ist das Ergebnis für mich unerwartet. Ich ging davon aus, dass die dritte Option die schnellste sein würde. Nun, das wird eine Lektion sein.

Danke fürs Lesen!

Liste der verwendeten Quellen:

1. Hierarchische (rekursive) Abfragen