Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 1/2009.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Tipps und Tricks zum Einsatz von benutzerdefinierten Funktionen in einer SQL Server-Datenbank

Techniken

SQL Server 2005, T-SQL

Voraussetzungen

SQL Server, T-SQL

Beispieldateien

BenutzerdefinierteFunktionen.zip

Shortlink

648

Benutzerdefinierte Funktionen im MS SQL Server

Bernd Jungbluth, Horn

"Die Drei von der Tankstelle", "Drei Engel für Charlie" und "Die drei Fragezeichen" haben eines gemeinsam: Es sind immer Drei. Drei, von denen jeder seine besonderen Fähigkeiten hat, und Drei, die sich perfekt ergänzen. So ist es auch beim SQL Server. Neben den gespeicherten Prozeduren und Triggern bietet der SQL Server mit den benutzerdefinierten Funktionen eine dritte Komponente, mit der Programmlogik gespeichert und wiederverwendet werden kann. Und weil aller guten Dinge nun mal drei sind, gibt es die benutzerdefinierten Funktionen auch gleich in drei verschiedenen Typen.

Diese drei verschiedenen Typen heißen Inlinefunktionen, Tabellenfunktionen und Skalarfunktionen.

Skalarfunktionen sind weitestgehend mit VBA-Funktionen in Access vergleichbar. Anhand einer optionalen Parameterübergabe wird ein Wert ermittelt und zurückgegeben. Die Inline- und Tabellenfunktionen gehen dort noch einen Schritt weiter, denn sie liefern nicht nur einen Wert, sondern komplette Ergebnismengen.

Die Ähnlichkeit zu VBA-Funktionen liegt aber nicht nur in der Ermittlung der Daten, sondern auch in ihrer Anwendung. Gerade die Verwendung eigener geschriebener Funktionen in SELECT-Anweisungen, Access-Abfragen, Gültigkeitsregeln und Modulen ist einer der Vorteile von Access. Dieser Vorteil gilt auch für benutzerdefinierte Funktionen, denn diese sind in der SQL Server-Entwicklung universell einsetzbar. Sie können in SELECT-Anweisungen, in Sichten, in gespeicherten Prozeduren, in Triggern und in anderen benutzerdefinierten Funktionen wie auch in Standardwerten und Einschränkungen verwendet werden und stehen somit für die Realisierung verschiedenster Anforderungen zur Verfügung.

Einsatzmöglichkeiten

Der Grund für den Einsatz von benutzerdefinierten Funktionen ist derselbe wie bei gespeicherten Prozeduren: Die Kapselung von Programmlogik und somit die Kapselung von Geschäftsregeln und von Regeln zur Datenkonsistenz.

Zwar können Sie mit benutzerdefinierten Funktionen keine Daten manipulieren, aber im Vergleich zu gespeicherten Prozeduren sind benutzerdefinierte Funktionen flexibler einsetzbar. Im Gegensatz zu gespeicherten Prozeduren können diese nämlich auch in Sichten, SELECT-Anweisungen oder in anderen benutzerdefinierten Funktionen verwendet werden. Insofern ist der Einsatz benutzerdefinierter Funktionen in manchen Fällen sinnvoller als der Einsatz gespeicherter Prozeduren.

Für die beispielsweise oft benötigte Ermittlung der Produktbezeichnung anhand einer Produktnummer können Sie eine Skalarfunktion erstellen, die immer genau den einen Wert - die Produktbezeichnung - anhand der übergebenen Produktnummer liefert.

Eine andere Skalarfunktion könnte die in der Northwind-Datenbank immer wieder notwendige Summe einer Bestellposition anhand der Spalten Einzelpreis, Menge und Discount berechnen.

Die Werte solcher Skalarfunktionen können Sie als Spalte in einer SELECT-Anweisung, als Filterkriterium in einer WHERE-Bedingung oder in einer INSERT- oder UPDATE-Anweisung verwenden.

In SELECT-Anweisungen und bei Datenmanipulationen stehen auch die Tabellen- und Inlinefunktionen zur Verfügung. Allerdings liefern diese Typen nicht einzelne Werte, sondern Ergebnismengen und sind somit ein guter Ersatz für Sichten oder Tabellen.

Tabellen- und Inlinefunktionen bieten gerade Sichten gegenüber einen großen Vorteil: Sie unterstützen Parameter.

Eine Sicht liefert immer alle Daten der in der Sicht angegebenen SQL-Anweisung. Die Datenermittlung ist starr und kann nicht durch Parameter beeinflusst werden. In Tabellen- und Inlinefunktionen hingegen sind Parameter erlaubt.

Dadurch können die Daten bereits bei der Ermittlung anhand der übergebenen Parameterwerte gefiltert werden.

Es ist schon ein Unterschied, ob in einer Tabellenverknüpfung von Bestellkopf und Bestellpositionen alle Bestellpositionen verknüpft werden oder dabei nur die Bestellpositionen berücksichtigt werden, die dem Wert eines übergebenen Parameters entsprechen.

Sie sehen, die Einsatzmöglichkeiten sind so vielfältig wie die von VBA-Funktionen in Access. Die Verwendung benutzerdefinierter Funktionen ist für Sie insofern eigentlich nichts Neues. Bis auf die Entwicklung dieser Funktionen, denn die schreiben Sie natürlich nicht in VBA, sondern in T-SQL. Dabei sind Art und Umfang der Entwicklungsmöglichkeit abhängig vom jeweiligen Typ.

Inline- und Tabellenfunktionen unterscheiden sich in der Werterückgabe gegenüber den Skalarfunktionen und die Skalar- und Tabellenfunktionen unterscheiden sich anhand der Möglichkeiten zur Ermittlung des Werts beziehungsweise der Werte von den Inlinefunktionen.

Inlinefunktionen

Inlinefunktionen liefern eine Ergebnismenge und können insofern wie Tabellen und Sichten - und somit fast überall - verwendet werden. Aber nur fast, denn bei der Ermittlung von Standardwerten oder der Einhaltung von Gültigkeitsregeln wird dieser Funktionstyp nicht unterstützt.

Der große Vorteil einer Inlinefunktion liegt darin, dass bei der Ermittlung der Daten übergebene Parameterwerte als Filterkriterien verwendet werden können. Damit ist eigentlich auch bereits die Definition einer Inlinefunktion beschrieben. Sie besteht aus einer einzigen SELECT-Anweisung, die anhand eines Parameterwerts gefiltert werden kann. Mehr außer dieser gibt es dort nicht. Von der Programmierung her ist die Inlinefunktion daher die einfachste Variante.

Eine neue Inlinefunktion können Sie mit dem SQL Server Management Studio anlegen. Sie finden die benutzerdefinierten Funktionen unter Programmierbarkeit innerhalb der jeweiligen Datenbank. Dort sehen Sie unter Gespeicherte Prozeduren auch den Knoten Funktionen, der Ihnen nach einem Klick auf den Knoten weitere Verzweigungen anzeigt.

Inlinefunktionen liefern eine Ergebnismenge über eine virtuelle Tabelle und gehören somit zur Gruppe der Tabellenwertfunktionen.

Durch einen Klick mit der rechten Maustaste auf den Knoten Tabellenwertfunktionen können Sie den Befehl Neue Inline-Tabellenwertfunktion (Abb. 1) auswählen. Sie erhalten auch hier - wie bei den gespeicherten Prozeduren und Triggern - ein neues Abfragefenster mit einem Vorlageskript.

pic01.TIF

Abb. 1: Eine neue Inlinefunktion

Die Definition einer neuen Funktion beginnt mit dem Befehl CREATE FUNCTION, gefolgt vom Funktionsnamen, der immer das entsprechende Schema enthalten muss. Wie bereits bei den gespeicherten Prozeduren beschrieben, werden über das Schema eines Objekts unter anderem die Zugriffsrechte verwaltet. Sofern Sie kein detailliertes Berechtigungskonzept einsetzen, ist das Schema in der Regel dbo. Sie sollten für eine einheitliche Basis für die Weiterentwicklung und eine spätere Konfiguration eines Berechtigungskonzepts immer dbo angeben.

Eine genauere Erklärung der Berechtigungsvergabe anhand der Schemata würde allerdings den Rahmen dieses Artikels sprengen.

Der oder die Parameter, die in der SELECT-Anweisung der Inlinefunktion verwendet werden, geben Sie durch Komma getrennt in Klammern hinter dem Namen der Funktion an. Dabei verwenden Sie das @-Zeichen zur Kennzeichnung jedes einzelnen Parameters sowie den Datentyp.

Nach den Parametern definieren Sie mit RETURNS TABLE, welchen Datentyp das Ergebnis der Funktion aufweist. Eine Inlinefunktion liefert eine Ergebnismenge anhand einer virtuellen Tabelle über den Datentyp TABLE. Die Struktur dieser virtuellen Tabelle ergibt sich aus der SELECT-Anweisung. Nach RETURNS TABLE leiten Sie mit AS den eigentlichen Programmcode ein, der in Klammern angegeben werden muss. Der Programmcode besteht wie bereits erwähnt nur aus einer einzelnen SELECT-Anweisung. Eine weitere Spezifikation zur Kennzeichnung der Tabellenfunktion als Inlinefunktion ist nicht notwendig.

Als Beispiel sehen Sie in Listing 1 die Inlinefunktion dbo.ifArtikelEinerKategorie, die alle aktiven oder inaktiven Artikel einer bestimmten Kategorie als Ergebnismenge liefert.

Listing 1: Beispiel für eine Inlinefunktion

CREATE FUNCTION dbo.ifArtikelEinerKategorie

(

@CategoryId int,

@Discontinued bit

)

RETURNS TABLE

AS

RETURN

(

SELECT

dbo.Products.ProductId, dbo.Products.ProductName,

dbo.Products.SupplierID, dbo.Suppliers.CompanyName,

dbo.Products.CategoryID, dbo.Categories.CategoryName,

dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,

dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,

dbo.Products.ReorderLevel, dbo.Products.Discontinued

FROM

dbo.Products

INNER JOIN dbo.Categories

ON dbo.Products.CategoryID = dbo.Categories.CategoryID

INNER JOIN dbo.Suppliers

ON Products.SupplierID = Suppliers.SupplierID

WHERE

dbo.Products.CategoryId = @CategoryId

AND

dbo.Products.Discontinued = @Discontinued

)

Die Erklärung dieser Inlinefunktion ist recht einfach. Die SELECT-Anweisung ermittelt alle Artikeldaten zuzüglich der Bezeichnung der Kategorie, zu der die Artikel gehören, sowie den Firmennamen des Lieferanten. Diese Ergebnismenge wird bei der Ermittlung auf eine Kategorie gefiltert, die über den Parameter @CategoryId an die SELECT-Anweisung übergeben wird. Zusätzlich wird über den Parameter @Discontinued bestimmt, ob die Ergebnismenge nur die aktiven Artikel (Wert 0) oder nur die inaktiven Artikel (Wert 1) enthält.

Bei der SELECT-Anweisung müssen Sie darauf achten, dass alle Spalten einen Namen bekommen. Dies gilt insbesondere bei der Verwendung von Konstanten oder berechneten Spalten. Hier muss zwingend ein Alias vergeben werden. Diese Inlinefunktion können Sie nun wie eine "parametrisierte Sicht" verwenden. Folgender Aufruf liefert alle aktiven Artikel der Kategorie 1:

SELECT * FROM dbo.ifArtikelEinerKategorie (1, 0)

Sie können die Inlinefunktion auch in einer Tabellenverknüpfung verwenden. Die folgende Abfrage etwa liefert alle Bestellpositionen, die inaktive Artikel der Kategorie 1 beinhalten:

SELECT * FROM dbo.[Order Details] OD

INNER JOIN dbo.ifArtikelEinerKategorie(1, 1) AEK

ON OD.ProductId = AEK.ProductId

Für einfache SELECT-Anweisungen ist die Inlinefunktion die erste Wahl, da sie schnell programmiert und flexibel einsetzbar ist. Flexibel einsetzbar sind auch die Tabellenfunktionen - und sie sind auch flexibel in der Ermittlung der Ergebnismenge.

Tabellenfunktionen

Im Unterschied zu einer Inlinefunktion können in einer Tabellenfunktion mehrere SQL-Anweisungen zur Ermittlung der Ergebnismenge gespeichert werden.

Dies sagt bereits der Kontextmenübefehl des Knotens Tabellenwertfunktionen aus: Neue Tabellenwertfunktion mit mehreren Anweisungen (s. Abb. 1). Auch dieser Befehl liefert Ihnen eine Skriptvorlage in einem neuen Abfragefenster. Diese unterscheidet sich in einigen Punkten von der der Inlinefunktion.

Bei einer Tabellenfunktion wird die Struktur der virtuellen Tabelle nicht anhand einer SELECT-Anweisung bestimmt, sondern muss durch eine TABLE-Variable explizit definiert werden. Insofern gibt es hier kein RETURNS TABLE, sondern ein RETURNS gefolgt von der Definition der TABLE-Variablen.

Die einzelnen SQL-Anweisungen zur Ermittlung der Ergebnismenge werden in einer Tabellenfunktion im BEGIN...END-Block zusammengefasst. Mit diesen SQL-Anweisungen ermitteln Sie nach und nach die Daten und füllen mit diesen die TABLE-Variable. Dabei stehen Ihnen alle Möglichkeiten der T-SQL-Programmierung zur Verfügung, bis auf wenige Ausnahmen:

  • Ausgabe von Meldungen: Benutzerdefinierte Funktionen liefern immer Werte oder Ergebnismengen. Meldungen, die zum Beispiel auf einen Fehler hinweisen und durch RAISERROR oder PRINT ausgelöst werden, werden nicht unterstützt.
  • Datenmanipulationen außerhalb der Funktion: Innerhalb der Tabellenfunktion können Sie die Daten der dort enthaltenen Objekte - sprich temporären Tabellen oder Table-Variablen - manipulieren; die Daten anderer Tabellen jedoch nicht.
  • Verwendung von gespeicherten Prozeduren: Gespeicherte Prozeduren können in benutzerdefinierten Funktionen nicht aufgerufen werden. Der Befehl EXECUTE wird nicht unterstützt. Dies würde auch der gerade erwähnten Einschränkung widersprechen, dass in benutzerdefinierten Funktionen keine Daten von Tabellen manipuliert werden können. Wären in benutzerdefinierten Funktionen gespeicherte Prozeduren erlaubt, wären über diese die Datenmanipulationen möglich.

Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

Sind Sie Abonnent?Jetzt einloggen ...
 

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Verwandte Beiträge:

Abfragen von Access zum SQL Server

Gespeicherte Prozeduren

Upsizing von Access nach SQL Server: Tabellen

Anmeldung an SQL Server und Co.

Der DBMS-Connection-Wizard

Trigger

Datensicherung mit dem MS SQL Server

DBMS-unabhängiger Zugriff auf SQL Server und Co.

Performanter Webzugriff auf MySQL-Datenbanken

Access, MySQL und Berechtigungsverwaltung

Indizierung mit Access

© 2003-2015 André Minhorst Alle Rechte vorbehalten.