ตอนที่ 6 : การสร้าง Loop ข้อมูลบน Stored Procedure (MySQL : Stored Procedure)

WHILE expression DO
   #Statements
END WHILE

DROP PROCEDURE IF EXISTS getDataLoop;

DELIMITER //
CREATE PROCEDURE getDataLoop()
BEGIN

	DECLARE i  INT DEFAULT 0;
	DECLARE strTemp VARCHAR(255);

	SET strTemp =  '';
	WHILE i  <= 10 DO
	   SET  strTemp = CONCAT(strTemp,i,'=>');
	   SET  i = i + 1; 
	END WHILE;
	SELECT strTemp;
        
END //

DELIMITER ;

CALL getDataLoop();

DROP PROCEDURE IF EXISTS getDataLoop;

DELIMITER //
CREATE PROCEDURE getDataLoop()
BEGIN
	
	DECLARE i  INT DEFAULT 1;

	# Create Temp Table
	CREATE TEMPORARY TABLE IF NOT EXISTS temp(
		RowID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		ItemDetail VARCHAR(50)) ENGINE = MEMORY;

	WHILE i  <= 15 DO
		# Insert Table
		INSERT INTO temp (ItemDetail) VALUES(CONCAT('Item No : ',i));
		SET  i = i + 1; 
	END WHILE;

	# Return Rows
	SELECT * FROM temp;

	# Drop Table
	DROP TABLE temp;	
        
END //

DELIMITER ;

CALL getDataLoop()

REPEAT
   #Statements;
UNTIL expression
END REPEAT

DROP PROCEDURE IF EXISTS getDataLoop;

DELIMITER //
CREATE PROCEDURE getDataLoop()
BEGIN
	
	DECLARE i  INT DEFAULT 0;
	DECLARE strTemp VARCHAR(255);

	SET strTemp =  '';
	REPEAT
	   SET  strTemp = CONCAT(strTemp,i,'=>');
	   SET  i = i + 1; 
	UNTIL i  >= 10
	END REPEAT;

	SELECT strTemp;
        
END //

DELIMITER ;

CALL getDataLoop();

DROP PROCEDURE IF EXISTS getDataLoop;

DELIMITER //
CREATE PROCEDURE getDataLoop()
BEGIN
	
	DECLARE i  INT DEFAULT 1;

	# Create Temp Table
	CREATE TEMPORARY TABLE IF NOT EXISTS temp(
		RowID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		ItemDetail VARCHAR(50)) ENGINE = MEMORY;

	# Repeat Loop
	REPEAT
		# Insert Table
		INSERT INTO temp (ItemDetail) VALUES(CONCAT('Item No : ',i));
		SET  i = i + 1; 
		UNTIL i  >= 10
	END REPEAT;

	# Return Rows
	SELECT * FROM temp;

	# Drop Table
	DROP TABLE temp;	
        
END //

DELIMITER ;

CALL getDataLoop()