إنشاء وظائف محددة من قبل المستخدم

مكتمل

تشبه الوظائف المعرفة من قبل المستخدم (UDF) الإجراءات المخزنة من حيث تخزينها بشكل منفصل عن الجداول في قاعدة البيانات. تقبل هذه الدالات المعلمات، وتنفذ إجراء، ثم ترجع نتيجة الإجراء كقيمة واحدة (عددية) أو مجموعة نتائج (قيمة الجدول). يمكنك بعد ذلك استخدام الدالة بدلا من جدول عند كتابة عبارة SELECT. تهدف الدالات المعرفة من قبل المستخدم إلى إجراء العمليات الحسابية واستخدام تلك النتيجة ضمن عبارة أخرى. بينما يمكن للإجراءات المخزنة تغليف الدالة والعصا، وحتى تعديل البيانات داخل قاعدة البيانات.

سنراجع ثلاثة أنواع من الوظائف المعرفة من قبل المستخدم. لمزيد من التفاصيل عن الوظائف المختلفة، راجع الوثائق المرجعية T-SQL.

دالات قيم الجدول المضمنة

الدالات المضمنة ذات القيمة الجدولية (TVF) هي أبسط وظيفة تم إنشاؤها استنادا إلى عبارة SELECT، وهي الخيار المفضل للأداء.

في المثال التالي، يتم إنشاء دالة ذات قيمة جدول مع معلمة إدخال ل unitprice.

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost; 

عند تشغيل الدالة ذات القيمة الجدولية بقيمة المعلمة، سيتم إرجاع جميع المنتجات التي لها سعر وحدة أكثر من هذه القيمة.

تستخدم التعليمات البرمجية التالية الدالة ذات القيمة الجدولية بدلا من جدول.

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

دالات ذات قيمة جدول متعددة العبارات

على عكس TVF المضمن، يمكن أن تحتوي الدالة متعددة العبارات ذات القيمة الجدولية (MSTVF) على أكثر من عبارة واحدة ولها متطلبات بناء جملة مختلفة.

لاحظ كيف في التعليمات البرمجية التالية، نستخدم BEGIN/END بالإضافة إلى RETURN:

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

بمجرد الإنشاء، يمكنك الرجوع إلى MSTVF بدلا من جدول تماما كما هو الحال مع الدالة المضمنة السابقة أعلاه. يمكنك أيضا الرجوع إلى الإخراج في عبارة FROM وربطه بجداول أخرى.

SELECT *
FROM Sales.mstvf_OrderStatus();

الاعتبارات الخاصة بالأداء

يتعذر على "محسن الاستعلامات" تقدير عدد الصفوف التي سترجع لدالة ذات قيمة جدول متعددة العبارات، ولكن يمكن ذلك باستخدام الدالة المضمنة ذات القيمة الجدولية. لذلك، استخدم TVF المضمن عندما يكون ذلك ممكنا للحصول على أداء أفضل. إذا لم تكن بحاجة إلى الانضمام إلى MSTVF مع جداول أخرى و/أو كنت تعرف أن النتيجة ستكون فقط عدد قليل من الصفوف، فإن تأثير الأداء ليس متعلقا. إذا كنت تتوقع مجموعة نتائج كبيرة وتحتاج إلى الانضمام إلى جداول أخرى، ففكر بدلا من ذلك في استخدام جدول مؤقت لتخزين النتائج ثم الانضمام إلى الجدول المؤقت.

في إصدارات SQL Server 2017 والإصدارات الأحدث، قدمت Microsoft ميزات لمعالجة الاستعلام الذكي لتحسين أداء MSTVF. راجع المزيد من التفاصيل حول ميزات معالجة الاستعلام الذكي في الوثائق المرجعية T-SQL.

الدالات العددية المعرفة من قبل المستخدم

ترجع الدالة العددية المعرفة من قبل المستخدم قيمة واحدة فقط على عكس الدالات ذات القيمة الجدولية وبالتالي غالبا ما تستخدم لعبارات بسيطة ومتكررة.

فيما يلي مثال للحصول على سعر قائمة المنتجات لمنتج معين في يوم معين:

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

لهذه الدالة، يجب توفير كلتا المعلمتين للحصول على القيمة. اعتمادا على الدالة، يمكنك سرد الدالة في عبارة SELECT في استعلام أكثر تعقيدا.

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

ربط الدالة بالكائنات المشار إليها

يعد SCHEMABINDING اختياريا عند إنشاء الدالة. عند تحديد SCHEMABINDING، فإنه يربط الدالة بالكائنات المشار إليها، ومن ثم لا يمكن تعديل الكائنات دون تعديل الدالة أيضا. يجب أولا تعديل الدالة أو إسقاطها لإزالة التبعيات قبل تعديل الكائن.

تتم إزالة SCHEMABINDING إذا حدث أي مما يلي:

• تم إسقاط الدالة
• يتم تعديل الدالة باستخدام عبارة ALTER دون تحديد SCHEMABINDING