Lag skalarfunksjoner
Skalarfunksjoner er essensielle verktøy i SQL Server som lar deg kapsle inn gjenbrukbar logikk og returnere én enkelt verdi. Du kan bruke dem direkte i SELECT setninger, WHERE klausuler og andre T-SQL-uttrykk , noe som gjør spørringene dine mer vedlikeholdbare og koden mer modulær.
Forstå grunnprinsippene for skalarfunksjoner
En skalarfunksjon aksepterer null eller flere parametere og returnerer én enkelt verdi av en spesifisert datatype. I motsetning til lagrede prosedyrer kan skalarfunksjoner legges direkte inn i SQL-uttrykk der du bruker en kolonne eller variabel.
De viktigste egenskapene til skalarfunksjoner inkluderer deres evne til å akseptere inputparametere, utføre beregninger eller transformasjoner, og returnere nøyaktig én verdi. Du definerer returdatatypen eksplisitt i funksjonsdefinisjonen, som SQL Server validerer ved opprettelse.
Når du lager en skalarfunksjon, lager du et gjenbrukbart stykke logikk som andre utviklere kan kalle gjennom hele databasen. Dette fremmer gjenbruk av kode og bidrar til å opprettholde konsistens på tvers av applikasjonene dine.
Definer skalarfunksjonssyntaks
For å lage en skalarfunksjon bruker du setningen CREATE FUNCTION med spesifikke syntakskomponenter. Den grunnleggende strukturen inkluderer funksjonsnavn, parametere, returtype og funksjonskropp.
Her er det grunnleggende syntaksmønsteret:
CREATE FUNCTION schema_name.function_name
(
@parameter1 datatype,
@parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
-- Function logic here
RETURN @result
END
Klausulen RETURNS spesifiserer datatypen til den enkeltverdien funksjonen returnerer. Innenfor blokken BEGIN...END skriver du din T-SQL-logikk og bruker en RETURN setning for å sende tilbake resultatet.
For eksempel kan du lage en enkel funksjon som beregner omsetningsavgift:
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 funksjonen aksepterer to parametere og returnerer det beregnede skattebeløpet. Du kan bruke denne funksjonen i hvilken som helst SELECT setning.
Implementer skalarfunksjoner med forretningslogikk
Skalarfunksjoner utmerker seg i å kapsle inn forretningsregler og beregninger som du må bruke konsekvent på tvers av databasen din. Med skalarfunksjoner sentraliserer du logikk som ellers kunne blitt duplisert i flere forespørsler eller applikasjonskode.
Tenk deg et scenario der du må beregne ansattes levetid i år. Du lager en skalar funksjon som aksepterer en ansettelsesdato og returnerer antall 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 bruke denne funksjonen i spørringer for å vise ansettelsesinformasjon:
SELECT
EmployeeName,
HireDate,
dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5
Denne tilnærmingen sikrer konsistent beregning av ansettelsestid i hele databasen din. Hvis forretningsreglene endres, endrer du funksjonen én gang i stedet for å oppdatere flere spørringer.
Bemerkning
Denne funksjonen bruker GETDATE(), noe som gjør den ikke-deterministisk. Ikke-deterministiske funksjoner kan ikke brukes i indekserte visninger eller indekser på beregnede kolonner. For scenarier som krever determinisme, oppgi den nåværende datoen som parameter i stedet.
Bruk beste praksis for skalarfunksjoner
Når du lager skalarfunksjoner, bidrar flere beste praksiser til å sikre optimal ytelse og vedlikeholdbarhet. Å forstå disse praksisene hjelper deg å unngå vanlige fallgruver og skape effektive, pålitelige funksjoner.
Først, hold skalarfunksjonene dine deterministiske når det er mulig. En deterministisk funksjon returnerer alltid det samme resultatet gitt de samme inputparameterne. Funksjoner som refererer til systemdato-/tidsfunksjoner eller tabeller er ikke-deterministiske og kan forhindre visse spørringsoptimaliseringer.
Unngå også bivirkninger i funksjonene dine. Skalarfunksjoner bør ikke endre databasestatus eller ha avhengigheter til eksterne ressurser. Denne begrensningen eksisterer fordi SQL Server kan utføre funksjoner flere ganger eller i annen rekkefølge enn du forventer.
Til slutt, vær oppmerksom på ytelsesimplikasjoner. Når du bruker en skalarfunksjon i en WHERE klausul eller SELECT liste med store tabeller, kan SQL Server utføre funksjonen for hver rad. Dette kan ha betydelig innvirkning på spørringsytelsen. For slike scenarioer, vurder funksjoner med tabellverdi som et alternativ.
Her er et eksempel på en godt designet skalarfunksjon som følger disse praksisene:
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 funksjonen er deterministisk, har ingen bivirkninger, og utfører en enkel transformasjon. Den håndterer ugyldig input elegant ved å returnere den opprinnelige verdien når telefonnummeret ikke samsvarer med forventet format.
Modifiser og administrer skalarfunksjoner
Etter at du har opprettet en skalarfunksjon, kan du endre definisjonen ved hjelp av setningen ALTER FUNCTION . Syntaksen ALTER FUNCTION speiler, CREATE FUNCTION men lar deg endre funksjonen uten å fjerne og lage den på nytt, noe som bevarer tillatelser og avhengigheter.
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 å fjerne en skalarfunksjon bruker du setningen DROP FUNCTION :
DROP FUNCTION IF EXISTS dbo.CalculateSalesTax
Klausulen IF EXISTS forhindrer feil hvis funksjonen ikke eksisterer, noe som er nyttig i distribusjonsskript. Før du fjerner en funksjon, verifiser du at ingen andre databaseobjekter er avhengige av den ved å sjekke systemvisninger som sys.sql_expression_dependencies.