Functions, Stored Procedures und Triggers im Microsoft SQL Server

Microsoft SQL Server ist eines der am häufigsten verwendeten relationalen Datenbankmanagement-Systeme (RDBMS) auf dem Markt. Eine der Stärken des SQL Servers ist seine Fähigkeit, komplexe Geschäftslogik in der Datenbank selbst zu implementieren. Dazu stehen verschiedene Konstrukte zur Verfügung, darunter Functions, Stored Procedures und Triggers. In diesem Blog-Artikel werden wir uns diese Konstrukte genauer ansehen und ihre Verwendung im SQL Server erklären.

Functions

Eine Funktion ist eine spezielle Art von Prozedur, die einen Wert zurückgibt. Im SQL Server gibt es zwei Arten von Funktionen: Skalarfunktionen und Tabellenfunktionen.

Skalarfunktionen

Skalarfunktionen sind Funktionen, die einen einzelnen Wert zurückgeben. Sie können verwendet werden, um komplexe Berechnungen durchzuführen oder Daten in einer bestimmten Weise zu manipulieren. Skalarfunktionen können in Abfragen, Ausdrücken oder anderen Funktionen aufgerufen werden.

Hier ist ein Beispiel für eine einfache Skalarfunktion im Microsoft SQL Server:

CREATE FUNCTION dbo.GetFullName (@firstName nvarchar(50), @lastName nvarchar(50))
RETURNS nvarchar(100)
AS
BEGIN
    DECLARE @fullName nvarchar(100)
    SET @fullName = @firstName + ' ' + @lastName
    RETURN @fullName
END

Diese Funktion heißt GetFullName und nimmt zwei Parameter an: @firstName und @lastName, die jeweils als nvarchar-Datentyp definiert sind. Die Funktion gibt einen nvarchar-Wert zurück, der den vollständigen Namen des Benutzers darstellt.

Die Funktionsdefinition beginnt mit der CREATE FUNCTION-Anweisung, gefolgt vom Funktionsnamen (dbo.GetFullName) und den Parametern in Klammern. Der Rückgabetyp der Funktion (nvarchar(100)) wird nach dem RETURNS-Schlüsselwort angegeben.

In der Funktion wird zuerst eine Variable @fullName definiert, die den vollständigen Namen des Benutzers speichert. Die beiden Parameter @firstName und @lastName werden mithilfe des +-Operators zusammengeführt, um den vollständigen Namen zu bilden.

Schließlich wird der vollständige Name mit dem RETURN-Schlüsselwort zurückgegeben, damit er in einer Abfrage oder einer anderen Funktion verwendet werden kann.

Hier ist ein Beispiel für die Verwendung dieser Funktion in einer SELECT-Abfrage:

SELECT dbo.GetFullName('John', 'Doe') AS FullName

Dies würde den vollständigen Namen „John Doe“ als Ergebnis zurückgeben.

Tabellenfunktionen

Tabellenfunktionen hingegen geben eine Tabelle zurück. Dies kann eine Inline-Tabellefunktion oder eine Multiline-Tabellefunktion sein. Eine Inline-Tabellefunktion gibt eine Tabelle zurück, die in der Abfrage selbst definiert ist. Eine Multiline-Tabellefunktion hingegen gibt eine Tabelle zurück, die von einer SELECT-Anweisung innerhalb der Funktion definiert wird.

Hier ist ein Beispiel für eine Tabellenfunktion:

CREATE FUNCTION dbo.GetEmployeesByDepartment (@departmentName nvarchar(50))
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, Title, HireDate
    FROM Employees
    INNER JOIN EmployeeDepartment ON Employees.EmployeeID = EmployeeDepartment.EmployeeID
    INNER JOIN Departments ON EmployeeDepartment.DepartmentID = Departments.DepartmentID
    WHERE Departments.DepartmentName = @departmentName
)

Diese Funktion heißt GetEmployeesByDepartment und nimmt einen Parameter @departmentName an. Die Funktion gibt eine Tabelle zurück, die alle Mitarbeiter in einer bestimmten Abteilung enthält.

Die Funktionsdefinition beginnt wieder mit der CREATE FUNCTION-Anweisung, gefolgt vom Funktionsnamen (dbo.GetEmployeesByDepartment) und den Parametern in Klammern. Der Rückgabetyp der Funktion (TABLE) wird nach dem RETURNS-Schlüsselwort angegeben.

In der Funktion wird eine SELECT-Abfrage verwendet, um alle Mitarbeiter in der angegebenen Abteilung abzurufen. Die Abfrage verwendet JOINs, um die Mitarbeiter- und Abteilungsinformationen zu verknüpfen, und WHERE, um nur Mitarbeiter in der angegebenen Abteilung auszuwählen.

Schließlich wird die SELECT-Abfrage als Rückgabewert der Funktion verwendet, indem sie innerhalb der RETURN-Anweisung eingeschlossen wird.

Hier ist ein Beispiel für die Verwendung dieser Funktion in einer SELECT-Abfrage:

SELECT *
FROM dbo.GetEmployeesByDepartment('Sales')

Dies würde eine Tabelle mit allen Mitarbeitern in der Verkaufsabteilung zurückgeben, die aus der GetEmployeesByDepartment-Funktion abgerufen wurden.

Stored Procedures

Gespeicherte Prozeduren sind vordefinierte Abfolgen von SQL-Anweisungen, die in der Datenbank gespeichert werden. Sie werden typischerweise verwendet, um komplexe Geschäftslogik in der Datenbank zu implementieren. Gespeicherte Prozeduren können Parameter akzeptieren, die vom Aufrufer bereitgestellt werden, und können auch Ausgabeparameter haben.

Ein Vorteil von gespeicherten Prozeduren ist, dass sie wiederverwendet werden können. Anstatt denselben Code immer wieder zu schreiben, kann eine gespeicherte Prozedur erstellt werden, die in verschiedenen Teilen der Anwendung verwendet wird. Gespeicherte Prozeduren können auch zur Implementierung von Transaktions- und Sicherheitskontrollen verwendet werden.

Hier ist ein Beispiel für eine gespeicherte Prozedur im Microsoft SQL Server:

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @customerID int
AS
BEGIN
    SELECT OrderID, OrderDate, ShipDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @customerID
    ORDER BY OrderDate DESC
END

Diese gespeicherte Prozedur heißt GetOrdersByCustomer und nimmt einen Parameter @customerID an. Die Prozedur gibt alle Bestellungen eines Kunden zurück, sortiert nach Bestelldatum absteigend.

Die Prozedurdefinition beginnt mit der CREATE PROCEDURE-Anweisung, gefolgt vom Prozedurnamen (dbo.GetOrdersByCustomer) und den Parametern in Klammern. Der @customerID-Parameter ist vom int-Datentyp. Es können auch mehrere Parameter definiert werden, je nach Bedarf.

In der Prozedur wird eine SELECT-Abfrage verwendet, um alle Bestellungen des angegebenen Kunden abzurufen. Der WHERE-Filter beschränkt die Ergebnisse auf den angegebenen Kunden, und das ORDER BY-Schlüsselwort sortiert die Ergebnisse nach Bestelldatum absteigend.

Die Prozedurdefinition wird mit der END-Anweisung abgeschlossen.

Hier ist ein Beispiel für die Ausführung dieser Prozedur:

EXEC dbo.GetOrdersByCustomer @customerID = 12345

Dies würde alle Bestellungen für den Kunden mit der ID 12345 zurückgeben, wie sie von der gespeicherten Prozedur GetOrdersByCustomer definiert wurden.

Trigger

Ein Trigger ist eine spezielle Art von gespeicherter Prozedur, die automatisch ausgeführt wird, wenn eine bestimmte Aktion in der Datenbank ausgeführt wird. Diese Aktionen können das Einfügen, Aktualisieren oder Löschen von Datensätzen sein. Wenn der Trigger ausgeführt wird, kann er SQL-Anweisungen ausführen, um andere Aktionen in der Datenbank auszulösen.

Trigger können verwendet werden, um Daten zu validieren oder zu bereinigen, bevor sie in die Datenbank eingefügt werden. Sie können auch verwendet werden, um sicherzustellen, dass bestimmte Datenintegritätsregeln eingehalten werden. Ein Beispiel für die Verwendung von Triggern wäre die Implementierung einer Überprüfung auf doppelte Datensätze.

Hier ist ein Beispiel für einen Trigger:

CREATE TRIGGER TR_OrderDetails_Insert
ON OrderDetails
AFTER INSERT
AS
BEGIN
    -- Insert new records into the OrderDetailHistory table
    INSERT INTO OrderDetailHistory
    SELECT i.OrderID, i.ProductID, i.Quantity, GETDATE()
    FROM inserted i
END

Dieser Trigger heißt TR_OrderDetails_Insert und wird ausgelöst, nachdem eine neue Zeile in der OrderDetails-Tabelle eingefügt wurde. Der Trigger fügt die Details dieser neuen Zeile in die OrderDetailHistory-Tabelle ein.

Die Trigger-Definition beginnt mit der CREATE TRIGGER-Anweisung, gefolgt vom Triggernamen (TR_OrderDetails_Insert) und der Tabelle, auf die der Trigger angewendet werden soll (OrderDetails). Das AFTER INSERT-Schlüsselwort gibt an, dass der Trigger nach einem INSERT-Vorgang auf die Tabelle ausgelöst werden soll.

In der Trigger-Behandlung wird eine INSERT-Abfrage verwendet, um die Details der eingefügten Zeile in die OrderDetailHistory-Tabelle einzufügen. Die inserted-Tabelle wird verwendet, um auf die Details der eingefügten Zeile zuzugreifen. Es werden die Spalten OrderID, ProductID, Quantity und die aktuelle Datum/Uhrzeit (mittels GETDATE()-Funktion) in die OrderDetailHistory-Tabelle eingefügt.

Die Trigger-Definition wird mit der END-Anweisung abgeschlossen.

Hier ist ein Beispiel für die Verwendung dieses Triggers:

INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
VALUES (1001, 1234, 5, 9.99)

Dies würde eine neue Zeile in der OrderDetails-Tabelle einfügen und den Trigger TR_OrderDetails_Insert auslösen, der eine neue Zeile in der OrderDetailHistory-Tabelle mit den Details der eingefügten Zeile einfügt.

Fazit

Zusammenfassend lässt sich sagen, dass Functions, Stored Procedures und Triggers wichtige Konzepte im Microsoft SQL Server sind, die Entwicklern dabei helfen, komplexe Operationen zu implementieren und wiederholte Aufgaben zu automatisieren.

Durch die Verwendung von Funktionen kann eine benutzerdefinierte Operation entwickelt werden, die einen Wert zurückgibt. Dies kann dazu beitragen, Code zu strukturieren und zu vereinfachen.

Stored Procedures ermöglichen es Entwicklern, mehrere Transact-SQL-Anweisungen als eine Einheit auszuführen, was Anwendungen beschleunigen und die Wartung vereinfachen kann.

Trigger sind nützlich, um automatisch auf bestimmte Aktionen in der Datenbank zu reagieren, beispielsweise um Daten zu archivieren oder Änderungen an Daten zu protokollieren.

Insgesamt sind diese Konzepte wichtige Werkzeuge für Entwickler. Durch die Verwendung von Functions, Stored Procedures und Triggers können diese effektivere Anwendungen erstellen und Wartungsaufgaben reduzieren.