CODEWITHSUNDEEP
mysqlstored-proceduresfor-loopcursor
stackoverflow
stackoverflow

Reputation: 19444

MySQL How do I loop a stored procedure?

I have a stored procedure called Evaluate(something1, something2); Evaluate will take a some parameters, search for them in the database and insert findings into a result table.

Now I wish to create some type of stored procedure that will constantly call Evaluate to iterate over an entire comma-delimited file. Is this possible?

psuedo code

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

Upvotes: 1

Views: 3946

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65274

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 ;

Upvotes: 1

Related Questions