Stored Procedures و Functions

۶.۱ مقدمه: کِی از Stored Procedure استفاده کنیم؟

Stored Procedure یک تکه کد SQL از پیش کامپایل شده است که در سرور ذخیره می‌شود. مزایا و معایب:

مزایا

• کاهش traffic شبکه (یک call به‌جای ده‌ها query)
• اجرای یکسان منطق در همه اپلیکیشن‌ها
• امنیت (دسترسی فقط به procedure، نه جدول)
• Performance بهتر برای عملیات پیچیده

معایب

• سخت‌تر version control
• دیباگ سخت‌تر از کد اپلیکیشن
• وابستگی به vendor (انتقال به دیتابیس دیگر سخت می‌شود)
• logic در دو جا (اپ و DB) دشوار است

۶.۲ ساختار اولیه Stored Procedure

SQL📋 کپی
DELIMITER $$

CREATE PROCEDURE get_user_orders(IN p_user_id INT)
BEGIN
    SELECT id, total, status, created_at
    FROM orders
    WHERE user_id = p_user_id
    ORDER BY created_at DESC;
END$$

DELIMITER ;

-- فراخوانی
CALL get_user_orders(5);

-- حذف
DROP PROCEDURE IF EXISTS get_user_orders;

۶.۳ Parameters: IN، OUT، INOUT

SQL📋 کپی
DELIMITER $$

-- IN: ورودی فقط
-- OUT: خروجی فقط
-- INOUT: هر دو
CREATE PROCEDURE order_summary(
    IN p_user_id INT,
    OUT p_total_count INT,
    OUT p_total_amount BIGINT
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total), 0)
    INTO p_total_count, p_total_amount
    FROM orders
    WHERE user_id = p_user_id;
END$$

DELIMITER ;

-- استفاده
CALL order_summary(5, @count, @amount);
SELECT @count, @amount;

۶.۴ متغیرها و کنترل جریان

SQL📋 کپی
DELIMITER $$

CREATE PROCEDURE apply_discount(IN p_user_id INT)
BEGIN
    -- متغیر محلی
    DECLARE v_total BIGINT DEFAULT 0;
    DECLARE v_discount_pct INT DEFAULT 0;
    DECLARE v_status VARCHAR(20);

    -- محاسبه مجموع خرید
    SELECT COALESCE(SUM(total), 0) INTO v_total
    FROM orders
    WHERE user_id = p_user_id AND status = 'paid';

    -- IF / ELSEIF / ELSE
    IF v_total >= 10000000 THEN
        SET v_discount_pct = 20;
        SET v_status = 'VIP';
    ELSEIF v_total >= 5000000 THEN
        SET v_discount_pct = 10;
        SET v_status = 'Gold';
    ELSEIF v_total >= 1000000 THEN
        SET v_discount_pct = 5;
        SET v_status = 'Silver';
    ELSE
        SET v_discount_pct = 0;
        SET v_status = 'Regular';
    END IF;

    -- ذخیره
    UPDATE users
    SET discount_pct = v_discount_pct,
        membership_level = v_status
    WHERE id = p_user_id;

    SELECT v_total AS total_purchases,
           v_discount_pct AS discount,
           v_status AS level;
END$$

DELIMITER ;

-- CASE WHEN هم پشتیبانی می‌شود
CREATE PROCEDURE classify_order(IN p_total BIGINT, OUT p_class VARCHAR(20))
BEGIN
    SET p_class = CASE
        WHEN p_total >= 5000000 THEN 'large'
        WHEN p_total >= 1000000 THEN 'medium'
        ELSE 'small'
    END;
END$$

۶.۵ Loops: WHILE، REPEAT، LOOP

SQL📋 کپی
DELIMITER $$

-- WHILE
CREATE PROCEDURE generate_test_data(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        INSERT INTO test_users (name, email)
        VALUES (CONCAT('User ', i), CONCAT('user', i, '@example.com'));
        SET i = i + 1;
    END WHILE;
END$$

-- REPEAT (مثل do-while)
CREATE PROCEDURE example_repeat()
BEGIN
    DECLARE i INT DEFAULT 1;
    REPEAT
        SELECT i;
        SET i = i + 1;
    UNTIL i > 5
    END REPEAT;
END$$

-- LOOP با LEAVE (break) و ITERATE (continue)
CREATE PROCEDURE example_loop()
BEGIN
    DECLARE i INT DEFAULT 0;
    my_loop: LOOP
        SET i = i + 1;
        IF i > 10 THEN
            LEAVE my_loop;
        END IF;
        IF i % 2 = 0 THEN
            ITERATE my_loop;  -- skip even
        END IF;
        SELECT i;  -- only odd
    END LOOP my_loop;
END$$

DELIMITER ;

۶.۶ Cursors – پیمایش نتایج

وقتی نیاز داشتید روی نتیجه یک SELECT ردیف به ردیف کار کنید:

SQL📋 کپی
DELIMITER $$

CREATE PROCEDURE recalculate_user_totals()
BEGIN
    DECLARE v_user_id INT;
    DECLARE v_done INT DEFAULT 0;

    -- تعریف cursor
    DECLARE user_cursor CURSOR FOR
        SELECT id FROM users WHERE is_active = 1;

    -- handler برای پایان داده‌ها
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    OPEN user_cursor;

    read_loop: LOOP
        FETCH user_cursor INTO v_user_id;
        IF v_done THEN
            LEAVE read_loop;
        END IF;

        -- پردازش هر کاربر
        UPDATE users
        SET total_spent = (
            SELECT COALESCE(SUM(total), 0)
            FROM orders
            WHERE user_id = v_user_id AND status = 'paid'
        )
        WHERE id = v_user_id;
    END LOOP;

    CLOSE user_cursor;
END$$

DELIMITER ;

CALL recalculate_user_totals();

UPDATE users u
LEFT JOIN (
    SELECT user_id, SUM(total) AS s
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
) o ON o.user_id = u.id
SET u.total_spent = COALESCE(o.s, 0)
WHERE u.is_active = 1;

۶.۷ Exception Handling

SQL📋 کپی
DELIMITER $$

CREATE PROCEDURE transfer_money(
    IN p_from_user INT,
    IN p_to_user INT,
    IN p_amount BIGINT
)
BEGIN
    DECLARE v_from_balance BIGINT;

    -- handler برای هر خطای SQL
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;  -- خطا را به caller برگردان
    END;

    -- handler برای SQLWARNING
    DECLARE CONTINUE HANDLER FOR SQLWARNING
        SELECT 'هشدار رخ داد' AS warning;

    -- handler برای کد خطای خاص (Duplicate key)
    DECLARE CONTINUE HANDLER FOR 1062
        SELECT 'این رکورد قبلاً وجود دارد' AS warning;

    START TRANSACTION;

    SELECT balance INTO v_from_balance
    FROM users WHERE id = p_from_user FOR UPDATE;

    IF v_from_balance < p_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'موجودی کافی نیست';
    END IF;

    UPDATE users SET balance = balance - p_amount WHERE id = p_from_user;
    UPDATE users SET balance = balance + p_amount WHERE id = p_to_user;

    COMMIT;
END$$

DELIMITER ;

۶.۸ Stored Functions - تفاوت با Procedure

Function یک مقدار برمی‌گرداند و در SELECT قابل استفاده است:

SQL📋 کپی
DELIMITER $$

-- function ساده
CREATE FUNCTION calculate_tax(p_amount BIGINT, p_rate DECIMAL(4,2))
RETURNS BIGINT
DETERMINISTIC
BEGIN
    RETURN p_amount * p_rate / 100;
END$$

-- function برای فرمت قیمت فارسی
CREATE FUNCTION format_price_persian(p_amount BIGINT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    -- ساده‌ترین حالت
    RETURN CONCAT(FORMAT(p_amount / 10, 0), ' تومان');
END$$

-- function برای محاسبه سن
CREATE FUNCTION calculate_age(p_birthdate DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_birthdate, CURDATE());
END$$

DELIMITER ;

-- استفاده در SELECT
SELECT
    title,
    format_price_persian(price) AS price_formatted,
    calculate_tax(price, 9.0) AS tax
FROM products;

-- استفاده در WHERE (با احتیاط!)
SELECT * FROM users WHERE calculate_age(birthdate) >= 18;

کلمات کلیدی مهم

• DETERMINISTIC: با ورودی یکسان، خروجی همیشه یکسان است (می‌تواند cache شود)
• NOT DETERMINISTIC: خروجی می‌تواند متفاوت باشد (مثل NOW())
• READS SQL DATA: فقط SELECT انجام می‌دهد
• MODIFIES SQL DATA: INSERT/UPDATE/DELETE می‌کند
• NO SQL: هیچ SQL ندارد (فقط محاسبه)

۶.۹ مثال جامع: ثبت سفارش با تراکنش

SQL📋 کپی
DELIMITER $$

CREATE PROCEDURE create_order(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_message VARCHAR(200)
)
BEGIN
    DECLARE v_price BIGINT;
    DECLARE v_stock INT;
    DECLARE v_total BIGINT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_order_id = 0;
        SET p_message = 'خطا در ثبت سفارش';
    END;

    START TRANSACTION;

    -- قفل کردن ردیف محصول
    SELECT price, stock INTO v_price, v_stock
    FROM products WHERE id = p_product_id FOR UPDATE;

    IF v_stock < p_quantity THEN
        SET p_order_id = 0;
        SET p_message = CONCAT('موجودی کافی نیست. موجودی: ', v_stock);
        ROLLBACK;
    ELSE
        SET v_total = v_price * p_quantity;

        INSERT INTO orders (user_id, total, status, created_at)
        VALUES (p_user_id, v_total, 'pending', NOW());

        SET p_order_id = LAST_INSERT_ID();

        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (p_order_id, p_product_id, p_quantity, v_price);

        UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

        COMMIT;
        SET p_message = 'سفارش با موفقیت ثبت شد';
    END IF;
END$$

DELIMITER ;

-- استفاده
CALL create_order(5, 12, 2, @order_id, @message);
SELECT @order_id, @message;

۶.۱۰ مدیریت Stored Procedures موجود

SQL📋 کپی
-- لیست همه procedures
SHOW PROCEDURE STATUS WHERE Db = 'my_database';

-- نمایش کد یک procedure
SHOW CREATE PROCEDURE get_user_orders;

-- یا از information_schema
SELECT routine_name, routine_type, created
FROM information_schema.routines
WHERE routine_schema = 'my_database';

-- backup همه procedures
mysqldump --routines --no-data --no-create-info my_database > procs.sql

۶.۱۱ خلاصه فصل

• Procedure برای عملیات پیچیده، Function برای محاسبه و استفاده در SELECT
• IN/OUT/INOUT برای ارسال و دریافت پارامتر
• Cursor برای پیمایش ردیف به ردیف (اما set-based معمولاً سریع‌تر است)
• EXIT HANDLER برای exception، SIGNAL برای throw کردن خطا
• DETERMINISTIC را برای function‌های ثابت تعیین کنید
• Logic اصلی را در اپلیکیشن نگه دارید؛ DB فقط برای کارهای مرتبط با داده