Home > Database > Mysql Tutorial > body text

How to write a loop statement in mysql

coldplay.xixi
Release: 2020-08-25 09:57:42
Original
20432 people have browsed it

Mysql method of writing loop statements: 1. Use a while loop, the code is [WHILE i<5 DO]; 3. Use a repeat loop; the code is [REPEAT INSERT INTO test VALUES(i 11,'test' ,'20')].

How to write a loop statement in mysql

[Related learning recommendations: mysql tutorial]

How to write loop statements in mysql:

There are three common looping methods in mysql: while, repeat and loop loops. There is also a kind of goto, which is not recommended.

1.while loop

-- 设置mysql分隔符为//,也就意味着,当遇到下一个//时,整体执行SQL语句
DELIMITER //
 
DROP PROCEDURE if EXISTS ‘test’;    # 如果存在test存储过程则删除
CREATE procedure test() # 创建无参存储过程,名称为test
BEGIN
DECLARE i INT;  # 申明变量
SET i = 0;  # 变量赋值
WHILE i<5 DO # 结束循环的条件: 当i大于5时跳出while循环
INSERT INTO test VALUES(i+11,&#39;test&#39;,&#39;20&#39;);  # 往test表添加数据
SET i = i+1;    # 循环一次,i加1
END WHILE;  # 结束while循环
SELECT * FROM test; # 查看test表数据
END
//  # 结束定义语句
CALL test();    # 调用存储过程
DELIMITER ; # 重新将分隔符设置为;
Copy after login

2.repeat loop

DELIMITER //
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test()
BEGIN
  DECLARE i INT;
  SET i = 0;
  REPEAT
    INSERT INTO test VALUES(i+11,&#39;test&#39;,&#39;20&#39;);           # 往test表添加数据
    SET i = i + 1;                                  # 循环一次,i加一
  UNTIL i > 10 END REPEAT;                           # 结束循环的条件: 当i大于10时跳出repeat循环
  SELECT * FROM test;
END
//
CALL test();
DELIMITER ;
Copy after login

3.loop loop

DELIMITER //
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test()
BEGIN
  DECLARE i INT;
  SET i = 0;
    lp : LOOP                                   # lp为循环体名,可随意 loop为关键字
        INSERT INTO test VALUES(i+11,&#39;test&#39;,&#39;20&#39;);   # 往test表添加数据
        SET i = i + 1;                          # 循环一次,i加一
        IF i > 10 THEN                       # 结束循环的条件: 当i大于10时跳出loop循环
                    LEAVE lp;
            END IF;
    END LOOP;
    SELECT * FROM test;
END
//
CALL test();
DELIMITER ;
Copy after login

The above is the detailed content of How to write a loop statement in mysql. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template