How to Loop Through All Rows of a MySQL Table

1. Loop Through All Rows of a MySQL Table
2. Use a WHILE Loop in a Stored Procedure to Loop Through All Rows of a MySQL Table
3. Use CURSOR in a Stored Procedure to Loop Through All Rows of a MySQL Table
4. Use a FOR Loop in a Stored Procedure to Loop Through All Rows of a MySQL Table
5. Comparison of WHILE, CURSOR, and FOR Usage in MySQL
6. Conclusion

Looping through rows of a MySQL table is a common operation in database programming. It allows you to process each record individually, perform calculations, apply updates, or extract specific information.

Today, we will be learning about the use of the WHILE loop, CURSOR, and FOR loop in a stored procedure to loop through all rows of a MySQL table. We will also explore the pros and cons of each technique to differentiate which one is suitable in what situation.

Loop Through All Rows of a MySQL Table

Looping through all rows of a MySQL table involves iterating through each row in a table and performing operations on the data contained within.

We will learn about various approaches that we can use to loop through all rows of a MySQL table to read/insert data from a particular or multiple tables. Let’s learn each of them with a code example.

For that purpose, we have two tables: employees and the other is emp_performance. The employees table has EMP_ID, FIRSTNAME, LASTNAME, GENDER, and AGE as attributes (also called column names).

The emp_performance table has PERFORM_ID, FIRSTNAME, LASTNAME, and PERFORMANCE fields, where the FIRSTNAME and LASTNAME are the same as the employees table.

Just imagine we have to copy FIRSTNAME and LASTNAME from the employees table and insert them into the emp_performance table to calculate the PERFORMANCE for each employee every month.

There must be a way to SELECT the necessary values from the employees table, INSERT into the emp_performance table, and continue with the PERFORMANCE calculation later. You can also create employees and emp_perfomance tables to continue with us; the code is given below.

#create an `employees` table
CREATE TABLE employees (
  EMP_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  GENDER VARCHAR(45) NOT NULL,
  AGE INT NOT NULL,
  PRIMARY KEY (EMP_ID));

#insert data
INSERT INTO employees (FIRSTNAME, LASTNAME, GENDER, AGE) VALUES
('Mehvish','Ashiq', 'Female', 30),
('Thomas', 'Christopher', 'Male', 22),
('John', 'Daniel', 'Male', 34),
('Saira', 'James', 'Female', 27);

#create a `emp_performance` table
CREATE TABLE emp_performance (
  PERFORM_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  PERFORMANCE  VARCHAR(45) NULL,
  PRIMARY KEY (PERFORM_ID));

We can use WHILE, CURSOR, and FOR in a stored procedure to loop through all rows of the employees table and INSERT into the emp_performance table.

Use a WHILE Loop in a Stored Procedure to Loop Through All Rows of a MySQL Table

The WHILE loop is a control flow construct in MySQL that allows a block of code to be executed repeatedly as long as a specified condition is true. This loop is particularly useful when the exact number of iterations is not known beforehand.

The basic syntax of a WHILE loop in MySQL is as follows:

WHILE condition DO
    -- Code to be executed for each iteration
END WHILE;

Here, condition is a Boolean expression that determines whether the loop should continue executing. As long as condition evaluates to true, the code within the loop will be executed.

Now that we have our tables ready, we can write and execute the following procedure to SELECT the FIRSTNAME and LASTNAME from the employees table and INSERT into the emp_performance table.

Example Code:

DROP PROCEDURE IF EXISTS CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE CALCPERFORMANCE()
BEGIN
DECLARE length INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
SELECT COUNT(*) FROM employees INTO length;
SET counter=0;
WHILE counter<length DO
  INSERT INTO emp_performance(FIRSTNAME, LASTNAME)
  SELECT FIRSTNAME, LASTNAME FROM employees LIMIT counter,1;
  SET counter = counter + 1;
END WHILE;
End;
;;

DELIMITER ;
CALL CALCPERFORMANCE();

This code defines a stored procedure called CALCPERFORMANCE that performs a specific task. The procedure begins by checking if a procedure named CALCPERFORMANCE already exists and drops it if it does.

The DELIMITER ;; statement changes the delimiter used in the SQL script to ;; instead of the default ;, allowing for more complex commands to be included in the procedure.

Inside the procedure, two variables, length and counter, are declared to hold integer values. The length is initialized to 0, while the counter is set to 0.

The procedure then counts the number of records in the employees table and stores this count in the length variable.

Next, a WHILE loop is initiated, and it iterates as long as counter is less than length. Within the loop, an INSERT INTO statement is used to add a record to the emp_performance table.

This record contains the FIRSTNAME and LASTNAME values from the employees table, selected based on the current value of counter.

After the record is inserted, counter is incremented by 1, and the loop continues. This process repeats until counter is no longer less than length.

Finally, the procedure ends with the END statement. The DELIMITER ; statement is used to reset the delimiter back to the default value. The procedure is called using CALL CALCPERFORMANCE();.

Once the records are inserted, use the SELECT command to see the emp_performance table.

SELECT * from emp_performance;

Output (emp_performance table using WHILE loop):

+------------+-----------+-------------+-------------+
| PERFORM_ID | FIRSTNAME | LASTNAME    | PERFORMANCE |
+------------+-----------+-------------+-------------+
|          1 | Mehvish   | Ashiq       | NULL        |
|          2 | Thomas    | Christopher | NULL        |
|          3 | John      | Daniel      | NULL        |
|          4 | Saira     | James       | NULL        |
+------------+-----------+-------------+-------------+
4 rows in set (0.001 sec)

Once you execute the CALCPERFORMANCE stored procedure, only PERFORM_ID, FIRSTNAME, and LASTNAME will be populated.

Similarly, we can use CURSOR to loop through all table rows in MySQL.

Use CURSOR in a Stored Procedure to Loop Through All Rows of a MySQL Table

A CURSOR in MySQL is a database object that allows you to process individual rows returned by a query.

It’s particularly useful when dealing with result sets that contain multiple rows. Cursors are used within stored procedures, functions, and triggers.

A cursor generally involves the following operations:

• Declaration: This is where the cursor is defined, specifying the query it will execute.
• Opening: The cursor is opened, allowing it to fetch rows from the result set.
• Fetching: Rows are retrieved one at a time and can be processed.
• Closing: After all rows are processed, the cursor is closed.
• Deallocating: The cursor is removed from memory.

We use a CURSOR to handle the result set in a stored procedure. It lets us loop through a set of records (rows) returned by a query and process every row individually.

One must have the following properties of CURSOR while using it.

• The CURSOR is sensitive; it is unnecessary that the server also makes the result table’s copy.
• The CURSOR is not updatable because it’s read-only.
• The CURSOR is non-scrollable. We can only traverse it in one direction without skipping and jumping through records (rows) in a result set.

Let’s go through an example of creating a stored procedure that utilizes a CURSOR to iterate through all rows of a table. For this example, we’ll consider a table named employees.

DROP PROCEDURE IF EXISTS cursor_CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE cursor_CALCPERFORMANCE()
BEGIN
DECLARE cursor_FIRSTNAME VARCHAR(45) DEFAULT "";
DECLARE cursor_LASTNAME VARCHAR(45) DEFAULT "";
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_e CURSOR FOR SELECT FIRSTNAME,LASTNAME FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_e;
read_loop: LOOP
  FETCH cursor_e INTO cursor_FIRSTNAME, cursor_LASTNAME;
  IF done THEN
    LEAVE read_loop;
  END IF;
  INSERT INTO emp_performance (FIRSTNAME,LASTNAME)
  VALUES (cursor_FIRSTNAME, cursor_LASTNAME);
END LOOP;
CLOSE cursor_e;
END;
;;

DELIMITER ;
CALL cursor_CALCPERFORMANCE();

The above code defines a stored procedure named cursor_CALCPERFORMANCE, which utilizes a cursor to process rows from the employees table. The procedure starts by checking if a procedure with the same name already exists and drops it if it does.

The DELIMITER ;; statement changes the delimiter used in the script to ;; instead of the default ;, allowing for more complex commands to be included.

Inside the procedure, four variables are declared: cursor_FIRSTNAME and cursor_LASTNAME as VARCHAR strings to hold first and last names, and done as a Boolean-like integer for loop control.

A cursor cursor_e is declared to select FIRSTNAME and LASTNAME from the employees table. A handler is established to manage exceptions in case no more rows are found.

The cursor is then opened with OPEN cursor_e; and a loop named read_loop is initiated.

Within this loop, FETCH cursor_e INTO cursor_FIRSTNAME, cursor_LASTNAME; retrieves the next set of names from the cursor, and IF done THEN LEAVE read_loop; checks if there are no more rows. If so, it exits the loop.

For each pair of names, an INSERT INTO statement is used to add a record to the emp_performance table, transferring the first and last names. This process continues until all rows are processed.

Next, the cursor is closed with CLOSE cursor_e;. The DELIMITER ; statement is used to reset the delimiter back to the default value.

Finally, the procedure is called with CALL cursor_CALCPERFORMANCE();.

Once the records are inserted, use the SELECT command to see the emp_performance output.

SELECT * from emp_performance;

Output (emp_performance table using CURSOR):

+------------+-----------+-------------+-------------+
| PERFORM_ID | FIRSTNAME | LASTNAME    | PERFORMANCE |
+------------+-----------+-------------+-------------+
|          1 | Mehvish   | Ashiq       | NULL        |
|          2 | Thomas    | Christopher | NULL        |
|          3 | John      | Daniel      | NULL        |
|          4 | Saira     | James       | NULL        |
+------------+-----------+-------------+-------------+
4 rows in set (0.000 sec)

Use a FOR Loop in a Stored Procedure to Loop Through All Rows of a MySQL Table

MySQL supports various loop constructs, and the FOR loop is one of them.

The FOR loop is particularly useful when you know in advance how many iterations you need to perform. It allows you to specify a range of values and execute a block of code for each value in that range.

The basic syntax of a FOR loop in MySQL is as follows:

BEGIN
    DECLARE loop_counter INT;

    FOR loop_counter IN range DO
        -- Code to be executed for each iteration
    END FOR;
END

Here, loop_counter is the loop variable that will take on values from the specified range. The loop will iterate over the range, and for each iteration, the code within the loop will be executed.

To loop through all rows of a MySQL table, you’ll need to determine the range for the loop and fetch the corresponding rows in each iteration.

Let’s see an example of how this can be done.

DELIMITER $$

CREATE PROCEDURE ProcessEmployees()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE max_rows INT;
    DECLARE id INT;
    DECLARE first_name VARCHAR(255);
	DECLARE last_name VARCHAR(255);

    -- Get the total number of rows in the employees table
    SELECT COUNT(*) INTO max_rows FROM employees;

    -- Loop through the result set
    FOR i IN 1..max_rows DO
        -- Retrieve employee details
        SELECT EMP_ID, FIRSTNAME, LASTNAME INTO id, first_name, last_name
        FROM employees WHERE EMP_ID = i;

        -- Perform operations with emp_id and emp_name
        -- (e.g., INSERT INTO another table, perform calculations, etc.)
        INSERT INTO emp_performance (FIRSTNAME,LASTNAME) VALUES (first_name, last_name);

    END FOR;
END$$

DELIMITER ;

This example uses a FOR loop to fetch and process each row of the employees table. It counts the total number of rows, then iterates through them, extracting specific details for further operations and, in this example, inserting them into another table named emp_performance.

This code defines a stored procedure named ProcessEmployees that iterates through all the rows of a table called employees.

First, the DELIMITER $$ statement changes the delimiter temporarily to $$ to allow the definition of the procedure with semicolons.

Inside the procedure, several variables are declared: i as an integer counter starting at 1, max_rows to store the total number of rows in the employees table, and id, first_name, and last_name to hold employee details.

The query SELECT COUNT(*) INTO max_rows FROM employees; counts the total number of rows in the employees table and assigns it to max_rows.

The FOR i IN 1..max_rows DO ... END FOR; loop is initiated. It iterates from 1 to the total number of rows.

Within the loop, SELECT EMP_ID, FIRSTNAME, LASTNAME INTO id, first_name, last_name FROM employees WHERE EMP_ID = i; fetch the EMP_ID, FIRSTNAME, and LASTNAME of the employee corresponding to the current value of i and assigns them to the respective variables.

Next, an operation is performed using id, first_name, and last_name. In this case, an insertion into another table named emp_performance is executed with the values of first_name and last_name.

Finally, DELIMITER ; resets the delimiter back to the default semicolon.

Execute the procedure and look into the emp_performance table using the following SQL statement:

CALL ProcessEmployees();
SELECT * from emp_performance;

This will initiate the loop, and you’ll see the emp_performance table.

Output:

+------------+-----------+-------------+-------------+
| PERFORM_ID | FIRSTNAME | LASTNAME    | PERFORMANCE |
+------------+-----------+-------------+-------------+
|          1 | Mehvish   | Ashiq       | NULL        |
|          2 | Thomas    | Christopher | NULL        |
|          3 | John      | Daniel      | NULL        |
|          4 | Saira     | James       | NULL        |
+------------+-----------+-------------+-------------+
4 rows in set (0.000 sec)

Comparison of WHILE, CURSOR, and FOR Usage in MySQL

When it comes to looping through all rows of a MySQL table, you have several options at your disposal. Three common methods include using a WHILE loop, a FOR loop, and a CURSOR.

Each method has its own set of advantages and disadvantages, which we’ll explore in detail below.

WHILE Loop

Pros:

• The WHILE loop allows for a wide range of conditional expressions, providing great flexibility in controlling the loop’s behavior.
• The loop’s condition can be updated within the loop, allowing for dynamic changes to the iteration process.
• It’s well-suited for scenarios where the iteration logic is complex or not easily expressible using other loop types.
• It is faster and uses minimum locks than the CURSOR.
• They don’t make a copy of data in the tempdb.

Cons:

• If the condition is not properly controlled, there’s a risk of creating an infinite loop.
• You need to manually manage loop variables, including initialization, condition, and update.
• Difficult to move backward and forward.
• There is a risk of the infinite loop if it is not handled properly.

FOR Loop

Pros:

• The FOR loop provides a more streamlined syntax compared to the WHILE loop, making it easier to write and understand.
• When the number of iterations is known or can be calculated in advance, a FOR loop is a natural choice.
• The loop variable is automatically initialized and updated, reducing the risk of overlooking these steps.

Cons:

• The loop’s condition is typically based on a numerical range, which may not be suitable for all scenarios.
• It’s less adaptable to situations where the iteration process needs to be dynamically adjusted.

CURSOR

Pros:

• The CURSOR is specifically designed for iterating over result sets returned from a query, making it ideal for database operations.
• It can handle multiple columns returned by a query, providing a convenient way to process complex data structures.
• The cursor automatically fetches rows from the result set, simplifying the iteration process.
• We can pass cursors to the stored procedures.
• Cursors do not require a condition, and we can move backward and forward in the CURSOR.

Cons:

• Setting up and using a cursor involves more code compared to simple loop constructs, which can lead to increased complexity.
• Cursors may consume more server resources, particularly when dealing with large result sets.
• The performance decreases as compared to using CTE or the WHILE loop.
• Having global cursors in the code may lead to the risk of errors since the CURSOR may be closed by a stored procedure nested in the code.

When to Use Each Method

The choice between WHILE, FOR, and CURSOR depends on the specific requirements of your task. Here are some guidelines:

• WHILE Loop: Use this when the number of iterations is not known in advance or when you need flexibility in defining the loop termination condition.
• FOR Loop: Employ this when you have a predefined range of iterations, such as processing a set of integers or dates.
• CURSOR: Opt for a cursor when you need to perform complex operations on each row or when you want more control over row retrieval from a specific query result.

Remember, the most suitable method may also depend on your coding style, the specific MySQL version, and the complexity of the task at hand. It’s always a good practice to thoroughly test and benchmark different methods to ensure optimal performance.

Conclusion

This article covers various techniques for looping through rows of a MySQL table, essential for tasks like processing individual records. Three methods are discussed: the versatile WHILE loop, suitable for dynamic iterations; the structured FOR loop, ideal for known iteration ranges; and the specialized CURSOR, designed for a result set processing.

Each method has distinct strengths and considerations, so check each method to see which one suits you best.