Client Area

MySQL Command: WHILE

MySQL Command: WHILE

In MySQL, the WHILE command is a powerful tool that allows you to execute a block of code repeatedly as long as a specified condition is true. This command is particularly useful when you need to perform a certain task multiple times based on a specific condition.

Syntax

The syntax for the WHILE command in MySQL is as follows:

WHILE condition DO
    -- code to be executed
END WHILE;

The condition is an expression that evaluates to either TRUE or FALSE. If the condition is TRUE, the code block within the WHILE loop will be executed. Once the code block is executed, the condition will be evaluated again. If the condition is still TRUE, the code block will be executed again, and this process will continue until the condition becomes FALSE.

Example

Let’s say we have a table called employees with the following structure:

+----+----------+-----------+
| id | name     | salary    |
+----+----------+-----------+
| 1  | John     | 5000      |
| 2  | Jane     | 6000      |
| 3  | Michael  | 4500      |
| 4  | Emily    | 5500      |
+----+----------+-----------+

Now, suppose we want to increase the salary of each employee by 10% until their salary reaches $7000. We can achieve this using the WHILE command as follows:

DELIMITER $$
CREATE PROCEDURE increase_salary()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10, 2);
    
    -- Cursor to fetch employee data
    DECLARE cur CURSOR FOR SELECT id, salary FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO emp_id, emp_salary;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        IF emp_salary < 7000 THEN
            SET emp_salary = emp_salary * 1.1;
            UPDATE employees SET salary = emp_salary WHERE id = emp_id;
        END IF;
    END LOOP;
    
    CLOSE cur;
END$$
DELIMITER ;

In the above example, we first create a stored procedure called increase_salary. Inside the procedure, we declare three variables: done to indicate if the loop is done, emp_id to store the employee ID, and emp_salary to store the employee’s current salary.

We then declare a cursor to fetch the employee data from the employees table. The CONTINUE HANDLER is used to set the done variable to TRUE when there are no more rows to fetch.

Next, we open the cursor and start a loop using the LOOP statement. Inside the loop, we fetch the employee ID and salary into the respective variables. If the salary is less than $7000, we increase it by 10% and update the employees table.

The loop continues until there are no more rows to fetch, at which point the done variable is set to TRUE and the loop is exited.

Summary

The WHILE command in MySQL allows you to execute a block of code repeatedly as long as a specified condition is true. It is a powerful tool for performing tasks that require repetitive execution based on a specific condition. By using the WHILE command, you can automate processes and make your MySQL queries more efficient.

If you are looking for reliable and high-performance VPS hosting solutions, consider Server.HK. With a wide range of plans and excellent customer support, Server.HK is a trusted provider in the industry.