<< Click to Display Table of Contents >> Navigation: SQL-Server Objekte > Benutzerdefinierte Funktionen |
Benutzerdefinierte Funktionen werden mit der CREATE FUNCTION-Anweisung erstellt und jeder benutzerdefinierte Funktionsname muss eindeutig sein. Des Weiteren gelten die Regeln des MS SQL-Servers zur Definition, Änderung und Nutzung von benutzerdefinierten Funktionen.
Eine benutzerdefinierte Funktion verwendet null oder mehr Eingabeparameter und gibt einen Wert oder eine Tabelle zurück. Eine Funktion kann maximal 1024 Eingabeparameter haben.
Für die Namen der Funktionen müssen folgende Namenskonventionen eingehalten werden:
a)Präfix "fnc_user"
b)Die nachfolgende Namenseingabe darf nicht nur aus Nummern bestehen z.B. nicht fnc_user_2, fnc_user_56, …
c)Zulässige Zeichen sind : a-z, A-Z, 0-9, _
d)Der gesamte Name inkl. Präfix muss mindestens 9 Zeichen aber höchstens 63 Zeichen lang sein.
Hinweis: rimacon omniSuite kann nur die selbst erstellten benutzerdefinierten Funktionen verwalten. Falls solch eine benutzerdefinierte Funktionen woanders (z.B. über SQL Server Management Studio) geändert wird, wird zwar die neue Definition gelten, aber beim Öffnen in der Management Console erscheint die alte Definition und nach dem Speichern wird die von SQL Server Management Studio erstellte Funktion überschrieben.
Benutzerdefinierte Funktionen, die außerhalb von rimacon omniSuite erstellt wurden, sind für rimacon omniSuite nicht sichtbar.
Für benutzerdefinierte Funktionen bestehen Einschränkungen für Parametertypen und für Ergebnistypen. Ein Ergebnistyp kann auch eine Tabelle sein. Funktionen mit Tabellenrückgabe können ähnlich wie Tabellen genutzt werden -> „select * from dbo.fnc_user_xxx()"
Beispiel:
create function dbo.fnc_user_RechnernameToTyp(@name varchar(50)) returns int as begin declare @ret int set @ret=1 if @name like 's%' and len(@name)=5 set @ret=2 if @name like 'nb%' set @ret=3
return @ret end
|
Benutzerdefinierte Funktionen können genutzt werden
1)bei der Ermittlung der Ausgabewerte von dynamischen Spalten
2)in SQL-Sichten
3)bei dynamischen n:m-Beziehungen sowie
4)in allen SQL-Scripten
Benutzerdefinierte Funktionen erstellen/bearbeiten
Registerkarte „Allgemein“
Benutzen Sie diese Registerkarte, um die benutzerdefinierten Funktionen zu erstellen und zu bearbeiten.
Name
Gibt den Namen der benutzerdefinierten Funktion an.
Parameter
▪Zeigt den Rückgabewert und den Typ (Zeichenfolge, numerisch, Tabellenname, etc.) an.
▪Zeigt die Parameter und die Typen der Parameter entsprechend der vorgegebenen Reihenfolge an.
nach unten bzw. nach oben
Mit diesen Optionen kann die Reihenfolge der Parameter geändert werden.
hinzufügen und bearbeiten
Rückgabewert oder Parameter hinzufügen oder ändern. Wird eine Tabelle zurückgegeben, müssen die Tabellenparameter eingegeben werden.
löschen
Angegebenen Rückgabewert oder Parameter löschen.
Assistent
Über den Assistenten werden SQL-Anweisungen generiert, um z.B. bei rekursiven Beziehungen Werte von niedrigeren Stufen zur nächsthöheren Stufe zu aggregieren.
Beispiel:
Auf jeder Standortebene soll ermittelt werden, wie viele Rechner sich an jeder Ebene befinden bzw. zu jeder Ebene gehören -> … Anzahl Rechner in Hollenstedt -> Anzahl Rechner in Niedersachsen -> Anzahl Rechner in Deutschland.
Nach Auswahl der entsprechenden Tabelle und der „Parent-Spalte“ der Spalte werden z.B. für den Standort folgende SQL-Anweisungen generiert:
declare @mdtid int declare @matrix table(parentid int,childid int) declare @new table(parentid int,childid int)
set @mdtid=24
insert @matrix select STO_ID, STO_ID from STO where STO_MDT_ID=@mdtid
while 1 = 1 begin delete from @new
insert @new select ret.parentid, STO_ID from @matrix ret inner join STO on STO_MDT_ID=@mdtid and STO_STO_ID=ret.childid and STO_STO_ID<>0
delete @new from @new new inner join @matrix ret on ret.parentid=new.parentid and ret.childid=new.childid
if not exists (select parentid from @new) break -- nichts neues ==> end of loop insert @matrix select parentid,childid from @new -- neue append to @matrix end
insert @return select parentid, childid from @matrix return
|
SQL-Anweisungen müssen nun vor der return-Anweisung definiert werden, um die Anzahl der Rechner auf jeder Ebene zu ermitteln:
insert @return select distinct parentid, HWM_ID from @matrix inner join HWM on HWM.VCOL_Standort=childid
|
SQL
Definition des SQL-Scripts für die benutzerdefinierte Funktion, wobei die Anweisungen für das Anlegen der benutzerdefinierten Funktion „Create function dbo.() und returns int as begin .. end“ generiert werden.
Registerkarte „Beschreibung“
Beschreibung
Dokumentation der benutzerdefinierten Funktion
Registerkarte „SQL-Script“
Zeigt alle von Ihnen erstellten und alle generierten SQL-Anweisungen für die benutzerdefinierte Funktion an.
Registerkarte „Test“
Beschreibung
Testet die Korrektheit der benutzerdefinierten Funktion und zeigt das entsprechende Ergebnis an.