SQL Functions

توابع SQL: ابزاری قدرتمند برای پردازش داده‌ها

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

انواع توابع SQL

توابع SQL به دو دسته اصلی تقسیم می‌شوند:

1. توابع از پیش تعریف شده (Built-in Functions): این توابع توسط سیستم مدیریت پایگاه داده ارائه می‌شوند و برای انجام عملیات رایج مانند محاسبات ریاضی، دستکاری رشته‌ها، تاریخ و زمان و … استفاده می‌شوند. برخی از توابع از پیش تعریف شده رایج عبارتند از:

   • توابع ریاضی: SUM, AVG, COUNT, MIN, MAX
   • توابع رشته‌ای: UPPER, LOWER, CONCAT, SUBSTRING
   • توابع تاریخ و زمان: CURRENT_DATE, CURRENT_TIME, DATE_ADD, DATE_DIFF
   • توابع گروهی: GROUP_CONCAT, HAVING

2. توابع تعریف شده توسط کاربر (User-Defined Functions): این توابع توسط خود کاربر ایجاد می‌شوند و برای انجام عملیات خاصی که توسط توابع از پیش تعریف شده پشتیبانی نمی‌شوند، استفاده می‌شوند. توابع تعریف شده توسط کاربر می‌توانند پارامتر ورودی داشته باشند و یک مقدار یا یک جدول را به عنوان خروجی برگردانند.

توابع ریاضی در SQL: محاسبات اساسی روی داده‌ها

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

۱. تابع SUM

• کاربرد: برای محاسبه مجموع مقادیر یک ستون عددی استفاده می‌شود.
• سینتکس: SUM(column_name)
• مثال:
  SQL

  SELECT SUM(price) AS total_price
  FROM products;
  

  این دستور مجموع قیمت تمام محصولات موجود در جدول products را محاسبه کرده و آن را با نام مستعار total_price نمایش می‌دهد.

۲. تابع AVG

• کاربرد: برای محاسبه میانگین مقادیر یک ستون عددی استفاده می‌شود.
• سینتکس: AVG(column_name)
• مثال:
  SQL

  SELECT AVG(age) AS average_age
  FROM customers;
  

  این دستور میانگین سن تمام مشتریان موجود در جدول customers را محاسبه کرده و آن را با نام مستعار average_age نمایش می‌دهد.

۳. تابع COUNT

• کاربرد: برای شمارش تعداد سطرها یا مقادیر غیر NULL یک ستون استفاده می‌شود.
• سینتکس:
  • COUNT(*): تعداد کل سطرها را شمارش می‌کند.
  • COUNT(column_name): تعداد مقادیر غیر NULL یک ستون خاص را شمارش می‌کند.
• مثال:
  SQL

  SELECT COUNT(*) AS total_orders
  FROM orders;
  

  این دستور تعداد کل سفارشات موجود در جدول orders را شمارش می‌کند.

۴. تابع MIN

• کاربرد: برای یافتن حداقل مقدار یک ستون عددی استفاده می‌شود.
• سینتکس: MIN(column_name)
• مثال:
  SQL

  SELECT MIN(price) AS lowest_price
  FROM products;
  

  این دستور کمترین قیمت موجود در جدول products را پیدا می‌کند.

۵. تابع MAX

• کاربرد: برای یافتن حداکثر مقدار یک ستون عددی استفاده می‌شود.
• سینتکس: MAX(column_name)
• مثال:
  SQL

  SELECT MAX(sale_date) AS latest_sale
  FROM sales;
  

  این دستور آخرین تاریخ فروش موجود در جدول sales را پیدا می‌کند.

مثال ترکیبی:

SELECT department, COUNT(*) AS total_employees, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

این دستور تعداد کارمندان و میانگین حقوق هر بخش را محاسبه و نمایش می‌دهد.

نکته: توابع ریاضی معمولاً با عبارت GROUP BY استفاده می‌شوند تا نتایج را بر اساس گروه‌های مختلف دسته‌بندی کنند.

کاربردهای دیگر توابع ریاضی:

• محاسبه درصد تغییرات
• مقایسه مقادیر
• محاسبه آماره‌های توصیفی مانند انحراف استاندارد
• و …

SELECT CONCAT(UPPER(substring(customer_name, 1, 1)), '.', LOWER(substring(customer_name, 2))) AS initials
FROM customers;

SELECT customer_name, order_date, DATE_ADD(order_date, INTERVAL 30 DAY) AS delivery_date
FROM orders;

توابع گروهی در SQL: تجمیع و فیلتر کردن داده‌ها

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

۱. تابع GROUP_CONCAT

• کاربرد: مقادیر چندین سطر از یک ستون را به یک رشته متصل می‌کند. این تابع معمولاً با عبارت GROUP BY استفاده می‌شود.
• سینتکس: GROUP_CONCAT(column_name separator 'separator')
• مثال:
  SQL

  SELECT order_id, GROUP_CONCAT(product_name SEPARATOR ', ') AS products
  FROM order_items
  GROUP BY order_id;
  

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

۲. عبارت HAVING

• کاربرد: برای فیلتر کردن گروه‌هایی که توسط عبارت GROUP BY ایجاد شده‌اند، استفاده می‌شود. این عبارت شبیه به عبارت WHERE است، اما برای گروه‌ها استفاده می‌شود.
• سینتکس: HAVING condition
• مثال:
  SQL

  SELECT department, COUNT(*) AS num_employees
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 10;
  

  این دستور تعداد کارمندان هر بخش را محاسبه می‌کند و فقط بخش‌هایی را نمایش می‌دهد که بیش از ۱۰ کارمند دارند.

کاربردهای ترکیبی توابع گروهی

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

مثال ترکیبی:

SELECT customer_id, GROUP_CONCAT(product_name SEPARATOR ', ') AS products_purchased
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2;

این دستور نام مشتریانی که بیش از دو محصول خریداری کرده‌اند و لیستی از محصولات خریداری شده توسط هر مشتری را نمایش می‌دهد.

نکات مهم

• تابع GROUP_CONCAT: این تابع در همه پایگاه داده‌ها پشتیبانی نمی‌شود و ممکن است سینتکس آن کمی متفاوت باشد.
• عبارت HAVING: باید بعد از عبارت GROUP BY استفاده شود.
• ترکیب با سایر توابع: توابع گروهی را می‌توان با توابع دیگر مانند COUNT, SUM, AVG و … ترکیب کرد تا اطلاعات جامع‌تری به دست آورد.

تفاوت بین توابع تعریف شده توسط کاربر (UDF) و رویه‌های ذخیره شده (Stored Procedures)

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

توابع تعریف شده توسط کاربر (UDF)

• هدف: عمدتاً برای انجام محاسبات و بازگرداندن یک مقدار استفاده می‌شوند.
• خروجی: می‌توانند یک مقدار اسکالر (مانند عدد، رشته، تاریخ) یا یک جدول را برگردانند.
• استفاده: در پرس‌و‌جوها به عنوان بخشی از عبارت SELECT, WHERE یا HAVING استفاده می‌شوند.
• محدودیت‌ها: معمولاً نمی‌توانند تغییرات ساختاری در پایگاه داده ایجاد کنند (مانند ایجاد جدول جدید) و نمی‌توانند مستقیماً با کاربر تعامل داشته باشند.

رویه‌های ذخیره شده (Stored Procedures)

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

جدول مقایسه

چه زمانی از کدام یک استفاده کنیم؟

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

مثال‌ها

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

خلاصه

انتخاب بین UDF و Stored Procedure بستگی به پیچیدگی عملیات، نوع خروجی مورد نظر و نیازهای خاص شما دارد. UDF ها برای محاسبات ساده و قابل استفاده مجدد مناسب هستند، در حالی که Stored Procedures برای عملیات پیچیده‌تر و کنترل جریان بهتر هستند.

پارامترهای ورودی و خروجی در توابع تعریف شده توسط کاربر (UDF)

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

پارامترهای ورودی (Input Parameters)

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

مثال:

CREATE FUNCTION calculate_area(length DECIMAL(10,2), width DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
    RETURN length * width;
END;

در این مثال، length و width پارامترهای ورودی هستند که ابعاد یک مستطیل را مشخص می‌کنند.

پارامترهای خروجی (Output Parameters)

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

مثال:

CREATE PROCEDURE get_employee_info(IN employee_id INT, OUT first_name VARCHAR(50), OUT last_name VARCHAR(50))
BEGIN
    SELECT first_name, last_name INTO first_name, last_name
    FROM employees
    WHERE employee_id = employee_id;
END;

در این مثال، first_name و last_name پارامترهای خروجی هستند که نام و نام خانوادگی کارمند را در خود نگه می‌دارند.

پارامترهای ورودی/خروجی (INOUT Parameters)

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

مثال:

CREATE PROCEDURE update_count(INOUT count INT)
BEGIN
    SET count = count + 1;
END;

در این مثال، پارامتر count هم مقدار اولیه را دریافت می‌کند و هم مقدار نهایی به روز شده را برمی‌گرداند.

نکات مهم در مورد پارامترها

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

مزایای استفاده از پارامترها

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

در کل، پارامترها یکی از مهم‌ترین ویژگی‌های UDF ها و Stored Procedures هستند و به شما اجازه می‌دهند تا توابع قدرتمند و قابل استفاده مجدد ایجاد کنید.

انواع مختلف داده‌های بازگشتی در UDF ها

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

در ادامه به بررسی انواع اصلی داده‌های بازگشتی در UDF ها می‌پردازیم:

۱. داده‌های اسکالر (Scalar Data)

• تعریف: داده‌های ساده‌ای هستند که یک مقدار منفرد را نشان می‌دهند.
• انواع: شامل اعداد صحیح (INT)، اعداد اعشاری (DECIMAL)، رشته‌ها (VARCHAR، CHAR)، تاریخ (DATE)، زمان (TIME)، و …
• مثال: تابعی که سن یک فرد را بر اساس تاریخ تولد محاسبه می‌کند و یک عدد صحیح را برمی‌گرداند.

۲. جدول‌ها (Tables)

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

۳. XML

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

۴. JSON

• تعریف: مشابه XML، نتایج را به صورت یک ساختار داده JSON برمی‌گرداند.
• کاربرد: برای تبادل داده با برنامه‌های وب و ایجاد API ها بسیار محبوب است.
• مثال: تابعی که اطلاعات چندین محصول را به صورت یک آرایه JSON برمی‌گرداند.

عوامل موثر در انتخاب نوع داده بازگشتی

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

مثال‌ها

• تابعی که میانگین سنی کارمندان را محاسبه می‌کند:
  SQL

  CREATE FUNCTION avg_employee_age()
  RETURNS DECIMAL(10,2)
  BEGIN
      DECLARE avg_age DECIMAL(10,2);
      SELECT AVG(age) INTO avg_age FROM employees;
      RETURN avg_age;
  END;
  

• تابعی که لیست محصولات با قیمت بالاتر از یک مقدار مشخص را برمی‌گرداند:
  SQL

  CREATE FUNCTION get_expensive_products(min_price DECIMAL(10,2))
  RETURNS TABLE
  AS
  RETURN 
  SELECT * FROM products
  WHERE price > min_price;
  

نکات مهم

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

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

CREATE FUNCTION calculate_age(birthdate DATE)
RETURNS INT
BEGIN
    RETURN DATEDIFF(YEAR, birthdate, CURRENT_DATE);
END;

استفاده از UDF ها در Trigger ها

Trigger ها و توابع تعریف شده توسط کاربر (UDF) هر دو ابزار قدرتمندی در SQL هستند که برای خودکارسازی عملیات و اعمال منطق سفارشی در پایگاه داده استفاده می‌شوند. ترکیب این دو ابزار می‌تواند به شما امکان دهد تا عملیات پیچیده‌تر و هوشمندتری را پیاده‌سازی کنید.

چه زمانی از UDF در Trigger استفاده کنیم؟

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

مثال

فرض کنید می‌خواهیم هر زمان که یک رکورد جدید به جدول orders اضافه می‌شود، به صورت خودکار مالیات بر ارزش افزوده (VAT) محاسبه و در یک ستون جدید ذخیره شود. برای این کار، می‌توانیم یک UDF برای محاسبه VAT تعریف کنیم و سپس از آن در یک Trigger استفاده کنیم:

-- تعریف UDF برای محاسبه VAT
CREATE FUNCTION calculate_vat(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE vat_rate DECIMAL(5,2) := 0.09; -- نرخ VAT
    RETURN price * vat_rate;
END;

-- تعریف Trigger
CREATE TRIGGER calculate_vat_on_order_insert
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
    UPDATE orders
    SET vat = calculate_vat(NEW.price);
END;

در این مثال، Trigger calculate_vat_on_order_insert پس از هر عملیات درج در جدول orders اجرا می‌شود و مقدار ستون vat را با استفاده از UDF calculate_vat محاسبه و به‌روزرسانی می‌کند.

نکات مهم

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

مزایای استفاده از UDF در Trigger ها

• مدولار بودن: کد را به بخش‌های کوچکتر و قابل مدیریت‌تر تقسیم می‌کند.
• قابلیت استفاده مجدد: UDF ها را می‌توان در Trigger های مختلف استفاده کرد.
• افزایش خوانایی کد: کد Trigger ها را ساده‌تر و قابل فهم‌تر می‌کند.
• کاهش خطا: با کاهش تکرار کد، احتمال بروز خطا کاهش می‌یابد.

محدودیت‌ها

• عملکرد: UDF ها ممکن است بر عملکرد پایگاه داده تاثیر بگذارند.
• پیچیدگی: ایجاد UDF های پیچیده می‌تواند دشوار باشد.
• وابستگی: اگر UDF تغییر کند، تمام Trigger هایی که از آن استفاده می‌کنند باید به‌روزرسانی شوند.

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

مزایای استفاده از توابع SQL

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

نکات مهم

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

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