BfaxZfnW

1MySQL How do I loop a stored procedure?
2assessEntireFile()
3{
4  loop file
5    Evaluate(single line from file)
6   end loop
7}
8
9
10
11**The work flow of assessEntireFile() will be as followed:**
121. call assessEntireFile()
13   --assessEntireFile will load and iterate over an input file (line-by-line)
142. each iteration will call Evaluate() on that line
15   --evualte() will produce results in a table
163. complete
17	
18DELIMITER $$
19
20CREATE PROCEDURE assessEntireTable()
21  READS SQL DATA
22BEGIN
23
24-- Declare variables according to your table/file structure
25  DECLARE field1 int DEFAULT 0;
26  DECLARE field2 VARCHAR(250) CHARACTER SET utf8;
27-- /Declare variables according to your table/file structure
28
29  DECLARE done int DEFAULT 0;
30  DECLARE currentrow CURSOR FOR SELECT * FROM assessEntireFileTmp; 
31  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
32
33  OPEN currentrow;
34-- Adapt next line according to your table/file structure
35  FETCH currentrow INTO field1,field2, ... ;
36  WHILE done=0 DO
37-- Adapt next 2 lines according to your table/file structure
38     CALL Evaluate(field1, field2, ...);
39     FETCH currentrow INTO field1,field2, ... ;
40  END WHILE;
41END $$
42
43CREATE PROCEDURE assessEntireFile()
44BEGIN
45  DROP TABLE IF EXISTS assessEntireFileTmp;
46  CREATE TABLE assessEntireFileTmp (
47
48--  Your needed structure here
49
50  );
51  LOAD DATA INFILE '<file_name>'
52     INTO TABLE assessEntireFileTmp
53--   More needed parameters here
54  ;
55
56  CALL assessEntireTable();
57
58  DROP TABLE assessEntireFileTmp;
59END $$  
60
61DELIMITER ;
62	
63@count = 1
64while @count < 1000
65    begin
66    update etc
67@count = @count + 1
68end