Skapa skalärfunktioner
Skalära funktioner är viktiga verktyg i SQL Server som gör att du kan kapsla in återanvändbar logik och returnera ett enda värde. Du kan använda dem direkt i SELECT instruktioner, WHERE satser och andra T-SQL-uttryck , vilket gör dina frågor mer underhållsbara och koden mer modulär.
Förstå grunderna för skalära funktioner
En skalär funktion accepterar noll eller fler parametrar och returnerar ett enda värde av en angiven datatyp. Till skillnad från lagrade procedurer kan skalärfunktioner bäddas in direkt i SQL-uttryck oavsett var du använder en kolumn eller variabel.
De viktigaste egenskaperna för skalära funktioner är möjligheten att acceptera indataparametrar, utföra beräkningar eller transformeringar och returnera exakt ett värde. Du definierar returdatatypen explicit i funktionsdefinitionen, som SQL Server verifierar när den skapas.
När du skapar en skalär funktion skapar du en återanvändbar logik som andra utvecklare kan anropa i hela databasen. Detta främjar återanvändning av kod och hjälper till att upprätthålla konsekvens i dina program.
Definiera syntax för skalär funktion
Om du vill skapa en skalär funktion använder du -instruktionen CREATE FUNCTION med specifika syntaxkomponenter. Den grundläggande strukturen innehåller funktionsnamn, parametrar, returtyp och funktionstext.
Här är det grundläggande syntaxmönstret:
CREATE FUNCTION schema_name.function_name
(
@parameter1 datatype,
@parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
-- Function logic here
RETURN @result
END
Satsen RETURNS anger datatypen för det enskilda värde som funktionen returnerar.
BEGIN...END I blocket skriver du din T-SQL-logik och använder en RETURN instruktion för att skicka tillbaka resultatet.
Du kan till exempel skapa en enkel funktion som beräknar 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
Den här funktionen accepterar två parametrar och returnerar det beräknade skattebeloppet. Du kan använda den här funktionen i valfri SELECT instruktion.
Implementera skalära funktioner med affärslogik
Skalärfunktioner utmärker sig för att kapsla in affärsregler och beräkningar som du behöver tillämpa konsekvent i databasen. Med skalära funktioner centraliserar du logik som annars kan dupliceras i flera frågor eller programkod.
Tänk dig ett scenario där du behöver beräkna anställningstiden för anställda i flera år. Du skapar en skalär funktion som accepterar ett anställningsdatum och returnerar antalet fullständiga å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 använda den här funktionen i frågor för att visa anställningstidinformation.
SELECT
EmployeeName,
HireDate,
dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5
Den här metoden säkerställer konsekvent beräkning av besittningsrätt i hela databasen. Om affärsreglerna ändras ändrar du funktionen en gång i stället för att uppdatera flera frågor.
Anmärkning
Den här funktionen använder GETDATE(), vilket gör den icke-deterministisk. Icke-deterministiska funktioner kan inte användas i indexerade vyer eller index på beräknade kolumner. För scenarier som kräver determinism skickar du det aktuella datumet som en parameter i stället.
Tillämpa metodtips för skalära funktioner
När du skapar skalära funktioner hjälper flera metodtips till att säkerställa optimal prestanda och underhåll. Genom att förstå dessa metoder kan du undvika vanliga fallgropar och skapa effektiva och tillförlitliga funktioner.
Håll först skalära funktioner deterministiska när det är möjligt. En deterministisk funktion returnerar alltid samma resultat med samma indataparametrar. Funktioner som refererar till systemets datum-/tidsfunktioner eller tabeller är icke-deterministiska och kan förhindra vissa frågeoptimeringar.
Undvik också biverkningar i dina funktioner. Skalärfunktioner bör inte ändra databastillstånd eller ha beroenden för externa resurser. Den här begränsningen finns eftersom SQL Server kan köra funktioner flera gånger eller i andra ordrar än du förväntar dig.
Slutligen bör du tänka på prestandakonsekvenserna. När du använder en skalär funktion i en WHERE sats eller SELECT lista med stora tabeller kan SQL Server köra funktionen för varje rad. Detta kan påverka frågeprestanda avsevärt. I sådana scenarier bör du betrakta infogade tabellvärdesfunktioner som ett alternativ.
Här är ett exempel på en väldesignad skalär funktion som följer dessa metoder:
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
Den här funktionen är deterministisk, har inga biverkningar och utför en enkel transformering. Den hanterar ogiltiga indata på ett korrekt sätt genom att returnera det ursprungliga värdet när telefonnumret inte matchar det förväntade formatet.
Ändra och hantera skalärfunktioner
När du har skapat en skalär funktion kan du ändra dess definition med hjälp av -instruktionen ALTER FUNCTION . Syntaxen ALTER FUNCTION speglar CREATE FUNCTION men gör att du kan ändra funktionen utan att släppa och återskapa den, vilket bevarar behörigheter och beroenden.
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
Om du vill ta bort en skalär funktion använder du -instruktionen DROP FUNCTION :
DROP FUNCTION IF EXISTS dbo.CalculateSalesTax
IF EXISTS Satsen förhindrar fel om funktionen inte finns, vilket är användbart i distributionsskript. Innan du tar bort en funktion kontrollerar du att inga andra databasobjekt är beroende av den genom att kontrollera systemvyer som sys.sql_expression_dependencies.