Cursor Basics

A cursor is a tool used in stored procedures to process a recordset.

# Basic Parts:
# Containers for cursor values:
DECLARE csr_id INT;
DECLARE csr_month_key VARCHAR(10);
DECLARE csr_month DATE;

DECLARE csr_done INT DEFAULT 0;	# Must go before cursor
# The Cursor:
DECLARE csr_keys CURSOR FOR
    SELECT id, month_key, the_month FROM key_manager 
    ORDER BY MONTH(the_month);

# Cursor to find the end of the recordset:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET csr_done=1;

# Using cursor:
# Open cursor for reading:
OPEN csr_keys;
get_keys:LOOP
        # Stepping through cursor and assigning values into variables:
        FETCH csr_keys INTO csr_id, csr_month_key, csr_month;
        
        # Do something (here will do an update):
        IF MONTH(NOW()) > MONTH(csr_month) THEN
                UPDATE key_manager SET the_month = CONCAT(YEAR(NOW), '-',
                MONTH(csr_month), '-', DAY(csr_day));
        END IF;

        # Use this to break out of recordset when at last row:
	IF csr_done=1 THEN
		LEAVE get_keys;
	END IF;
END LOOP get_keys;