MySQL: Loop Through Rows

There is often a requirement to loop through rows of a MySQL table and process the data from each row. In this article, we will be doing similar operations to understand better how to loop through rows of a table.

Table of Contents:

• Introduction
• MySQL loop through rows and INSERT
• MySQL loop through rows to fetch data into a variable

Introduction

We will be using cursors to run through each row of a table in a loop.

Cursors supported by MySQL are used to process through each row individually of a result set. There are few properties of a cursor.

• Cursors are asensitive: The server may not necessarily make a copy of its result table.
• Cursors are read-only: Not updatable.
• Cursors are nonscrollable: Cursors can be traversed only in one direction and cannot skip or jump through rows in the result set.

In the below sections, we will be taking a few examples to understand the concept.

MySQL loop through rows and INSERT

This section will create a cursor to fetch each row from one table and insert the data into another table. Let us start by looking into the data to be used in the example.

Assume that we have a table student_enroll_date with the below rows.

SELECT * FROM student_enroll_date;

Action message Output Response:-

19 row(s) returned.

Output:-

There is another table student_enroll_date_duplicate, empty at the moment.

SELECT * FROM student_enroll_date_duplicate;

Action message Output Response:-

0 row(s) returned.

Output:-

Let us now write the cursor and insert rows into student_enroll_date_duplicate

DROP PROCEDURE IF EXISTS proc_cursor_to_loopAndInsert;
DELIMITER ;;
CREATE PROCEDURE proc_cursor_to_loopAndInsert()
BEGIN
  DECLARE CURSOR_STUDENT_ID INT;
  DECLARE CURSOR_ENROLL_DATE DATE;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_studentEnrollDate CURSOR FOR SELECT student_id, enroll_date FROM student_enroll_date;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cursor_studentEnrollDate;
  loop_through_rows: LOOP
    FETCH cursor_studentEnrollDate INTO CURSOR_STUDENT_ID,CURSOR_ENROLL_DATE;
    IF done THEN
      LEAVE loop_through_rows;
    END IF;
    INSERT INTO student_enroll_date_duplicate(student_id,enroll_date,duplicate_flag) VALUES(CURSOR_STUDENT_ID,CURSOR_ENROLL_DATE,TRUE);
  END LOOP;
  CLOSE cursor_studentEnrollDate;
END;
;;

Explanation:-

1. Procedure proc_cursor_to_loopAndInsert() demonstrates the working of a cursor named cursor_studentEnrollDate within it.
2. The cursor cursor_studentEnrollDate is declared for data retrieved from SELECT student_id, enroll_date FROM student_enroll_date.
3. Open the cursor cursor_studentEnrollDate. The processing is done inside the cursor to fetch each row’s value into two variables CURSOR_STUDENT_ID and CURSOR_ENROLL_DATE.
4. Using the variables CURSOR_STUDENT_ID and CURSOR_ENROLL_DATE, data is inserted into the table student_enroll_date_duplicate to columns student_id and enroll_date, respectively.
5. An additional column duplicate_flag is populated with a boolean value (TRUE) 1 for all rows.
6. Close the cursor.

Let us now run the procedure proc_cursor_to_loopAndInsert() 

CALL proc_cursor_to_loopAndInsert();

To see what got inserted into the table student_enroll_date_duplicate let us execute:

SELECT * FROM student_enroll_date_duplicate;

Action message Output Response:-

19 row(s) returned.

Output:-

Rows are inserted into the table student_enroll_date_duplicate.

MySQL loop through rows to fetch data into a variable

Another example is to loop through rows and get the data into a variable. 

Let us get started by looking into the sample table students_details.

SELECT * FROM students_details;

Action message Output Response:-

8 row(s) returned.

Output:-

Here we will be looping through all the rows of the table students_details and retrieve the values from columns first_name and last_name. These values will be displayed as comma-separated string in the output.

We will write a cursor within a procedure proc_cursor_to_GetNames to fulfill the task.

DROP PROCEDURE IF EXISTS proc_cursor_to_GetNames;
DELIMITER ;;
CREATE PROCEDURE proc_cursor_to_GetNames(
INOUT nameList varchar(4000)
)
BEGIN
  DECLARE c_first_name varchar(100) DEFAULT "";
  DECLARE c_last_name varchar(100) DEFAULT "";

  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_studentNames CURSOR FOR SELECT first_name,last_name FROM students_details;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cursor_studentNames;
  loop_through_rows: LOOP
    FETCH cursor_studentNames INTO c_first_name,c_last_name;
    IF done THEN
      LEAVE loop_through_rows;
    END IF;
    SET nameList = CONCAT(c_first_name," ",c_last_name, " , ",nameList);
    SET nameList = TRIM(TRAILING ' , ' FROM nameList ); 
  END LOOP;
  CLOSE cursor_studentNames;
END;
;;

Explanation:-

1. Procedure proc_cursor_to_GetNames() demonstrates the working of a cursor named cursor_studentNames inside it.
2. The cursor cursor_studentNames is declared for data received from SELECT first_name, last_name FROM students_details.
3. The cursor_studentNames is then opened, the processing is done inside the cursor to fetch each row’s value into two variables c_first_name and c_last_name.
4. Insert the First name and Last name from c_first_name and c_last_name into the variable nameList concatenated in every iteration using the CONCAT() function.
5. Remove the trailing “, ” from nameList using TRIM() function.
6. Close the cursor.

Let us now run the procedure proc_cursor_to_GetNames()

SET @listOfStudentNames = ""; 
CALL proc_cursor_to_GetNames(@listOfStudentNames); 
SELECT @listOfStudentNames;

Action message Output Response:-

1 row(s) returned.

Output:-

We hope this article helped you to get an understanding of looping through rows using cursors. Good Luck !!!