توابع تعریف شده توسط کاربر (UDF) در SQL Server چیست؟
توابع تعریف شده توسط کاربر یا User Defined Functions (UDF) در SQL Server مشابه متدهای اسکالر در زبان‌های برنامه‌نویسی هستند که پارامتر می‌گیرند، محاسبات پیچیده انجام می‌دهند و یک مقدار یا مجموعه‌ای از داده‌ها را بازمی‌گردانند. این توابع به شما امکان می‌دهند منطق تکراری را یکبار تعریف و بارها استفاده کنید و کدهای SQL را خواناتر و بهینه‌تر نمایید.
انواع توابع در SQL Server
SQL Server دو نوع تابع دارد:
توابع سیستمی (Built-in Functions): توابع آماده و از پیش تعریف شده که توسط مایکروسافت ارائه شده‌اند (مانند توابع ریاضی، رشته‌ای، تاریخی و تجمعی).
توابع تعریف شده توسط کاربر (User Defined Functions): توابعی که خودتان می‌توانید بسازید و در پایگاه داده ذخیره کنید.
انواع توابع تعریف شده توسط کاربر
تابع اسکالر (Scalar Function): مقداری منفرد (مانند عدد، رشته) برمی‌گرداند.
تابع جدول‌دار (Table Valued Function): جدولی از داده‌ها را بازمی‌گرداند.
تابع جدول‌دار خطی (Inline Table Valued Function): جدولی بر اساس یک دستور SELECT ساده برمی‌گرداند.
مزایای استفاده از توابع تعریف شده توسط کاربر
جلوگیری از تکرار کد و منطق در چندین کوئری.
تعریف یکبار تابع و استفاده نامحدود در کل پایگاه داده.
کاهش زمان کامپایل کوئری‌ها با استفاده از کش برنامه اجرا (Execution Plan).
جداسازی محاسبات پیچیده از کوئری‌های اصلی برای فهم و دیباگ بهتر.
کاهش ترافیک شبکه به دلیل استفاده از کش.
امکان استفاده در بخش WHERE کوئری برای محدود کردن داده‌های برگشتی.
ساختار کلی تعریف تابع (Syntax)
CREATE FUNCTION FunctionName (@Parameter1 DataType, @Parameter2 DataType, ...)
RETURNS ReturnDataType
AS
BEGIN
-- بدنه تابع (Query یا محاسبات)
RETURN (ReturnValue)
END
FunctionName: نام تابع (غیر از کلمات رزرو شده).
@Parameter: پارامترهای ورودی با نوع داده مشخص.
ReturnDataType: نوع داده بازگشتی (مقدار منفرد یا جدول).
بدنه تابع: شامل دستورات SQL یا محاسبات مورد نظر.
مثال‌های کاربردی توابع تعریف شده توسط کاربر در SQL Server
1. تابع اسکالر بدون پارامتر
تابعی که مجموع درآمد سالیانه همه کارمندان را برمی‌گرداند:
CREATE FUNCTION NoParameters()
RETURNS INT
AS
BEGIN
RETURN (SELECT SUM([YearlyIncome]) FROM [MyEmployees Table])
END
استفاده:
SELECT dbo.NoParameters() AS [Total Yearly Income];
2. تابع اسکالر با پارامتر
تابعی که نام کامل کارمند را با ترکیب نام و نام خانوادگی برمی‌گرداند:
CREATE FUNCTION fullName (@firstName VARCHAR(50), @lastName VARCHAR(50))
RETURNS VARCHAR(200)
AS
BEGIN
RETURN (@firstName + SPACE(2) + @lastName)
END
استفاده:
SELECT dbo.fullName([FirstName], [LastName]) AS [Name] FROM [MyEmployees Table];
3. تابع اسکالر با پارامتر در WHERE Clause
تابعی که مجموع فروش را برای شغل مشخص شده محاسبه می‌کند:
CREATE FUNCTION average (@Occupation VARCHAR(50))
RETURNS FLOAT
AS
BEGIN
RETURN (SELECT SUM([Sales]) FROM [MyEmployees Table] WHERE [Occupation] = @Occupation)
END
استفاده:
SELECT [Occupation], dbo.average([Occupation]) AS [Total Sales] FROM [MyEmployees Table] GROUP BY [Occupation];
4. استفاده از تابع اسکالر در WHERE Clause
تابعی که میانگین فروش را محاسبه می‌کند و در کوئری برای فیلتر استفاده می‌شود:
CREATE FUNCTION AverageSale()
RETURNS FLOAT
AS
BEGIN
RETURN (SELECT AVG([Sales]) FROM [MyEmployees Table])
END
استفاده:
SELECT * FROM [MyEmployees Table] WHERE [Sales] >= dbo.AverageSale();
5. تابع جدول‌دار خطی بدون پارامتر
تابعی که ۱۰ رکورد اول جدول کارمندان را برمی‌گرداند:
CREATE FUNCTION TopTenCustomers()
RETURNS TABLE
AS
RETURN (
SELECT TOP 10 [FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales], [HireDate]
FROM [MyEmployees Table]
)
استفاده:
SELECT * FROM dbo.TopTenCustomers();
6. تابع جدول‌دار خطی با پارامتر
تابعی که کارمندان یک دپارتمان مشخص را برمی‌گرداند:
CREATE FUNCTION CustomerbyDepartment (@profession VARCHAR(50))
RETURNS TABLE
AS
RETURN (
SELECT e.[FirstName], e.[LastName], e.[Occupation], e.[Education], d.DepartmentName, e.[YearlyIncome], e.[Sales]
FROM [MyEmployees Table] e
INNER JOIN Department d ON d.id = e.DeptID
WHERE e.[Occupation] = @profession
)
استفاده:
SELECT * FROM dbo.CustomerbyDepartment('Management');
7. تابع جدول‌دار چنددستوری (Multi-Statement Table Valued Function)
تابعی که داده‌ها را در جدول متغیر ذخیره و سپس به‌روزرسانی می‌کند:
CREATE FUNCTION CustomerDepartment()
RETURNS @customers TABLE (
EmployeeID smallint NOT NULL,
FirstName nvarchar(30),
LastName nvarchar(40),
Education nvarchar(255),
Occupation nvarchar(255),
YearlyIncome float,
Sales float,
HireDate date,
DepartmentName VARCHAR(50)
)
AS
BEGIN
INSERT INTO @customers
SELECT e.EmployeeID, e.FirstName, e.LastName, e.Education, e.Occupation, e.YearlyIncome, e.Sales, e.HireDate, d.DepartmentName
FROM [MyEmployees Table] e
INNER JOIN Department d ON d.id = e.DeptID;
UPDATE @customers
SET YearlyIncome = YearlyIncome + 35200
WHERE Sales > (SELECT AVG(Sales) FROM [MyEmployees Table]);
RETURN;
END
استفاده:
SELECT * FROM dbo.CustomerDepartment();
توابع تو در تو (Nested UDFs)
می‌توان یک تابع تعریف شده توسط کاربر را درون تابع دیگر فراخوانی کرد:
CREATE FUNCTION CustDepartment (@education VARCHAR(50))
RETURNS TABLE
AS
RETURN (
SELECT dbo.fullName(FirstName, LastName) AS Name, Occupation, Education, d.DepartmentName, YearlyIncome, Sales
FROM [MyEmployees Table] e
INNER JOIN Department d ON d.id = e.DeptID
WHERE Education = @education
)
مدیریت توابع تعریف شده توسط کاربر
مشاهده تعریف تابع:
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.AverageSale')) AS Definition;
تغییر نام تابع:
EXEC sp_rename 'OldFunctionName', 'NewFunctionName';
ویرایش تابع: با دستور
ALTER FUNCTIONیا از طریق SSMS.حذف تابع:
DROP FUNCTION IF EXISTS dbo.FunctionName;
توجه: حذف تابع در صورتی امکان‌پذیر است که به آن ارجاعی وجود نداشته باشد.
دیدگاهتان را بنویسید