Oracle: REF CURSOR, out-param explizit deklarieren
Hallo Christian,
das Zauberwort bei Oracle heisst REF CURSOR. Während bei T-SQL mit einem SELECT in einer procedure implizit eine Cursor-Variable angelegt wird, muss dies bei Oracle explizit passieren, hier ein Beispiel (nur um den Sachverhalt zu verdeutlichen, kann sein, dass der Code nicht kompiliert…): Die Prozedur bekommt eine id übergeben und hat zwei out-Parameter: Einmal einen int-Wert, den ich der Einfachheit halber mit einer Konstante belege und dann einen Tupel-Cursor (als Ergebnis eines SELECTs).
So sieht der Code mit T-SQL / MS-SQL-Server aus:
create procedure sqlserver\_style (@id int, @value int output)
as
begin
set @value = 4711
select \* from demo\_table where id = @id
end
go
Jetzt mit PL/SQL (Oracle)
create or replace package oracle\_style
as
type gencurtype is ref cursor;
procedure oracle\_style (v\_id in number, v\_value out number, v\_refcursor out oracle\_style.gencurtype);
end;
/
create or replace package body oracle\_style
as
procedure oracle\_style (v\_id in number, v\_value out number, v\_refcursor out oracle\_style.gencurtype)
is
begin
v\_value := 4711;
open v\_refcursor for select \* from demo\_table where id = v\_id;
end;
end;
/
Wie schon gesagt, soll Dir nur als Hint dienen, wie Du das Thema angehen kannst, erspart möglicherweise nicht Doku-Studium 
Noch ein paar Hinweise zur Portierung von T-SQL nach PL/SQL:
-
bei T-SQL werden Parameter über @ referenziert und können somit z.B. von Attributen unterschieden werden. Dies gibt’s bei Oracle nicht, deshalb ist eine Namenskonvention (wie bei meinem Beispiel mit dem Präfix v_ für variable) hilfreich.
-
Du wirst die Prozedur wohl über JDBC, … oder was auch immer aufrufen. Die genaue Syntax und die Parameterbindung hängt typischerweise von Deinem Dataprovider ab, für manche ist die Reihenfolge wesentlich, andere führen die Bindung über den Parameternamen durch… in der Doku nachschauen, wie sich der Dataprovider bzgl. SQL-Server / Oracle verhält.
-
Du bekommt einen geöffneten Tupel-Cursor zurück, dieser muss vom aufrufenden Code (explizit oder implizit) geschlossen werden, sonst verbrätst Du Ressourcen - typischer Effekt: in der Testumgebung funktioniert alles, in der Produktion fällst Du nach 5 Min. über den Oracle-Fehler „too many open cursors“
-
Noch ein interessanter Hinweis: Bei SQL-Server müssen zuerst ALLE zurückgegebenen Tupel-Cursor VOLLSTÄNDIG abgearbeitet werden (via fetch), erst dann stehen die restlichen Parameter zur Verfügung, hier im Beispiel out-Param value. Oracle hat diese Restriktion nicht, der out-Param v_value steht sofort zur Verfügung, egal ob und wieviele Tupel von v_refcursor abgeholt wurden.
Hoffe, dass Dir diese Infos weiterhelfen, Grüße,
Bernhard
