mysql - 循环遍历多个循环

Question

DROP PROCEDURE fillBin;
DELIMITER $$
 CREATE  PROCEDURE fillBin(IN a INT(11), IN b INT(11), IN c INT(11), IN d INT(11), IN e     INT(11))
wholeblock:BEGIN
 DECLARE i INT UNSIGNED DEFAULT 1;
 DECLARE j INT UNSIGNED DEFAULT 1;
 DECLARE k INT UNSIGNED DEFAULT 1;
 DECLARE l INT UNSIGNED DEFAULT 1;
 DECLARE m INT UNSIGNED DEFAULT 1;
 DECLARE strA VARCHAR(255) DEFAULT '';
 DECLARE strB VARCHAR(255) DEFAULT '';
 DECLARE strC VARCHAR(255) DEFAULT '';
 DECLARE strD VARCHAR(255) DEFAULT '';
 DECLARE strE VARCHAR(255) DEFAULT '';
 DECLARE strF VARCHAR(255) DEFAULT '';

 SET strA = 'A';
 SET strB = 'G';
 SET strC = 'R';
 SET strD = 'S';
 SET strE = 'B';
 SET strF= '';

 WHILE i < (a+1) DO
 SET strA=CONCAT(strA,i) ;
WHILE j < (b+1) DO
SET strB = CONCAT(strB,j);
    WHILE k < (c+1) DO
    SET strC =CONCAT(strC,k);
        WHILE l < (d+1) DO
        SET strD = CONCAT(strD,l);
            WHILE m < (e+1) DO
            SET strE = CONCAT(strE,m);
            SET strF = CONCAT(strA, strB, strC, strD, strE);
            INSERT INTO BIN (`aisle`, `room`, `rack`, `shelf`, `bin`, `barcode`, `warehouse_id`) 
            VALUES(strA,strB, strC, strD, strE, strF, 1);

SET m=m+1;
SET strE = 'B';
END WHILE;
SET l=l+1;
SET strD = 'S';
END WHILE;
SET k=k+1;
SET strC = 'R';
END WHILE;
SET j=j+1;
SET strB = 'G';
END WHILE;
SET i=i+1;
SET strA = 'A';
END WHILE;
END $$

//calling the procedure
CALL fillBin(1, 1, 1, 2, 2);

我的过程插入值:A1G1R1S1B1 和 A1G1R1S1B2。并且内循环中断。

但正确的插入应该是:A1G1R1S1B1和A1G1R1S1B2和A1G1R1S2B1和A1G1R1S2B2。

插入内容显然不完整。我应该如何放置插入语句或更改循环以满足需要？

请帮忙。谢谢

Answer 1

为什么不使用简单的 SQL 来完成此任务？
看下面的例子

我们需要一个包含从 1 到 X 的数字序列的表 - 其中 X 是我们需要时的最大数字。

CREATE TABLE numbers(
  x int primary key auto_increment
);

INSERT INTO numbers
SELECT null FROM information_schema.columns;

SELECT max( x ) FROM numbers;
| MAX( X ) |
|----------|
|      558 |

现在下面的简单选择将为我们生成所有必需的记录
(请参阅此演示:http://sqlfiddle.com/#!2/1e5c4b/7
- 您需要滚动到页面底部才能查看查询结果):

SET @strA = 'A';
SET @strB = 'G';
SET @strC = 'R';
SET @strD = 'S';
SET @strE = 'B';
SET @strF= '';

SET @a = 2;
SET @b = 2;
SET @c = 2;
SET @d = 2;
SET @e = 2;


SELECT *,
       CONCAT(strA, strB, strC, strD, strE) strF,
       1 As warehouse_id
FROM (
  SELECT Concat(@strA,x) strA
  FROM numbers 
  WHERE x <= @a
) A
CROSS JOIN
(
  SELECT Concat(@strB,x) strB
  FROM numbers
  WHERE x <= @b
) B
CROSS JOIN
(
  SELECT Concat(@strC,x) strC
  FROM numbers
  WHERE x <= @c
) C
CROSS JOIN
(
  SELECT Concat(@strD,x) strD
  FROM numbers
  WHERE x <= @d
) D
CROSS JOIN
(
  SELECT Concat(@strE,x) strE
  FROM numbers
  WHERE x <= @e
) E

现在只需在上述查询的顶部放置一条 INSERT 语句即可:

INSERT INTO BIN (`aisle`, `room`, `rack`, `shelf`, `bin`, `barcode`, `warehouse_id`) 
    SELECT *,
           CONCAT(strA, strB, strC, strD, strE) strF,
           1 As warehouse_id
    FROM (
      SELECT Concat(@strA,x) strA
      FROM numbers 
      WHERE x <= @a
    ) A
    CROSS JOIN
    (
      SELECT Co .....
    ........
    ........
    ........