mysql stored procedure for loop

MySQL stored procedure is a method of writing, storing and reusing SQL code in MySQL. Stored procedures can accept and process parameter input, execute SQL statements to complete specific tasks, and return results. In MySQL, stored procedures are very useful for complex business logic and data processing, which can improve the efficiency and reliability of the database. The for loop is one of the commonly used control flow statements in MySQL stored procedures. It can be used to repeatedly execute certain code blocks.

The basic syntax format of the for loop statement is as follows:

for loop_variable [reverse] in lower_bound..upper_bound loop
    -- 可执行的代码块
end loop;

Among them, loop_variable is a loop variable, which will be automatically updated in each loop; reverse is optional, indicating whether to execute in the opposite direction. Loop; lower_bound and upper_bound are the start and end values ​​of the for loop, they can be variables, expressions or constants.

Using for loops in stored procedures can make the code more concise and efficient. The following will introduce in detail how to use for loops in MySQL stored procedures through an example.

Suppose there is a student table student, which contains information such as student ID, name, and scores. Now you need to write a stored procedure to count the number of students according to their scores and store the statistical results in a new table. We can use a for loop statement to iterate through the scores of all students, and then count the number of students based on the range of scores.

First, we need to create a new table score_statistics to store score statistical results:

CREATE TABLE score_statistics (
  id INT(11) NOT NULL AUTO_INCREMENT,
  score_range VARCHAR(50) DEFAULT NULL,
  count INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

Next, we write a stored procedure calculate_score_count to traverse the student table and count the score ranges Number of students:

DELIMITER $$
CREATE PROCEDURE calculate_score_count()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE score_range VARCHAR(50);
  DECLARE count INT DEFAULT 0;
  DECLARE lower_bound INT DEFAULT 0;
  DECLARE upper_bound INT DEFAULT 0;
  SET i = 1;
  WHILE i <= 10 DO
    SET lower_bound = (i - 1) * 10;
    SET upper_bound = i * 10;
    SET score_range = CONCAT(lower_bound, '-', upper_bound);
    SET count = (SELECT COUNT(*) FROM student WHERE score BETWEEN lower_bound AND upper_bound);
    INSERT INTO score_statistics (score_range, count) VALUES (score_range, count);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

In the above code, we use a while loop statement to traverse the range of scores. For each score range, we calculate the corresponding lower_bound and upper_bound, and then use the SELECT statement to retrieve the values ​​from the student table Count the number of people within this range. Finally, score_range and count are inserted into the score_statistics table.

Calling the stored procedure, we can get the following results:

call calculate_score_count();
select * from score_statistics;

The score_statistics table stores the score range and the corresponding head count results. The data is as follows:

The above is the detailed content of mysql stored procedure for loop. For more information, please follow other related articles on the PHP Chinese website!