For loop example in MySQL

Question

In MySQL  I have this stored procedure with a LOOP  DELIMITER      CREATE PROCEDURE ABC       BEGIN       DECLARE a INT Default 0         simple loop  LOOP          SET a a 1           select a           IF a 5 THEN             LEAVE simple loop           END IF     END LOOP simple loop  END     It always prints 1   What is the correct syntax for a MySQL Loop

User · Answer

Assume you have one table with name  table1   It contain one column  col1  with varchar type  Query to crate table is give below  CREATE TABLE  table1         col1  VARCHAR 50  NULL DEFAULT NULL     Now if you want to insert number from 1 to 50 in that table then use following stored procedure  DELIMITER      CREATE PROCEDURE ABC       BEGIN       DECLARE a INT Default 1         simple loop  LOOP                   insert into table1 values a            SET a a 1           IF a 51 THEN             LEAVE simple loop           END IF     END LOOP simple loop  END      To call that stored procedure use  CALL  ABC

User · Answer

drop table if exists foo  create table foo   id int unsigned not null auto increment primary key  val smallint unsigned not null default 0   engine innodb   drop procedure if exists load foo test data   delimiter   create procedure load foo test data   begin  declare v max int unsigned default 1000  declare v counter int unsigned default 0     truncate table foo    start transaction    while v counter  lt  v max do     insert into foo  val  values   floor 0    rand     65535          set v counter v counter 1    end while    commit  end    delimiter    call load foo test data     select   from foo order by id

User · Answer

You can exchange this local variable for a global  it would be easier    DROP PROCEDURE IF EXISTS ABC  DELIMITER      CREATE PROCEDURE ABC       BEGIN       SET  a   0        simple loop  LOOP          SET  a  a 1           select  a           IF  a 5 THEN             LEAVE simple loop           END IF     END LOOP simple loop  END

User · Answer

While loop syntax example in MySQL   delimiter     CREATE procedure yourdatabase while example   wholeblock BEGIN   declare str VARCHAR 255  default       declare x INT default 0    SET x   1     WHILE x  lt   5 DO     SET str   CONCAT str x           SET x   x   1    END WHILE     select str  END     Which prints   mysql gt  call while example     ------------    str           ------------    1 2 3 4 5     ------------    REPEAT loop syntax example in MySQL   delimiter     CREATE procedure yourdb repeat loop example   wholeblock BEGIN   DECLARE x INT    DECLARE str VARCHAR 255     SET x   5    SET str          REPEAT     SET str   CONCAT str x           SET x   x - 1      UNTIL x  lt   0   END REPEAT     SELECT str  END     Which prints   mysql gt  call repeat loop example     ------------    str           ------------    5 4 3 2 1     ------------    FOR loop syntax example in MySQL   delimiter     CREATE procedure yourdatabase for loop example   wholeblock BEGIN   DECLARE x INT    DECLARE str VARCHAR 255     SET x   -5    SET str          loop label  LOOP     IF x  gt  0 THEN       LEAVE loop label      END IF      SET str   CONCAT str x           SET x   x   1      ITERATE loop label    END LOOP     SELECT str   END     Which prints   mysql gt  call for loop example     -------------------    str                  -------------------    -5 -4 -3 -2 -1 0     -------------------  1 row in set  0 00 sec    Do the tutorial  http   www mysqltutorial org stored-procedures-loop aspx  If I catch you pushing this kind of MySQL for-loop constructs into production  I m going to shoot you with the foam missile launcher   You can use a pipe wrench to bang in a nail  but doing so makes you look silly

[mysql] For loop example in MySQL

Examples related to mysql

Examples related to for-loop