MySQL การใช้ Cursor ใน Stored Procedure เพื่อวนลูปประมวลผลข้อมูลทีละแถว

DECLARE cursor_name CURSOR FOR SELECT_statement;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor_name;
READ_LOOP: LOOP
  FETCH cursor_name INTO var1, var2, ...;
  IF done THEN
    LEAVE READ_LOOP;
  END IF;

  -- คำสั่งที่ต้องการทำกับแต่ละแถว

END LOOP;
CLOSE cursor_name;

DELIMITER //

CREATE PROCEDURE calculate_customer_totals()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE cid INT;
  DECLARE total DECIMAL(10,2);

  DECLARE cur CURSOR FOR
    SELECT customer_id, SUM(amount)
    FROM payments
    WHERE status = 'paid'
    GROUP BY customer_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO cid, total;
    IF done THEN
      LEAVE read_loop;
    END IF;

    INSERT INTO customer_totals(customer_id, total_payment)
    VALUES (cid, total);
  END LOOP;

  CLOSE cur;
END //

DELIMITER ;

CALL calculate_customer_totals();