بررسی نحوه ایجاد توابعی با خروجی جدول محور در SQL Server

نوشته شده توسط: تیم فنی نیک آموز

۰۸ اسفند ۱۴۰۰

زمان مطالعه: 10 دقیقه

مقدمه

در این مقاله می خواهیم به بررسی نحوه ایجاد توابعی با خروجی جدول محور در SQL Server را مورد بررسی قرار دهیم

در این آموزش، نحوه استفاده از تابع table value function شامل تابع با ارزش value table functionو توابع multiple statement value function را در قالب مثال یاد خواهید گرفت

تعریف Table Value Function در SQL Server چیست؟

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

ایجاد یک Table Value Function

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

ابتدا با دستور use دیتابیس bike store را فراخوانی می کنیم:

USE BikeStores
GO

حال در ادامه اگر تابعی به نام udfProductInYear وجود داشته باشد برای این منظور ابتدا این تابع را حذف می کنیم
DROP FUNCTION IF EXISTS udfProductInYear;

حال درادامه با استفاده از دستور create function تابعی به نام udfProductInYear که شامل متغیر@model year از نوع عدد صحیح یا integer بوده است را می نویسیم.

برای این منظور کوئری زیر را می نویسیم:

--create value table function
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

سینتکس شبیه به چیزی است که یک تابع تعریف شده توسط کاربر ایجاد می کند.

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

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

هنگامی که تابع table value function ایجاد کردید می توانید آن را در قسمت programability> function> table value functionهمانطور که در تصویر زیر نشان داده شده است، پیدا کنید:

تابع بالا مجموعه نتایج یک دستور SELECT را برمی‌گرداند، بنابراین به عنوان in line table value function شناخته می شود برای این منظور کوئری زیر را می نویسیم

در واقع با استفاده از دستور select رکوردهایی را از اسکمیای Production جدول Product می خواهیم که مدل سال تولید آن ها برابر با ۲۰۱۷ باشد برای این منظور کوئری زیر را می نویسیم:

SELECT *
FROM udfProductInYear(2017)

خروجی کوئری فوق رکوردهایی از جدول Product از اسکیمای Production را نشان می دهد که سال تولید یا فیلد model_year آن ها برابر با ۲۰۱۷ باشد.

در این مثال، محصولاتی را انتخاب کردیم که سال مدل آنها ۲۰۱۷ است.

همچنین می توانید تعیین کنید که کدام ستون ها از تابع table value function برگردانده شوند:

حال در ادامه با استفاده از تابع udfProductInYear رکوردهایی را می خواهیم که سال تولید آن ها برابر با ۲۰۱۸ باشد و تنها فیلدهای Product_name (نام محصول) و List_price (لیست قیمت) را می خواهیم نمایش دهیم.

در واقع table value function می توان همانند table در دستورات select استفاده کرد برای این منظور کوئری زیر را می نویسیم:

SELECT
product_name,
list_price
FROM
udfProductInYear(2018);

خروجی کوئری فوق مطابق تصویر زیر می باشد.

اصلاح Table Value Function

برای تغییر یک table value function، به جای کلمه کلیدی CREATE از ALTER استفاده می کنید. بقیه قسمت ها نیز هم همینطور است.

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

حال در ادامه با استفاده از کلمه کلیدی aterfunction در تابع فعلی udfProductInYear تغییراتی می دهیم به صورتیکه تابعی به نام udfProductInYear ایجاد می کنیم که شامل متغیرهای سال شروع و سال پایان تولید محصولات باشد و نوع متغیرهای سال های شروع و پایان از نوع int تعیین می کنیم.

در ادامه از کلمات کلیدی return table as , return استفاده کرده کارکرد تابع udfProductInYear به این صورت هست که فیلدهای product_name,model_year,list_price از اسکیمای Production جدول Product استخراج کرده به شرطی که رکوردهایی را می خواهیم که سال تولید آن ها ما بین متغیر سال شروع و متغیر سال پایان تولید باشد برای این منظور کوئری زیر را می نویسیم:

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 را فراخوانی می‌کند تا محصولاتی را که سال‌های مدل آن‌ها بین سال‌های ۲۰۱۷ و ۲۰۱۸ است به دست آورد:

حال در ادامه می خواهیم کوئری بنویسیم که فیلدهای نام محصول و سال تولید محصول و قیمت واحد از تابع جدول وار udfProductInYear برای رکوردهایی که سال تولید آغازین و سال تولید پایانی آن ها ما بین ۲۰۱۷ تا ۲۰۱۸ باشد نمایش دهد و مرتبسازی براساس فیلد نام محصول یا product_name باشد.

برای این منظور کوئری زیر را می نویسیم:

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

خروجی کوئری فوق مطابق تصویر زیر می باشد.

ایجاد توابعی از نوع Multi Statement Table Value Function

یک mutiple statement table valueیا MSTVF یک تابع با ارزش جدول است که نتیجه چند عبارت را برمی گرداند.

تابع multi-statement-table-valued بسیار مفید است زیرا می توانید چندین پرس و جو را در تابع اجرا کنید و نتایج را در جدول برگشتی جمع آوری کنید.

برای تعریف یک multi-statement-table-valued ، از یک متغیر جدول به عنوان مقدار بازگشتی استفاده می کنید. در داخل تابع، یک یا چند کوئری را اجرا می کنید و داده ها را در این متغیر جدول قرار می دهید.

تابع udfcontacts() زیر کارکنان و مشتریان را در یک لیست مخاطب ترکیب می کند:

با استفاده از دستور create function یک تابع به نام udfcntacts() که خروجی این تابع جدولی هست که شامل فیلدهای first_name,last_name,email,phone,contact_type می باشد حال در ادامه با استفاده از دستور insert in to select فیلدهای firstname,lastname,email,phone,staff, از اسکیمای sales جدول staffs داخل جدول @contacts می ریزیم

با استفاده از دستور insert in to select فیلدهای first name,last name, email,phone,customer از اسکیمای sales جدول customers به درون جدول @contacts می ریزیم برای این منظور کوئری زیر را اجرا می کنیم:

--create mutiple statement table value function
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 که از نوع multi-statement-table-valued را نشان می دهد:

SELECT * FROM udfContacts()

خروجی کوئری فوق مطابق تصویر زیر می باشد.

زمان استفاده از توابع با ارزش جدول

ما معمولاً از توابع table value functionنماهای پارامتری استفاده می کنیم. در مقایسه با store procedures، توابع table value functionانعطاف‌پذیرتر هستند، زیرا می‌توانیم از آنها در هرجایی که از جداول استفاده می‌شود استفاده کنیم.