MySQL · December 20, 2023

MySQL Command: LOOP

MySQL Command: LOOP

MySQL is a popular open-source relational database management system that is widely used for web applications and other data-driven projects. It provides a wide range of commands and functions to manipulate and query data efficiently. One such command is the LOOP command, which allows you to iterate over a set of records and perform certain actions based on specific conditions.

Understanding the LOOP Command

The LOOP command in MySQL is used to create a loop that iterates over a set of records until a specific condition is met. It is commonly used in stored procedures and functions to perform repetitive tasks or calculations. The basic syntax of the LOOP command is as follows:

LOOP
    -- statements to be executed
END LOOP;

Within the loop, you can include any valid SQL statements, such as SELECT, INSERT, UPDATE, or DELETE, to manipulate the data. The loop continues to execute until the condition specified in the loop body evaluates to false or until an explicit EXIT statement is encountered.

Example Usage

Let's consider an example where we have a table named "employees" with columns "id," "name," and "salary." We want to increase the salary of all employees by 10% using the LOOP command. Here's how we can achieve this:

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 records
    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;
        
        -- Increase salary by 10%
        UPDATE employees SET salary = emp_salary * 1.1 WHERE id = emp_id;
    END LOOP;
    
    CLOSE cur;
END$$
DELIMITER ;

In this example, we first define a stored procedure named "increase_salary" that uses the LOOP command to iterate over the employee records. We declare variables to store the employee ID and salary, and a cursor to fetch the records from the "employees" table. We also define a CONTINUE HANDLER to handle the case when no more records are found.

Inside the loop, we fetch the values of emp_id and emp_salary from the cursor and update the salary by multiplying it by 1.1 (10% increase). The loop continues until all records are processed, and then the cursor is closed.

Summary

The LOOP command in MySQL is a powerful tool for performing repetitive tasks or calculations on a set of records. It allows you to iterate over the records until a specific condition is met, making it useful in various scenarios. By using the LOOP command effectively, you can automate complex data manipulations and improve the efficiency of your MySQL queries.

If you are interested in learning more about VPS hosting and how it can benefit your business, consider exploring Server.HK. With their top-notch VPS solutions, you can enjoy reliable and high-performance hosting for your applications.

You may also like...