آموزش توابع در SQL

مبانی توابع در SQL

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

یک تابع وظیفه ای شبیه به یک ماشین دارد که یک سری ورودی را می گیرد و با انجام عملیات بر روی ورودی های دریافتی (پارامتر یا آرگومان)، خروجی یا خروجی هایی را برمی گرداند. تابع یا Function یک تکه کد است که برای اجرای عملیات مجزا بکار می رود. پس از اجرای تکلیف و عملیات محول شده به آن، می توان به نتایج یا خروجی تابع دسترسی داشت. در SQL نیز توابع، کاربردی مشابه به توابع در دیگر زبان ها دارند، بدین معنی که می توان از آن ها برای اجرای عملیات مختلف بهره گرفت.

در زبان Transact-SQL ، تابع یک شی محسوب می شود. پس از ایجاد یا تعریف آن، تابع در پایگاه داده ذخیره می شود، سپس می توان آن را در صورت نیاز و هر زمان که لازم بود صدا زده و اجرا کرد.

ایجاد پایگاه داده ی جدید

1. رایانه را راه اندازی کرده و وارد سیستم شوید.
2. محیط Microsoft SQL Server را راه اندازی نموده و در کادر محاوره ای Connect to Server، دکمه ی Connect را کلیک نمایید.
3. در پنجره ی Object Explorer ، گره Databases را باز کنید.
4. به منظور ایجاد یک پایگاه داده ی جدید، بر روی Databases راست کلیک نموده سپس گزینه یNew Database... را انتخاب نمایید.
5. در کادر متن Database Name ، واژه ی IceCreamShop را وارد نمایید.
6. OK را کلیک نمایید.
7. حال به پنجره ی Object Explorer مراجعه کرده، بر روی Databases راست کلیک کنید و گزینه ی Refresh را انتخاب نمایید.
8. گره Databases و به دنبال آن IceCreamShop را باز کنید.
9. در زیر IceCreamShop ، بر روی Security راست کلیک نموده سپس مکان نمای موس را بر روی New قرار دهید. اکنون Schema... را انتخاب کنید.
10. در کادر متن Schema Name ، واژه ی Payroll را به عنوان نام اسکیما وارد کنید.
11. OK را کلیک کنید.

نامگذاری تابع در SQL

همان طور که پیش تر گفته شد، در زبان T-SQL ، تابع حکم یک شی را دارد. از این رو، باید یک اسم برای آن انتخاب کرد. در مقاله ی حاضر از قوانین زیر برای نام گذاری توابع استفاده می کنیم:

1. اسم تابع می بایست با یک "_" یا حرف آغاز شود.
2. پس از انتخاب زیر خط یا حرف به عنوان کاراکتر آغازین اسم تابع، می توان ترکیبی از
3. در اسم تابع، استفاده از کاراکترهای ویژه نظیر !، @، #، $، %، ^، & یا * مجاز نمی باشد.
4. از بکار بردن space نیز در اسم توابع خودداری شود.
5. چنانچه اسم تابع متشکل از چندین واژه است، در آن صورت هر کلمه با حرف بزرگ آغاز می شود.

تعریف تابع در SQL

راه های مختلفی برای اقدام به تعریف یا ایجاد یک تابع وجود دارد:

روش اول:

• در Object Explorer، گره پایگاه داده ی دلخواه را باز کنید. بعد از آن گره Programmatically را باز نمایید. گره Functions را باز کنید. بر روی Scalar-Valued Function راست کلیک کرده و New Scalar-Valued Function... انتخاب کنید.

• کد نمونه برای اسکیما ایجاد خواهد شد:

            -- ================================================
            -- Template generated from Template Explorer using:
            -- Create Scalar Function (New Menu).SQL
            --
            -- Use the Specify Values for Template Parameters 
            -- command (Ctrl-Shift-M) to fill in the parameter 
            -- values below.
            --
            -- This block of comments will not be included in
            -- the definition of the function.
            -- ================================================
            SET ANSI_NULLS ON
            GO
            SET QUOTED_IDENTIFIER ON
            GO
            -- =============================================
            -- Author:  < author,,name>
            -- Create date: < create date, ,>
            -- Description: < description, ,>
            -- =============================================
            CREATE FUNCTION < scalar_function_name, sysname, functionname> 
            (
            -- Add the parameters for the function here
            < @param1, sysname, @p1> < data_type_for_param1, , int>
            )
            RETURNS < function_data_type, ,int>
            AS
            BEGIN
            -- Declare the return variable here
            DECLARE < @resultvar, sysname, @result> < function_data_type, ,int>
            -- Add the T-SQL statements to compute the return value here
            SELECT < @resultvar, sysname, @result> = < @param1, sysname, @p1>
            -- Return the result of the function
            RETURN < @resultvar, sysname, @result>
            END
            GO
        

• سپس می توانید آن را مطابق نیاز خود ویرایش کنید.

روش دوم:

• یک Query Editor جدید باز کنید. پنجره ی Templates Explorer (در تب View) را باز کرده و گره Function را با کلیک بر روی آن باز کنید. مکان نمای موس را روی Create Scalar Function (New Menu) قرار داده، بر روی آن کلیک کنید، سپس عبارت فوق را در Query Editor جای گذاری نمایید.

روش سوم:

• یک Query Editor جدید باز کرده و شروع به کدنویسی می کنید

برای مثال:

در پنجره ی Object Explorer، بر روی پایگاه داده ی IceCreamShop راست کلیک کرده، سپس بر روی دکمه ی New Query کلیک نمایید و از دستور زیر جهت تعریف تابع استفاده کنید:

            USE IceCreamShop;
            GO
            CREATE FUNCTION Payroll.CalculateWeeklySalary()
            RETURNS Decimal(8, 2)
            AS
            BEGIN
            RETURN 880.44
            END;
            GO
        

برای اجرای دستور مورد نظر، در نوار ابزار SQL Editor، بر روی دکمه ی Execute کلیک نمایید.

در پنجره ی Object Explorer، گره IceCreamShop را باز کرده، سپس گره Programmability را باز نمایید. حال گره Functions و به دنبال آن Scalar-Valued Functions را باز کنید. بی شک متوجه وجود گره CalculateWeeklySalary خواهید شد.

تعیین خروجی تابع در SQL

برای اینکه تابع مفید باشد، باید یک خروجی برگرداند (یا نتیجه ای را تولید کند). در زمان ایجاد تابع، باید نوع مقدار بازگشتی تابع را نیز مشخص کنید. برای این منظور، پس از اسم تابع کلیدواژه ی RETURNS و به دنبال آن نوع داده ی مقدار بازگشتی را مشخص نمایید.

مثال:

            CREATE FUNCTION Payroll.CalculateWeeklySalary()
            RETURNS Decimal(8, 2)
        

تعریف بدنه تابع در SQL

پس از تعریف نوع داده ی مقدار بازگشتی تابع، می توانید یک بدنه برای آن ایجاد کنید. بدنه ی تابع با کلیدواژه ی BEGIN آغاز شده و با کلیدواژه ی END نیز پایان می یابد.

مثال:

            CREATE FUNCTION Payroll.CalculateWeeklySalary()
            RETURNS Decimal(8, 2)
            BEGIN
            END
        

در صورت تمایل، می توانید کلیدواژه ی AS را پیش از کلیدواژه ی BEGIN تایپ کنید:

            CREATE FUNCTION Payroll.CalculateWeeklySalary()
            RETURNS Decimal(8, 2)
            AS
            BEGIN
            END
        

بین دو کلیدواژه ی BEGIN و END، بخشی است که بدنه ی تابع را تشکیل می دهد. در این بخش عملیاتی که تابع انجام می دهد را مشخص می کنید. پس از انجام این کار، درست قبل از کلیدواژه ی END، بایستی مقداری را که تابع بازمی گرداند، مشخص نمایید. برای این منظور ابتدا کلیدواژه ی RETURN و بلافاصله پس از آن عبارت را تایپ می کنید. فرمول:

            CREATE FUNCTION Payroll.CalculateWeeklySalary()
            RETURNS Decimal(8, 2)
            AS
            BEGIN
            RETURN Expression
            END
        

            CREATE FUNCTION GetFullName()
            RETURNS nvarchar(100)
            AS
            BEGIN
            RETURN N'Doe, John'
            END
        

تعریف متغیرهای محلی تابع در SQL

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

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

            DECLARE @Number1 int
        

در مثال Payroll.CalculateWeeklySalary() برای تعریف متغیر های محلی به صورت زیر عمل میکنیم:

            CREATE FUNCTION Payroll.CalculateWeeklySalary()
            RETURNS Decimal(8, 2)
            AS
            BEGIN
            DECLARE
            @HourlySalary Decimal(8, 2),
            @WeeklyHours Real,
            @FullName varchar(100);
            SET @HourlySalary = 24.15;
            SET @WeeklyHours = 42.50;
            RETURN @HourlySalary * @WeeklyHours
            END;
            GO
        

ورودی های تابع در SQL

یک تابع برای اینکه بتواند وظیفه ی محول شده را به انجام برساند، به مقادیری نیاز دارد. به بیانی دیگر، هنگامی که تابع جدید ایجاد می کنیم، علاوه بر متغیرهای محلی، می خواهیم کدی که تابع را صدا می زند، مقادیر لازم برای اجرای عملیات یا تکلیف محول شده را ارائه نماید.

فرض کنید می خواهیم تابعی تعریف کنیم که آدرس ایمیل کارکنان را زمانی که کاربر اسم و اسم خانوادگی را وارد می کند، فراهم نماید. به هنگام ایجاد تابع، هیچ اطلاعی از اسم کارکنان، از جمله آن هایی که هنوز استخدام نشده اند، نداریم. در این مورد به خصوص، می توان کل تابع را تعریف کرده، اما یک یا چندplaceholder یا مکان نگهدار برای مقادیری که در زمان فراخوانی تابع فراهم می شود، درنظر گرفت. مقدار خارجی که در اختیار تابع قرار داده می شود، پارامتر خوانده می شود. یک تابع می تواند (ممکن است) چندین پارامتر دریافت کند (بیش از یک پارامتر داشته باشد). از اینرو در زمان ایجاد تابع جدید، بایستی مشخص کنید چه تعداد و چه پارامتری (از چه نوعی) می خواهید به آن ارسال کنید.

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

مثال:

            CREATE FUNCTION Payroll.CalculateWeeklySalary(@Number1 Decimal(6,2))
        

زمانی که تابع پارامتر ورودی می پذیرد، می توانید آن پارامتر را در بدنه ی تابع بکار ببرید، گویا کاملا از مقدار آن اطلاع دارید، البته به شرط اینکه نوع مقدار را در نظر داشته باشید.

مثال:

            CREATE FUNCTION Payroll.CalculateWeeklySalary(@Number1 Decimal(6,2))
            RETURNS Decimal(6,2)
            BEGIN
            RETURN @Number1 + 1450
            END
        

تعریف تابع با چند ورودی

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

            CREATE FUNCTION Payroll.CalculateWeeklySalary(@WeeklyHours Decimal(6,2),@HourlySalary SmallMoney)
            RETURNS Decimal(8, 2)
            AS  
            BEGIN
            DECLARE @Weekly SmallMoney
            SELECT  @Weekly = @WeeklyHours * @HourlySalary
            RETURN @Weekly
            END;
            GO
        

ورودی های پیش فرض

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

مثال:

            USE IceCreamShop;
            GO
            CREATE FUNCTION CalculateTaxAmount(@Price money, @Rate decimal(6, 2))
            RETURNS decimal(6, 3)
            AS
            BEGIN
            DECLARE @ResultVar money
            SELECT @ResultVar = @Price * @Rate / 100
            RETURN @ResultVar
            END
            GO
        

مثالی از فراخوانی تابع:

            USE IceCreamShop;
            GO
            PRINT dbo.CalculateTaxAmount(140.00, 5.75);
            PRINT dbo.CalculateTaxAmount(195.95, 5.75);
            PRINT dbo.CalculateTaxAmount(250.00, 7.55);
            PRINT dbo.CalculateTaxAmount(125.95, 5.75);
            GO
        

خروجی زیر را تولید می کند:

            8.05
            11.27
            18.88
            7.24
        

اگر تابعی بارها با آرگومانی با مقدار یکسان (آرگومانی که هر بار مقداری یکسان به آن ارسال می شود) فراخوانی می شود، می توانید یک مقدار پیش فرض برای آن آرگومان مشخص کنید. در زمان صدا زدن تابع، می توان مقدار آرگومان را حذف کرد.

مثال:

            USE IceCreamShop;
            GO
            CREATE FUNCTION CalculateTaxAmount(@Price money, @Rate decimal(6, 2) = 5.75)
            RETURNS decimal(6, 2)
            AS
            BEGIN
            DECLARE @ResultVar money
            SELECT @ResultVar = @Price * @Rate / 100
            RETURN @ResultVar
            END
            GO
        

در زمان فراخوانی تابعی که دارای آرگومانی با مقدار پیش فرض می باشد، دیگر نیازی به ارسال مقدار به آن آرگومان نیست. البته در صورت تمایل، ارسال مقدار به آن مانعی ندارد. بنابراین، به هنگام فراخوانی چنین تابعی، در جای (placeholder) آرگومان، کافی است کلیدواژه ی DEFAULT را تایپ نمایید.

مثال:

            USE IceCreamShop;
            GO
            PRINT dbo.CalculateTaxAmount(140.00, DEFAULT);
            PRINT dbo.CalculateTaxAmount(195.95, 5.75);
            PRINT dbo.CalculateTaxAmount(250.00, 7.55);
            PRINT dbo.CalculateTaxAmount(125.95, default);
            GO
        

فراخوانی تابع در SQL

پس از ایجاد تابع، فقط از مقدار بازگشتی یا خروجی آن استفاده می کنیم. استفاده از تابع و فراخوانی آن هر دو یک معنی را دارند برای این منظور، اسم Schema ای که پایگاه داده به آن تعلق دارد را مشخص کرده، عملگر نقطه را درج نمایید، سپس اسم تابع و بلافاصله پس از آن پرانتز را درج کنید. فرمول مورد استفاده به ترتیب زیر می باشد:

            SchemaName.FunctionName();
        

همچنین می توانید اول اسم پایگاه داده ای که تابع در آن ایجاد شده را درج نمایید. فرمول:

            DatabaseName.SchemaName.FunctionName();
        

می توانید خروجی یا مقدار بازگشتی تابع را مطابق میل خود مورد استفاده قرار دهید. به عنوان مثال، می توانید به وسیله ی کلیدواژه ی PRINT و SELECT مقدار تابع را در Query Editor نمایش دهید. در زیر مثالی را مشاهده می کنید که در آن تابع CalculateWeeklySalary() صدا زده می شود:

            PRINT IceCreamShop.Payroll.CalculateWeeklySalary();
        

برای فراخوانی تابع، در پنجره Object Explorer ، گره Functions را در زیر گره پایگاه داده مورد نظر باز کنید. بر روی نام تابع مورد نظر راست کلیک کنید. مکان نما (موس) را روی گزینه "Script Function As" و سپس "SELECT To" ببرید.گزینه "New Query Editor Window" را انتخاب کنید.

با انجام این مراحل، یک پنجره جدید Query Editor باز می‌شود که کد تابع در آن قرار دارد. حال شما با فشار دادن کلید F5 میتوانید کد را اجرا و تابع را فراخوانی کنید.

در روش دیگر، یک صفحه New Query جدید ایجاد کرده و دستور زیر را وارد میکنیم:

            USE IceCreamShop;
            GO
            PRINT IceCreamShop.Payroll.CalculateWeeklySalary();
            GO
        

سپس با زدن F5 دستور را اجرا میکنیم:

برای مشخص کردن یک اسم برای ستونی که قرار است مقدار بازگشتی تابع را در خود ذخیره کند، فراخوانی تابع را به صورت زیر ویرایش کنید:

            SELECT IceCreamShop.Payroll.CalculateWeeklySalary()
            AS [Weekly Salary];
            GO
        

دکمه ی F5 را برای اجرای دستور فشار دهید:

فراخوانی تابع دارای ورودی

زمانی که تابعی را فراخوانی می کنید که یک پارامتر ورودی دارد، بایستی مقداری برای آن تابع فراهم کنید. برای این منظور، مقدار پارامتر را داخل پرانتزهای تابع مورد نظر درج نمایید. در مثال زیر "488" وروردی تابع Addition() میباشد:

در زمان فراخوانی تابعی که چندین پارامتر ورودی می گیرد، داخل پرانتزهای تابع، به ازای هر پارامتر یک مقدار ارائه دهید (به هر پارامتر یک مقدار ارسال نمایید)، البته به همان ترتیبی که در پرانتز تابع لیست شده است. برای مثال:

        DECLARE @Hours Decimal(5,2),
        @Hourly SmallMoney
    SELECT  @Hours = 42.50
    SELECT  @Hourly = 18.62
    SELECT  'Hermine Singh' As [Employee Name],
        @Hours As [Weekly Hours],
        @Hourly As [Hourly Salary],
        RealEstate1.Payroll.CalculateWeeklySalary(@Hours, @Hourly)
        AS [Weekly Salary];
    GO

کلید F5 را فشار داده تا مجموعه دستورات اجرا شود.

نگهداشت تابع

به این دلیل که زبان T-SQL با تابع به عنوان یک شی برخورد می کند، تابع مانند شی نیاز به نگهداشت دارد. عملیاتی که در راستای نگهداشت تابع انجام می دهیم عبارت است از تغییر نام، ویرایش یا حذف تابع.

ویرایش نام تابع در SQL

پس از ایجاد و اجرای تابع، خواهید دید که تابع مورد نظر در گره Scalar-Valued Functions با اسمی که به آن تخصیص داده اید، ذخیره می شود. در صورت تمایل می توانید اسم تابع را تغییر دهید در عین حال کارایی و عملکرد آن را حفظ کنید. به منظور ویرایش اسم تابع، به پنجره ی Object Explorer مراجعه کرده و بر روی تابع مورد نظر راست کلیک کنید، سپس گزینه ی Rename را انتخاب نمایید. اکنون اسم دلخواه را تایپ کرده و کلید Enter را فشار دهید.

حذف تابع در SQL

پس از ایجاد تابع و بهره وری از آن، می توانید تابع مورد نظر را حذف کنید. در روشی ساده برای حذف تابع، در پنجره ی Object Explorer تابع مورد نظر را در زیر گره Scalar-Valued Functions، یافته و بر روی آن راست کلیک کنید و سپس گزینه ی Delete را انتخاب نمایید. در پی این کار کادر محاوره ای Delete Object نمایان می شود. اگر همچنان قصد حذف تابع را دارید، روی دکمه ی OK کلیک کنید و در غیر این صورت Cancel را انتخاب نمایید.

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

1. در Query Editor، دستور یا عبارت کلیدی DROP FUNCTION و به دنبال آن اسم تابع را درج نمایید و سپس دستور ذکر شده را اجرا کنید.
2. در پنجره ی Object Explorer، بر روی اسم تابع راست کلیک کرده، مکان نمای موس را روی Script Function Asو سپس, DROP To قرار داده و بعد گزینه ی New Query Editor Window را کلیک کنید.
3. یک پنجره ی Query Editor جدید مربوط به پایگاه داده ای که دربردارنده ی تابع مورد نظر است باز کنید. پنجره ی Templates Explorer را نمایش داده، گره Function را باز نمایید. گره Drop Function را کشیده و در محیط Query Editor تهی جای گذاری نمایید.

---