آموزش جامع SQL Server (جلسه ۲۴: توابع تعریف شده توسط کاربر)
1398/04/05 17:18 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۲۴: توابع تعریف شده توسط کاربر)

در این جلسه راجع به توابع تعریف شده توسط کاربر (user-defined) خواهید آموخت، همچنین با توابع دارای مقدار اسکالر که فقط یک مقدار بازمی‌گردانند و تابع دارای مقدار جدول که ردیف‌هایی از داده را بازمی‌گرداند، آشنا خواهید شد.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

توابع ایجاد شده توسط کاربر در SQL Server به شما کمک می‌کنند تا با کپسوله‌سازی منطق‌های کسب و کار پیچیده و ایجاد قابلیت استفاده مجدد از آن‌ها در هر query، فرآیند توسعه نرم‌افزار خود را تسهیل کنید.

در این جلسه مباحث زیر مورد بررسی قرار می‌گیرند:

  • توابع اسکالر تعریف شده توسط کاربر: توابع اسکالر تعریف شده توسط کاربر را پوشش می‌دهد. این توابع به شما امکان می‌دهند تتا فرمول‌ها یا منطق‌های کسب و کار پیچیده را کپسوله سازی کنید و در همه queryها مجدداً از آن‌ها استفاده کنید.

  • متغیرهای جدول: چگونگی استفاده از متغیرهای جدول به عنوان یک مقدار بازگشتی از توابع تعریف شده توسط کاربر را خواهید آموخت.

  • توابع دارای مقدار جدول: شما را با تابع دارای مقدار جدول تک خطی و تابع دارای مقدار جدول چند کدی آشنا می‌کند تا بتوانید توابع تعریف شده توسط کاربر ایجاد کنید که داده‌های نوع‌های جدول را بازگردانند.

  • حذف توابع تعریف شده توسط کاربر: چگونگی حذف یک یا چند تابع تعریف شده توسط کاربر موجود از پایگاه داده را توضیح می‌دهند.

در ادامه تمام این موارد را به صورت مفصل توضیح خواهیم داد. با ما همراه باشید!

توابع اسکالر در SQL Server

تابع اسکار در SQL Server یک یا چند پارامتر می‌گیرد و یک مقدار بازمی‌گرداند.

توابع اسکالر به شما کمک می‌کنند کدتان را ساده سازی کنید. برای مثال، ممکن است یک محاسبه پیچیده داشته باشید که در queryهای زیادی از آن استفاده شده است.

به جای درج فرمول در همه queryها، می‌توانید یک تابع اسکالر ایجاد کنید که فرمول را کپسوله‌سازی کرده و از آن در queryها استفاده کند.

ایجاد یک تابع اسکالر

برای ایجاد یک تابع اسکالر، می‌توانید از کد CREATE FUNCTION به شکل زیر استفاده کنید:

CREATE FUNCTION [schema_name.]function_name (parameter_list)
RETURN data_type AS
BEGIN
    statements
    RETURN value
END

در این syntax:

  • اول، نام تابع بعد از کلیدواژه‌های CREATE FUNCTION مشخص شده است. نام شما اختیاری است. اگر صریحاً نام شما را مشخص نکنید، SQL Server به صورت پیش‌فرض از dbo استفاده می‌کند.

  • دوم، لیستی از پارامترها در بین پرانتزها بعد از نام تابع مشخص شده‌اند.

  • سوم، نوع داده از مقدار بازگشتی در کد RETURN مشخص شده است.

  • چهارم، داخل بدنه تابع، از دستور RETURN باید استفاده شود تا یک مقدار بازگردانده شود.

مثال زیر یک تابع ایجاد می‌کند که فروش خالص را بر اساس کمیت، قیمت و تخفیف محاسبه می‌کند:

CREATE FUNCTION sales.udfNetSale(
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2)
)
RETURNS DEC(10,2)
AS 
BEGIN
    RETURN @quantity * @list_price * (1 - @discount);
END;

سپس، می‌توانید از این کد برای محاسبه فروش خالص هر سفارش فروش در جدول order_items از پایگاه داده نمونه Bikestores استفاده کنید.

جدول order_items

بعد از ایجاد تابع اسکالر، می‌توانید آن را در آدرس programmability > Functions > Scalar-valued Functions که در تصویر زیر مشاهده می‌کنید بیابید:

بعد از ایجاد تابع اسکالر، می‌توانید آن را در آدرس programmability > Functions > Scalar-valued Functions  بیابید

فراخوانی یک تابع اسکالر

فراخوانی تابع اسکالر دقیقاً همانند فراخوانی یک تابع معمولی است. برای مثال، کد زیر چگونگی فراخوانی تابع udfNetSale را نشان می‌دهد:

SELECT 
    sales.udfNetSale(10,100,0.1) net_sale

خروجی به این شکل است:

نتایج

مثال زیر، چگونگی استفاده از تابع Sales.udfNetSale برای به دست آوردن فروش خالص سفارش‌های فروش در جدول order_items را نشان می‌دهد:

SELECT 
    order_id, 
    SUM(sales.udfNetSale(quantity, list_price, discount)) net_amount
FROM 
    sales.order_items
GROUP BY 
    order_id
ORDER BY
    net_amount DESC;

تصویر زیر بخشی از خروجی را نمایش می‌دهد:

نتایج

تغییر دادن یک تابع اسکالر

برای تغییر دادن یک تابع اسکالر، می‌توانید به جای کلیدواژه CREATE از دستور ALTER استفاده کنید. بقیه کدها دست نخورده باقی می‌مانند:

ALTER FUNCTION [schema_name.]function_name (parameter_list)
    RETURN data_type AS
    BEGIN
        statements
        RETURN value
    END

توجه کنید که اگر تابع تعریف شده توسط کاربر وجود ندارد، می‌توانید از دستور CREATE OR ALTER برای ایجاد یک تابع تعریف شده توسط کاربر یا برای تغییر دادن یک تابع اسکالر از پیش موجود استفاده کنید:

CREATE OR ALTER FUNCTION [schema_name.]function_name (parameter_list)
        RETURN data_type AS
        BEGIN
            statements
            RETURN value
        END

حذف یک تابع اسکالر

برای حذف یک تابع اسکالر از پیش موجود، می‌توانید از دستور DROP FUNCTION استفاده کنید:

DROP FUNCTION [schema_name.]function_name;

برای مثال، برای حذف تابع Sales.udfNetSale می‌توانید از کد زیر استفاده کنید:

DROP FUNCTION sales.udfNetSale;

نکات تابع اسکالر در SQL Server

در لیست زیر نکات کلیدی توابع اسکالر را مشاهده می‌کنید:

  • توابع اسکالر را تقریباً می‌توان در همه جا درون کدهای T-SQL به کار برد.

  • توابع اسکالر یک یا چند پارامتر می‌پذیرند، اما فقط یک مقدار بازمی‌گردانند، در نتیجه باید شامل یک دستور RETURN باشند.

  • توابع اسکالر می‌توانند از منطق‌هایی مانند قطعات کد IF یا حلقه‌های WHILE استفاده کنند.

  • توابع اسکالر نمی‌توانند داده‌ها را update کنند. می‌توانند به داده‌ها دسترسی داشته باشند، اما این کار اصلاً توصیه نمی‌شود.

  • توابع اسکالر می‌توانند توابع دیگر را فراخوانی کنند.

به این صورت، چگونگی استفاده از توابع اسکالر برای کپسوله‌سازی فرمول‌ها یا منطق‌های کسب و کار پیچیده و استفاده مجدد از آن‌ها در Queryها را آموختید.

متغیرهای جدول در SQL Server

متغیرهای جدول نوعی متغیر هستند که به شما امکان می‌دهند ردیف‌های داده را در آن‌ها نگهداری کنید، که بسیار شبیه به جدول‌های موقتی هستند.

چگونگی تعریف متغیرهای جدول

برای تعریف یک متغیر جدول، می‌توانید از دستور DECLARE به شکل زیر استفاده کنید:

DECLARE @table_variable_name TABLE (
    column_list
);

در این syntax، نام متغیر جدول در بین کلیدواژه‌های DECLARE و TABLE قرار گرفته است. نام متغیرهای جدول باید با یک علامت @ شروع شوند.

در زیر کلیدواژه TABLE، ساختار متغیر جدول تعریف شده است که شبیه به ساختار یک جدول معمولی است که شامل تعاریف ستون، نوع داده، سایز، ویژگی اختیاری و غیره می‌باشد.

دامنه متغیرهای جدول

مشابه متغیرهای منطقی، متغیرهای جدول نیز در پایان قطعه کد از دامنه خارج می‌شوند.

اگر یک متغیر جدول را در یک stored procedure یا تابع تعریف شده توسط کاربر تعریف کنید، آنگاه بعد از اینکه سیستم از stored procedure یا تابع تعریف شده توسط کاربر خارج شد، متغیر جدول دیگر وجود نخواهد داشت.

مثال متغیر جدول

برای مثال، کد زیر یک متغیر جدول به نام product_table@ تعریف می‌کند که شامل سه ستون است: brand_id، product_name و list_price.

DECLARE @product_table TABLE (
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);

درج داده درون متغیرهای جدول

وقتی متغیر جدول تعریف می‌شود، مقدار آن خالی است. می‌توانید با استفاده از دستور INSERT به متغیرهای جدول ردیف اضافه کنید:

INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;

به دست آوردن داده‌ها از متغیرهای جدول

درست همانند جداول موقتی، می‌توانید با استفاده از دستور SELECT داده‌ها را از متغیرهای جدول به دست آورید:

SELECT
    *
FROM
    @product_table;

توجه کنید که شما باید تمام قطعه کد یا batch را اجرا کنید، وگرنه سیستم ارور می‌دهد:

DECLARE @product_table TABLE (
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);
 
INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;
 
SELECT
    *
FROM
    @product_table;
GO

تصویر زیر بخشی از خروجی را نشان می‌دهد:

نتایج

محدودیت‌های متغیرهای جدول

اول، مجبورید ساختار متغیر جدول را در طول تعریف، مشخص کنید. بر خلاف یک جدول معمولی یا موقتی، نمی‌توانید ساختار متغیر جدول را بعد از تعریف alter کنید.

دوم، آمارها (statistics) به بهینه‌سازی query کمک کرده و باعث می‌شوند طرح اجرایی query خیلی بهتر شود. متأسفانه، متغیرهای جدول شامل آمارها نیستند. در نتیجه، باید از متغیرهای جدول برای نگه داشتن تعداد کمی ردیف استفاده کنید.

سوم، نمی‌توانید از متغیر جدول به عنوان یک پارامتر ورودی یا خروجی مانند نوع‌های داده‌ای دیگر استفاده کنید. به هر حال، می‌توانید یک متغیر جدول را از یک تابع تعریف شده توسط کاربر بازگردانید.

چهارم، نمی‌توانید ایندکس‌های غیر خوشه‌ای برای متغیرهای جدول ایجاد کنید.

به هر حال، از SQL Server 2014 به بعد، متغیرهای جدول بهینه‌سازی شده از نظر حافظه، از طریق تعریف OLTP درون حافظه‌ای جدید که به شما امکان اضافه کردن ایندکس‌های غیر خوشه‌ای به عنوان بخشی از تعریف متغیر جدول را می‌دهد، در دسترس کاربران قرار گرفتند.

پنجم، اگر از متغیر جدول همراه با یک join استفاده می‌کنید، باید برای جدول از Alias استفاده کنید تا بتوانید query را اجرا کنید. برای مثال:

SELECT
    brand_name,
    product_name,
    list_price
FROM
    brands b
INNER JOIN @product_table pt ON p.brand_id = pt.brand_id;

عملکرد متغیرهای جدول

استفاده از متغیرهای جدول در یک stored procedure نسبت به استفاده از یک جدول موقت، ترکیبات کمتری ایجاد می‌کند.

علاوه بر این، یک متغیر جدول از منابع کمتری نسبت به یک جدول موقتی استفاده می‌کند و همچنین locking و logging کمتری نیز دارد.

مشابه جدول موقتی، متغیرهای جدول نیز در پایگاه داده tempdb قرار دارند نه در حافظه.

استفاده از متغیرهای جدول در توابع تعریف شده توسط کاربر

نام تابع تعریف شده توسط کاربر زیر ()ufnSplit است که یک متغیر جدول بازمی‌گرداند.

CREATE OR ALTER FUNCTION udfSplit(
    @string VARCHAR(MAX), 
    @delimiter VARCHAR(50) = ' ')
RETURNS @parts TABLE
(    
idx INT IDENTITY PRIMARY KEY,
val VARCHAR(MAX)   
)
AS
BEGIN
 
DECLARE @index INT = -1;
 
WHILE (LEN(@string) > 0) 
BEGIN 
    SET @index = CHARINDEX(@delimiter , @string)  ;
    
    IF (@index = 0) AND (LEN(@string) > 0)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (@string);
        BREAK  
    END 
 
    IF (@index > 1)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (LEFT(@string, @index - 1));
        
        SET @string = RIGHT(@string, (LEN(@string) - @index));  
    END 
    ELSE
    SET @string = RIGHT(@string, (LEN(@string) - @index)); 
    END
RETURN
END
GO

کد زیر تاب ()udfSplit را فراخوانی می‌کند:

SELECT 
    * 
FROM 
    udfSplit('foo,bar,baz',',');

خروجی به این شکل است:

نتایج

در نتیجه، چگونگی استفاده از متغیرهای جدول را آموختید و فهمیدید که این متغیرها در مقایسه با جدول‌های موقتی مزایا و انعطاف‌پذیری بیشتری دارند.

توابع دارای مقدار جدول در SQL Server

یک تابع با مقدار جدول در واقع یک تابع تعریف شده توسط کاربر است که داده‌های یک نوع جدول را بازمی‌گرداند. نوع بازگشتی یک تابع با مقدار جدول، یک جدول است. در نتیجه، می‌توانید از این توابع همان‌طور که از یک جدول استفاده می‌کنید، استفاده کنید.

ایجاد یک تابع با مقدار جدول

مثال زیر یک تابع با مقدار جدول ایجاد می‌کند که لیستی از محصولات (شامل نام محصول، سال مدل و قیمت) را برای یک سال مدل خاص بازمی‌گرداند:

CREATE FUNCTION udfProductInYear (
    @model_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

Syntax شبیه به syntax یک تابع تعریف شده توسط کاربر است.

دستور RETURNS TABLE مشخص می‌کند که تابع یک جدول را بازمی‌گرداند. همان‌طور که مشاهده می‌کنید، هیچ عبارت BEGIN…END وجود ندارد. کد بالا به زبان ساده، داده‌ها را از جدول production.products به دست می‌آورد.

تابع udfProductInYear یک پارامتر به نام model_year@ از نوع INT را می‌پذیرد. این تابع محصولاتی را بازمی‌گرداند که سال‌های مدل‌ آن‌ها برابر با پارامتر model_year@ باشند.

وقتی تابع دارای مقدار جدول ایجاد شد، می‌توانید آن را در آدرس Programmability > Functions > Table-valued Functions بیابید که در تصویر زیر نیز مشاهده می‌کنید:

می‌توانید آن را در آدرس Programmability > Functions > Table-valued Functions بیابید

 تابع بالا مجموعه نتیجه یک دستور SELECT را بازمی‌گرداند، در نتیجه همچنین به عنوان تابع جدولی تک خطی نیز شناخته می‌شود.

اجرای یک تابع دارای مقدار جدول

برای ایجاد یک تابع دارای مقدار جدول، می‌توانید از دستور FROM از عبارت SELECT استفاده کنید:

SELECT 
    * 
FROM 
    udfProductInYear(2017);

نتایج

در این مثال، ما محصولاتی که سال مدل آن‌ها 2017 است را انتخاب کردیم.

همچنین می‌توانید مشخص کنید کدام ستون‌ها باید از تابع دارای مقدار جدول بازگردانده شوند، همانند کد زیر:

SELECT 
    product_name,
    list_price
FROM 
    udfProductInYear(2018);

بخشی از خروجی به این شکل است:

نتایج

تغییر یک تابع دارای مقدار جدول

برای تغییر یک تابع دارای مقدار جدول، می‌توانید به جای کلیدواژه CREATE از دستور ALTER استفاده کنید. بقیه اسکریپت مثل قبل باقی می‌ماند.

برای مثال، کد زیر تابع udfProductInYear را با تغییر دادن پارامتر موجود و اضافه کردن یک پارامتر دیگر، تغییر می‌دهد:

ALTER FUNCTION udfProductInYear (
    @start_year INT,
    @end_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year BETWEEN @start_year AND @end_year

تابع udfProductInYear حالا محصولاتی که سال مدل آن‌ها بین یک بازه آغاز و پایان یک سال می‌باشد را بازمی‌گرداند.

کد زیر تابع udfProductInYear را فراخوانی می‌کند تا محصولاتی که سال مدل آن‌ها بین 2017 و 2018 است را به دست آورد:

SELECT 
    product_name,
    model_year,
    list_price
FROM 
    udfProductInYear(2017,2018)
ORDER BY
    product_name;

بخشی از خروجی به این شکل است:

نتایج

تابع‌های دارای مقدار جدول چند کدی (MSTVF)

یک تابع دارای مقدار جدول چند کدی یا MSTVF یک تابع دارای مقدار جدول است که نتیجه چندین خط کد را بازمی‌گرداند.

تابع دارای مقدار جدول چند کدی بسیار مفید است، چون می‌توانید چندین query را درون تابع اجرا کرده و نتایج را درون جدول بازگشتی به صورت یکجا نمایش دهید.

برای تعریف یک تابع دارای مقدار جدل چند کدی می‌توانید از یک متغیر جدول به عنوان مقدار بازگشتی استفاده کنید. داخل تابع، یک یا چند query را اجرا کرده  داده‌ها را درون این متغیر جدول وارد می‌کنید.

تابع ()udfContacts زیر، کارمندان و مشتریان را در یک لیست تماس ترکیب می‌کند:

CREATE FUNCTION udfContacts()
    RETURNS @contacts TABLE (
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(255),
        phone VARCHAR(25),
        contact_type VARCHAR(20)
    )
AS
BEGIN
    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Staff'
    FROM
        sales.staffs;
 
    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Customer'
    FROM
        sales.customers;
    RETURN;
END;

کد زیر چگونگی اجرای تابع دارای مقدار جدول چند کدی به نام udfContacts را نشان می‌دهد:

SELECT 
    * 
FROM
    udfContacts();

چه موقع باید از توابع دارای مقدار جدول استفاده کنیم؟

معمولاً از توابع دارای مقدار جدول به عنوان viewهای دارای پارامتر استفاده می‌کنیم. در مقایسه با stored procedureها، توابع دارای مقدار جدول انعطاف‌پذیری بیشتری دارند، چون می‌توانیم از آن‌ها در هر جایی که از جدول استفاده می‌شود، استفاده کنیم.

به این صورت، با تابع دارای مقدار جدول و به خصوص با توابع دارای مقدار جدول تک خطی و توابع دارای مقدار جدول چند کدی یا چند خطی آشنا شدید.

دستور DROP FUNCTION در SQL Server

برای حذف یک تابع ایجاد شده توسط کاربر که به وسیله دستور CREATE FUNCTION ایجاد شده است، می‌توانید از دستور DROP FUNCTION به این شکل استفاده کنید:

DROP FUNCTION [ IF EXISTS ] [ schema_name. ] function_name;

در این syntax:

IF EXISTS

کد اختیاری IF EXISTS به شما اجازه می‌دهد تابع را فقط در صورتی که وجود داشته باشد، حذف کنید. در غیر این صورت، کد هیچ کاری انجام نمی‌دهد. اگر سعی کنید یک تابع ناموجود را بدون به کار بردن کد اختیاری IF EXISTS حذف کنید، سیستم به شما ارور می‌دهد.

Schema_name

Schema_name نام شمایی که تابع تعریف شده توسط کاربر (همان تابعی که می‌خواهید حذف شود) به آن تعلق دارد را مشخص می‌کند. نام شما اختیاری است.

Function_name

Function_name نام تابعی است که می‌خواهید حذف شود.

نکات

اگر تابعی که می‌خواهید حذف شود توسط viewها یا توابع دیگری که با استفاده از کد اختیاری WITH SCHEMABINDING ایجاد شده‌اند مورد اشاره یا رفرنس قرار گرفته باشد، آنگاه دستور DROP FUNCTION عمل نمی‌کند.

علاوه بر این، اگر ویژگی‌هایی مانند CHECK یا DEFAULT و ستون‌های محاسبه‌ای وجود دارند که به تابع اشاره می‌کنند، آنگاه کد DROP FUNCTION باز هم عمل نمی‌کند.

برای حذف چند تابع تعریف شده توسط کاربر، باید یک لیست مجزا شده با ویرگول از اسامی توابع را پس از دستور DROP FUNCTION وارد کنیم:

DROP FUNCTION [IF EXISTS] 
    schema_name.function_name1, 
    schema_name.function_name2,
    ...;

مثال DROP FUNCTION در SQL Server

باز هم برای مثال از جدول order_items از پایگاه داده نمونه Bikestores استفاده می‌کنیم:

جدول order_items

یک مثال ساده از دستور DROP FUNCTION در SQL Server

مثال زیر تابعی ایجاد می‌کند که میزان تخفیف را از کمیت، قیمت و درصد تخفیف محاسبه می‌کند:

CREATE FUNCTION sales.udf_get_discount_amount (
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2) 
)
RETURNS DEC(10,2) 
AS 
BEGIN
    RETURN @quantity * @list_price * @discount
END

برای حذف تابع sales.udf_get_discount_amount، می‌توانید از دستور زیر استفاده کنید:

DROP FUNCTION IF EXISTS sales.udf_get_discount_amount;
مثال استفاده از DROP FUNCTION همراه با SCHEMABINDING در SQL Server

مثال زیر تابع sales.udf_get_discount_amount را با استفاده از کد اختیاری WITH SCHEMABINDING دوباره ایجاد می‌کند:

CREATE FUNCTION sales.udf_get_discount_amount (
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2) 
)
RETURNS DEC(10,2) 
WITH SCHEMABINDING
AS 
BEGIN
    RETURN @quantity * @list_price * @discount
END

و کد زیر یک view ایجاد می‌کند که از تابع sales.udf_get_discount_amount استفاده می‌کند:

CREATE VIEW sales.discounts
WITH SCHEMABINDING
AS
SELECT
    order_id,
    SUM(sales.udf_get_discount_amount(
        quantity,
        list_price,
        discount
    )) AS discount_amount
FROM
    sales.order_items i
GROUP BY
    order_id;

حالا، اگر سعی کنید تابع sales.udf_get_discount_amount را حذف کنید، سیستم ارور می‌دهد:

DROP FUNCTION sales.udf_get_discount_amount;

SQL Server ارور زیر را می‌دهد:

Cannot DROP FUNCTION 'sales.udf_get_discount_amount' because it is being referenced by object 'discounts'.

اگر می‌خواهید تابع را حذف کنید، باید ابتدا view به نام sales.discounts را حذف کنید:

DROP VIEW sales.discounts;

و سپس تابع را حذف کنید:

DROP FUNCTION sales.udf_get_discount_amount;

به این شکل، چگونگی استفاده از دستور DROP FUNCTION در SQL Server برای حذف یک یا چند تابع ایجاد شده توسط کاربر را آموختید.

خلاصه

در این جلسه به صورت کامل توابع ایجاد شده توسط کاربر و کار با آن‌ها را بررسی کردیم. با توابع اسکالر، متغیرهای جدول، توابع دارای مقدار جدول و روش حذف توابع آشنا شدید و مثال حل کردیم.

در جلسه بعدی به بررسی triggerها خواهیم پرداخت.

با MUG همراه باشید!

 منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات