UDTF (oder Stored Procedure?) in reinem SQL erstellen

[dschroeder]

Guten Tag,
ich habe schon oft User Defined Table Functions erstellt oder auch SQL-Procedures geschrieben. Aber eigentlich immer nur, um damit RPG-Programme zu kapseln. Jetzt möchte ich das ganze mal ohne RPG machen. Vielleicht kann mir da jemand einen Tipp geben.

Mein Problem ist, dass ich jetzt eine View habe (sie heißt rapkoste), die mir ein paar Felder aus einer Kostenstellenanwendung liefert.

Diese View würde ich gerne (weil noch weitere Selektionen notwendig sind) mittels einer UDTF zur Verfügung stellen. Es soll alsio eine UDTF geben, die mehrere Parameter (auch optionale Parameter) empfängt und die passenden selektierten Daten der View zurückgibt.

Ich denke, ich weiß, wie man UDTFs mit optionalen Parametern baut. Aber wie greife ich innerhalb einer UDTF auf die View zu und wie gebe ich die Daten zurück? Bisher habe ich das immer in RPG gemacht. Jetzt soll die UDTF ganz ohne RPG (also nur mit SQL-Befehlen) gebaut werden. Ich habe das schon mal irgendwo gelesen, finde es im Moment aber nicht. Hat da vielleicht jemand ein Beispiel oder einen Tipp?

Alternativ dazu habe ich gelesen, dass eine Stored Procedure ebenfalls ein Resultset zurückgeben kann. Das habe ich auch noch nie gebaut. Falls das geht: Wie geht das und was ist die bessere Lösung: Eine UDTF oder eine Stored Procedure?

Vielen Dank im Voraus.

Dieter

[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.