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

المؤشرات والإجراءات المتقدمة (Cursors & Advanced Procedures)

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

المؤشرات والإجراءات المتقدمة (Cursors & Advanced Procedures)

تسمح لك المؤشرات بالتكرار عبر نتائج الاستعلام صفاً تلو الآخر داخل الإجراءات المخزنة. بالاشتراك مع تقنيات متقدمة مثل SQL الديناميكي ومعالجة الأخطاء المتطورة، فإنها تمكّن معالجة البيانات المعقدة التي قد تكون صعبة أو مستحيلة باستخدام SQL القياسي. في هذا الدرس، سنتقن المؤشرات وأنماط الإجراءات المتقدمة.

ما هي المؤشرات؟

المؤشر هو كائن قاعدة بيانات يسمح لك بالتنقل ومعالجة مجموعات النتائج صفاً واحداً في كل مرة. فكر فيه كمؤشر يتحرك عبر نتائج الاستعلام.

مفهوم أساسي: المؤشرات قوية ولكنها أبطأ من العمليات المستندة إلى المجموعات. استخدمها فقط عندما تكون المعالجة صفاً تلو الآخر ضرورية.

بناء جملة المؤشر الأساسي

تتبع المؤشرات دورة حياة محددة: DECLARE و OPEN و FETCH و CLOSE:

DELIMITER //

CREATE PROCEDURE process_customers()
BEGIN
    -- متغيرات لحمل بيانات المؤشر
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_customer_id INT;
    DECLARE v_customer_name VARCHAR(255);
    DECLARE v_total_spent DECIMAL(10,2);

    -- تعريف المؤشر
    DECLARE customer_cursor CURSOR FOR
        SELECT customer_id, customer_name, total_spent
        FROM customers
        WHERE status = 'active'
        ORDER BY total_spent DESC;

    -- تعريف معالج لنهاية المؤشر
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- فتح المؤشر
    OPEN customer_cursor;

    -- التكرار عبر المؤشر
    read_loop: LOOP
        -- جلب الصف التالي
        FETCH customer_cursor INTO v_customer_id, v_customer_name, v_total_spent;

        -- الخروج من الحلقة إذا لم يكن هناك المزيد من الصفوف
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- معالجة الصف
        INSERT INTO customer_report (customer_id, name, spending, processed_at)
        VALUES (v_customer_id, v_customer_name, v_total_spent, NOW());
    END LOOP;

    -- إغلاق المؤشر
    CLOSE customer_cursor;
END //

DELIMITER ;

مؤشر مع معالجة معقدة

DELIMITER //

CREATE PROCEDURE calculate_customer_rewards()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_customer_id INT;
    DECLARE v_total_spent DECIMAL(10,2);
    DECLARE v_order_count INT;
    DECLARE v_reward_points INT;
    DECLARE v_reward_tier VARCHAR(20);

    DECLARE customer_cursor CURSOR FOR
        SELECT
            c.customer_id,
            COALESCE(SUM(o.total_amount), 0) AS total_spent,
            COUNT(o.order_id) AS order_count
        FROM customers c
        LEFT JOIN orders o ON c.customer_id = o.customer_id
        WHERE c.status = 'active'
        GROUP BY c.customer_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN customer_cursor;

    process_loop: LOOP
        FETCH customer_cursor INTO v_customer_id, v_total_spent, v_order_count;

        IF done THEN
            LEAVE process_loop;
        END IF;

        -- حساب نقاط المكافأة (نقطة واحدة لكل دولار يتم إنفاقه)
        SET v_reward_points = FLOOR(v_total_spent);

        -- تحديد الفئة بناءً على الإنفاق
        SET v_reward_tier = CASE
            WHEN v_total_spent >= 10000 THEN 'Platinum'
            WHEN v_total_spent >= 5000 THEN 'Gold'
            WHEN v_total_spent >= 1000 THEN 'Silver'
            ELSE 'Bronze'
        END;

        -- إضافة نقاط إضافية للطلبات المتكررة
        IF v_order_count > 20 THEN
            SET v_reward_points = v_reward_points + 500;
        ELSEIF v_order_count > 10 THEN
            SET v_reward_points = v_reward_points + 250;
        END IF;

        -- تحديث مكافآت العميل
        UPDATE customers
        SET
            reward_points = v_reward_points,
            reward_tier = v_reward_tier,
            rewards_updated_at = NOW()
        WHERE customer_id = v_customer_id;

        -- تسجيل التحديث
        INSERT INTO reward_log (customer_id, points_awarded, tier, created_at)
        VALUES (v_customer_id, v_reward_points, v_reward_tier, NOW());
    END LOOP;

    CLOSE customer_cursor;

    -- رسالة ملخص
    SELECT CONCAT('تمت معالجة المكافآت لـ ', ROW_COUNT(), ' عميل') AS message;
END //

DELIMITER ;

مؤشرات متداخلة

يمكنك استخدام مؤشرات متعددة، حتى متداخلة، للمعالجة المعقدة:

DELIMITER //

CREATE PROCEDURE generate_order_summaries()
BEGIN
    DECLARE done1, done2 INT DEFAULT FALSE;
    DECLARE v_customer_id INT;
    DECLARE v_customer_name VARCHAR(255);
    DECLARE v_order_id INT;
    DECLARE v_order_date DATE;
    DECLARE v_total_amount DECIMAL(10,2);
    DECLARE v_summary TEXT;

    -- المؤشر الخارجي: العملاء
    DECLARE customer_cursor CURSOR FOR
        SELECT customer_id, customer_name
        FROM customers
        WHERE status = 'active'
        LIMIT 100; -- حد للأداء

    -- المؤشر الداخلي: الطلبات لكل عميل
    DECLARE order_cursor CURSOR FOR
        SELECT order_id, order_date, total_amount
        FROM orders
        WHERE customer_id = v_customer_id
        ORDER BY order_date DESC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;

    OPEN customer_cursor;

    customer_loop: LOOP
        FETCH customer_cursor INTO v_customer_id, v_customer_name;

        IF done1 THEN
            LEAVE customer_loop;
        END IF;

        -- تهيئة الملخص لهذا العميل
        SET v_summary = CONCAT('طلبات ', v_customer_name, ':\n');
        SET done2 = FALSE;

        -- معالجة الطلبات لهذا العميل
        OPEN order_cursor;

        order_loop: LOOP
            FETCH order_cursor INTO v_order_id, v_order_date, v_total_amount;

            IF done2 THEN
                LEAVE order_loop;
            END IF;

            SET v_summary = CONCAT(
                v_summary,
                'طلب #', v_order_id,
                ' - ', v_order_date,
                ' - $', v_total_amount,
                '\n'
            );
        END LOOP;

        CLOSE order_cursor;

        -- تخزين الملخص
        INSERT INTO customer_summaries (customer_id, summary, generated_at)
        VALUES (v_customer_id, v_summary, NOW());

        -- إعادة تعيين معالج المؤشر الداخلي
        SET done2 = FALSE;
    END LOOP;

    CLOSE customer_cursor;
END //

DELIMITER ;

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

SQL الديناميكي مع PREPARE

يتيح لك SQL الديناميكي بناء وتنفيذ عبارات SQL في وقت التشغيل:

DELIMITER //

CREATE PROCEDURE dynamic_table_query(
    IN table_name VARCHAR(64),
    IN condition VARCHAR(255)
)
BEGIN
    SET @sql = CONCAT(
        'SELECT * FROM ',
        table_name,
        ' WHERE ',
        condition
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

-- SQL ديناميكي أكثر تعقيداً
CREATE PROCEDURE generate_report(
    IN report_type VARCHAR(50),
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
    SET @sql = CASE report_type
        WHEN 'sales' THEN
            CONCAT(
                'SELECT DATE(order_date) AS date, ',
                'SUM(total_amount) AS revenue, ',
                'COUNT(*) AS orders ',
                'FROM orders ',
                'WHERE order_date BETWEEN \'', start_date, '\' AND \'', end_date, '\' ',
                'GROUP BY DATE(order_date)'
            )
        WHEN 'customers' THEN
            CONCAT(
                'SELECT customer_id, customer_name, ',
                'COUNT(order_id) AS total_orders ',
                'FROM customers c ',
                'JOIN orders o ON c.customer_id = o.customer_id ',
                'WHERE order_date BETWEEN \'', start_date, '\' AND \'', end_date, '\' ',
                'GROUP BY c.customer_id, c.customer_name'
            )
        ELSE
            'SELECT "نوع تقرير غير صالح" AS error'
    END;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

معالجة الأخطاء المتقدمة

معالجة أخطاء متطورة مع رموز أخطاء محددة:

DELIMITER //

CREATE PROCEDURE safe_customer_update(
    IN p_customer_id INT,
    IN p_email VARCHAR(255),
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE duplicate_entry CONDITION FOR 1062;
    DECLARE foreign_key_error CONDITION FOR 1452;
    DECLARE EXIT HANDLER FOR duplicate_entry
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'عنوان البريد الإلكتروني موجود بالفعل';
    END;

    DECLARE EXIT HANDLER FOR foreign_key_error
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'معرف عميل غير صالح';
    END;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            @sqlstate = RETURNED_SQLSTATE,
            @errno = MYSQL_ERRNO,
            @text = MESSAGE_TEXT;

        SET p_success = FALSE;
        SET p_message = CONCAT('خطأ ', @errno, ': ', @text);
    END;

    -- محاولة التحديث
    UPDATE customers
    SET email = p_email, updated_at = NOW()
    WHERE customer_id = p_customer_id;

    SET p_success = TRUE;
    SET p_message = 'تم تحديث العميل بنجاح';
END //

DELIMITER ;

مؤشر مع التحكم في المعاملات

DELIMITER //

CREATE PROCEDURE batch_order_processing()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_order_id INT;
    DECLARE v_customer_id INT;
    DECLARE v_error_count INT DEFAULT 0;
    DECLARE v_success_count INT DEFAULT 0;

    DECLARE order_cursor CURSOR FOR
        SELECT order_id, customer_id
        FROM orders
        WHERE status = 'pending'
        LIMIT 1000;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error_count = v_error_count + 1;

    OPEN order_cursor;

    process_loop: LOOP
        FETCH order_cursor INTO v_order_id, v_customer_id;

        IF done THEN
            LEAVE process_loop;
        END IF;

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

        -- معالجة الطلب
        UPDATE orders
        SET status = 'processing', processed_at = NOW()
        WHERE order_id = v_order_id;

        -- تحديث المخزون
        UPDATE products p
        JOIN order_items oi ON p.product_id = oi.product_id
        SET p.stock_quantity = p.stock_quantity - oi.quantity
        WHERE oi.order_id = v_order_id;

        -- تحديث إحصائيات العميل
        UPDATE customers
        SET total_orders = total_orders + 1
        WHERE customer_id = v_customer_id;

        -- التزام إذا لم تكن هناك أخطاء
        IF v_error_count = 0 THEN
            COMMIT;
            SET v_success_count = v_success_count + 1;
        ELSE
            ROLLBACK;
            SET v_error_count = 0; -- إعادة التعيين للتكرار التالي
        END IF;
    END LOOP;

    CLOSE order_cursor;

    -- إرجاع الملخص
    SELECT
        v_success_count AS orders_processed,
        (SELECT COUNT(*) FROM orders WHERE status = 'pending') AS pending_orders;
END //

DELIMITER ;

بدائل خالية من المؤشرات

في كثير من الأحيان، يمكنك تجنب المؤشرات تماماً بعمليات تعتمد على المجموعات:

-- بدلاً من مؤشر لتحديث فئات العملاء
-- سيئ: استخدام مؤشر (بطيء)
DELIMITER //
CREATE PROCEDURE update_tiers_cursor()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_spent DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT customer_id, total_spent FROM customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    LOOP
        FETCH cur INTO v_id, v_spent;
        IF done THEN LEAVE; END IF;
        UPDATE customers SET tier = CASE
            WHEN v_spent > 10000 THEN 'Platinum'
            WHEN v_spent > 5000 THEN 'Gold'
            ELSE 'Bronze'
        END WHERE customer_id = v_id;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- جيد: استخدام عملية تعتمد على المجموعات (سريع)
UPDATE customers
SET tier = CASE
    WHEN total_spent > 10000 THEN 'Platinum'
    WHEN total_spent > 5000 THEN 'Gold'
    ELSE 'Bronze'
END;

نصيحة أداء: يمكن أن تكون عملية تعتمد على المجموعات أسرع بـ 100-1000 مرة من المؤشر. حاول دائماً حل المشكلات باستخدام SQL القياسي قبل اللجوء إلى المؤشرات.

مثال واقعي: ترحيل البيانات

DELIMITER //

CREATE PROCEDURE migrate_legacy_data()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_old_id INT;
    DECLARE v_old_data VARCHAR(255);
    DECLARE v_new_id INT;
    DECLARE v_migrated INT DEFAULT 0;
    DECLARE v_errors INT DEFAULT 0;

    DECLARE legacy_cursor CURSOR FOR
        SELECT id, data_field
        FROM legacy_table
        WHERE migrated = 0
        LIMIT 10000;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET v_errors = v_errors + 1;
        ROLLBACK;
    END;

    OPEN legacy_cursor;

    migrate_loop: LOOP
        FETCH legacy_cursor INTO v_old_id, v_old_data;

        IF done THEN
            LEAVE migrate_loop;
        END IF;

        START TRANSACTION;

        -- تحويل وإدراج البيانات
        INSERT INTO new_table (data, legacy_id, migrated_at)
        VALUES (UPPER(v_old_data), v_old_id, NOW());

        SET v_new_id = LAST_INSERT_ID();

        -- وضع علامة كمرحّل
        UPDATE legacy_table
        SET migrated = 1, new_id = v_new_id
        WHERE id = v_old_id;

        COMMIT;
        SET v_migrated = v_migrated + 1;

        -- تقرير التقدم كل 1000 سجل
        IF v_migrated % 1000 = 0 THEN
            INSERT INTO migration_log (records_migrated, timestamp)
            VALUES (v_migrated, NOW());
        END IF;
    END LOOP;

    CLOSE legacy_cursor;

    -- الملخص النهائي
    INSERT INTO migration_log (records_migrated, errors, timestamp)
    VALUES (v_migrated, v_errors, NOW());
END //

DELIMITER ;

تصحيح الإجراءات المخزنة

DELIMITER //

CREATE PROCEDURE debug_example()
BEGIN
    DECLARE v_step INT DEFAULT 1;

    -- إنشاء جدول مؤقت للتصحيح
    CREATE TEMPORARY TABLE IF NOT EXISTS debug_log (
        step INT,
        message VARCHAR(255),
        logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    INSERT INTO debug_log (step, message)
    VALUES (v_step, 'بدأ الإجراء');

    SET v_step = v_step + 1;

    -- المنطق الخاص بك هنا
    INSERT INTO debug_log (step, message)
    VALUES (v_step, CONCAT('معالجة الطلب ', order_id));

    SET v_step = v_step + 1;

    -- منطق إضافي...
    INSERT INTO debug_log (step, message)
    VALUES (v_step, 'اكتمل الإجراء');

    -- عرض سجل التصحيح
    SELECT * FROM debug_log ORDER BY step;
END //

DELIMITER ;

تمرين عملي:

إنشاء نظام تنفيذ طلبات متقدم:

أنشئ إجراءً يستخدم مؤشراً لمعالجة الطلبات المعلقة:
- التحقق من توفر المخزون لجميع العناصر
- إذا كان المخزون كافياً، احجز المخزون ووضع علامة على الطلب كمؤكد
- إذا كان المخزون غير كافٍ، وضع علامة على الطلب كطلب متأخر
- تحديث حالة إشعار العميل
- تسجيل جميع الإجراءات مع الطوابع الزمنية
أضف التحكم في المعاملات لكل طلب
نفذ معالجة أخطاء شاملة
أرجع ملخصاً للطلبات المعالجة والمؤكدة والمتأخرة

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

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

الملخص

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

المؤشرات تسمح بالمعالجة صفاً تلو الآخر لمجموعات النتائج
دورة حياة المؤشر: DECLARE و OPEN و FETCH و CLOSE
استخدم CONTINUE HANDLER للتكرار على المؤشر
SQL الديناميكي مع PREPARE/EXECUTE/DEALLOCATE
معالجة الأخطاء المتقدمة مع شروط محددة
التحكم في المعاملات داخل حلقات المؤشر
العمليات المستندة إلى المجموعات تكون دائماً أسرع من المؤشرات تقريباً
يجب أن تكون المؤشرات الملاذ الأخير، وليس الحل الأول

التالي: في الدرس الأخير من هذه الوحدة، سنستكشف مخطط المعلومات للفحص الذاتي لقاعدة البيانات واستعلامات البيانات الوصفية!