Start

Unternehmen

Buch-Katalog

Seminare

Leserservice

Comelio-Blog

Datenbanken SQL MS SQL Server Programmierbarkeit Funktionen Verwaltungsarbeiten bei Module Programmierung mit T-SQL XML in T-SQL zerlegen Variablen mit XML-Inhalt XML mit XPath zerlegen Datentypmethoden für XML-Bearb XPath Definition von Webservices XSLT Technologien von Webservices Webservices im MS SQL Server SOAP-Webservices Nachrichtenstruktur von Webser Batch-Einsatz bei Webservices Oracle PHP UML C#.NET XML Schema XSLT

Übersicht

Alle Seminare Info-Broschüre Anmeldeformular Hotels & Anfahrt AGB

Comelio GmbH
Rellinghauser Straße 10
D-45128 Essen
Deutschland
Fon: 0201-437517-0
Fax: 0201-437517-10
info@comelio.com

Comelio GmbH
Goethestraße 34
D-13086 Berlin
Deutschland
Fon: 030-921019-85
Fax: 030-921019-89
info@comelio.com

Comelio GmbH (Ecos)
Glockengießerwall 17
D-20095 Hamburg
Deutschland
Fon: 040-4689908-91
Fax: 040-4689908-95
info@comelio.com

Comelio GmbH (Ecos)
Mainzer Landstraße 27-31
D-60329 Frankfurt
Deutschland
Fon: 069-2475030-35
Fax: 069-2475030-39
info@comelio.com

Comelio GmbH (Ecos)
Stiglmaierplatz/Dachauer Str. 37
D-80335 München
Deutschland
Fon: 089-2000154-90
Fax: 089-2000154-94
info@comelio.com

Comelio GmbH (Ecos)
Liebknechtstr. 33
D-70565 Stuttgart
Deutschland
Fon: 0711-252534-20
Fax: 0711-252534-24
info@comelio.com

Comelio-Blog > MS SQL Server > Funktionen

Funktionen

Neben den Prozeduren gibt es auch noch die Möglichkeit, benutzerdefinierte Funktionen zu erstellen. Beide Modularten haben – wie in allen Datenbanken, die überhaupt solche Strukturen anbieten – große Gemeinsamkeiten. Daher werden viele Aspekte bereits bekannt sein, weswegen die Darstellung von Funktionen ein wenig kürzer ausfallen kann.

Kontakt Anrede* Herr Frau Vorname* Nachname* Firma E-Mail* Tel-Nr. Bereich* Comelio-Blog > MS SQL Server > Funktionen Freitext

Programmierbarkeit: Funktionen

Eine Funktion kann als Ausdruck, auf der rechten Seite einer Zuweisung (was ebenfalls ein Ausdruck ist) oder auch in einer FROM-Klausel erscheinen. Die ersten beiden Möglichkeiten sollten angesichts der verschiedenen, schon in der Datenbank vorhandenen Systemfunktionen sowie natürlich aufgrund der gängigen SQL-Funktionen schon bekannt sein. Neu ist möglicherweise die Überlegung, dass solche Funktionen auch vom Benutzer erstellt werden und direkt in SQL-Anweisungen wie bspw. SELECT aufgerufen werden können. Neu dürfte auch sein, dass eine Funktion innerhalb einer FROM-Klausel anstelle einer Tabelle auftreten kann. In diesem Sinne ähnelt sie einer Prozedur, die eine relationale Ergebnismenge zurückgibt, welche wiederum mit zusätzlichen Filtern, Sortierungen und Gruppierungen sowie auch Spaltenauswahlen genutzt wird. Eine Prozedur hingegen könnte an dieser Stelle niemals stehen, sondern bildet bereits die gesamte Abfrage an.

Ein wesentlicher Unterschied von Funktionen und Prozeduren besteht darin, dass eine Funktion den Status einer Datenbank nicht ändern kann. Die folgende Liste enthält Anweisungen, die innerhalb einer benutzerdefinierten Funktion überhaupt zulässig sind:

• Variablendeklarationen und zugehörige Zuweisungen, sowie Kontroll-strukturen und gängige T-SQL-Programmstrukturen außer Ausnahme-behandlung. Die Erstellung von Cursorn ist dagegen erlaubt.
• SELECT-Anweisungen, welche ihre Werte lokalen Variablen zuweisen, sowie die Verarbeitung von Cursorn.
• Nutzung von lokalen Cursorn, die also innerhalb der Funktion erstellt und wieder gelöscht werden. Die Rückgabe von Cursorn an den Klienten sowie die Nutzung von Prozedur-Cursorn ist nicht zulässig.
• INSERT-, UPDATE- und DELETE-Anweisungen, die sich auf lokale table-Variablen auswirken. Eine Änderung von tatsächlichen DB-Tabellen ist in Funktionen nicht gestattet.
• EXECUTE-Anweisungen, die gespeicherte Prozeduren aufrufen, sofern sie die oben angegebenen Regeln nicht verletzen, oder erweiterte gespeicherte Prozeduren.

Funktionen können daher verschiedene Eigenschaften haben, die ihnen automatisch zugewiesen werden und die sich aus der Gesamtheit der in ihnen enthaltenen T-SQL-Anweisungen ergeben. Folgende Anweisungen sind in 2005 verfügbar:

• IsDeterministic meint, dass eine Funktion bei gleichen Eingabedaten und gleichem Datenbankstatus das gleiche Ergebnis liefert. Solche Funktionen, die aktuelle Informationen wie Uhrzeiten abrufen, sind bspw. nicht deterministisch.
• IsPrecise prüft die Nutzung von Gleitkommatransaktionen, die in unpräzisen Funktionen auftreten.
• IsSystemVerified gibt an, ob die Präzisions- und Determinismus-eigenschaften vom MS SQL Server geprüft werden können.
• SystemDataAccess gibt an, ob die Funktion in der lokalen MS SQL Server-Instanz auf Systemdaten wie den Systemkatalog oder virtuelle Systemtabellen zugreift.
• UserDataAccess prüft, ob eine Funktion auf Benutzerdaten in der lokalen Instanz von SQL Server zugreift, wobei benutzerdefinierte und temporäre Tabellen, aber keine Tabellenvariablen eingeschlossen sind.

Prozeduren zum Einsatz kam, aufgebaut. Sie stelle die verschiedenen Arten von Funktionen sowie ihre typische Verwendungsweise vor.

Auf der rechten Seite befindet sich wieder ein Datenbanksymbol, welches zeigen soll, dass sich diese Funktionen nicht in der Software, sondern vielmehr in der Datenbank selbst befinden. Oben sieht man zwei Kästen, von denen der eine bspw. in .NET geschriebene, individuelle Software und der andere ein beliebiges T-SQL-Skript, wie es im Management Studio ausgeführt werden kann, symbolisiert. Beide können die verschiedenen Prozeduren in T-SQL-Anweisungen, die zur Datenbank geschickt werden, aufrufen.

Man unterscheidet im Wesentlichen zwei Arten von benutzerdefinierten Funktionen, die wie die Prozeduren zuvor als Rauten innerhalb der Abbildung zu sehen sind:

• Skalarfunktionen entsprechen bekannten SQL-Funktionen, welche auf Basis keines, eines oder mehrerer Parameter einen Rückgabewert zurückliefern, der überall dort, wo ein Ausdruck erwartet wird, benutzt werden kann. Diese Funktionen können ihre Werte aus Tabellen abrufen; sie können dagegen allerdings auch beliebige Berechnungen ohne Tabellenbezug ausführen.
• Tabellenwerfunktionen dagegen führen eine SELECT-Anweisung aus und liefern meist auf Basis einer oder mehrerer Tabellen ein relationales Ergebnis zurück. Da diese Funktionen innerhalb einer FROM-Klausel aufgerufen werden, können alle möglichen SELECT-Techniken auf diese Daten angewandt werden.
• .NET-Funktionen: Es ist möglich, in .NET geschriebene Funktionen aus DLLs im MS SQL Server zu speichern und genauso zu verwenden wie in T-SQL geschriebene. Dies wird allerdings nicht in diesem Text erläutert, weil natürlich .NET-Kenntnisse erforderlich sind.

An dieser Stelle folgte bei der Darstellung der Prozeduren die allgemeine Syntax für CREATE, ALTER und DROP. Wenn Funktionen vorgestellt werden, ist dies jedoch nicht sinnvoll, weil die zwei verschiedenen Arten von Funktionen eine unterschiedliche Erstellungs-/Änderungssyntax aufweisen. Sie folgt später in eigenen Abschnitten.

Skalare Funktionen

Eine skalare Funktion liefert einen Wert zurück, sodass sie überall dort, wo ein Ausdruck erwartet wird, aufgerufen werden kann. Man erstellt sie ebenfalls über den CREATE-Befehl und kann sie in einem Schema speichern. Parameter werden mit ihrem Namen und dem vorangestellten @-Zeichen angegeben, können auch einen Standardwert aufweisen oder natürlich NULL sein. Ein solcher Standardwert muss allerdings beim Aufruf in einer SQL-Anweisung immer auch mit default angegeben werden. Man kann nicht wie bei einer Prozedur diese Parameter auslassen. Die Besonderheit einer skalaren Funktion besteht darin, dass sie einen Rückgabewert besitzt und dass dessen Datentyp in der RETURNS-Klausel angegeben ist. Danach folgt nach einem optionalen AS innerhalb von BEGIN und END die Reihe an T-SQL-Anweisungen, welche die Funktion speichert und die mit einer RETURN-Anweisung enden, welche den Rückgabewert schließlich zurückliefern. Dies erinnert insgesamt sehr an eine Methode gängiger Programmiersprachen.

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] 
       parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

Ein Beispiel soll diese Technik zeigen. Zunächst prüft man mit Hilfe der OBJECT_ID-Funktion, ob die entsprechende Funktion überhaupt schon in der Datenbank vorhanden ist, um sie ggf. zu löschen. Dabei ist zu beachten, dass die verschiedenen Funktionsarten eigene Testwerte haben. Eine skalare Funktion prüft man bspw. mit der Zeichenkette FN.

Die Funktion soll nun endlich ein Problem in Angriff nehmen, das während des bisher noch keine Berücksichtigung fand: die Währungsrechnung. Innerhalb der Bestellungen, die von Kunden eingehen, gibt es eine Verknüpfung zu einer Währungstabelle namens CurrenyRate, welche historisierte Wechselkurse enthält, und die wiederum mit einer Tabelle Currency verbunden ist. Sie enthält die Namen von Währungen.

Die Funktion ufnGetBuyerCurrency erwartet die Nummer eines Verkaufs sowie einen Währungsbetrag in Dollar, der umgerechnet werden soll. Da in einem Datensatz der SalesOrderHeader-Tabelle mehrere Spalten vom Datentyp money enthalten sind, soll diese Funktion dynamisch zwar den zurzeit der Bestellung gültigen Wechselkurs ermitteln - doch die Summe, die umgerechnet werden soll, wird als Wert übergeben. So kann man diese Funktion für die Gesamtsumme genauso wie für die Steuerlast oder die Frachtkosten nutzen. Innerhalb der Funktion steht ein letztlich ganz gewöhnliches T-SQL-Skript, welches den für die übergebene Bestellnummer gültigen Wechselkurs ermittelt, die Berechnung durchführt und diese schließlich in der RETURNS-Klausel zurückliefert. Letztlich ist es nur die RETURNS-Klausel, welche dieses innere Skript von einem nicht gespeicherten unterscheidet. An seiner Stelle hätte man möglicherweise sonst eher die PRINT-Anweisung verwendet.

-- Auf Existenz prüfen und ggf. löschen
IF OBJECT_ID (N'dbo.ufnGetBuyerCurrency', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufnGetBuyerCurrency
GO
-- Erstellen
CREATE FUNCTION ufnGetBuyerCurrency(
 @salesOrderID int,
 @sum money
)
RETURNS varchar(15)
AS BEGIN
-- Lokale Variablen
DECLARE @averageRate float, @currency varchar(5)
-- SQL-Anweisungen
SELECT @averageRate = cr.AverageRate,
       @currency = cr.ToCurrencyCode
  FROM Sales.SalesOrderHeader AS sh
       INNER JOIN Sales.CurrencyRate AS cr
    ON sh.CurrencyRateID = cr.CurrencyRateID
WHERE sh.SalesOrderID = @salesOrderID
-- Rückgabe
RETURN RTRIM(LTRIM(STR(@sum * @averageRate) + ' ' + @currency))
END

Spektakulär ist nun insbesondere, dass die doch sehr schwierige Umrechnung aufgrund der Verknüpfungen völlig in der Funktion gekapselt ist und die ansonsten vermiedene Berücksichtigung der tatsächlichen Währungen überaus einfach in dieser Funktion durchgeführt wird. Dies alles gelingt in einem T-SQL-Programm genauso gut, lässt sich aber gerade auch in einer gewöhnlichen SELECT-Anweisung nutzen. Andere typische SQL-Anweisungen könnten hier ebenfalls mit dieser Funktion umgangen werden. So wäre es möglich, den einzutragenden oder zu aktualisierenden Wert mit Hilfe dieser Funktion zu erzeugen.

SELECT SalesOrderID,
       dbo.ufnGetBuyerCurrency(SalesOrderID,
                               SubTotal) AS SubTotal,
       dbo.ufnGetBuyerCurrency(SalesOrderID,
                               TaxAmt)   AS Tax,
       dbo.ufnGetBuyerCurrency(SalesOrderID,
                               Freight)  AS Freight,
       dbo.ufnGetBuyerCurrency(SalesOrderID,
                               TotalDue) AS Total
 FROM Sales.SalesOrderHeader
 WHERE SalesOrderID IN (43661, 43662)

Man erhält für die beiden angeforderten Bestellungen die jeweiligen Werte in kanadischen Dollar.

SalesOrderID SubTotal     Tax       Freight         Total
------------ ------------ --------- --------------- ----------
43661        57718 CAD     4617 CAD  1443 CAD        63778 CAD
43662        50789 CAD     4063 CAD  1270 CAD        56122 CAD
(2 Zeile(n) betroffen)

Tabellenwertfunktion

Die zweite Gruppe an Funktionsarten wird aus den beiden verschiedenen Arten von Tabellenwertfunktionen gebildet. Man kann sie als parametrisierte Sicht verstehen, da hier auf der einen Seite eine gespeicherte Abfrage unter einem eigenen Namen existiert, diese hingegen auf der anderen Seite über Parameterwerte gefiltert werden kann. Im Gegensatz zu einer Sicht oder einer gewöhnlichen Tabelle ist hier also für die Filterung zunächst keine WHERE-Klausel notwendig, weil die vom Besitzer der Funktion als wesentlich erachteten Filtermöglichkeiten schon vorgegeben wurden und besonders einfach in ihrer Verwendung sind. Davon ist unberührt, zusätzliche Filterungen in der WHERE-Klausel anzugeben.

Einfache Tabellenwertfunktion

Die erste Untergruppe der Tabellenwertfunktionen bezeichnet man als „einfach,“ weil hier der Charakter einer Sicht besonders deutlich zum Tragen kommt. Diese Funktion besitzt die Möglichkeit, Parameter anzulegen, die durch einen Datentyp beschrieben sind und einen Standardwert oder NULL enthalten können. Im Gegensatz zu den Skalarfunktionen kündigt man hier den Rückgabewert in der RETURNS-Klausel mit dem Datentyp table an. Bei einer einfachen Tabellenwertfunktion folgt hier nun kein BEGIN…END-Block, sondern lediglich nach einem optionalen AS die SELECT-Anweisung nach dem Schlüsselwort RETURN. Sie kann zwar die verschiedenen Parameter enthalten und auch eine stattliche Größe erreichen, doch alles, was sich nicht in einer einzelnen SELECT-Anweisung ausdrücken lässt, ist dann für diese einfache Form nicht mehr geeignet.

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] 
       parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Erweiterte Tabellenwertfunktion

Als erweiterte Tabellenwertfunktion bezeichnet man tatsächlich eine erweiterte Form der zuvor angegebenen. Sie besitzt ebenfalls die Möglichkeit, Parameter über die bekannte Methode anzugeben, präsentiert allerdings eine ganz andere Form der RETURNS-Anweisung. Sie dient auf der einen Seite dazu, die Rückgabevariable vom Typ table zu deklarieren, um sie später auffüllen zu können, und auf der anderen Seite auch die Struktur der Tabelle anzugeben. Innerhalb dieser Tabellendefinition folgt die übliche Auflistung an Spaltennamen und Datentypen, wie sie auch innerhalb einer table-Definition üblich ist, weil es sich ja auch genau um diesen Datentyp handelt. Diese erweiterte Form der Tabellenwertfunktionen erlaubt es dann, in ihrem BEGIN…END-Block, wie in einer Prozedur beliebigen T-SQL-Anweisungen zu verwenden. Die Tabellenvariable füllt man dann wie jede andere Variable vom Typ table über DML-Operationen auf, aktualisiert die Daten oder löscht sie. Diese Tabelle wir dann quasi zurückgeliefert – quasi deshalb, weil die RETURN-Klausel leer ist und die Rückgabevariable schon in der RETURNS-Klausel angekündigt wurde.

CREATE FUNCTION name( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

Die Tabellenvariable kann man über die folgende allgemeine Syntax beschreiben und gleichzeitig anlegen:

<table_type_definition>, ( 
   { <column_definition> <column_constraint> , 
     | <computed_column_definition> } ,
     [ <table_constraint> ] [ ,...n ], ) }

Optionen

Die Funktionen besitzen noch verschiedene zusätzliche Optionen, von denen nur zwei (ENCRYPTION und EXECUTE AS) wiederum mit denen von Prozeduren und von denen eine mit Sichten (SCHEMABINDING) übereinstimmen. Sie sollen hier vergleichend dargestellt und dann für die Funktionsoptionen mit Beispielen erläutert werden. Folgende Optionen sind für eine Funktion möglich:

<function_option>::= {
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ] }

Folgende Optionen sind für eine Prozedur möglich:

<procedure_option> ::= {
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ] }

Zur Erinnerung: Für Prozeduren legte RECOMPILE fest, dass kein Ausführungsplan gespeichert wird, weil davon auszugehen war, dass keine sinnvolle Annahmen über den Wert der Parameter getroffen werden konnten und daher die Berücksichtigung des ansonsten die Ausführung beschleunigenden Ausführungsplans die Ausführung tatsächlich nur behindern würde. Mit der Klausel EXECUTE AS konnte man den so genannten Sicherheitskontext festlegen, was später noch einmal im Zusammenhang erläutert wird. Mit ENCRYPTION konnte man festlegen, dass der Quelltext verschlüsselt wird und nur mit großen Berechtigungen wieder lesbar gemacht wird.

Für Funktionen gibt es neben diesen schon von Prozeduren bekannten Optionen noch die folgenden weiteren:

• SCHEMABINDING legt fest, dass Funktionen, die in anderen Objekten, welche gleichfalls schemagebunden sind, nicht gelöscht werden können.
• RETURN NULL ON NULL INPUT liefert bei Funktionsaufruf mit lauter NULL-Werten als Parameter automatisch den Wert NULL zurück, ohne die Funktion überhaupt auszuführen. Die Standardvariante CALLED ON NULL INPUT führt dagegen die Funktion aus.

APPLY-Operator

Es gibt noch einen weiteren neuen Operator, der zur T-SQL-Syntax der Version 2005 hinzugefügt wurde, und der innerhalb der FROM-Klausel genutzt werden kann. Dabei kann man diesen Operator sowohl mit Unterabfragen als auch mit Tabellenwertfunktionen nutzen. Die allgemeine Syntax lautet:

left_table_source { CROSS | OUTER } APPLY right_table_source

Beide Ausdrücke links und rechts vom APPLY-Operator stellen Tabellenausdrücke dar. Dabei können beide Ausdrücke neben Unterabfragen auch Tabellenwertfunktionen enthalten. Die rechte kann als Argument eine ganze Spalte aus dem Tabellenausdruck auf der linken Seite empfangen. Dies ist für die linke nicht möglich. Sofern keine solche Tabellenwertfunktion genutzt wird, kommt für den rechten Tabellenausdruck eine korrelierte Unterabfrage zum Einsatz kommen.

Die Funktionsweise des Operators lässt sich wie folgt beschreiben: Der rechte Ausdruck wertet den linken Ausdruck aus, um Ergebnisse zu ermitteln. Dies lässt sich entweder durch eine Korrelation erklären oder durch die Übergabe von Rückgabewerten. Da er insbesondere für die Nutzung mit Funktionen geschaffen wurde, wird er in diesem Kapitel beschrieben.

Das erste Beispiel zeigt allerdings zunächst seine Funktionsweise anhand von zwei Unterabfragen und damit auch diese Einsatzalternative. Auf der linken Seiten des Operators ruft man die Tabelle ProductSubcategory auf. Sie liefert die Eingabedaten für den rechten Ausdruck, der in Form einer Korrelationsunterabfrage auftritt. Dabei handelt es sich um die Daten aus der Product-Tabelle, welche zu den Kategorien aus dem linken Tabellenausdruck passen. Anstelle eines solchen Operators hätte man im Normalfall einfach beide Tabellen über einen INNER JOIN verbunden, um das gleiche Ergebnis zu erzielen. Der Operator CROSS sorgt dafür, dass nur die Datensätze, welche in beiden Tabellen Treffer finden, in die Ergebnismenge kommen.

SELECT sc.Name AS Category, p.Name, ProductNumber
  FROM Production.ProductSubcategory AS sc
       CROSS APPLY 
       (SELECT Name, ProductNumber, Color, Size
          FROM Production.Product
         WHERE ProductSubCategoryID = 
               sc.ProductSubCategoryID) AS p
 WHERE sc.Name LIKE '%Bike%