UDTF (oder Stored Procedure?) in reinem SQL erstellen

[B.Hauser]

1. In einer Stored Procedure oder User Defined Table Function wird auf eine View wie auf eine Tabelle zugegriffen.
2. Bei einer UDTF werden die Ergebnisse normalerweise durch ein SELECT-Statement in Verbindung mit dem RETURN ausgegeben.
3. Sofern eine Einzelsatz-Verarbeitung innerhalb der UDTF notwendig ist, weil zunächst Aktionen auf Satz-Ebene erforderlich sind, kann ein ganz normaler Cursor definiert und verarbeitet werden. Die einzelnen Zeilen werden mit dem SQL-Befehl PIPE ausgegeben. Beim Return darf in diesem Fall KEIN SELECT-Statement angegeben werden.
4. Bei einer Stored Procedure wird ein Result Set dardurch ausgegeben, dass ein Cursor definiert und innerhalb der Stored Procedure geöffnet, jedoch nicht geschlossen wird. Das Schließen des Cursors muss in dem rufenden Programm (RPG oder andere Programmiersprache oder auch SQL) erfolgen.

Die bessere Lösung ist m.E. eine UDTF, da beim Aufruf noch Feld-Auswahlen, zusätzliche WHERE-Bedingungen und Order-By Anweisungen angegeben werden können.
Bei der Verarbeitung eines Result-Sets müssen alle Spalten und Sätze in der Reihenfolge wie ausgegeben verarbeitet werden. Ist eine andere Reihenfolge erforderlich oder sind nicht alle Zeilen relevant ist zusätzlicher Programmieraufwand erforderlich.

[dschroeder]

Hallo Birgitta,

herzlichen Dank für deine Ausführungen. Dann werden ich mich für eine UDTF entscheiden und mal schauen, ob ich die Syntax hinkriege.

Dieter

[Fuerchau]

Die einfache Variante:

create or replace function myfunction
(p1 char(1)
,p2 date)

returns table (
f1 char(1)
,f2 date
,f3 nvarchar(30)
:
:
)

language SQL
reads sql data
deterministic

return

select f1, f2, f3 ....
from mytable
where k1=p1 and k2=p2

Der spätere Aufruf erfolgt dann mit

select * from table (myfunction('A', current date))

[dschroeder]

Die einfache Variante:

create or replace function myfunction
(p1 char(1)
,p2 date)

returns table (
f1 char(1)
,f2 date
,f3 nvarchar(30)
:
:
)

language SQL
reads sql data
deterministic

return

select f1, f2, f3 ....
from mytable
where k1=p1 and k2=p2

Der spätere Aufruf erfolgt dann mit

select * from table (myfunction('A', current date))

Vielen Dank! Das heißt, beim return gibt man einfach das select - Statement an? Ok, werde ich mal ausprobieren.

[Fuerchau]

Der einzige Nachteil ist halt, dass sich der Create nicht die Spalten aus dem Select selber ermitteln kann.
Allerdings kann man ja auch vor dem return noch jede Menge anderes Zeug machen.

Ich habe da z.B. auch ein CALL auf ein RPG-Programm gemacht (man braucht ja keine Procedur dafür). Dies erstellt eine Tabelle in QTEMP (wegen der Parallelität) und der Return macht dann nur noch ein "select * from qtemp/mytable".
Der Nachteil ist, dass hier jede Dynamik (variable Anzahl Spalten) nicht möglich ist.

[dschroeder]

Vielen Dank, Baldur.