DELIMITER //

DROP PROCEDURE IF EXISTS ProcessSales;

CREATE PROCEDURE ProcessSales()
BEGIN
    DECLARE done INT DEFAULT false;
    DECLARE sale_total DECIMAL(10, 2);
    DECLARE sale_id DECIMAL(10,0);
    DECLARE grand_total DECIMAL(10,2);
    
    -- Declare the cursor
    DECLARE sales_cursor CURSOR FOR SELECT id, total FROM sales WHERE processed = false;

    -- Declare a SQL exception handler
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Handle SQL exceptions
        ROLLBACK;
        SELECT 'An error occurred. Transaction rolled back.';
    END;

    -- Declare a handler for the NOT FOUND condition
    -- Otherwise, we get an error when the data is finished
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

    -- Start a transaction
    START TRANSACTION;

    -- Open the cursor
    OPEN sales_cursor;

    -- Loop through the rows
    SET grand_total = 0.0;
    read_loop: LOOP
        FETCH sales_cursor INTO sale_id, sale_total;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Example logic: Process each sale and handle errors
        BEGIN
            -- Some processing logic (e.g., update sale status)
            SET grand_total = grand_total + sale_total;
            UPDATE sales SET processed = true WHERE id = sale_id;
            -- all done
            SELECT CONCAT("Sale id ", sale_id, 
                          " for amount ", sale_total, 
                          " processed successfully") as message;
        END;

    END LOOP;

    SELECT CONCAT("Total sales processed: ", grand_total) as Total_sales_processed;

    -- Close the cursor
    CLOSE sales_cursor;

    -- Commit the transaction
    COMMIT;
END //

DELIMITER ;

-- Call ProcessSales();

