MySQL 如何循环存储过程？

Question

我有一个名为 Evaluate(something1, something2); 的存储过程评估将采用一些参数，在数据库中搜索它们并将结果插入结果表。

现在我想创建某种类型的存储过程，它会不断调用 Evaluate 来遍历整个以逗号分隔的文件。这可能吗？

伪代码

assessEntireFile()
{
  loop file
    Evaluate(single line from file)
   end loop
}



**The work flow of assessEntireFile() will be as followed:**
1. call assessEntireFile()
   --assessEntireFile will load and iterate over an input file (line-by-line)
2. each iteration will call Evaluate() on that line
   --evualte() will produce results in a table
3. complete

Answer 1

DELIMITER $$

CREATE PROCEDURE assessEntireTable()
  READS SQL DATA
BEGIN

-- Declare variables according to your table/file structure
  DECLARE field1 int DEFAULT 0;
  DECLARE field2 VARCHAR(250) CHARACTER SET utf8;
-- /Declare variables according to your table/file structure

  DECLARE done int DEFAULT 0;
  DECLARE currentrow CURSOR FOR SELECT * FROM assessEntireFileTmp; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

  OPEN currentrow;
-- Adapt next line according to your table/file structure
  FETCH currentrow INTO field1,field2, ... ;
  WHILE done=0 DO
-- Adapt next 2 lines according to your table/file structure
     CALL Evaluate(field1, field2, ...);
     FETCH currentrow INTO field1,field2, ... ;
  END WHILE;
END $$

CREATE PROCEDURE assessEntireFile()
BEGIN
  DROP TABLE IF EXISTS assessEntireFileTmp;
  CREATE TABLE assessEntireFileTmp (

--  Your needed structure here

  );
  LOAD DATA INFILE '<file_name>'
     INTO TABLE assessEntireFileTmp
--   More needed parameters here
  ;

  CALL assessEntireTable();

  DROP TABLE assessEntireFileTmp;
END $$  

DELIMITER ;