MySQL وتصميم قواعد البيانات

الإجراءات المخزنة (Stored Procedures)

13 دقيقة الدرس 21 من 40

الإجراءات المخزنة (Stored Procedures)

الإجراءات المخزنة هي مجموعات مُسماة من عبارات SQL مخزنة في قاعدة البيانات يمكن تنفيذها كوحدة واحدة. إنها تغلف منطق الأعمال المعقد، وتحسن الأداء، وتعزز الأمان. في هذا الدرس، سنتعلم كيفية إنشاء واستخدام الإجراءات المخزنة بفعالية.

ما هو الإجراء المخزن؟

الإجراء المخزن هو كود SQL محضّر يمكنك حفظه وإعادة استخدامه. بدلاً من كتابة نفس عبارات SQL بشكل متكرر، يمكنك تغليفها في إجراء واستدعائه كلما دعت الحاجة.

فائدة رئيسية: تقلل الإجراءات المخزنة من حركة مرور الشبكة عن طريق تنفيذ عبارات متعددة على الخادم باستدعاء واحد، وتركز منطق الأعمال في مكان واحد.

إنشاء إجراءات مخزنة أساسية

استخدم عبارة CREATE PROCEDURE. لاحظ تغيير المحدد للسماح بالفاصلة المنقوطة داخل الإجراء:

-- تغيير المحدد مؤقتاً
DELIMITER //

-- إجراء بسيط بدون معاملات
CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT customer_id, customer_name, email
    FROM customers
    ORDER BY customer_name;
END //

DELIMITER ;

-- استدعاء الإجراء
CALL GetAllCustomers();

نصيحة: استخدم دائماً DELIMITER لتغيير محدد العبارة عند إنشاء الإجراءات. هذا يسمح بالفواصل المنقوطة داخل جسم الإجراء.

المعاملات: IN و OUT و INOUT

يمكن للإجراءات قبول المعاملات في ثلاثة أوضاع:

DELIMITER //

-- معامل IN: تمرير قيمة إلى الإجراء
CREATE PROCEDURE GetCustomerByID(IN customer_id_param INT)
BEGIN
    SELECT customer_id, customer_name, email, phone
    FROM customers
    WHERE customer_id = customer_id_param;
END //

-- معامل OUT: إرجاع قيمة من الإجراء
CREATE PROCEDURE GetCustomerCount(OUT total_customers INT)
BEGIN
    SELECT COUNT(*) INTO total_customers
    FROM customers;
END //

-- معامل INOUT: إدخال وإخراج معاً
CREATE PROCEDURE DoubleValue(INOUT value INT)
BEGIN
    SET value = value * 2;
END //

DELIMITER ;

-- استدعاء الإجراءات مع المعاملات
CALL GetCustomerByID(5);

-- استخدام معامل OUT
SET @count = 0;
CALL GetCustomerCount(@count);
SELECT @count AS total_customers;

-- استخدام معامل INOUT
SET @num = 10;
CALL DoubleValue(@num);
SELECT @num AS doubled_value; -- يعيد 20

المتغيرات والتحكم في التدفق

يمكن للإجراءات استخدام المتغيرات وهياكل التحكم:

DELIMITER //

CREATE PROCEDURE ProcessOrder(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10,2);

    -- التحقق من توفر المخزون
    SELECT stock_quantity, price INTO v_stock, v_price
    FROM products
    WHERE product_id = p_product_id;

    IF v_stock < p_quantity THEN
        SET p_message = 'مخزون غير كافٍ';
        SET p_order_id = -1;
    ELSE
        -- إنشاء طلب
        INSERT INTO orders (customer_id, order_date, total_amount, status)
        VALUES (p_customer_id, NOW(), v_price * p_quantity, 'pending');

        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_quantity = stock_quantity - p_quantity
        WHERE product_id = p_product_id;

        SET p_message = 'تم إنشاء الطلب بنجاح';
    END IF;
END //

DELIMITER ;

-- استخدام الإجراء
SET @order_id = 0;
SET @message = '';
CALL ProcessOrder(1, 5, 2, @order_id, @message);
SELECT @order_id, @message;

المنطق الشرطي

استخدم IF و CASE وهياكل التحكم الأخرى:

DELIMITER //

CREATE PROCEDURE GetCustomerDiscount(
    IN p_customer_id INT,
    OUT p_discount_rate DECIMAL(5,2)
)
BEGIN
    DECLARE v_total_spent DECIMAL(10,2);
    DECLARE v_order_count INT;

    -- الحصول على إحصائيات العميل
    SELECT COALESCE(SUM(total_amount), 0), COUNT(*)
    INTO v_total_spent, v_order_count
    FROM orders
    WHERE customer_id = p_customer_id;

    -- تحديد الخصم باستخدام CASE
    SET p_discount_rate = CASE
        WHEN v_total_spent > 10000 THEN 20.00
        WHEN v_total_spent > 5000 THEN 15.00
        WHEN v_total_spent > 1000 THEN 10.00
        WHEN v_order_count > 5 THEN 5.00
        ELSE 0.00
    END;
END //

DELIMITER ;

الحلقات في الإجراءات

يدعم MySQL حلقات WHILE و REPEAT و LOOP:

DELIMITER //

-- مثال على حلقة WHILE
CREATE PROCEDURE GenerateSequence(IN max_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;

    DROP TEMPORARY TABLE IF EXISTS temp_sequence;
    CREATE TEMPORARY TABLE temp_sequence (num INT);

    WHILE counter <= max_num DO
        INSERT INTO temp_sequence VALUES (counter);
        SET counter = counter + 1;
    END WHILE;

    SELECT * FROM temp_sequence;
END //

-- مثال على حلقة REPEAT
CREATE PROCEDURE CalculateFactorial(
    IN n INT,
    OUT factorial BIGINT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET factorial = 1;

    REPEAT
        SET factorial = factorial * i;
        SET i = i + 1;
    UNTIL i > n
    END REPEAT;
END //

-- حلقة LOOP مع LEAVE (كسر) و ITERATE (استمرار)
CREATE PROCEDURE ProcessBatch(IN batch_size INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE v_id INT;

    process_loop: LOOP
        SET counter = counter + 1;

        IF counter > batch_size THEN
            LEAVE process_loop; -- الخروج من الحلقة
        END IF;

        -- منطق المعالجة هنا
        -- استخدم ITERATE process_loop للانتقال إلى التكرار التالي

    END LOOP process_loop;
END //

DELIMITER ;

معالجة الأخطاء

استخدم معالجات DECLARE للقبض على الأخطاء ومعالجتها:

DELIMITER //

CREATE PROCEDURE SafeTransfer(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10,2),
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        -- التراجع عند أي خطأ
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'فشل التحويل: خطأ في قاعدة البيانات';
    END;

    DECLARE exit handler FOR SQLWARNING
    BEGIN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'فشل التحويل: حدث تحذير';
    END;

    -- بدء المعاملة
    START TRANSACTION;

    -- الخصم من الحساب المصدر
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account
    AND balance >= p_amount;

    -- التحقق من نجاح الخصم
    IF ROW_COUNT() = 0 THEN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'رصيد غير كافٍ';
    ELSE
        -- الإضافة إلى الحساب الوجهة
        UPDATE accounts
        SET balance = balance + p_amount
        WHERE account_id = p_to_account;

        COMMIT;
        SET p_success = TRUE;
        SET p_message = 'نجح التحويل';
    END IF;
END //

DELIMITER ;

-- استخدام الإجراء
SET @success = FALSE;
SET @msg = '';
CALL SafeTransfer(1, 2, 100.00, @success, @msg);
SELECT @success, @msg;

مهم: تعامل دائماً مع الأخطاء في الإجراءات التي تعدل البيانات. استخدم المعاملات مع معالجات الأخطاء المناسبة للحفاظ على سلامة البيانات.

استدعاء الإجراءات من PHP

إليك كيفية استدعاء الإجراءات المخزنة من PHP باستخدام MySQLi و PDO:

// نهج MySQLi
$mysqli = new mysqli('localhost', 'user', 'password', 'database');

// استدعاء إجراء مع معامل OUT
$mysqli->query("SET @count = 0");
$mysqli->query("CALL GetCustomerCount(@count)");
$result = $mysqli->query("SELECT @count AS total");
$row = $result->fetch_assoc();
echo "إجمالي العملاء: " . $row['total'];

// نهج PDO
$pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');

// استدعاء إجراء مع معامل IN
$stmt = $pdo->prepare("CALL GetCustomerByID(?)");
$stmt->execute([5]);
$customers = $stmt->fetchAll(PDO::FETCH_ASSOC);

// استدعاء إجراء مع معاملات OUT
$stmt = $pdo->prepare("CALL ProcessOrder(?, ?, ?, @order_id, @message)");
$stmt->execute([1, 5, 2]);

$result = $pdo->query("SELECT @order_id AS order_id, @message AS message");
$row = $result->fetch(PDO::FETCH_ASSOC);
echo "معرف الطلب: " . $row['order_id'];
echo "الرسالة: " . $row['message'];

إدارة الإجراءات المخزنة

-- سرد جميع الإجراءات في قاعدة البيانات الحالية
SHOW PROCEDURE STATUS WHERE Db = 'your_database';

-- إظهار تعريف الإجراء
SHOW CREATE PROCEDURE GetAllCustomers;

-- حذف إجراء
DROP PROCEDURE IF EXISTS GetAllCustomers;

-- التحقق من وجود الإجراء قبل الإنشاء
DROP PROCEDURE IF EXISTS ProcessOrder;
DELIMITER //
CREATE PROCEDURE ProcessOrder(...)
BEGIN
    -- جسم الإجراء
END //
DELIMITER ;

أمثلة واقعية للإجراءات

DELIMITER //

-- إجراء معالجة طلب معقد
CREATE PROCEDURE CompleteOrderProcessing(
    IN p_order_id INT,
    OUT p_status VARCHAR(50),
    OUT p_tracking_number VARCHAR(100)
)
BEGIN
    DECLARE v_customer_id INT;
    DECLARE v_total_amount DECIMAL(10,2);

    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'خطأ';
        SET p_tracking_number = NULL;
    END;

    START TRANSACTION;

    -- الحصول على تفاصيل الطلب
    SELECT customer_id, total_amount INTO v_customer_id, v_total_amount
    FROM orders
    WHERE order_id = p_order_id;

    -- تحديث حالة الطلب
    UPDATE orders
    SET status = 'processing',
        processed_at = NOW()
    WHERE order_id = p_order_id;

    -- توليد رقم التتبع
    SET p_tracking_number = CONCAT('TRK', LPAD(p_order_id, 10, '0'));

    -- تحديث معلومات الشحن
    INSERT INTO shipments (order_id, tracking_number, status, created_at)
    VALUES (p_order_id, p_tracking_number, 'pending', NOW());

    -- تحديث إحصائيات العميل
    UPDATE customers
    SET total_orders = total_orders + 1,
        total_spent = total_spent + v_total_amount,
        last_order_date = NOW()
    WHERE customer_id = v_customer_id;

    COMMIT;
    SET p_status = 'نجح';
END //

-- إجراء تنظيف البيانات
CREATE PROCEDURE CleanupOldData(IN days_old INT)
BEGIN
    DECLARE deleted_orders INT DEFAULT 0;
    DECLARE deleted_logs INT DEFAULT 0;

    -- حذف الطلبات الملغاة القديمة
    DELETE FROM orders
    WHERE status = 'cancelled'
    AND created_at < DATE_SUB(NOW(), INTERVAL days_old DAY);

    SET deleted_orders = ROW_COUNT();

    -- حذف إدخالات السجل القديمة
    DELETE FROM activity_logs
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_old DAY);

    SET deleted_logs = ROW_COUNT();

    -- تسجيل إجراء التنظيف
    INSERT INTO maintenance_log (action, details, created_at)
    VALUES (
        'cleanup',
        CONCAT('تم حذف ', deleted_orders, ' طلبات و ', deleted_logs, ' سجلات'),
        NOW()
    );
END //

DELIMITER ;

المزايا والعيوب

المزايا:
✓ تقليل حركة مرور الشبكة (استدعاء واحد مقابل استعلامات متعددة)
✓ مركزية منطق الأعمال
✓ تحسين الأمان (إخفاء هيكل الجدول)
✓ أداء أفضل (مترجم مسبقاً)
✓ إعادة استخدام الكود
✓ إدارة المعاملات

العيوب:
✗ ارتباط بالمورد (بناء جملة خاص بـ MySQL)
✗ أصعب في التحكم بالإصدارات
✗ أكثر صعوبة في التصحيح
✗ يمكن أن يزيد الحمل على خادم قاعدة البيانات
✗ غير مناسب لجميع التطبيقات

تمرين عملي:

إنشاء إجراء إدارة المخزون:

أنشئ إجراءً 'RestockProduct' يقوم بـ:
- أخذ product_id والكمية كمعاملات IN
- إرجاع حالة النجاح والرسالة كمعاملات OUT
- التحقق من وجود المنتج
- تحديث كمية المخزون
- تسجيل إجراء إعادة التخزين مع الطابع الزمني
- معالجة الأخطاء بالرسائل المناسبة
اختبر الإجراء مع معرفات منتجات صالحة وغير صالحة

أفضل الممارسات

✓ استخدم أسماء وصفية للإجراءات (فعل + اسم)
✓ أضف تعليقات لشرح المنطق المعقد
✓ استخدم دائماً معالجات الأخطاء للتعديلات على البيانات
✓ استخدم المعاملات للعمليات التي تعدل جداول متعددة
✓ التحقق من صحة معاملات الإدخال
✓ إرجاع رسائل حالة ذات معنى
✓ اجعل الإجراءات مركزة (مسؤولية واحدة)
✓ وثق أنواع وأغراض المعاملات
✗ لا تضع الكثير من المنطق في الإجراءات
✗ تجنب الإجراءات المعقدة جداً
✗ لا تتجاهل معالجة الأخطاء

الملخص

في هذا الدرس، تعلمت:

الإجراءات المخزنة تغلف منطق SQL في قاعدة البيانات
يمكن أن تكون المعاملات IN أو OUT أو INOUT
المتغيرات وهياكل التحكم تمكن المنطق المعقد
معالجات الأخطاء تضمن سلامة البيانات
الإجراءات تقلل من حركة مرور الشبكة وتركز المنطق
DELIMITER مطلوب عند إنشاء الإجراءات
يمكن استدعاء الإجراءات من PHP باستخدام MySQLi أو PDO

التالي: في الدرس التالي، سنستكشف الدوال المعرفة من قبل المستخدم والمشغلات لمزيد من أتمتة عمليات قاعدة البيانات!