Oracle: Was ist "Insert into Returning into" ?

[Phoenix]

Hi,

also ich halte mich ja eigentlich für relativ fit was SQL angeht, aber mir ist gerade ein Statement über den Weg gelaufen, das ich nicht lesen kann / verstehe:

SQL-Code:

INSERT INTO tabelle
  (feld1, feld2, blobfeld)
VALUES
  (:param1, :param2, EMPTY_BLOB())
RETURNING
  blobfeld
INTO
  :blobparam

Der blobparam ist als ParamType ptInput deklariert, und ihm wird ein Filestream übergeben.
Ich kann den Code drumrum auch benutzen, aber ich würde ihn halt gerne auch verstehen ;-)

Also: Was macht dieses Schlüsselwort Returning und wieso kann ich - wenn es wirklich ein Rückgabewert des Statements ist, in diesen Rückgabewert mit INTO dann auch noch was schreiben?

[DeddyH]

Hallo, Google mal nach dem Stichwort "LOB_LOCATOR". In Oracle ist es so, dass ein Blobfeld nicht direkt den BLOB enthält, sondern eine Art Zeiger darauf, den sog. LOB_LOCATOR. Daher ist es notwendig (zumindest bei BLOBs > 4 Kb), diese beim Einfügen zunächst mit EMPTY_BLOB zu initialisieren und anschließend über den angelegten LOB_LOCATOR (der im Returning zurückgegeben wird), die Daten einzufügen. Falls ich diesen Sachverhalt nicht korrekt wiedergegeben habe, bitte ich die Experten um Korrektur.

[Elvis]

Zitat:

Zitat von DeddyH

Hallo, Google mal nach dem Stichwort "LOB_LOCATOR". In Oracle ist es so, dass ein Blobfeld nicht direkt den BLOB enthält, sondern eine Art Zeiger darauf, den sog. LOB_LOCATOR. Daher ist es notwendig (zumindest bei BLOBs > 4 Kb), diese beim Einfügen zunächst mit EMPTY_BLOB zu initialisieren und anschließend über den angelegten LOB_LOCATOR (der im Returning zurückgegeben wird), die Daten einzufügen. Falls ich diesen Sachverhalt nicht korrekt wiedergegeben habe, bitte ich die Experten um Korrektur.

Ist nicht ganz richtig. Ein Loblocator kann auch implizit erzeugt werden indem man einfach einer Variablen einen Lob zuweist.
Im Falle eines CLob könnte es sogar ein großer String sein...
Es ist dann die Zugriffsbibliothek, die entscheidet wie es am effizientesten umzusetzen ist. Zum Beispiel indem sie einen Stream nimmt und dort direkt den Inhalt des Lobs reinschiebt.

Zitat:

Zitat von Phoenix

Also: Was macht dieses Schlüsselwort Returning und wieso kann ich - wenn es wirklich ein Rückgabewert des Statements ist, in diesen Rückgabewert mit INTO dann auch noch was schreiben?

Dann hast du dich aber mit Oracle noch nicht genau genug auseinandergesetzt. ;)
Returning gibt dir die Werte der Tabelle nachdem alle Trigger gelaufen sind. So kriegst du zum Beispiel bei einem Insert die ID, die ein Trigger erzeugt hat, oder ein Änderungsdatum nach einem Insert oder Update.
Was hier passiert ist etwas Gaga, da es praktisch ein Ausgabewert ist, und dein DataProvider wird dann anhand des zurückgegeben Loblocators die tatsächlichen Daten anfordern und in den Stream schieben.
Oracle beschwert sich nicht, wenn du in einem SQL Statement Werte in einen In-Parameter schreibst.

Das interessante hier ist, dass ein leerer Lob in die Tabelle geschoben wird, du den "Zeiger" darauf aber zurückbekommst. Nun kannst du dort Daten reinschieben, ohne ein weiteres Update auszuführen.
Das gehört IMHO zu den coolen Dingen an Oracle. :)

[Phoenix]

Zitat:

Zitat von Elvis

Zitat:

Zitat von Phoenix

Also: Was macht dieses Schlüsselwort Returning und wieso kann ich - wenn es wirklich ein Rückgabewert des Statements ist, in diesen Rückgabewert mit INTO dann auch noch was schreiben?

Dann hast du dich aber mit Oracle noch nicht genau genug auseinandergesetzt. ;)

Wie gesagt: Ich bin relativ fit in SQL, und ich kenne die wichtigsten Unterschiede in der SQL Syntax zwischen den meisten DBMS. Ich bekomme auch Trigger, Funktionen und SP's in PL/SQL und T-SQL hin und bekomme damit so ziemlich alles zum laufen, was ich zum Laufen bringen musste. Darüber hinaus habe ich mich aber nie explizit mit den Spezialitäten eines spezifischen DBMS beschäftigt. Warum auch? Wenn ich so spezialdinger benutze bin ich nicht mehr flexibel genug um später kurz die DB auszutauschen, und das könnte hier sogar irgendwann nötig werden.

[mkinzler]

RETURNING wird auch von anderen DBMS unterstützt, wenn auch in einer etwas anderen Form.
Imho ist ein Außerachtlassen von Features, weil diese von anderen DBMS nicht unterstützt werden, weil man vielleicht später auf ein anderes System wechseln will, auch nicht unbedingt optimal. Dann schon Bridge-Pattern, welche dir diese Unterschiede kappseln.

[Elvis]

Zitat:

Zitat von Phoenix

Warum auch? Wenn ich so spezialdinger benutze bin ich nicht mehr flexibel genug um später kurz die DB auszutauschen, und das könnte hier sogar irgendwann nötig werden.

Dieses "Spezialding" hat seine Entsprechung in jedem besseren DBMS.
In SQL2005 heißt es Output[1] und gibt leider keine Parameterwerte sondern eine Ergebnismenge zurück.
In Firebird2 heißt es ebenfalls Returning[1] und funktioniert wie im SQL server, sieht aber wie in Oracle aus.
Es ist die Aufgabe jedes noch so kleinen ORMs das wegzuabstrahieren. ;)
Denn ohne Returning/Output würde man ja die ID des neuen Datensatzes nicht kennen.
Oder man müsste in MSSQL auf das abartig lahme @@IDENTITY zurückgreifen.

[1]

SQL-Code:

INSERT INTO SomeTable
OUTPUT Inserted.Id
(SomeField)
VALUES
(@SomeField)

[2]

SQL-Code:

INSERT INTO SomeTable
(SomeField)
VALUES
(:SomeField)
RETURNING Id

[Phoenix]

Zitat:

Zitat von Elvis

Denn ohne Returning/Output würde man ja die ID des neuen Datensatzes nicht kennen.

Bisher habe ich für sowas meist Funktionen gebaut, die die Guid gleich zurückgegeben haben...
Das System greift nur hier in dem gegebenen ERD ned mehr ;-)