انواع Functions

[fakhravari]

با سلام
دوستان چند نمونه Functions داریم و چگونگی ایجاد و اعمال در Query را توضیح دهید ممنون میشم.

[baktash.n81@gmail.com]

اگه از توابع سیستمی بگزریم ... چون خیلی زیاد و متنوع هستند ...
3 نوع تابع داریم ... Table... Scalar ... aggregate که :
Table Valued Function خروجیشون یه جدوله ... که در قسمت From در دستور Select می توان ازش استفاده کرد ... در واقع دقیقا مثل یه جدول می شه روش Query زد
Scalar Valued Function مثل توابع معمولی خروجیشون یه مقدار بیشتر نیست ... و بعد از Select می شه ازشون استفاده کرد ... توی شرط ها هم استفاده می شن
Aggregate توایعی مثل Sum Count ... و غیره هستند که روی یک یا چند ستون اعمال می شن ( مثل توابع معمولی پارامتر ورودیشون یه مقدار نیست) ... اینا رو هم می شه جلوی Select استفاده کرد و در صورت استفاده از Group by می شه توی شرط Having هم استفاده کرد.

[fakhravari]

با سلام
ممنون از baktash.n81
با مفهوم توابع اشنا شدم
الان اگر ممکن در قالب چنتا Query موارد را جلو ببریم تا هم پست جالبی بشه هم دیگر دوستان استفاده کنند.

[fakhravari]

برای مثال من میخواهم در یک Query یم مقدار بدم و خروجی آن نام موضوع باشه
create FUNCTION [dbo].[GetName@Category](@CategoryID int)
RETURNS nvarchar
BEGIN
SELECT CategoryName FROM B_Categories where CategoryID = @CategoryID
--RETURN CategoryName
END;

میخواهم مقدار CategoryName را برگردونم.
خطای سینتکس داره ممنون میشم توضیح دهید

[baktash.n81@gmail.com]

خوب اولین نکته این که بعد از مشخص کردن نوع تابع یه لغت AS اضافه کنید ... نکته مهم تر اینه که شما باید یه متغیر تعریف کنید از نوع برگشتی تابع بعد بهش مقدار بدید و اونو برگردونید ...
تابع اصلاح شده شما به این شکل خواهد بود
CREATE FUNCTION [dbo].[GetName@Category](@CategoryID int)
RETURNS NVARCHAR
AS
BEGIN
DECLARE @R nvarchar(50)
SELECT @R=CategoryName FROM B_Categories where CategoryID = @CategoryID
RETURN @R
END

[baktash.n81@gmail.com]

حالا یه نکته دیگه ... فرض کنید می خوایم یه تابع بنویسیم اما Syntax ش درست یادمون نیست ... خوب می تونید توی اینترنت یا راهنمای خود SQL اونو جستجو کنید اما یه روش دیگه هم هست ... Management Studio رو باز کنید از منوی View گزینه Template Explorer رو فعال کنید یه Tool Box سمت راست براتون باز می شه از توش قسمت تابع رو انتخاب می کنیم ... یه Query جدید اضافه می کنیم بعد هر کدوم از توایع رو که خواستیم از منوی Template Explorer می کشیم میندازیم رو صفحه ... کدهای اضافه و کامنت ها رو پاک می کنیم ... و کد رو هر جور که لازم داریم اصلاح می کنیم ...

[fakhravari]

با سلام
ممنون از پاسخ گویی دوستمون baktash
خب الان با Scalar Valued Function که خروجیش یک مقدار بود آشنا شدیم.
در مورد Table Valued Function 2 گزینه داره ممنون میشم توضیحی بدین و مثالی اگر دارید بزنید.

[baktash.n81@gmail.com]

خوب بازم اگه خیلی ساده بخوایم بگیم ... Table valued function همونطور که از اسمش معلومه یه جدول بر می گردونه ... توی inline شما فقط یه Select دارید که نتیجه اونو بر می گردونید اما توی Multi-statement شما می تونید یه جدول با ساختار مشخص بسازید اطلاعات توش بریزید و تابع اون جدول رو برای شما بر می گردونه ... و البته فقط همین نیست ...

این یک inline table valued function هست که از راهنمای Sql کپی کردم ...
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);

اینم یه muti-statement ... که بدون توجه به اینکه کد داخلش چی هست ! می تونید ببینید که اول ساختار جدول ایجاد شده ...

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte
RETURN
END;

[fakhravari]

ممنون که وقت میزاری .
برسی میکنم تحت چنتا Query انجام میدم میزارم.

[fakhravari]

با سلام
در مورد inline table valued function چیزی که فهمیدم یک جدول بیرون میاره که View هم چین کاری میکنه اما این خاصیت فیلتر هم داره .
یه مثال
ALTER FUNCTION [dbo].[GetTable](@CategoryID int)
RETURNS TABLE
AS
RETURN
(
SELECT dbo.B_Topics.Subject, dbo.B_Forums.ForumName, dbo.B_Categories.CategoryName
FROM dbo.B_Categories INNER JOIN
dbo.B_Forums ON dbo.B_Categories.CategoryID = dbo.B_Forums.CategoryID INNER JOIN
dbo.B_Topics ON dbo.B_Forums.ForumID = dbo.B_Topics.ForumID
WHERE dbo.B_Categories.CategoryID = @CategoryID
);

در در یک Query اینطوری کار کردم
select * from dbo.GetTable(1)
------------------------------------------------------------------------------------------
در مورد Multi-Statement Function اگر ممکن یه توضیح بدین و کاربردش در چه جاهایی

[fakhravari]

یک مثال RETURNS TABLE
ALTER FUNCTION [dbo].[DatesBetween](@num1 int, @num2 int)
RETURNS @dates TABLE (
DateValue int NOT NULL
)
AS
BEGIN
WHILE (@num1 < @num2) BEGIN
INSERT INTO @dates VALUES (@num1);
SET @num1 = @num1 + 1;
END;
RETURN;
END;
استفاده
select * from DatesBetween(0,5)
خروجی
0
1
2
3
4