MySQL Procedure – Is Loop Inside of Query Possible?

concatMySQLstored-proceduressyntax

I have the following query:

INSERT INTO major (key_1, key_2, ... , key_N) 
VALUES (v_1_1, v_1_2, v_1_3, ... , c_1_N)
       (v_2_1, v_2_2, v_2_3, ... , c_2_N)
       ...
       (v_K_1, v_K_2, v_K_3, ... , c_K_N)

where key_i is name of column, and v_i_j is random value from 1 to L. N,L and K are positive integers (our input).

I am going to write mysql procedure that create random (with values from 1 to L) matrices* (KxN) and place it to database using query presented above.

Now It is realised by code in php and doctrine but I want to achieve the same result in pure SQL procedure that as input gets (N,L,K). Theres is php code:

$conn = DriverManager::getConnection($connectionParams);
$conn->beginTransaction();
try {
    for ($i = 1; $i <= $k; $i++) {     // row in table major
        $content=[];
        for ($j = 1; $j <= $n; $j++) {   // foreign key of row
            $content['key_' . $j] = rand(1, $l);
        }
        $conn->insert('major', $content);
    }
    $conn->commit();
} catch(\Exception $e) {
    $conn->rollBack();
    throw $e;
}

*I wrote matrices but I know that without order of rows it is not matrix in strict sense.

So I do not want to make query inside loop, but loop inside query. I know it is bad practice, but I am thinking about concatenating and evaluating query inside of procedure? It is possible? Or is these better solution?

Best Answer

I solved this writhing the following code:

drop procedure if exists populate;
delimiter $$
CREATE PROCEDURE populate (IN n INT, IN l INT, IN k INT)
BEGIN
  declare _k int unsigned DEFAULT 0;
  declare _n int unsigned DEFAULT 0;
  declare _l int unsigned DEFAULT 0;
  DECLARE _stmt TEXT DEFAULT 'INSERT INTO major_1 (';

  WHILE _n < n DO set _n=_n+1;
    set _stmt = CONCAT(_stmt,'minor_',_n,'_id');
    IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
  END WHILE;
  set _stmt = CONCAT(_stmt,') VALUES ');

  while _k < k do set _k=_k+1;
    set _stmt = CONCAT(_stmt,'(');
    set _n=0; WHILE _n < n do set _n=_n+1;
      SET _stmt = CONCAT(_stmt,CEIL(RAND()*l));
      IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
    END WHILE;
    IF _k<k THEN set _stmt = CONCAT(_stmt,'),');
    ELSE set _stmt = CONCAT(_stmt,');'); END IF;
  end while;

# code to debug:
# SELECT n,l,k,_stmt;

  SET @STMT = _stmt;
  PREPARE stmt FROM @stmt;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
delimiter ;

And this can be used (for N=20,L=25,K=3) by command:

CALL populate(20,25,3);

Sources:

Loops in MySQL

https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql

Execution of text variable as query

https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure

Related Question