خانه SQL Server بررسی نحوه ایجاد توابعی با خروجی جدول محور در SQL Server 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انعطافپذیرتر هستند، زیرا میتوانیم از آنها در هرجایی که از جداول استفاده میشود استفاده کنیم. منبع https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/ چه رتبه ای میدهید؟ میانگین ۳ / ۵. از مجموع ۱ اولین نفر باش برچسب ها # SQL Server# Table Value Ffunction# تابع Table Value Ffunction# تابع Table Value Ffunction در SQL Server معرفی نویسنده مقالات 172 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تیم فنی نیک آموز پروفایل نویسنده معرفی محصول مهدی شیشه بری دوره آموزش کوئری نویسی در SQL Server 1.000.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ