Showing posts with label MySQL While Loop. Show all posts
Showing posts with label MySQL While Loop. Show all posts

Thursday, June 15, 2017

MySQL Insert with While Loop | Use LOOP variable for INSERT in MySQL | INSERT using a procedure variable

Use while loop in MySQL is now very easy. It can be achieved by MySQL Procedure execution. We can all type of operation here and all type of condition checking like if, do while, else if and many more. Below is a simple code snippet to describe basic implementation of MySQL Procedure.


DELIMITER //
DROP PROCEDURE IF EXISTS doWhile;
CREATE PROCEDURE doWhile()
  BEGIN
    DECLARE i INT DEFAULT (SELECT COUNT(*) FROM HOME);
    DECLARE j INT DEFAULT i + 2;
    WHILE (i < j) DO
      INSERT INTO HOME (name, roll) VALUES (CONCAT("Name-", i), CONCAT("ROLL-", i));
      SET i = i + 1;
    END WHILE;
  END;
//
CALL doWhile();

DELIMITER //
DROP PROCEDURE IF EXISTS doWhile;
CREATE PROCEDURE doWhile()
  BEGIN
    DECLARE namex VARCHAR(300);
    DECLARE rollx VARCHAR(300);
    DECLARE cursor1 CURSOR FOR (SELECT name,roll FROM HOME WHERE id % 2 = 0);

    OPEN cursor1;
    read_loop: LOOP
      FETCH FROM cursor1 INTO namex, rollx;
      INSERT INTO HOME (name, roll) VALUE (namex, rollx);
    END LOOP;
    CLOSE cursor1;
  END;
//
CALL doWhile();