Erstellen skalarer Funktionen
Skalare Funktionen sind wichtige Tools in SQL Server, mit denen Sie wiederverwendbare Logik kapseln und einen einzelnen Wert zurückgeben können. Sie können sie direkt in SELECT Anweisungen, WHERE Klauseln und anderen T-SQL-Ausdrücken verwenden, wodurch Ihre Abfragen besser verwaltet werden können und Ihr Code modularer ist.
Grundlegendes zu skalaren Funktionen
Eine skalare Funktion akzeptiert null oder mehr Parameter und gibt einen einzelnen Wert eines angegebenen Datentyps zurück. Im Gegensatz zu gespeicherten Prozeduren können skalare Funktionen direkt in SQL-Ausdrücke eingebettet werden, unabhängig davon, wo Sie eine Spalte oder Variable verwenden würden.
Zu den wichtigsten Merkmalen von Skalarfunktionen gehören die Fähigkeit, Eingabeparameter zu akzeptieren, Berechnungen oder Transformationen durchzuführen und genau einen Wert zurückzugeben. Sie definieren den Rückgabedatentyp explizit in der Funktionsdefinition, den SQL Server zur Erstellungszeit überprüft.
Wenn Sie eine skalare Funktion erstellen, erstellen Sie eine wiederverwendbare Logik, die andere Entwickler in der gesamten Datenbank aufrufen können. Dadurch wird die Wiederverwendung von Code gefördert und die Konsistenz in Ihren Anwendungen gewährleistet.
Definieren der Syntax der skalaren Funktion
Zum Erstellen einer skalaren Funktion verwenden Sie die CREATE FUNCTION Anweisung mit bestimmten Syntaxkomponenten. Die grundlegende Struktur enthält den Funktionsnamen, parameter, den Rückgabetyp und den Funktionstext.
Hier ist das grundlegende Syntaxmuster:
CREATE FUNCTION schema_name.function_name
(
@parameter1 datatype,
@parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
-- Function logic here
RETURN @result
END
Die RETURNS Klausel gibt den Datentyp des einzelnen Werts an, den die Funktion zurückgibt. Innerhalb des BEGIN...END Blocks schreiben Sie Ihre T-SQL-Logik und verwenden eine RETURN Anweisung, um das Ergebnis zurückzusenden.
Sie können z. B. eine einfache Funktion erstellen, die die Mehrwertsteuer berechnet:
CREATE FUNCTION dbo.CalculateSalesTax
(
@Amount DECIMAL(10,2),
@TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @TaxAmount DECIMAL(10,2)
SET @TaxAmount = @Amount * @TaxRate
RETURN @TaxAmount
END
Diese Funktion akzeptiert zwei Parameter und gibt den berechneten Steuerbetrag zurück. Sie können diese Funktion in einer beliebigen SELECT Anweisung verwenden.
Implementieren skalarer Funktionen mit Geschäftslogik
Skalare Funktionen zeichnen sich durch Kapselung von Geschäftsregeln und Berechnungen aus, die Sie für die einheitliche Anwendung in Ihrer Datenbank benötigen. Mit skalaren Funktionen zentralisieren Sie Logik, die andernfalls in mehreren Abfragen oder Anwendungscode dupliziert werden kann.
Betrachten Sie ein Szenario, in dem Sie die Mitarbeiter-Tenure in Jahren berechnen müssen. Sie erstellen eine skalare Funktion, die ein Einstellungsdatum akzeptiert und die Anzahl der vollständigen Jahre zurückgibt:
CREATE FUNCTION dbo.GetEmployeeTenure
(
@HireDate DATE
)
RETURNS INT
AS
BEGIN
DECLARE @Tenure INT
SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
RETURN @Tenure
END
Sie können diese Funktion in Abfragen verwenden, um Amtszeitinformationen anzuzeigen.
SELECT
EmployeeName,
HireDate,
dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5
Durch diesen Ansatz wird eine konsistente Dauerberechnung in der gesamten Datenbank sichergestellt. Wenn sich die Geschäftsregeln ändern, ändern Sie die Funktion einmal, anstatt mehrere Abfragen zu aktualisieren.
Hinweis
Diese Funktion verwendet GETDATE(), wodurch sie nicht deterministisch ist. Nicht deterministische Funktionen können nicht in indizierten Ansichten oder Indizes für berechnete Spalten verwendet werden. Übergeben Sie für Szenarien, die einen Determinismus erfordern, stattdessen das aktuelle Datum als Parameter.
Anwenden bewährter Methoden für skalare Funktionen
Wenn Sie skalare Funktionen erstellen, sorgen mehrere bewährte Methoden für optimale Leistung und Wartungsfreundlichkeit. Das Verständnis dieser Methoden hilft Ihnen, häufige Fallstricke zu vermeiden und effiziente, zuverlässige Funktionen zu erstellen.
Halten Sie zunächst ihre skalaren Funktionen nach Möglichkeit deterministisch. Eine deterministische Funktion gibt immer dasselbe Ergebnis zurück, wenn die gleichen Eingabeparameter vorhanden sind. Funktionen, die auf Systemdatums-/Uhrzeitfunktionen oder Tabellen verweisen, sind nicht deterministisch und können bestimmte Abfrageoptimierungen verhindern.
Vermeiden Sie außerdem Nebenwirkungen in Ihren Funktionen. Skalare Funktionen sollten den Datenbankstatus nicht ändern oder abhängigkeiten von externen Ressourcen haben. Diese Einschränkung ist vorhanden, da SQL Server Funktionen möglicherweise mehrmals oder in unterschiedlichen Reihenfolgen ausführen kann als erwartet.
Achten Sie schließlich auf Leistungsauswirkungen. Wenn Sie eine skalare Funktion in einer WHERE Klausel oder SELECT Liste mit großen Tabellen verwenden, kann SQL Server die Funktion für jede Zeile ausführen. Dies kann sich erheblich auf die Abfrageleistung auswirken. Berücksichtigen Sie für solche Szenarien Inline-Tabellenwertfunktionen als Alternative.
Hier ist ein Beispiel für eine gut gestaltete Skalarfunktion, die auf diese Methoden folgt:
CREATE FUNCTION dbo.FormatPhoneNumber
(
@PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
DECLARE @FormattedNumber VARCHAR(14)
IF LEN(@PhoneNumber) = 10
SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
SUBSTRING(@PhoneNumber, 4, 3) + '-' +
SUBSTRING(@PhoneNumber, 7, 4)
ELSE
SET @FormattedNumber = @PhoneNumber
RETURN @FormattedNumber
END
Diese Funktion ist deterministisch, hat keine Nebenwirkungen und führt eine einfache Transformation durch. Er behandelt ungültige Eingaben ordnungsgemäß, indem der ursprüngliche Wert zurückgegeben wird, wenn die Telefonnummer nicht mit dem erwarteten Format übereinstimmt.
Ändern und Verwalten von Skalarfunktionen
Nachdem Sie eine skalare Funktion erstellt haben, können Sie die Definition mithilfe der ALTER FUNCTION Anweisung ändern. Die ALTER FUNCTION Syntax spiegelt CREATE FUNCTION sich, ermöglicht es Ihnen jedoch, die Funktion zu ändern, ohne sie zu löschen und neu zu erstellen, wodurch Berechtigungen und Abhängigkeiten erhalten bleiben.
ALTER FUNCTION dbo.CalculateSalesTax
(
@Amount DECIMAL(10,2),
@TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @TaxAmount DECIMAL(10,2)
-- Updated logic with rounding
SET @TaxAmount = ROUND(@Amount * @TaxRate, 2)
RETURN @TaxAmount
END
Um eine skalare Funktion zu entfernen, verwenden Sie die DROP FUNCTION Anweisung:
DROP FUNCTION IF EXISTS dbo.CalculateSalesTax
Die IF EXISTS Klausel verhindert Fehler, wenn die Funktion nicht vorhanden ist, was in Bereitstellungsskripts nützlich ist. Stellen Sie vor dem Ablegen einer Funktion sicher, dass keine anderen Datenbankobjekte davon abhängen, indem Sie Die Systemansichten wie folgt sys.sql_expression_dependenciesüberprüfen.