Wenn es nicht gerade darum geht binär Dateien in der Datenbank zu speichern dann wird man in den meisten Fällen zu den Spalten Typen clob oder nclob greifen. Die sind für Inhalte geeignet, die in anderen Datenbanken als text gespeichert würden. Allerdings ist die Umsetzung in Oracle ein bisschen komplexer und erfordert deshalb vom Nutzer die Bereitschaft mit zu denken.
LOBs speichern nämlich typischerweise einen Zeiger in der LOB Spalte. Wenn dieser Zeiger nicht da ist, hat die Spalte den Wert NULL, also keinen oder einen undefinierten Wert. Wenn er aber da ist, kann er immer noch auf etwas Leeres zeigen, oder eben auf eine echte Bytesequenz mit Daten.
CREATE TABLE demo ( id NUMBER(10), lob CLOB ); INSERT INTO demo (id) VALUES (1);
Nach diesem insert ist die Spalte noch NULL, sowie man es bei jedem anderen Datentyp auch kennt und erwartet.
UPDATE demo SET lob = empty_clob() WHERE id = 1;
Jetzt gibt eine Abfrage mit WHERE lob is null keine Zeilen mehr zurück. Statt dessen können wir aber mit den gängigen String-Funktionen überprüfen, was in dem LOB gespeichert ist:
SELECT id, length(lob) as std_length, dbms_lob.getlength(lob) as other_length FROM demo WHERE id = 1;
In beiden Fällen wird die Länge 0 sein.
Unabhängig davon, ob die LOB Spalte jetzt NULL ist oder ein Zeiger auf einen leeren Bereich, kann man die Spalte einfach wie eine VARCHAR Spalte per UPDATE (oder auch per INSERT) mit einem Wert versehen. Etwas anders verhalten sich LOB Variablen in PL/SQL.
UPDATE demo SET lob = 'Text would also fit in a VARCHAR' where id = 1; DECLARE locator CLOB; content VARCHAR2(100); BEGIN INSERT INTO demo (id,lob) VALUES (2, empty_clob()) RETURNING lob INTO locator; content := 'Das ist der Inhalt'; -- Inhalt in den lob-Zeiger (Variable locator) schreiben dbms_lob.write( locator, length(content), 1, content); COMMIT; END;
Wichtig ist dabei, zu verstehen, dass sich locator insofern von einer normalen
PL/SQL Variablen unterscheidet, dass der Wert der Variablen locator sich nicht
in der SGA mit allen anderen PL/SQL Variablen befindet (die Zuweisung passiert
ja auch nicht mit dem Operator :=), sondern direkt im Tablespace,
auf den diese Zeigervariable verweist (die Zuweisung mit der Funktion
dbms_lob.write macht eben genau diesen Unterschied). Die Variable bzw. deren
Inhalt muss also nicht mehr per UPDATE set lob = locator in die
Tabelle gebracht werden, wohl aber braucht es ein COMMIT nach dem Aufruf der
Funktion dbms_lob.write, denn die gehört noch zu der Transaktion, die mit der
INSERT SQL-Anweisung begonnen wurde, dazu.
DECLARE lob CLOB; text VARCHAR2(100) := 'Ein bisschen Text'; BEGIN dbms_lob.write(lob, length(text), 1, text); END; Error: Invalid LOB locator specified: ORA-22275
Da die Variable lob nicht durch eine Abfrage auf eine LOB Spalte ihren Wert bekommen hat (der auch ein leerer Zeiger sein könnte), ist sie noch nicht initialisiert. Das muss also separat in PL/SQL passieren und zwar vor dem dbms_lob.write Statement:
dbms_lob.createtemporary(lob, true, dbms_lob.session);
Sobald eine LOB-Variable mit SQL-Semantik bearbeitet wird, also nicht mit dem dbms_lob Paket, wird von der Zeiger-Variable eine Kopie in der SGA erstellt. Und zwar nicht nur von der Zeiger-Variablen, sondern eben auch von dem Daten Block im Hauptspeicher auf den sie verweist.
DECLARE locator CLOB; v_lob CLOB; text VARCHAR2(100) := ' Ein bisschen Text anhängen'; BEGIN SELECT lob INTO locator FROM demo WHERE id = 1 FOR UPDATE OF lob; dbms_lob.writeappend(locator, length(text), 1, text); v_lob := upper(locator); -- v_lob ist jetzt neue unabhängige Variable -- deshalb muss sie per Update in die Datenbank-Tabelle geschrieben werden UPDATE demo SET lob = v_lob WHERE id = 1; END;
Prozeduren oder Funktionen, die Daten, z.B in einer Schleife, berechnen, tun dies im Normalfall komplett, bevor sie das Ergebnis zurück liefern. Bekanntestes Beispiel dafür ist vielleicht dbms_output, dessen Ausgabe erst zu sehen ist, wenn die Prozedur komplett zu Ende ist. Das schränkt natürlich ihren Nutzen als Ablaufverfolgung beim Debugging erheblich ein. Wenn es große Datenmengen sind, die da durch PL/SQL durch geschleust werden, kann das auch dazu führen, dass es zu einer nicht unerheblichen Speicherbelastung kommt, da der Speicher, der im Verlauf der Prozedur genutzt wird, nur am Ende auf einen Schlag, nicht aber inkrementell frei gegeben werden kann.
Solche Wartezeiten sind auch nicht im Sinne einer parallelen Weiterverarbeitung der Daten. Wenn es eine Unmenge von Daten gibt, könnte die nächste Verarbeitungsstufe ja schon mal mit dem ersten Teil anfangen, anstatt warten zu müssen, bis auch der 25. und letzte Teil durch ist. Solche Modelle kennt man von der klassischen Unix Pipe in der Shell und konsequenterweise heißt dann auch die Lösung, mit der Oracle für seine Datenbank aufwartet, pipelined Functions oder Tabellenfunktionen.
Der zweite Namen, Tabellenfunktion, kommt daher, weil solche Funktionen immer dort einsetzbar sind, wo ansonsten eine View oder eine Tabelle selber stehen würde:
SELECT INTO other_table SELECT * FROM pipelined_function;
Das ist das, was PostgreSQL schon ziemlich lange mit seinen RETURN SETOF praktiziert. Eine derartige Funktion kann tatsächlich wie eine Tabelle oder wie eine View benutzt werden.
Während es bei PostgreSQL erforderlich ist, dass man einen Record als Basis-Rückgabe-Typ definiert, muss bei Oracle sogar eine Collection definiert werden. Außerdem darf es auch keine Index-by PL/SQL Tabelle sein, sondern es muss eine nested table sein. Aus dem gleichen Grund wie auch eine User-Defined PL/SQL Funktion keinen boolean Wert zurück geben darf, wenn sie direkt in einer SQL Anweisung verwendet werden soll, weil der Typ boolean als Spaltentyp nicht erlaubt ist und damit auch nicht in einer SQL-Abfrage vorkommen darf. Das gleiche gilt für eine Index-by PL/SQL Tabelle. Der Typ, um den die Collection gewickelt ist, kann entweder ein einfacher skalarer Typ sein (NUMBER, VARCHAR2) oder ein Objekt-Typ. Ein Record artiger Typ ist nicht möglich — zumindest nicht in Oracle 9i. In 11g scheint sich das etwas vereinfacht zu haben.
Die folgende Funktion baut in Oracle nach, was PostgreSQL mit der built-in Funktion generate_series schon von Hause aus mit bringt: einen Generator von Zahlen:
CREATE TYPE numset_t IS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_range(p_max IN NUMBER) RETURN numset_t PIPELINED IS i NUMBER(10) := 0; BEGIN LOOP i := i + 1; EXIT WHEN i > p_max; PIPE ROW(i); END LOOP; RETURN; END;
Das obige Beispiel, das zeigt, wie eine pipelined Funktion schlicht und einfach an Stelle eines Views verwendet werden kann, funktioniert allerdings nur im PL/SQL Kontext (erkennbar am SELECT INTO). Wenn die Funktion innerhalb von SQL verwendet werden soll, muss das Set, das sie in die PL/SQL Variable schreibt, in eine n x m ResultSet Struktur entpackt werden. Für so was ist die CAST-Funktion TABLE( .. ) zuständig.
SELECT * FROM TABLE(pipelined_function); -- oder mit der eben angelegten Funktion und mehr Praxisbezug: SELECT sysdate + column_value AS next_week FROM table(generate_range(7));
Ohne pipelined Funktionen, die es seit 9i gibt, kann man das mit der gleichen
Funktionssignatur implementieren. Allerdings muss die Collection-Table
innerhalb des Funktionsrumpfes komplett aufgebaut werden und als ganzes in
einem return Statement zurück gegeben werden. Das ist zwar näher an dem, was
man von der allgemeinen prozeduralen Programmierung kennt, und deshalb
einfacher zu verstehen, aber die Vorteile der eventuellen parallelen
Verarbeitung und des geringeren Speicherverbrauchs sind weg.
CREATE OR REPLACE FUNCTION generate_range(p_max IN NUMBER) RETURN numset_t IS INIT_SIZE CONSTANT NUMBER(10) := 1; i NUMBER(10) := INIT_SIZE + 1; series numset_t; BEGIN -- Initialize the collection with the constructor (and INIT_SIZE elements) series := numset_t(1); IF p_max > INIT_SIZE THEN -- Extend the nested table to the requested size series.EXTEND(p_max - INIT_SIZE); -- ... and fill the additional elements with sequential numbers in a loop WHILE i <= p_max LOOP series(i) := i; i := i + 1; END LOOP; END IF; RETURN series; END;
Das next week Anwendungsbeispiel sollte als Inspiration ausreichen, um den Nutzen solch einer series pipelined Funktion zu sehen. Sie kann immer da verwendet werden, wo man es bei der Pseudo-Tabelle dual bedauert hat, dass sie nur eine Zeile als Result Set hat und nicht mehrere. Ich habe schon häufiger Datenbanken gesehen in der es eine super_dual Tabelle gab, die mit 10 oder 100 sequenziellen Zahlen befüllt war.
Bei allem Spot über super_dual Tabellen sollte man sich aber doch manchmal auch an die eigene Nase fassen. Wer sich ein bisschen eingehender mit den Oracle Erweiterungen befasst hat, der kennt die CONNECT BY Klausel mit der sich ohne den PL/SQL Umweg direkt (und performant) in SQL ein Range erzeugen lässt:
SELECT level AS num_in_range FROM dual CONNECT BY level <= 10;
Seit Oracle 11g R2 kann man das auch durch eine rekursive Common Table
Expression erreichen. Rekursive With Klauseln oder CTEs brauchen zwingend
eine Vorab-Deklaration der Spalten, da der Parser die Spalten wohl nicht
aus dem rekursiven UNION ALL Sub-Select herauslesen kann (was übrigens bei
allen anderen Datenbanken, die rekursive CTEs unterstützen auch so ist).
Im nachfolgenden Beispiel ist das numbers(n). Und das stellt einen kombinierten
Tabellen und Spalten Alias da, mit numbers als Tabellenname und n als
Spaltenname. Aber solche kombinierten Aliase wurden von Oracle vor 11g nicht
unterstützt:
WITH numbers(n) AS ( SELECT 1 FROM dual UNION ALL SELECT n + 1 FROM numbers WHERE n <= 10 ) SELECT * FROM numbers;
Der beschränkte Einsatz von Record Types in den pipelined Functions ist aber durchaus zu verschmerzen, denn die Deklaration eines Object-Typs unterscheidet sich nicht sonderlich von der eines Record-artigen. Eine Record artige Variable muss lediglich initialisiert werden, während für die Objekt-Variable eine Instanzierung zwingend notwendig ist.
CREATE TYPE MyType AS OBJECT ( num_var NUMBER(10), date_var DATE, text_var VARCHAR2(30) ); CREATE TYPE MyType_set_t AS TABLE OF MyType; BEGIN my_data MyType := MyType(NULL,NULL,NULL); END; /
Sinnvoll nutzbar ist so was zum Beispiel beim Einbinden einer where in Klausel,
ohne dafür auf dynamisches SQL zurückgreifen zu müssen, wenn ich nicht im
voraus weiß, wie die Elemente in meiner IN Liste heißen, bzw. wie viele es
sind: IN (:A, :B, :C) gingen zwar schon, aber flexibel ist das nicht, denn
weder den Fall einer Liste mit 2 Elementen, noch den einer mit 4 Elementen
kann ich damit abbilden:
Unser Ziel ist:
SELECT t.* FROM the_table t INNER JOIN table(string_to_tab(csv_liste)) tf ON t.col_a = tf.column_value; SELECT t.* FROM the_table WHERE t.col_a IN (select column_value from table(string_to_tab(csv_liste)))
Eine Anmerkung ist aber noch wichtig: Pipelined Functions oder generell Table Functions finden zwar im Kontext einer SQL-Abfrage statt (man kann ja von ihnen Zeilen selektieren wie von einer Tabelle oder von einer View), aber deshalb ist sie nicht Read-Commited so wie das etwa eine umfangreiche Report Abfrage mit etlichen Sub-Selects wäre. Innerhalb der Funktion können ja durchaus Kontext-Wechsel zischen SQL Engine und PL/SQL Engine auftreten.
Die angesprochene umfangreiche Select-Abfrage über 20 oder 30 Zeilen findet man aber gar nicht so oft. Typischer ist eine Report-Erstellung, die am Anfang einen 15 Zeilen Cursor ausführt, um alle relevanten Datensätze, die für den Report gebraucht werden, zu selektieren. Diese Abfrage des Cursors, ist allerdings Read-Committed, was bedeutet, dass auch wenn die Verarbeitung eines Cursor-Datensatzes 5 Sekunden dauert, die 24.selektierte Zeile dieses Cursors immer noch so aussieht, wie sie vor 120 Sekunden beim Öffnen des Cursors ausgesehen hätte.
Das Problem dabei ist nur, dass in den PL/SQL Statements zwischen den einzelnen Fetches vom Cursor Loop unter Umständen auch weitere Abfragen an die Datenbank stattfinden können. Diese Abfragen sind nicht im gleichen Kontext, wie die ursprüngliche Abfrage und daher aus dem "Read Committed" Kontext ausgeschlossen. Wenn aus der Tabelle, auf die der Cursor zugreift, — zwischen dem 2, und 3, Fetch — noch mal gelesen wird, kann das durchaus ein anderes ResultSet ergeben als kalkuliert, da just in dieser Zwischenzeit eine andere Session die 4 oder 5 Zeilen gelöscht haben könnte.
Das Paket DBMS_OBFUSCATION_TOOLKIT, das seit Oracle 8i unterstützt wird, bietet eine Funktion MD5, die einen String als Parameter erwartet und ihr Ergebnis auch als String zurück gibt. Also versucht man sich an dem naheliegenden:
SELECT dbms_obfuscation_toolkit.md5('123456') FROM dual;
Aber das bricht mit einem ORA-06553: PLS-307: too many declarations of 'MD5' match this call ab. Die typische Kontrolle mit DESC dbms_obfuscation_toolkit zeigt genau die erwartete Funktion:
FUNCTION MD5 RETURNS VARCHAR2(16) Argument Name Type In/Out ------------------------------ ----------------------- ------ INPUT_STRING VARCHAR2 IN FUNCTION MD5 RETURNS RAW(16) Argument Name Type In/Out ------------------------------ ----------------------- ------ INPUT RAW IN
Etwaige Cast-Versuche, um Oracle von einem bestimmten Parameter-Typen zu überzeugen, helfen auch nicht weiter. Die einzige Möglichkeit, um die Exception zu umgehen ist, die Funktion mit benannten Parametern aufzurufen. Aber damit steckt man dann in der Catch-22 Situation, denn Funktionsaufrufe mit benannten Parametern sind direkt in SQL nicht erlaubt (mit Version 11g hat sich das geändert).
Da die MD5-Checksumme aber typischerweise 7-bit ASCII clean sein soll, wird man den Wert, den dbms_obfuscation_toolkit.md5 zurück gibt, ohnehin nach bearbeiten. rawtohex gibt den hexadezimalen String mit Großbuchstaben zurück, was meistens nicht so gewollt ist und nochmal einen weiteren Wrapper mit lower erfordert.
DECLARE v_result VARCHAR2(255); BEGIN v_result := dbms_obfuscation_toolkit.md5(input_string => :v_input); dbms_output.put_line('MD5-Sum: ' || v_result); v_result := rawtohex( utl_raw.cast_to_raw(v_result) ); dbms_output.put_line('MD5-Sum: ' || v_result); END;
Wenn die Fragestellung ist, ob ein bestimmter Datensatz in einer Tabelle schon existiert, ist der erste Ansatz in PL/SQL meistens:
SELECT count(*) INTO has_rows FROM the_table WHERE column_a = 34 AND column_b = v_attribute_b; IF has_rows > 0 THEN -- code if the data already exists ELSE -- other code END IF;
SELECT count(*) ist eine dankbare Abfrage, weil es weder eine
NO_DATA_FOUND noch eine TOO_MANY_ROWS Exception verursachen kann
und außerdem auch im Gegensatz zu sum(1) nie NULL als Wert zurück
gibt.
Das Problem dabei ist aber, dass die PL/SQL Variable in die selektiert wird,
zwar has_rows heisst und damit ihren logischen Zweck beschreibt, aber die
SQL-Abfrage nicht dasselbe logische Ergebnis liefert, sondern die Anzahl der
Zeilen, die auf die Bedingung passen, also count_of_rows. Bei einer
umfangreichen Tabelle bedeutet das, dass die Datenbank nach dem ersten
passenden Datensatz die Tabelle noch weiter durchlaufen wird, um nach weiteren
passenden Zeilen zu suchen, die alle von der Aggregats-Funktion count(*)
verarbeitet werden müssen. Die Verarbeitung in dieser Aggregats-Funktion ist
zwar denkbar einfach und verursacht keine nennenswerte CPU Last, der Umstand,
dass die Tabelle aber weiter durchlaufen werden muss, ohne dass sich am
Ergebnis etwas ändert, kostet aber sehr wohl Ressourcen.
Der alternative, wenn auch etwas schreibaufwendigere Ansatz ist, einen
expliziten Cursor zu verwenden. Auch der vermeidet das zusätzliche Exception
Handling für NO_DATA_FOUND und TOO_MANY_ROWS:
DECLARE CURSOR cur(p_attribute VARCHAR2) IS SELECT 1 FROM the_table WHERE column_a = 34 AND column_b = p_attribute; has_rows NUMBER; BEGIN OPEN cur('a_string_value'); FETCH cur INTO has_rows; CLOSE cur; IF has_rows = 1 THEN ... END IF; END;
Der Cursor wird geöffnet (und dabei das SQL Statement ausgeführt) und dann wird der erste Datensatz in die Variable has_rows kopiert. Das war's.
Bei umfangreichen Tabellen ist das sicherlich eine effizientere Methode, aber
die beste Lösung ist, die anfängliche Fragestellung in adäquates SQL
umzusetzen. Denn es ist einfach nicht der richtige Weg, eine Prüfung auf
Existenz über die Anweisung count(*) durchzuführen. Dafür gibt es das
etwas umständlich anmutende, aber effiziente EXISTS Konstrukt:
SELECT 1 INTO has_rows FROM dual WHERE EXISTS (select 1 from the_table where column_a = 34 AND column_b = v_attribute_b);
Üblicherweise steht die Frage, ob ein Datensatz schon vorhanden ist, nicht
einfach nur so im Raum und will auch nicht nur rein theoretisch beantwortet
werden. Sondern sie steht in einem bestimmten Kontext und der ist eben oft
genug die Frage, ob ich, um meine Daten in die Tabelle zu bringen, einen
vorhandenen Datensatz (mit dem gleichen Schlüssel) updaten muss oder ob ich
einen neuen Datensatz einfügen muss. Und da ist count(*) allemal, aber auch
EXISTS der falsche Ansatz, denn in diesem Fall brauche ich die Abfrage atomar
zusammen mit meiner Änderungsanweisung. In einer Datenbank, als klassischem
Multiuser Environment, muss ich immer damit rechnen, dass etwas anderes
parallel die Daten ändert: Ein "Datensatz mit diesem Schlüssel existiert
nicht" kann in der nächsten hunderstel Sekunde schon falsch sein, da gerade
eben jemand anderes solch einen Datensatz eingefügt hat.
Von daher ist es sinnvoll, einfach mal ein UPDATE abzusetzen und auf die
SQL%ROWCOUNT zu achten, die die Information enthält, ob Zeilen vom UPDATE
betroffen waren, oder nicht. Falls ja, ist nichts mehr zu tun. Falls nein,
muss eben noch ein INSERT (leider auch kein atomares) hinterher geschoben
werden. Umgekehrt, sich erst am INSERT zu probieren und bei einer Dublicate Key
Exception, doch noch ein UPDATE zu versuchen, ist natürlich auch möglich und
je nach Datenlage günstiger, weil vielleicht (zu Beginn) die Wahrscheinlichkeit
auf doppelte Schlüssel zu treffen, sehr gering ist.
Im Gegensatz zu PostgreSQL kennt Oracle kein ILIKE zur case-insensitiven Suche, da muss man sich mit einem zusätzlichen upper oder lower, das um den Feldnamen gewickelt wird behelfen. Anders sieht das beim mit 9i neuen Vergleichsoperator mit regulären Ausdrücken regexp_like aus, der akzeptiert einen dritten optionalen Parameter 'i' für case-insensitive Vergleiche:
... WHERE regexp_like(col_name,'^H[a-e]','i')
Allein vom Hinschauen sollte man schon erkennen, dass auf so ein komplex anmutendes Konstrukt kein Index angewandt werden kann, wie das ja beim normalen LIKE möglich ist, wenn der gesuchte Ausdruck nicht mit einer Wildcard beginnt. Ich bin mir ziemlich sicher, dass die neueren Funktions basierten Indizes von Oracle bei den regulären Ausdrücken auch an ihre Grenzen kommen.
Was die Wildcards anbetrifft, muss man übrigens ziemlich lange suchen, bis man mal herausfindet, wie nach einen Wildcard-Zeichen selber zu suchen ist. Ich habe jedenfalls schon etliche Oracle-Bücher in der Hand gehabt, in denen dazu nichts zu finden war. Meistens muss man schon froh sein, wenn wenigstens noch erwähnt wird, dass auch der Unterstrich als Wildcard fungiert. Die typische Suche nach einem Prefix im Data-Dictionary macht nämlich nicht immer, was sie soll:
SELECT * FROM cat WHERE table_name like 'V_%';
Der Unterstrich wird nämlich nicht als Teil vom Prefix angesehen, sondern es wird alles ausgegeben, was mit V anfängt.
Nachdem man die üblichen Verdächtigen wie Backslash oder — analog zum verdoppelten Quote um einfache Quotes in literalen Strings unterzubringen — ein verdoppeltes Prozentzeichen erfolglos probiert hat, ist man versucht aufzugeben. Richtig, wenn auch sehr umständlich, ist das Escape-Zeichen in einer eigenen Klausel explizit mit anzugeben:
WHERE col_name LIKE 'C%\%' ESCAPE '\'
Der typische Anfänger wird seine INSERTs schreiben, wie es einigermaßen naheliegend ist:
INSERT INTO the_table (col_A, col_b, col_C) VALUES (23,'text',sysdate);
Das INSERT INTO ... SELECT x, y, ... FROM some_table ist am Anfang einfach
zu befremdlich für jemanden, der aus der prozeduralen Programmierung kommt.
Für die Profis oder Maniacs hingegen — wie auch immer man das sehen will —
ist das gegen die reine Lehre und sie bauen auch die literalen Werte in ein
SELECT FROM ein (es soll ja schließlich Mengen artig aussehen):
INSERT INTO the_table (col_A, col_b, col_C) SELECT 23,'text',sysdate FROM dual UNION ALL SELECT 24,'oder',sysdate -1/20 FROM dual;
Die Variante mit den Record-Variablen lehnt sich eher an den 1.Ansatz an, ist aber im Gegensatz zu den beiden anderen Methoden nur in PL/SQL nutzbar, da reines SQL nichts von Records weiß. Irgendwo her muss die Record Variable ja auch mit Werten befüllt werden und da braucht es nun meistens doch eine sehr herkömmliche PL/SQL Zuweisung.
DECLARE rec_row the_table%ROWTYPE; BEGIN rec_row.col_A := 21; rec_row.col_B := 'Some Text'; rec_row.col_c := sysdate; INSERT INTO the_table VALUES rec_row; -- or UPDATE dept SET ROW = rec_row WHERE deptno = 30; END;
Die Einschränkung, dass eine integer oder zählbare Variable als Index für
die PL/SQL Tabellen herhalten muss, ist mit 9iR2 weggefallen.
Der Inhalt einer PL/SQL Tabelle konnte ja schon immer alles sein (VARCHAR,
NUMBER, DATE oder ein RECORD Typ). Dadurch, dass strings als Index genutzt
werden können, sind sie auch wie Perl's Hash Implementierungen nutzbar.
Mit dict.FIRST und dict.NEXT('stringIndex') kann die dünn besetzte Tabelle
in einer While-Schleife immer noch durchlaufen werden. Nicht mehr aber in
einer FOR-Schleife, denn der Index ist ja nicht mehr abzählbar.
FOR i IN index_by.FIRST .. index_by.LAST LOOP ... END LOOP;
Diese Art von For-Schleife war auch früher schon sehr heikel, denn bei einem dünn besetzten Array gibt es keine Garantie, dass nach dem Index 4, der Index 5 auch tatsächlich vorhanden ist.
MERGE ist Oracle's Analogon zu REPLACE in MySQL. Allerdings ist merge sehr viel komplexer, aber auch flexibler als replace. In MySQL wird einfach das Schlüsselwort INSERT durch REPLACE ersetzt, was dazu führt, dass alle Zeilen eingefügt werden, bis auf die, die schon vorhanden sind. Die entscheidende Frage ist nun: Was ist das Kriterium dafür, ob eine Zeile vorhanden ist oder nicht. Die Antwort von MySQL darauf ist schlicht und einfach: Wenn die Schlüsselspalten gleich sind. Die Dokumentation schreibt dazu:
Oracle ist da flexibler, denn es lässt den Anwender selber bestimmen, in welchen Spalten die auf Gleichheit zu testenden Zeilen übereinstimmen sollen. Dabei wird die von Joins bekannte Syntax "ON (a.col = b.col)" verwendet (und wie bei den Joins müssen das natürlich nicht zwei gleichnamige Spalten sein und es gibt auch keine Bedingung, dass sie zum Unique Key gehören müssen):
MERGE INTO emp e USING emp_dept20 v ON (e.empno = v.empno) WHEN MATCHED THEN update set e.sal = v.sal WHEN NOT MATCHED THEN insert (empno, ename, mgr, job, salury, deptno) VALUES (v.empno, v.ename, v.mgr, b.job, v.sal, 20);
Interessanterweise kennt auch Microsofts MS-SQL eine merge Anweisung und deren Syntax ist nahezu identisch mit der von Oracle. MATCHED hat anscheinend eine Sonderstellung, denn in einer normalen Filter-Klausel müssten die zwei mit OR verknüpften Filter-Bedingungen in Klammern eingeschlossen werden.
MERGE merge_table AS m USING merge_delta AS d ON (m.ID = d.ID) WHEN MATCHED AND m.field1 <> d.field1 OR m.field2 <> d.field2 THEN UPDATE SET m.field1 = d.field1, m.field2 = d.field2 WHEN NOT MATCHED THEN INSERT (ID, field1, field2) VALUES (d.ID, d.field1, d.field2);
Temporäre Tabellen sind bei Oracle globale Objekte. Wenn in einer anderen Session eine temporary table mit gleichem Namen angelegt wird, dann wird dieser Versuch fehlschlagen. Lediglich die Inhalte der temporären Tabellen sind spezifisch für den jeweiligen User bzw. bei Verwendung von DELETE ROWS sogar nur in der aktuellen Transaktion sichtbar.
Diese Auftrennung zwischen Container (table) und Inhalt (rows) mutet etwas merkwürdig an, weil User A etwas anderes in der Tabelle sieht als User B. Auf den zweiten Blick kann man darin aber auch die konsequente Fortsetzung des Isolationsprinzip bei Transaktionen sehen. Dabei sieht ja User A auch nichts von den Änderungen, die User B vorgenommen hat, solange dessen Änderungen nicht per COMMIT festgeschrieben werden.
Ein Vergleich mit einer verschachtelten Transaktion ist dabei hilfreich. Verschachtelte Transaktionen findet man ja fast nur beim MS SQL-Server, fast alle anderen Datenbank Engines halten sie für Teufelszeug. Oracle auch. Das Verhalten der temporären Tabellen bei Oracle lässt sich ganz gut mit zwei verschachtelten Transaktionen erklären:
Session +-------- hidden outer transaction only for temporary tables | | +------ Inner normal transaction for all tables | | : : | | | +----- COMMIT OR ROLLBACK | | | : other transactions, statements, etc | : | +----- End of Session with silent outer rollback
Die Transaktion ist erst dann komplett wenn beide angefangenen Transaktionen
mit COMMIT enden. Die äußere für die temporären Tabellen mit PRESERVE ROWS
endet mit der Session, aber in einem ROLLBACK und damit sind die Daten dann
weg.
Bei DELETE ROWS ist die Verschachtelung umgekehrt. Die versteckte für die
temporäre Tabelle liegt innerhalb einer normalen Transaktionsklammer und
wird unmittelbar vor dem Beenden der normalen Transaktion auch mit beendet,
aber mit einem ROLLBACK. Und damit sind die Daten verschwunden. Für alle,
auch für die in der Session. Naja, und für die außerhalb der Session waren
sie ja noch nicht (richtig) da, also verschwinden sie nicht, sondern
"verbleiben im nichts".
Dieses Verhalten passt auch zu der Philosophie von Oracle im prozeduralen Kontext (also in anonymen PL/SQL Blöcken oder in Prozeduren) keine DDL Anweisungen zu erlauben. "Mal schnell eine temporäre Tabelle anlegen, weil ich die vier oder 5 Anweisungen später wieder brauche" wird von Oracle nicht gefördert. Dafür sind PL/SQL Tables vorgesehen, die ja auch Records als Elemente haben können und ihre Inhalte per FORALL bulk Binding in herkömmliche Tabellen schreiben können.
CREATE GLOBAL TEMPORARY TABLE temp_copy ON COMMIT PRESERVE ROWS AS SELECT * FROM another_table WHERE 1 = 0; CREATE GLOBAL TEMPORARY TABLE temp_explicit ( x INT, a_date DATE ) ON COMMIT DELETE ROWS;
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48812348054
Was passiert, wenn die temporäre Tabelle mit einem nicht leeren SELECT angelegt
wird? Bei DELETE ROWS wären die Inhalte ja eigentlich sofort wieder weg, da
eine DDL Anweisung (was CREATE TEMPORARY TABLE ja ist) bei Oracle
bekanntlich in ein implizites COMMIT davor und danach eingebettet ist.
Und genau so ist es. Eine nicht-leere temporäre Tabelle anzulegen, ist also nur mit der Option PRESERVE ROWS sinnvoll.
Gelöscht wird eine temporäre Tabelle ohne die zusätzlichen Attribute GLOBAL und TEMPORARY:
DROP TABLE temp_copy;
Sinnvoll sind solche Tabellen für klassische Lookup-Tabellen, bei denen eine numerische Id oder eine Abkürzung als Schlüssel für eine (längere) Beschreibung genutzt wird. Den unique Index für die Id Spalte braucht es ohnehin. Da aber beim Lesen der Zeile (und das wird immer die vorrangige Nutzung eine Lookup Tabelle sein), erst der Index (von der Platte) gelesen und dann mit einem zweiten (gezielten) Zugriff der jeweilige Block mit den Tabellendaten ausgelesen wird, ist der zweite Zugriff, wenn auch gegenüber einem Full Table Scan weitaus effizienter, etwas abstrus, denn aus dem 4 oder 8 kByte Block werden lediglich 100 oder 200 Zeichen ausgelesen. Eine Index organisierte Tabelle macht nun das naheliegende, indem sie diese geringen Nutzdaten gleich im Speichersegment des Index mit ablegt und sich damit den zweiten Festplatten Zugriff auf den Block mit den Tabellen-Daten spart.
CREATE TABLE salutation ( salutation_id VARCHAR2(4), description VARCHAR2(25 char), CONSTRAINT pk_salutation PRIMARY KEY (salutation_id) ) ORGANIZATION INDEX;
Beim Anlegen der Tabelle ist da an der Syntax von CREATE TABLE nicht viel zu ändern. Es muss lediglich ein ORGANIZATION INDEX angehängt werden.
External tables sind ein weiterer Tabellen-Typ, der wie eine normale Tabelle spezifiziert wird, aber dann ebenso wie die Index-Tabellen noch eine ORGANIZATION Klausel dazu bekommt. Bei den externen Tabellen ist die allerdings ein bisschen länger als bei den Index organisierten Tabellen. Das ist auch einleuchtend, denn in dieser zusätzlichen Klausel muss all das rein, was ansonsten im SQL*Loader Control-File steht.
Da ein Zugriff auf das Dateisystem statt findet, muss ein DIRECTORY Objekt, das für den Betriebssystem-Pfad mit den Dateien definiert ist, zwingend vorhanden sein. Und damit ist auch klar, dass es externe Tabellen nicht vor Oracle 9i gegeben haben kann, denn mit dieser Version wurden die Directories eingeführt.
CREATE TABLE ext_emp ( employee_id NUMBER(10), last_name VARCHAR2(60), first_name VARCHAR2(60), hire_date DATE ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_dir ACCESS PARAMETERS ( -- Need UTF-8, but comments must be first among access parameters -- hence it can't be placed directly to the `characterset` line RECORDS delimited by newline characterset AL32UTF8 badfile ext_dir: 'employee.bad' logfile ext_dir: 'employee.log' skip 1 FIELDS terminated by ';' missing field values are null ( employee_id INTEGER EXTERNAL, last_name CHAR, middle_name CHAR, first_name CHAR, hire_date CHAR date_format DATE mask "yyyy-mm-dd" ) ) LOCATION ('employee.txt') ) REJECT LIMIT 40; ALTER TABLE ext_emp REJECT LIMIT UNLIMITED;
Die "missing field values" Klausel gehört schon zur Field-List Spezifikation
und muss daher direkt vor der Liste mit Feldern in der Datei kommen. Die Liste
der Felder selber (in Klammer) kann dagegen weggelassen werden, wenn die Felder
in der Datei mit den vorher angegebenen Tabellen-Spalten übereinstimmen. In
diesem Fall hier würde man sich aber auf sehr heiklem Boden bewegen, weil man
dann annehmen würde, dass Oracle schon das richtige Datums-Format verwenden
wird. Die Regel ist also, sobald man eine Datumsspalte in der Datei mit dabei
hat, muss man die Felder explizit mit angeben, und zwar alle, auch wenn nur
eins von 20 Feldern ein Datum ist. Die Typ-Angaben kann man sich dagegen bei
den 19 nicht-Datums-Feldern sparen, denn da errät Oracle den Typ und das Format
immer richtig. Aber der Name des Feldes muss auf jeden Fall angegeben werden.
Durch eine Änderung der Reihenfolge der Felder in der ACCESS PARAMETERS
Klausel kann nämlich der Import auf eine andere Spaltenreihenfolge in der Datei
angepasst werden. Und nicht nur die Reihenfolge kann angepasst werden, sondern
es ist ein generelles Mapping zwischen Source und Destination möglich, bei dem
auch bestimmte Felder übersprungen werden können. Das FILLER Attribut, das man
von SQL*Loader her kennt, ist dabei nicht notwendig und auch gar nicht erlaubt.
Etwas unschön ist, dass es zwar auch ein ALTER TABLE für externe Tabellen gibt, aber das naheliegende, dass man dadurch nämlich die Datei, die "importiert" werden soll, auf eine andere umbiegen kann, funktioniert so nicht. Dafür muss die externe Tabelle neu angelegt werden. Wichtig ist auch zu verstehen, dass der Import, also der SQL*Loader Aufruf bei jedem lesenden Zugriff auf die externe Tabelle abläuft. Man wird also relativ schnell eine ganze Menge an Logfile Einträgen haben. Es sei denn man benutzt die Option NOLOGFILE anstelle der LOGFILE Klausel. Dann hängt man aber bei etwaigen Import-Fehlern ziemlich in der Luft.
Allerdings braucht man ein ALTER TABLE auch nicht unbedingt, um die externe Tabelle auf eine andere Daten-Datei umzubiegen. Die Vorgehensweise ist umgekehrt: Man schiebt der externen Tabelle eine andere Datendatei unter. Und das muss an noch nicht einmal mit Betriebsystem Befehlen bewerkstelligen, sondern man kann dafür die utl_file.fcopy oder utl_file.frename Funktionen nutzen. Da die externen Tabellen von Oracle als read-only behandelt werden, gibt es da auch meistens keine Locks oder andere Sperren, die ein Überschreiben der bisherigen Datei verhindern würden.
SQL*Loader kennt seit 8i sogenannte FILLER Felder, die inbesondere bei csv artigen Daten hilfreich sind. Sie erlauben es nämlich Felder zu spezifizieren und gleichzeitig beim Insert in die Datenbank-Tabelle zu überspringen. Bei Daten mit fester Breite pro Feld ist das keine Verbesserung, denn da konnte man ja einfach das nächste relevanter Feld bei Zeichen 23 statt bei Zeichen 16 anfangen lassen und hat damit automatisch die Spalte (oder die Spalten) zwischen Zeichen 16 und 22 übersprungen. Bei csv Dateien hat man sich da bisher mit den typischen Unix Tools wie sed, cut oder awk beholfen und die Import-Dateien in einem vorangestellten Umformatierungsprozess entsprechend präpariert.
Es ist zwar ein bisschen gegen die UNIX Philosophie, die spezialisierten Tools
nicht mehr das machen zu lassen, was sie am besten können und stattdessen dem
einen fast schon "eierlegende Wolllmichsau" Programm diesen Task auch noch
aufzubürden. Aber es hat den Vorteil, dass diese Spalte nicht weg ist, sondern
schon noch geparst und gelesen wird und lediglich beim letztendlichen Insert
nicht berücksichtigt wird. Ihr Wert kann aber benutzt werden, um Default-Werte
in anderen Spalten festzulegen oder in WHEN Bedingungen darüber entscheiden, ob
eine Zeile verarbeitet oder verworfen wird.
Die Verwendung in SQL Expressions, um csv Werte umzurechnen oder zu
konvertieren, ist erst ab Oracle 9i möglich. Und auch nur dann, wenn das FILLER
Feld im Control File nicht nur als FILLER, sondern als BOUND FILLER
angegeben ist.
OPTIONS ( SKIP=1 ) LOAD DATA CHARACTERSET 'WE8ISO8859P1' INFILE 'employee.txt' APPEND INTO TABLE employees FIELDS TERMINATED BY ';' ( employee_id INTEGER EXTERNAL, last_name CHAR TERMINATED BY ',', middle_name FILLER CHAR TERMINATED BY ',', first_name CHAR, hire_date DATE "yyyy-mm-dd", name_length "greatest(:first_name,:last_name)" )
Das "terminating" Zeichen wird nach INTO TABLE in einer FIELDS Clause angegeben
und dabei in einfache Quotes eingeschlossen. "Zeichen" bedeutet aber nicht
unbedingt, dass das nur ein einzelnes Zeichen sein darf. Es kann da durchaus
auch ein ganzer String wie '|-|' oder '";"' angegeben werden. Wobei sich
letztere Variante nicht lohnt, denn dafür gibt es (OPTIONALLY) ENCLOSED BY '"'.
Eine spezielle Variante von ein ganzer String ist WHITESPACE, das genau so
angegeben werden muss und nicht von einfachen Quotes eingeschlossen werden
darf. Besonders ist es deshalb, weil die Länge variabel ist und Tabs und Spaces
enthalten kann. Der Trenner kann auch durch die Angabe seines ASCII Codes
spezifiziert werden, dazu muss der Byte-Code hexadecimal mit vorangestelltem
X angegeben werden: X'20' Das wäre jetzt ein einfaches Leerzeichen, und zwar
wirklich ein einzelnes und keine Sequenz, wie das bei WHITESPACE ist.
Sinnvoll ist das, wenn Control File und Data File unterschiedliches Encoding
haben. Dann kann man nämlich einfach den Pointcode für den Trenner, eben in der
Codierung in der er in der Datendatei vorkommt, als Hexwert angbeben.
"TERMINATOR" ist ein mit Bedacht gewählter Name, denn eben das ist dieses Zeichen und kein Feldtrenner, wie am das von anderen Programmen gewohnt ist. Das wird dann wichtig, wenn das Terminierungszeichen direkt bei der Spalte angegeben wird und damit das generell für die Tabelle angegebene Zeichen überschreibt. Auf diese Weise sind auch verschachtelte Felder in eine flache Tabellen-Struktur zu konvertieren (in obigem Beispiel passiert das mit den 3 Namensanteilen, die sind durch Komma und nicht wie die restlichen Spalten durch Semikolon getrennt).
Die Syntax mit WITH ermöglicht es eine "inline" oder "virtuelle" View für die Dauer einer Abfrage zu erstellen.
WITH average AS (SELECT avg(e.salary) AS sal FROM department d INNER JOIN employess e ON d.departementid = e.departementid) SELECT d.department_name, avg(e.salary) AS avg_per_department, average.sal FROM department d INNER JOIN employess e ON d.departementid = e.departementid CROSS JOIN average GROUP BY d.department_name, average.sal HAVING avg(e.salary) > average.sal
Statt CROSS JOIN average hätte hier natürlich auch die (Sub-)Select Anweisung nach WITH average AS direkt als Subselect nach CROSS JOIN geschrieben werden können. Wenn der Alias average in dem nachfolgenden Statement nicht nur einmal sondern mehrfach genutzt wird, dann ist ein Duplizieren des Subselect an mehreren Stellen nicht mehr schneller ... und sicherlich auch schwerer zu lesen.
Hierarchien werden üblicherweise durch verkettete Listen bzw. verbundene
Baumstrukturen ausgedrückt, wobei jede Node ein weiteres Attribut
"is-a-children-of" hat, das die Verbindung zur nächsten Ebene ausdrückt.
In der einzelnen Node betrachtet, tut sich da noch nicht viel. Um ein
Gesamtbild der Baumstruktur zu bekommen, muss man den "Graphen zeichnen"
und dabei alle Nodes durchlaufen. Genau dazu kann man auch Oracle auffordern,
allerdings zeichnet die Datenbank nicht wirklich einen Baum, sondern
durchläuft ihn nur und gibt für jede Tabellenzeile mit Hilfe der Pseudospalte
LEVEL den Hierarchie-Level aus. Die Klausel START WITH gibt an, von wo aus
das Durchlaufen der Struktur beginnen soll.
Im folgenden Beispiel, wird der Root-Knoten als Startpunkt genommen, weil da eben am meisten zu sehen ist:
SELECT LEVEL, id, parent_id, last_name, SYS_CONNECT_BY_PATH(last_name,'/') as the_path FROM profile START WITH id = 1 CONNECT BY PRIOR id = parent_id; LEVEL ID PARENT_ID LAST_NAME THE_PATH ------- ------- --------- ---------------- -------------------------- 1 1 White /White 2 9 1 Bauer /White/Bauer 2 13 1 Ritter /White/Ritter 3 61 13 Erhard /White/Ritter/Erhard 3 81 13 Schröder /White/Ritter/Schröder 2 41 1 Reseller /White/Reseller 2 62 1 Maler /White/Maler 2 63 1 switchownerstaff /White/switchownerstaff 2 64 1 Kiosk /White/Kiosk
Außer der Pseudospalte LEVEL kommt auch noch eine spezielle Funktion zum
Einsatz, die nur in hierarchischen Abfragen funktioniert:
SYS_CONNECT_BY_PATH protokolliert in Form von Breadcrumbs den Weg mit, der
vom Startpunkt bis zur aktuellen Node/Zeile durchlaufen worden ist.
PRIOR gehört übrigens nicht fix zur CONNECT BY Klausel, sondern ist ein
Operator, der im konditionalen Ausdruck nach dem connect by auf einen der
beiden Terme angewandt werden muss. Ein Vertauschen von PRIOR bewirkt im
folgenden Fall, dass die Baumstruktur in umgekehrter Richtung durchlaufen
wird. Damit dabei was sinnvolles zu sehen ist, kann als Startpunkt natürlich
nicht die Wurzel genutzt werden, sondern idealerweise sollte das ein Blatt
sein.
SELECT LEVEL, id, parent_id, last_name, SYS_CONNECT_BY_PATH(last_name,'/') as the_path FROM profile START WITH id = 61 CONNECT BY id = PRIOR parent_id; LEVEL ID PARENT_ID LAST_NAME THE_PATH ------- ------- --------- ---------------- ------------------------- 1 61 13 Erhard /Erhard 2 13 1 Ritter /Erhard/Ritter 3 1 White /Erhard/Ritter/White
Wie man an die Abfrage ran kommt, die mit der View hinterlegt ist, ist meistens
bekannt. user_view ist nun wirklich nicht schwer zu erraten und nach einem
desc user_view ist auch ziemlich klar, was in den 5 oder 6 Spalten zu erwarten
ist.
Weniger bekannt ist, dass auch die Information wie und welche Spalten in einer View verändert werden können, im Data-Dictionary steckt. Dass eine group by Klausel und die Verwendung von Zeilen oder Aggregats-Funktionen zu read-only Views führt ist auch ziemlich schnell einleuchtend. Aber es gibt eben auch die anderen Fälle, bei denen es sehr verwirrend ist, weshalb da jetzt eine Spalte modifizierbar ist, die andere aber nicht. Da hilft ein Blick auf die Tabelle user_updatable_columns weiter:
SELECT table_name, column_name, updatable AS upd, insertable AS ins, deletable AS del FROM user_updatable_columns WHERE table_name in (select view_name from user_views);
Seit 10g gibt es in Oracle die Möglichkeit literale Strings mit einem Mechanismus zu Quoten, der an Here-Documents oder an Perl's q// Operator angelehnt ist. Dieses vereinfachte Quoten, bei dem auch einfache Quotes innerhalb des literalen Strings möglich sind, ist aber nur in PL/SQL verfügbar.
sql := q'[SELECT column_name FROM user_tab_columns WHERE table_name='A_TAB']';
SELECT deptno, listagg(ename, ';') within group (order by ename) AS liste FROM emp GROUP BY deptno;
Nimmt die Daten aus mehreren Zeilen und wendet den + String Operator, also eine Stringverkettung, statt den numerischen + an. Der zweite Parameter gibt ein Trennzeichen an, dass beim Verketten noch eingebaut wird und über die zusätzliche Klausel within group lässt sich die Reihenfolge, in der die gefundenen Felder verkettet werden sollen, festlegen. Die numerische Summe ist ja kommutativ, von daher ist bei ihr die Reihenfolge der Summanden egal.
Die Namensähnlichkeit zu xmlagg() ist auffällig und wohl auch gewollt, weil es
auch bei der XML Variante um eine String-Verkettung geht.
CREATE TABLE person ( person_no NUMBER, person_data XMLTYPE ); INSERT INTO person (person_no, person_data) VALUES (1, XMLTYPE('<prsData><Name>Meier</Name></prsData>') ); INSERT INTO person (person_no, person_data) VALUES (2, XMLTYPE('<prs age="23"> <Name><vorname name="Peter"/><nachname name="Maier"/></Name></prs>') ); -- or CREATE TABLE person_xml of XMLTYPE; INSERT INTO person_xml SELECT person_data FROM person WHERE person_no = 1;
Aus Rudolf Jansens Oracle, Java, XML über die Speicherung von XML in der Datenbank:
Voraussetzung für die strukturierte Speicherung über objekt-relationale
Tabellen, ist die Existenz einer XML Schema-Definition, auf der dann alle
XML-Dokumente, die in der XMLType Spalte oder Tabelle abgelegt werden, beruhen.
Ohne Einschränkung der Struktur der in der XMLType Spalte einzutragenden
XML-Dokumente können dort alle validen XML-Dokumente abgelegt werden, mehr oder
weniger als reiner CLOB Stream. Konsequenterweise gibt es auch keine Bedingung,
dass jedes XML-Type Feld, die gleiche XML-Hierarchie enthalten muss. In der
ersten Zeile dieser Tabelle kann zum Beispiel ein XML-String mit einem
Root-Knoten und genau einem einzigen Child-Knoten im Root enthalten sein.
In der zweiten Zeile dagegen ein Root-Knoten, der diesmal aber einen vollkommen
anderen Tagnamen haben kann, und der 3 Kind-Elemente enthält, die jeweils eine
unterschiedliche Anzahl an Attributen haben und eines dieser Kind-Element hat
sogar noch weitere eigene Kind-Elemente.
Mit einer XSD-Schema Datei wäre das natürlich in einem bestimmten Maße auch möglich, wenn es viele Elemente gibt, die nicht verpflichtend sondern nur optional sind. Aber zumindest die Root-Elemente müssten dann in allen Zeilen gleich heißen.
Beim Umgang mit dem XMLTYPE ist es gut, sich klar zu machen, dass es sich
dabei nur um einen speziellen, von Oracle schon vorab angelegten, Object Type
handelt. Der intuitive Zugriff auf die Tabellen bringt zwar brauchbare
Ergebnisse:
SQL> desc person Name Null? Type -------------------------- -------- ------------ PERSON_NO NUMBER PERSON_DATA XMLTYPE SQL> desc person_xml Name Null? Type -------------------------- -------- ------------ TABLE of XMLTYPE SQL> select person_data from person where person_no = 1; PERSON_DATA -------------------------------------------------------------- <prsData><Name>Meier</Name></prsData> SQL> select * from person_xml; SYS_NC_ROWINFO$ ------------------------------------------------------------- <prsData><Name>Meier</Name></prsData> -- beside SYS_NC_ROWINFO$ there is a second hidden column SYS_NC_OID$ SQL> select SYS_NC_ROWINFO$, SYS_NC_OID$ FROM documents; SYS_NC_ROWINFO$(DOC_ID, NAME, AUTHOR, URL, PUBLICATION_YEAR) SYS_NC_OID$ ---------------------------------------------------------------------- -------------------------------- DOC_T(1, 'Lügen im Heiligen Land', 'Peter Scholl-Latour', NULL, 1998) 660EF40881F54AEAE050A8C04D011F58 DOC_T(2, 'Anmerkungen zu Hitler', 'Sebastian Haffner', NULL, 1978) 660EF40881F64AEAE050A8C04D011F58
SYS_NC_ROWINFO$ verhält sich auch polymorph: Wenn es von einer Objekt-Tabelle
basierend auf Objekten, die auch Attribute und nicht nur Methoden haben (sowie
der XMLTYPE), gibt es in der Ergebnisdarstellung diese Attribute mit aus.
Aber bei allem, was darüber hinaus geht, wird es schwierig:
SQL> select person_data.getClobVal() from person where person_no = 1; select person_data.getClobVal() from person where person_no = 1 * FEHLER in Zeile 1: ORA-00904: "PERSON_DATA"."GETCLOBVAL": invalid identifier SQL> select getRootElement(person_data) from person where person_no = 1; select getRootElement(person_data) from person where person_no = 1 * FEHLER in Zeile 1: ORA-00904: "GETROOTELEMENT": invalid identifier SQL> select extract(p,'//Name') from person_xml p; select extract(p,'//Name') from person_xml p * ERROR at line 1: ORA-00904: "P": invalid identifier
Wichtig ist, dass zuerst mit dem VALUE Operator aus dem Objekt- bzw. XML-Typ
eine Instanz erstellt wird:
SQL> SELECT VALUE(p) FROM person_xml p; VALUE(P) ------------------------------------------------------------- <prsData><Name>Meier</Name></prsData> SQL> SELECT VALUE(d) FROM documents d WHERE doc_id = 2; VALUE(D)(DOC_ID, NAME, AUTHOR, URL, PUBLICATION_YEAR) ---------------------------------------------------------------------- DOC_T(2, 'Anmerkungen zu Hitler', 'Sebastian Haffner', NULL, 1978) SQL> select extractValue(value(p),'//Name') from person_xml p; EXTRACTVALUE(VALUE(P),'//NAME') ------------------------------------------------------------- Meier SQL> select extractValue(value(p),'//Name/@age') from person_xml p; EXTRACTVALUE(VALUE(P),'//NAME/@AGE') ------------------------------------------------------------- SQL> select extractValue(value(p),'//Name/text()') from person_xml p; EXTRACTVALUE(VALUE(P),'//NAME/TEXT()') ------------------------------------------------------------- Meier SQL> select extract(value(p),'//Name') from person_xml p; EXTRACT(VALUE(P),'//NAME') ------------------------------------------------------------- <Name>Meier</Name> -- Was mit extract funktioniert, klappt aber nicht immer, z.B. mit getClobVal() -- nicht -- SQL> select getClobVal(value(p)) from person_xml p; select getClobVal(value(p)) from person_xml p * FEHLER in Zeile 1: ORA-00904: "GETCLOBVAL": invalid identifier -- Richtig ist, erst aus dem Objekt-Typ mit value() eine Instanz bauen und dann -- für diese Instanz klassisch objekt orientiert die jeweilige Methode aufzurufen -- SQL> select value(p).getClobVal() from person_xml p; VALUE(P).GETCLOBVAL() ------------------------------------------------------------- <prsData><Name>Meier</Name></prsData> SQL> select value(p).extract('//Name') from person_xml p; VALUE(P).EXTRACT('//NAME') ------------------------------------------------------------- <Name>Meier</Name> SQL> select value(p).getRootElement() from person_xml p; VALUE(P).GETROOTELEMENT() ------------------------------------------------------------- prsData -- Ohne alias p für die Objekt-Tabelle geht interessanterweise auch nichts: -- SQL> select value(person_xml).getRootElement() from person_xml; select value(person_xml).getRootElement() from person_xml * FEHLER in Zeile 1: ORA-00904: "PERSON_XML": invalid identifier
Der Alias ist wirklich elementar, denn ohne den kommt auch mit der Tabelle mit dem XMLType in der Spalte nichts zustande. Es ist also hilfreich, sich die Objektmethoden als so eine Art korrelierte Unterabfragen vorzustellen, bei denen muss ja auch ein Tabellen-Alias in die Unterabfrage "hinab" gereicht werden.
SQL> select p.person_data.getRootElement() from person p; P.PERSON_DATA.GETROOTELEMENT() -------------------------------------------------- prsData prs SQL> select p.person_data.existsNode('//Name') from person p; P.PERSON_DATA.EXISTSNODE('//NAME') ---------------------------------- 1 1 SQL> UPDATE person p SET p.person_data = updateXML(p.person_data,'//Name/text()','Paul Meier') WHERE person_no = 1; -- Aber: select person.person_data.existsNode('//Name') from person where person_no = 1 * FEHLER in Zeile 1: ORA-00904: "PERSON"."PERSON_DATA"."EXISTSNODE": invalid identifier
Bei umfangreicheren XML Daten wird es kaum reichen, den XMLTYPE Konstruktor
mit einem VARCHAR2 als Parameter aufzurufen, literal schon mal gar nicht und
eine Tabellenspalte vom Typ VARCHAR2 ist auch nur bedingt nutzbar, da sie auf
8000 Zeichen beschränkt ist. PL/SQL erlaubt zwar mehr Zeichen in einer
VARCHAR2 Variable, aber wenn man schon von reinem SQL auf eine prozedurale
Sprache umsteigt, dann sollte man es gleich richtig machen und von einem
binary blob file laden. Bei Oracle läuft das unter dem Namen BFILE.
Wie bei allen Dateisystem-Zugriffen von Oracle (genauer vom Oracle-Server) aus,
muss ein DIRECTORY Objekt mit den entsprechenden Berechtigungen angelegt
werden, damit das funktioniert.
CREATE DIRECTORY XML_DATA AS '/os/path'; DECLARE bfl BFILE; BEGIN /* attach the XML document myDoc.xml to BFILE locator */ bfl := BFILENAME('XML_DATA', 'myDoc.xml'); INSERT INTO person (person_no, person_data) VALUES (3, XMLTYPE(bfl, nls_charset_id('WE8MSWIN1252')) ); COMMIT; END;
Die Nutzung des Directory Objekts bedingt, ebenso wie bei External Tables, dass die zu importierende Datei auf dem Oracle Datenbank-Server liegt. Typischerweise bedeutet das, dass derjenige, der den Import vornehmen will, auch Shell-Zugriff auf das Betriebssystem und das Dateisystem haben muss. Im Prinzip reichen auch ftp und WebDAV aus, aber bei diesen ftp bzw. Webserver basierten Methoden, kann man sich durchaus auch im Berechtigungssumpf verirren.
Ein alternativer Ansatz ist das Laden mit dem SQL*Loader, damit können auch binäre Daten vom Client zum Server geschaufelt werden:
CREATE TABLE demo_images ( inode NUMBER(10), bindata BLOB, file_size NUMBER(10), file_date DATE, mime_type VARCHAR2(30) );
LOAD DATA CHARACTERSET 'WE8ISO8859P1' INFILE 'png-files.dat' APPEND INTO TABLE demo_images FIELDS TERMINATED BY X'09' ( image_file FILLER CHAR, bindata LOBFILE(image_file) RAW TERMINATED BY EOF, file_size INTEGER EXTERNAL, inode INTEGER EXTERNAL, file_date DATE "yyyy-mm-dd hh24:mi:ss", mime_type CONSTANT 'image/png' )
Die zu importierende Datendatei sieht dann wie folgt aus (und kann mit stat in der Shell oder etwas bequemer innerhalb von Perl generiert werden):
$ stat --printf='%n\t%s\t%i\t%y\n' *.png
Feinlayout-Anpassen.png 5804 654361 2008-03-25 02:10:04.000000000 +0100 Feinlayout-Drucker.png 3586 654403 2008-03-25 00:22:50.000000000 +0100 Feinlayout-FVREF.png 4135 654315 2008-03-25 02:05:04.000000000 +0100 Feinlayout-Index.png 3872 654405 2008-03-25 02:19:20.000000000 +0100 Feinlayout-Seiten.png 5665 654412 2008-03-25 02:04:46.000000000 +0100
SQL> SELECT inode, file_size, file_date, mime_type, length(bindata) FROM demo_images;
INODE FILE_SIZE FILE_DATE MIME_TYPE LENGTH(BINDATA)
------- --------- ------------------- ------------- ---------------
654361 5804 2008-03-25 02:10:04 image/png 5804
654403 3586 2008-03-25 00:22:50 image/png 3586
654315 4135 2008-03-25 02:05:04 image/png 4135
654405 3872 2008-03-25 02:19:20 image/png 3872
654412 5665 2008-03-25 02:04:46 image/png 5665
Die SYS_XMLGEN Funktion kann innerhalb einer SQL-Abfrage zur Generierung von
XML-Konstrukten eingesetzt werden. Sie operiert auf Zeilenebene, d.h. für
jede Zeile der Ergebnismenge wird die SYS_XMLGEN Funktion ausgeführt. Sie
unterscheidet sich in dieser Tatsache vom DBMS_XMLGEN Package, dessen
Funktionen immer auf das Ergebnis einer gesamten Abfrage zugreifen.
SELECT sys_xmlgen(person_no) FROM person; SYS_XMLGEN(PERSON_NO) --------------------------------- <?xml version="1.0"?> <PERSON_NO>1</PERSON_NO> <?xml version="1.0"?> <PERSON_NO>2</PERSON_NO> 2 rows selected. SELECT sys_xmlgen(count(person_no)) FROM person; SYS_XMLGEN(COUNT(person_no)) ---------------------------- <?xml version="1.0"?> <ROW>2</ROW> 1 row selected.
Oder wenn man den Tagnamen des Elements selber festlegen will, kann man noch
einen zweiten, optionalen Parameter bei sys_xmlgen mit angeben:
SELECT sys_xmlgen(person_no, xmlformat.createformat('personNumber')) FROM person;
Interessanter wird das, wenn eine XMLType Spalte an sys_xmlgen verfüttert wird:
SELECT sys_xmlgen(person_data, xmlformat.createformat('personInfo')) FROM person; SYS_XMLGEN(PERSON_DATA,XMLFORMAT.CREATEFORMAT('PERSONINFO')) -------------------------------------------------------------- <?xml version="1.0"?> <personInfo> <prsData> <Name>Meier</Name> </prsData> </personInfo> <?xml version="1.0"?> <personInfo> <prs age="23"> <Name> <vorname name="Peter"/> <nachname name="Maier"/> </Name> </prs> </personInfo>
Dabei wird nämlich das spezifizierte Tag-Element um den XML Content der Spalte gewickelt.
Parallel zu sys_xmlgen kann das mehr am Standard SQLX orientierte
XMLElement verwendet werden. Es erwartet 2 Parameter: Den Namen des Tags,
das für die den XML-Knoten verwendet werden soll (da man dabei oft Tag-Namen
haben will, die nicht nur aus Blockbuchstaben bestehen, wird dieser 1.Parameter
sehr häufig in Anführungzeichen angebeben. Analog zu Spalten- oder
Tabellennamen werden hier double quotes verwendet, single quotes als String
Delimiter wären da vollkommen fehl am Platz) und als 2.Parameter einen
Spaltennamen einer Tabelle. Im Unterschied zu sys_xmlgen wird aber kein XML
Prolog mit der der XML Version ausgeben.
Die Zeilenfunktionen können dabei nicht nur Felder aus rein relationalen Tabellen in XML umwandeln, sondern sie akzeptieren auch Objekt-Spalten — typischerweise XMLType — als Parameter.
SELECT XMLElement(person_id,person_no) FROM person; XMLELEMENT(PERSON_ID,PERSON_NO) --------------------------------- <PERSON_ID>1</PERSON_ID> <PERSON_ID>2</PERSON_ID> 2 rows selected. SELECT XMLElement("PersonId",person_no) FROM person; SELECT XMLElement( "PersonData", XMLAttributes(person_no AS "id"), XMLElement("data",person_data) ) AS xml_string FROM person; XML_STRING ------------------------------------------- <PersonData id="1"><data><prsData> <Name>Meier</Name> </prsData> </data></PersonData> <PersonData id="2"><data><prs age="23"> <Name> <vorname name="Peter"/> <nachname name="Maier"/> </Name> </prs> </data></PersonData>
Das Result-Set dieser Operationen hat aber immer so viele Zeilen, wie die abgefragte Tabelle oder View. Will man da einen gemeinsamen String daraus machen, hilft das, was bei den hinlänglich bekannten rein relationalen Tabellen auch genutzt wird: Aggregation.
Im folgenden wird das exemplarisch anhand der emp Tabelle aus dem Scott Schema gezeigt (das Beispiel ist übrigens aus Jürgen Siebens sehr lesenswertem Buch "Oracle SQL" entnommen).
SELECT XMLElement("Mitarbeiterliste", XMLAttributes(deptno AS "abteilung"), xmlagg( XMLElement("Mitarbeiter", XMLAttributes(empno AS "id", hiredate AS "EinstellTag"), XMLForest( initcap(ename) AS "Name", initcap(job) AS "Beruf", trim(to_char(sal,'999G990D00l')) AS "Gehalt" ) ) ) ) AS xml_string FROM emp GROUP BY deptno;
xmlagg aggregiert dabei die einzelnen Zeilen — bzw. das was die innere Zeilenfunktion XMLElement aus der Zeile (mit den Feldern empno, hiredate, ename, job und sal) macht — indem es die als Parameter jeweils übergebenen Elemente per String-Verkettung aneinander hängt.