MySQL & Database Design

Cursors & Advanced Procedures

13 min Lesson 24 of 40

Cursors & Advanced Procedures

Cursors allow you to iterate through query results row by row within stored procedures. Combined with advanced techniques like dynamic SQL and sophisticated error handling, they enable complex data processing that would be difficult or impossible with standard SQL. In this lesson, we'll master cursors and advanced procedure patterns.

What are Cursors?

A cursor is a database object that allows you to traverse and manipulate result sets one row at a time. Think of it as a pointer that moves through query results.

Key Concept: Cursors are powerful but slower than set-based operations. Use them only when row-by-row processing is necessary.

Basic Cursor Syntax

Cursors follow a specific lifecycle: DECLARE, OPEN, FETCH, CLOSE:

DELIMITER //

CREATE PROCEDURE process_customers()
BEGIN
    -- Variables to hold cursor data
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_customer_id INT;
    DECLARE v_customer_name VARCHAR(255);
    DECLARE v_total_spent DECIMAL(10,2);

    -- Declare the cursor
    DECLARE customer_cursor CURSOR FOR
        SELECT customer_id, customer_name, total_spent
        FROM customers
        WHERE status = 'active'
        ORDER BY total_spent DESC;

    -- Declare handler for end of cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN customer_cursor;

    -- Loop through cursor
    read_loop: LOOP
        -- Fetch next row
        FETCH customer_cursor INTO v_customer_id, v_customer_name, v_total_spent;

        -- Exit loop if no more rows
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Process the row
        INSERT INTO customer_report (customer_id, name, spending, processed_at)
        VALUES (v_customer_id, v_customer_name, v_total_spent, NOW());
    END LOOP;

    -- Close the cursor
    CLOSE customer_cursor;
END //

DELIMITER ;

Cursor with Complex Processing

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;

        -- Calculate reward points (1 point per dollar spent)
        SET v_reward_points = FLOOR(v_total_spent);

        -- Determine tier based on spending
        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;

        -- Add bonus points for frequent orders
        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 customer rewards
        UPDATE customers
        SET
            reward_points = v_reward_points,
            reward_tier = v_reward_tier,
            rewards_updated_at = NOW()
        WHERE customer_id = v_customer_id;

        -- Log the update
        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;

    -- Summary message
    SELECT CONCAT('Processed rewards for ', ROW_COUNT(), ' customers') AS message;
END //

DELIMITER ;

Nested Cursors

You can use multiple cursors, even nested ones, for complex processing:

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;

    -- Outer cursor: customers
    DECLARE customer_cursor CURSOR FOR
        SELECT customer_id, customer_name
        FROM customers
        WHERE status = 'active'
        LIMIT 100; -- Limit for performance

    -- Inner cursor: orders per customer
    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;

        -- Initialize summary for this customer
        SET v_summary = CONCAT('Orders for ', v_customer_name, ':\n');
        SET done2 = FALSE;

        -- Process orders for this customer
        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,
                'Order #', v_order_id,
                ' - ', v_order_date,
                ' - $', v_total_amount,
                '\n'
            );
        END LOOP;

        CLOSE order_cursor;

        -- Store the summary
        INSERT INTO customer_summaries (customer_id, summary, generated_at)
        VALUES (v_customer_id, v_summary, NOW());

        -- Reset handler for inner cursor
        SET done2 = FALSE;
    END LOOP;

    CLOSE customer_cursor;
END //

DELIMITER ;

Performance Warning: Nested cursors can be extremely slow. Always consider set-based alternatives first. Use cursors only when absolutely necessary.

Dynamic SQL with PREPARE

Dynamic SQL allows you to build and execute SQL statements at runtime:

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 //

-- More complex dynamic 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 "Invalid report type" AS error'
    END;

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

DELIMITER ;

Advanced Error Handling

Sophisticated error handling with specific error codes:

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 = 'Email address already exists';
    END;

    DECLARE EXIT HANDLER FOR foreign_key_error
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'Invalid customer ID';
    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('Error ', @errno, ': ', @text);
    END;

    -- Attempt the update
    UPDATE customers
    SET email = p_email, updated_at = NOW()
    WHERE customer_id = p_customer_id;

    SET p_success = TRUE;
    SET p_message = 'Customer updated successfully';
END //

DELIMITER ;

Cursor with Transaction Control

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 for each order
        START TRANSACTION;

        -- Process order
        UPDATE orders
        SET status = 'processing', processed_at = NOW()
        WHERE order_id = v_order_id;

        -- Update inventory
        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 customer stats
        UPDATE customers
        SET total_orders = total_orders + 1
        WHERE customer_id = v_customer_id;

        -- Commit if no errors
        IF v_error_count = 0 THEN
            COMMIT;
            SET v_success_count = v_success_count + 1;
        ELSE
            ROLLBACK;
            SET v_error_count = 0; -- Reset for next iteration
        END IF;
    END LOOP;

    CLOSE order_cursor;

    -- Return summary
    SELECT
        v_success_count AS orders_processed,
        (SELECT COUNT(*) FROM orders WHERE status = 'pending') AS pending_orders;
END //

DELIMITER ;

Cursor-Free Alternatives

Often, you can avoid cursors entirely with set-based operations:

-- Instead of cursor to update customer tiers
-- BAD: Using cursor (slow)
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 ;

-- GOOD: Using set-based operation (fast)
UPDATE customers
SET tier = CASE
    WHEN total_spent > 10000 THEN 'Platinum'
    WHEN total_spent > 5000 THEN 'Gold'
    ELSE 'Bronze'
END;

Performance Tip: A set-based operation can be 100-1000x faster than a cursor. Always try to solve problems with standard SQL before resorting to cursors.

Real-World Example: Data Migration

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;

        -- Transform and insert data
        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();

        -- Mark as migrated
        UPDATE legacy_table
        SET migrated = 1, new_id = v_new_id
        WHERE id = v_old_id;

        COMMIT;
        SET v_migrated = v_migrated + 1;

        -- Progress reporting every 1000 records
        IF v_migrated % 1000 = 0 THEN
            INSERT INTO migration_log (records_migrated, timestamp)
            VALUES (v_migrated, NOW());
        END IF;
    END LOOP;

    CLOSE legacy_cursor;

    -- Final summary
    INSERT INTO migration_log (records_migrated, errors, timestamp)
    VALUES (v_migrated, v_errors, NOW());
END //

DELIMITER ;

Debugging Stored Procedures

DELIMITER //

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

    -- Create temp table for debugging
    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, 'Procedure started');

    SET v_step = v_step + 1;

    -- Your logic here
    INSERT INTO debug_log (step, message)
    VALUES (v_step, CONCAT('Processing order ', order_id));

    SET v_step = v_step + 1;

    -- More logic...
    INSERT INTO debug_log (step, message)
    VALUES (v_step, 'Procedure completed');

    -- View debug log
    SELECT * FROM debug_log ORDER BY step;
END //

DELIMITER ;

Practice Exercise:

Create an Advanced Order Fulfillment System:

Create a procedure that uses a cursor to process pending orders:
- Check inventory availability for all items
- If sufficient stock, reserve inventory and mark order as confirmed
- If insufficient stock, mark order as back-ordered
- Update customer notification status
- Log all actions with timestamps
Add transaction control for each order
Implement comprehensive error handling
Return a summary of processed, confirmed, and back-ordered orders

Best Practices

✓ Use cursors only when row-by-row processing is unavoidable
✓ Always close cursors when done
✓ Limit cursor result sets (use WHERE, LIMIT)
✓ Use CONTINUE handlers for cursor loops
✓ Prefer set-based operations whenever possible
✓ Add transaction control for data modifications
✓ Implement comprehensive error handling
✓ Log progress for long-running procedures
✓ Use temporary tables for complex transformations
✗ Don't use nested cursors unless absolutely necessary
✗ Avoid processing millions of rows with cursors
✗ Don't forget to DEALLOCATE prepared statements

Summary

In this lesson, you learned:

Cursors allow row-by-row processing of result sets
Cursor lifecycle: DECLARE, OPEN, FETCH, CLOSE
Use CONTINUE HANDLER for cursor iteration
Dynamic SQL with PREPARE/EXECUTE/DEALLOCATE
Advanced error handling with specific conditions
Transaction control within cursor loops
Set-based operations are almost always faster than cursors
Cursors should be a last resort, not the first solution

Next Up: In the final lesson of this module, we'll explore the Information Schema for database introspection and metadata queries!