Opret skalarfunktioner

Skalarfunktioner er essentielle værktøjer i SQL Server, der gør det muligt at indkapsle genanvendelig logik og returnere en enkelt værdi. Du kan bruge dem direkte i SELECT sætninger, WHERE klausuler og andre T-SQL-udtryk , hvilket gør dine forespørgsler mere vedligeholdelsesvenlige og din kode mere modulær.

Forstå grundprincipperne for skalarfunktioner

En skalarfunktion accepterer nul eller flere parametre og returnerer en enkelt værdi af en specificeret datatype. I modsætning til lagrede procedurer kan skalarfunktioner indlejres direkte i SQL-udtryk, hvor end du bruger en kolonne eller variabel.

De vigtigste karakteristika ved skalarfunktioner inkluderer deres evne til at acceptere inputparametre, udføre beregninger eller transformationer og returnere præcis én værdi. Du definerer return data-typen eksplicit i funktionsdefinitionen, som SQL Server validerer ved oprettelse.

Når du opretter en skalarfunktion, skaber du et genanvendelig stykke logik, som andre udviklere kan kalde i hele databasen. Dette fremmer genbrug af kode og hjælper med at opretholde konsistens på tværs af dine applikationer.

Definér skalarfunktionssyntaks

For at oprette en skalarfunktion bruger du sætningen CREATE FUNCTION med specifikke syntakskomponenter. Den grundlæggende struktur inkluderer funktionsnavn, parametre, returtype og funktionskrop.

Her er det grundlæggende syntaksmønster:

CREATE FUNCTION schema_name.function_name 
(
    @parameter1 datatype,
    @parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
    -- Function logic here
    RETURN @result
END

Klausulen RETURNS specificerer datatypen for den enkelte værdi, funktionen returnerer. Inden for blokken BEGIN...END skriver du din T-SQL-logik og bruger en RETURN sætning til at sende resultatet tilbage.

For eksempel kan du oprette en simpel funktion, der beregner moms:

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

Denne funktion accepterer to parametre og returnerer det beregnede skattebeløb. Du kan bruge denne funktion i enhver SELECT sætning.

Implementer skalarfunktioner med forretningslogik

Skalarfunktioner er dygtige til at indkapsle forretningsregler og beregninger, som du skal anvende konsekvent på tværs af din database. Med skalarfunktioner centraliserer du logik, som ellers kunne være duplikeret i flere forespørgsler eller applikationskode.

Overvej et scenarie, hvor du skal beregne medarbejdernes anciennitet i år. Du opretter en skalar funktion, der accepterer en ansættelsesdato og returnerer antallet af komplette år:

CREATE FUNCTION dbo.GetEmployeeTenure
(
    @HireDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Tenure INT
    SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
    RETURN @Tenure
END

Du kan bruge denne funktion i forespørgsler til at vise oplysninger om anciennitet:

SELECT 
    EmployeeName,
    HireDate,
    dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5

Denne tilgang sikrer ensartet beregning af anciennitet i hele din database. Hvis forretningsreglerne ændrer sig, ændrer du funktionen én gang i stedet for at opdatere flere forespørgsler.

Anvend bedste praksis for skalarfunktioner

Når du opretter skalarfunktioner, hjælper flere bedste praksisser med at sikre optimal ydeevne og vedligeholdelse. At forstå disse praksisser hjælper dig med at undgå almindelige faldgruber og skabe effektive, pålidelige funktioner.

For det første, hold dine skalarfunktioner deterministiske, når det er muligt. En deterministisk funktion returnerer altid det samme resultat givet de samme inputparametre. Funktioner, der refererer til systemdato-/tidsfunktioner eller tabeller, er ikke-deterministiske og kan forhindre visse forespørgselsoptimeringer.

Undgå også bivirkninger i dine funktioner. Skalarfunktioner bør ikke ændre databasestatus eller have afhængigheder af eksterne ressourcer. Denne begrænsning eksisterer, fordi SQL Server kan udføre funktioner flere gange eller i en anden rækkefølge, end du forventer.

Endelig, vær opmærksom på præstationsmæssige konsekvenser. Når du bruger en skalarfunktion i en WHERE klausul eller SELECT liste med store tabeller, kan SQL Server udføre funktionen for hver række. Dette kan have en betydelig indvirkning på forespørgselspræstationen. For sådanne scenarier kan man overveje funktioner med tabelværdi som et alternativ.

Her er et eksempel på en veludformet skalarfunktion, der følger disse praksisser:

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

Denne funktion er deterministisk, har ingen bivirkninger og udfører en ligetil transformation. Den håndterer ugyldig input elegant ved at returnere den oprindelige værdi, når telefonnummeret ikke matcher det forventede format.

Modificerer og administrer skalarfunktioner

Efter du har oprettet en skalar funktion, kan du ændre dens definition ved hjælp af sætningen ALTER FUNCTION . Syntaksen ALTER FUNCTION spejler, CREATE FUNCTION men lader dig ændre funktionen uden at fjerne og genskabe den, hvilket bevarer tilladelser og afhængigheder.

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

For at fjerne en skalar funktion bruger du udsagnet DROP FUNCTION :

DROP FUNCTION IF EXISTS dbo.CalculateSalesTax

Klausulen IF EXISTS forhindrer fejl, hvis funktionen ikke eksisterer, hvilket er nyttigt i deployment-scripts. Før du fjerner en funktion, skal du verificere, at ingen andre databaseobjekter er afhængige af den, ved at tjekke systemvisninger som sys.sql_expression_dependencies.