Bei der Lösung von Systemintegrationsproblemen ist es häufig erforderlich, eine bestimmte Datenmenge in dem einen oder anderen Format darzustellen. Gleichzeitig kann jeder ein Datenkonsument sein, aber die Quelle ist fast immer eine Unternehmensdatenbank. Beispielsweise kann ein Hersteller vom Lieferanten verlangen, regelmäßig über die Bewegung seiner Waren im XLSX- oder XML-Format usw. zu berichten.
Es gibt viele Tools zum Konvertieren von Daten in verschiedene Formate, und die Möglichkeit ihrer Verwendung hängt vom im Unternehmen verwendeten technologischen Stack und der Softwarearchitektur ab. Gleichzeitig möchten Sie immer, dass die Kette, die aus verschiedenen Bibliotheken, Frameworks und Systemschichten besteht, die zum Konvertieren der Quelldaten verwendet werden, so kurz wie möglich ist. Dies würde den Zeitaufwand für die Entwicklung der Lösung verringern und ihre Produktivität steigern.
Wenn wir annehmen, dass die SQL-Abfrage tatsächlich die Wurzel des Datenauswahlprozesses ist, wäre im Idealfall die Transformationskette wünschenswert, um dies zu sehen:
wo
- Ausgangsdaten,
- SQL-Abfrage zum Abrufen von Daten,
- eine Funktion, die die Auswahl in das gewünschte Format konvertiert,
- Daten im gewünschten Format.
Für Oracle PL / SQL gibt es eine Reihe integrierter Pakete und Pakete von Drittanbietern, die diese Funktionalität implementieren. Dies sind DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL / JSON und andere.
Als sich jedoch die Frage nach der Konvertierung von Daten in das CSV-Format stellte, gab es aus irgendeinem Grund keine vorgefertigten Lösungen. Ich musste es selbst machen, dann wird gezeigt wie.
Erklärung des Problems
Erstellen Sie ein Tool (PL / SQL-Paket), das eine beliebige SELECT-Abfrage als Zeichenfolge oder als Cursor-Variable an der Eingabe empfängt und ein Objekt vom Typ CLOB zurückgibt, das Daten im CSV-Format an der Ausgabe kapselt. Im Fehlerfall sollte NULL zurückgegeben werden. Das CSV-Format selbst muss nicht dargestellt werden - dies sind Zeichenfolgen, deren Elemente durch ein Zeichen getrennt sind, meistens ";", aber im allgemeinen Fall kann ein beliebiges Zeichen als Trennzeichen fungieren. Angenommen, die Zeichen 0x0D + 0x0A werden zum Trennen der Zeichenfolgen verwendet. Die erste Zeile in der CSV-Datei ist normalerweise die Kopfzeile und definiert die Spaltennamen.
Definieren Sie die Paketschnittstelle
CREATE OR REPLACE PACKAGE pp_csv AS PROCEDURE query2sheet( stmt IN VARCHAR2, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); PROCEDURE query2sheet( ref_cursor IN OUT SYS_REFCURSOR, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); END;
Es gibt zwei überladene Prozeduren. Der Unterschied besteht darin, dass eine von ihnen die Anforderung als Zeichenfolge und die andere als Verknüpfung zum Cursor empfängt. Der zweite Parameter ist die Ausgabe, dies ist das gewünschte Ergebnis im CLOB-Objekt. Schließlich ist der dritte Parameter das CSV-Trennzeichen.
Bei der Implementierung dieser Prozeduren hilft uns das integrierte DBMS_SQL-Paket, mit dem wir mit dynamischen Cursorn arbeiten können, wenn im Voraus (in der Kompilierungsphase) nicht genau bekannt ist, wie viele Spalten an der Auswahl beteiligt sind.
Mit den DBMS_SQL-Funktionen können Sie beliebige Abfragen dynamisch analysieren und ausführen. Bei einer Prozedur, die eine Anforderung als Zeichenfolge akzeptiert, geschieht dies folgendermaßen:
AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
Für eine Prozedur, die eine Cursor-Variable verwendet, ist alles einfacher - ab der 11. Version von Oracle ist die Konvertierung "Cursor-Variable → SQL-Cursornummer" verfügbar.
Die Funktion DBMS_SQL.TO_CURSOR_NUMBER konvertiert die Variable REFCURSOR (stark oder schwach typisiert) in die SQL-Cursornummer, die dann an DBMS_SQL-Routinen übergeben werden kann. In diesem Fall muss die Cursor-Variable geöffnet sein, bevor sie an die Funktion DBMS_SQL.TO_CURSOR_NUMBER übergeben wird.
cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);
In beiden Versionen des Aufrufs ging es darum, die
Nummer des dynamischen Cursors und den zugehörigen Datensatz zu ermitteln. Der nächste Schritt besteht darin, Informationen über die Spalten dieses Satzes zu erhalten und die Daten selbst zu extrahieren.
Mit dem DMBS_SQL-Paket können Sie die Spalten eines dynamischen Cursors beschreiben und Informationen zu jeder Spalte in einem assoziativen Array von Datensätzen zurückgeben.
Dazu müssen Sie die PL / SQL-Auflistung basierend auf dem Auflistungstyp DBMS_SQL.DESC_TAB deklarieren (oder DESC_TAB2, wenn die Abfrage Spaltennamen zurückgeben kann, die länger als 30 Zeichen sind). Danach können Sie Erfassungsmethoden verwenden, um die Tabelle zu durchlaufen und Cursorinformationen abzurufen.
AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER;
Als nächstes muss das DBMS_SQL-Paket über den Typ jeder Spalte informiert werden, die mithilfe der dynamischen Abfrage ausgewählt wurde. Dies erfolgt durch Aufrufen von DEFINE_COLUMN.
FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP;
Im zweiten Argument DEFINE_COLUMN wird eine Zahl übergeben - die sequentielle Position der Spalte in der Liste. Das dritte Argument legt den Datentyp der Cursorspalte fest. Es wird ein Ausdruck des entsprechenden Typs übergeben. Mit anderen Worten, DBMS_SQL.DEFINE_COLUMN wird keine Zeichenfolge mit einem Typnamen (z. B. "VARCHAR2") übergeben, sondern eine mit dem Typ VARCHAR2 definierte Variable.
Wenn Sie im vierten Argument eine Spalte vom Typ Zeichen definieren, müssen Sie die maximale Länge der Werte angeben, die in den Cursor geladen werden können.
Die vorbereitenden Vorgänge sind abgeschlossen. Jetzt können Sie eine Kopfzeile erstellen und mit dem Extrahieren von Daten beginnen.
AS cap CLOB;
Daten werden zeilenweise mit DBMS_SQL.FETCH_ROWS und nachfolgenden Aufrufen von DBMS_SQL.COLUMN_VALUE abgerufen, um die Werte der einzelnen Spalten abzurufen.
AS bod CLOB;
Dann bleibt nur noch die resultierende CSV zu sammeln
DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod);
Fehler behandeln
EXCEPTION WHEN OTHERS THEN sheet := NULL;
Und schließen Sie den Cursor
DBMS_SQL.CLOSE_CURSOR(cur);
Paketverwendungsoptionen
DECLARE csv CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet('SELECT empcode, fio FROM employee WHERE ROWNUM < 10', csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
DECLARE csv CLOB; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT empcode, fio FROM employee WHERE ROWNUM < 10; DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet(cur, csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
Das ist in der Tat alles, der
Quellcode ist beigefügt .
Das Buch half bei der Entwicklung
Feuerstein S., angekommen B. - Oracle PL / SQL. Für Profis.