Stored Procedures

مثال: محاسبه مالیات بر درآمد فردی

فرض کنید یک پایگاه داده داریم که اطلاعات افراد شامل درآمد سالانه، تعداد افراد تحت تکفل و … را ذخیره می‌کند. می‌خواهیم رویه‌ای ذخیره شده بنویسیم که بر اساس قوانین مالیاتی، مالیات بر درآمد هر فرد را محاسبه کند.

ساختار جدول افراد:

CREATE TABLE Persons (
    PersonID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    AnnualIncome DECIMAL(18,2),
    NumberOfDependents INT
);

رویه ذخیره شده برای محاسبه مالیات:

CREATE PROCEDURE CalculateIncomeTax
    @PersonID INT
AS
BEGIN
    DECLARE @TaxRate DECIMAL(18,2), @TaxableIncome DECIMAL(18,2);

    -- محاسبه درآمد مشمول مالیات با توجه به تعداد افراد تحت تکفل
    SELECT @TaxableIncome = AnnualIncome - (NumberOfDependents * 4000000) 
    FROM Persons
    WHERE PersonID = @PersonID;

    -- تعیین نرخ مالیات بر اساس درآمد مشمول مالیات
    IF @TaxableIncome <= 10000000
        SET @TaxRate = 0.1;
    ELSE IF @TaxableIncome <= 20000000
        SET @TaxRate = 0.15;
    ELSE
        SET @TaxRate = 0.2;

    -- محاسبه مالیات
    DECLARE @IncomeTax DECIMAL(18,2) = @TaxableIncome * @TaxRate;

    -- نمایش نتیجه
    PRINT 'مالیات بر درآمد برای فرد با شناسه ' + CAST(@PersonID AS NVARCHAR) + ' برابر است با: ' + CAST(@IncomeTax AS NVARCHAR);
END;

فراخوانی رویه:

EXEC CalculateIncomeTax 123; -- برای محاسبه مالیات فرد با شناسه ۱۲۳

توضیح مثال:

1. پارامتر ورودی: رویه یک پارامتر به نام @PersonID می‌گیرد که شناسه فرد مورد نظر را مشخص می‌کند.
2. محاسبه درآمد مشمول مالیات: با کسر مبلغ معافیت برای هر فرد تحت تکفل، درآمد مشمول مالیات محاسبه می‌شود.
3. تعیین نرخ مالیات: بر اساس درآمد مشمول مالیات، نرخ مالیات تعیین می‌شود. این قسمت را می‌توان با توجه به قوانین مالیاتی پیچیده‌تر سفارشی کرد.
4. محاسبه مالیات: با ضرب درآمد مشمول مالیات در نرخ مالیات، مقدار مالیات محاسبه می‌شود.
5. نمایش نتیجه: نتیجه محاسبه شده به صورت یک پیام به کاربر نمایش داده می‌شود.

نکات:

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

کاربردهای دیگر رویه‌های ذخیره شده در محاسبات عددی:

• محاسبه آمار توصیفی: میانگین، واریانس، انحراف استاندارد و …
• محاسبه احتمال: محاسبه احتمال وقوع رویدادها
• حل معادلات دیفرانسیل: استفاده از روش‌های عددی برای حل معادلات دیفرانسیل
• شبیه‌سازی سیستم‌ها: شبیه‌سازی رفتار سیستم‌های پیچیده

مزایای استفاده از رویه‌های ذخیره شده در محاسبات عددی:

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

مثال دیگری از رویه‌های ذخیره شده: محاسبه حقوق کارمندان

فرض:

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

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

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    BaseSalary DECIMAL(18,2),
    OvertimeHours INT,
    OvertimeRate DECIMAL(18,2)
);

رویه ذخیره شده برای محاسبه حقوق:

CREATE PROCEDURE CalculateSalary
    @EmployeeID INT
AS
BEGIN
    DECLARE @OvertimePay DECIMAL(18,2), @TotalSalary DECIMAL(18,2);

    -- محاسبه اضافه کاری
    SELECT @OvertimePay = OvertimeHours * OvertimeRate
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    -- محاسبه حقوق کل
    SELECT @TotalSalary = BaseSalary + @OvertimePay
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    -- نمایش نتیجه
    PRINT 'حقوق کل کارمند با شناسه ' + CAST(@EmployeeID AS NVARCHAR) + ' برابر است با: ' + CAST(@TotalSalary AS NVARCHAR);
END;

فراخوانی رویه:

EXEC CalculateSalary 456; -- برای محاسبه حقوق کارمند با شناسه ۴۵۶

توضیح مثال:

1. پارامتر ورودی: @EmployeeID شناسه کارمند مورد نظر را دریافت می‌کند.
2. محاسبه اضافه کاری: با ضرب ساعت‌های اضافه کاری در نرخ اضافه کاری، مقدار اضافه کاری محاسبه می‌شود.
3. محاسبه حقوق کل: حقوق پایه و اضافه کاری با هم جمع می‌شوند تا حقوق کل کارمند محاسبه شود.
4. نمایش نتیجه: نتیجه محاسبه شده به صورت یک پیام به کاربر نمایش داده می‌شود.

سایر مثال‌های کاربرد رویه‌های ذخیره شده در محاسبات:

• محاسبه سود و زیان: محاسبه سود و زیان یک شرکت بر اساس داده‌های فروش و هزینه‌ها.
• محاسبه موجودی انبار: محاسبه موجودی کالاها در انبار بر اساس عملیات ورود و خروج کالا.
• محاسبه نرخ بازگشت سرمایه: محاسبه نرخ بازگشت سرمایه برای پروژه‌های سرمایه‌گذاری.
• محاسبه ارزش فعلی خالص: محاسبه ارزش فعلی خالص یک پروژه سرمایه‌گذاری.

مزایای استفاده از رویه‌های ذخیره شده در محاسبات:

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

نکات مهم:

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

انواع پارامترها در رویه‌های ذخیره شده (Stored Procedures)

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

انواع پارامترها:

1. پارامتر ورودی (IN):

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

   مثال:

   SQL

   CREATE PROCEDURE GetCustomerByName
       @CustomerName NVARCHAR(50)
   AS
   BEGIN
       SELECT * FROM Customers
       WHERE CustomerName = @CustomerName;
   END
   

   در این مثال، پارامتر @CustomerName یک پارامتر ورودی است که برای جستجوی مشتری با نام مشخص استفاده می‌شود.

1. پارامتر خروجی (OUT):

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

   مثال:

   مثال دیگر از پارامتر خروجی (OUT) در رویه‌های ذخیره شده

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

   مثال:

   فرض کنید یک جدول به نام Employees داریم که اطلاعات کارمندان را ذخیره می‌کند. می‌خواهیم رویه‌ای بنویسیم که حداکثر حقوق کارمندان را محاسبه کرده و آن را به عنوان یک پارامتر خروجی برگرداند.

   SQL

   CREATE PROCEDURE GetMaxSalary
       @MaxSalary OUT
   AS
   BEGIN
       SELECT @MaxSalary = MAX(Salary) FROM Employees;
   END
   

   در این مثال:

   • @MaxSalary یک پارامتر خروجی از نوع DECIMAL است.
   • در داخل رویه، مقدار حداکثر حقوق از جدول Employees انتخاب شده و به پارامتر @MaxSalary اختصاص داده می‌شود.
   • وقتی رویه فراخوانی می‌شود، مقدار حداکثر حقوق در متغیری که به پارامتر @MaxSalary متصل شده است، ذخیره می‌شود.

   مثال فراخوانی رویه:

   SQL

   DECLARE @MaxSalary DECIMAL(18,2);
   EXEC GetMaxSalary @MaxSalary OUTPUT;
   PRINT 'حداکثر حقوق: ' + CAST(@MaxSalary AS NVARCHAR);
   

   در این مثال:

   • ابتدا یک متغیر به نام @MaxSalary از نوع DECIMAL تعریف می‌شود.
   • سپس رویه GetMaxSalary با پارامتر خروجی @MaxSalary فراخوانی می‌شود.
   • در نهایت، مقدار حداکثر حقوق که در متغیر @MaxSalary ذخیره شده است، نمایش داده می‌شود.

   مثال دیگر:

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

   SQL

   CREATE PROCEDURE CountWords
       @Text NVARCHAR(MAX),
       @WordCount INT OUTPUT
   AS
   BEGIN
       -- کد برای شمارش کلمات در رشته @Text
       -- ...
       SET @WordCount = -- تعداد کلمات محاسبه شده
   END
   

   در این مثال:

   • @Text پارامتر ورودی است که رشته مورد نظر را دریافت می‌کند.
   • @WordCount پارامتر خروجی است که تعداد کلمات محاسبه شده را ذخیره می‌کند.
   • داخل رویه، با استفاده از توابع رشته‌ای مناسب، تعداد کلمات در رشته @Text محاسبه شده و به پارامتر @WordCount اختصاص داده می‌شود.

   جمع‌بندی:

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

   نکات مهم:

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

    

2. پارامتر ورودی/خروجی (INOUT):

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

   مثال:

   پارامتر ورودی/خروجی (INOUT) در رویه‌های ذخیره شده

   پارامتر ورودی/خروجی (INOUT) در رویه‌های ذخیره شده، هم به عنوان ورودی و هم به عنوان خروجی عمل می‌کند. به این معنی که ابتدا مقداری را از فراخواننده دریافت می‌کند، سپس ممکن است در داخل رویه تغییر کند و در نهایت مقدار تغییر یافته به فراخواننده برگردانده می‌شود.

   مثال:

   فرض کنید یک جدول به نام Products داریم که اطلاعات محصولات را ذخیره می‌کند. می‌خواهیم رویه‌ای بنویسیم که قیمت یک محصول را به‌روزرسانی کند و قیمت جدید به‌روز شده را به عنوان خروجی برگرداند.

   SQL

   CREATE PROCEDURE UpdateProductPrice
       @ProductID INT,
       @NewPrice DECIMAL(18,2) OUTPUT
   AS
   BEGIN
       UPDATE Products
       SET Price = @NewPrice
       WHERE ProductID = @ProductID;
   
       SET @NewPrice = 'قیمت محصول با موفقیت به‌روزرسانی شد.';
   END
   

   در این مثال:

   • @ProductID یک پارامتر ورودی است که شناسه محصول مورد نظر را دریافت می‌کند.
   • @NewPrice یک پارامتر ورودی/خروجی است:
     • ابتدا قیمت جدید را از فراخواننده دریافت می‌کند.
     • در داخل رویه، قیمت محصول در جدول به‌روزرسانی می‌شود.
     • در نهایت، یک پیام موفقیت‌آمیز به عنوان مقدار جدید @NewPrice به فراخواننده برگردانده می‌شود.

   مثال فراخوانی رویه:

   SQL

   DECLARE @NewPrice DECIMAL(18,2);
   EXEC UpdateProductPrice 123, 150000 @NewPrice OUTPUT;
   PRINT @NewPrice;
   

   در این مثال:

   • ابتدا یک متغیر @NewPrice برای ذخیره پیام موفقیت‌آمیز تعریف می‌شود.
   • سپس رویه UpdateProductPrice با پارامترهای ورودی ۱۲۳ (شناسه محصول) و ۱۵۰۰۰۰ (قیمت جدید) فراخوانی می‌شود.
   • پارامتر @NewPrice به عنوان پارامتر خروجی مشخص شده است تا پیام موفقیت‌آمیز را دریافت کند.
   • در نهایت، پیام موفقیت‌آمیز در متغیر @NewPrice چاپ می‌شود.

   مثال دیگر:

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

   SQL

   CREATE PROCEDURE ConvertToUpperCase
       @Text NVARCHAR(MAX) OUTPUT
   AS
   BEGIN
       SET @Text = UPPER(@Text);
   END
   

   در این مثال:

   • @Text یک پارامتر ورودی/خروجی است:
     • ابتدا رشته ورودی را دریافت می‌کند.
     • در داخل رویه به حروف بزرگ تبدیل می‌شود.
     • سپس رشته تبدیل شده به عنوان مقدار جدید @Text به فراخواننده برگردانده می‌شود.

   جمع‌بندی:

   پارامترهای ورودی/خروجی (INOUT) انعطاف‌پذیری بیشتری را در طراحی رویه‌های ذخیره شده فراهم می‌کنند. این پارامترها برای مواردی مناسب هستند که می‌خواهیم هم مقداری را به رویه منتقل کنیم و هم مقداری را از رویه دریافت کنیم.

   نکات مهم:

   • پارامترهای ورودی/خروجی می‌توانند برای انواع داده‌های مختلف استفاده شوند.
   • مقدار اولیه پارامتر ورودی/خروجی قبل از استفاده در داخل رویه تعریف می‌شود.
   • مقدار نهایی پارامتر ورودی/خروجی پس از اجرای رویه به متغیری که به آن متصل شده است، منتقل می‌شود.

   کاربردهای رایج پارامترهای ورودی/خروجی:

   • به‌روزرسانی مقادیر: به‌روزرسانی مقادیر در جدول و برگرداندن مقدار جدید.
   • انجام محاسبات پیچیده: انجام محاسبات پیچیده و برگرداندن نتایج.
   • تبدیل داده‌ها: تبدیل داده‌ها از یک نوع به نوع دیگر.

    

نکات مهم در استفاده از پارامترها:

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

جمع‌بندی:

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