Loop - Heeyoung-Ahn/MariaDB-with-Excel-VBA GitHub Wiki

  • 배열형 반복
BEGIN
  DECLARE var VARCHAR(100) DEFAULT '31,35,40,37';
  DECLARE element int;
  CREATE OR REPLACE TABLE t1 (a int);

  WHILE var <> '' DO
    SET element = SUBSTRING_INDEX(var, ',', 1);
    IF LOCATE(',', var) > 0 THEN
      SET var = SUBSTRING(var, LOCATE(',', var) + 1);
    ELSE
      SET var = '';
    END IF;
    INSERT INTO t1 VALUES(element);
  END WHILE;
END

-- 참조
SET @str_array = 'apple,peach,banana';
select SUBSTRING(@str_array, LOCATE(',',@str_array) + 1);

SELECT SUBSTRING('2019-12-01', 6); -- 12-01
SELECT SUBSTRING('2019-12-01', 6, 2); -- 12
SELECT SUBSTRING_INDEX('2019-12-01', '-', 2); -- 2019-12
  • FOR문
CREATE OR REPLACE TABLE t3 (id INT AUTO_INCREMENT, a INT, PRIMARY KEY (id));
DELIMITER //
FOR i IN 1..20
DO
	INSERT INTO t3(a) VALUES(i);
END FOR;
//
DELIMITER ;

DELIMITER //
FOR i IN 1..20
DO
 	UPDATE t3 SET a = i * 2 WHERE id = i;
END FOR;
//
DELIMITER ;
  • WHILE문
DECLARE V1 INT DEFAULT 5;
CREATE OR REPLACE TABLE t1 (a INT);
WHILE V1 > 0
DO
	SET V1 = V1 - 1;
	INSERT INTO t1 VALUES (V1);
END WHILE;
  • 중첩 WHILE문
BEGIN
	DECLARE i INT DEFAULT 1;	
	CREATE OR REPLACE TABLE v1 (a INT, b INT);	
	WHILE i <= 5
	DO
		BEGIN
			DECLARE j INT DEFAULT 1;
			WHILE j <= 10
			DO
				INSERT INTO v1 VALUES(i, j);
				SET j = j + 1;
			END WHILE;
		END;
		SET i = i + 1;
	END WHILE;
END