mysql批量更新存储过程_mysql批量更新存储过程怎么写

create procedure batchUpdate(in n int)

begin

declare inParam int default n;

declare i int default 1;

declare id int;

declare username varchar(255);

declare integral varchar(255);

declare ranking int;

declare _resultSet cursor for select a.id, a.username,a.integral,a.ranking from user a order by a.integral desc limit inParam;

open _resultSet;

while i <= inParam do

fetch _resultSet into id, username, integral,ranking;

update user a set a.ranking = i where a.id = id;

set i = i +1;

end while;

close _resultSet;

end;

mysql> select * from user;

+----+----------+----------+---------+

| id | username | integral | ranking |

+----+----------+----------+---------+

| 1 | abc | 1 | 0 |

| 2 | abc | 2 | 0 |

| 3 | abc | 3 | 0 |

| 4 | abc | 4 | 0 |

| 5 | abc | 5 | 0 |

| 6 | abc | 6 | 0 |

| 7 | abc | 7 | 0 |

| 8 | abc | 8 | 0 |

| 9 | abc | 9 | 0 |

| 10 | abc | 10 | 0 |

| 11 | abc | 11 | 0 |

| 12 | abc | 12 | 0 |

| 13 | abc | 13 | 0 |

| 14 | abc | 14 | 0 |

+----+----------+----------+---------+

14 rows in set

mysql> call batchUpdate(14);

Query OK, 1 row affected

mysql> select * from user;

+----+----------+----------+---------+

| id | username | integral | ranking |

+----+----------+----------+---------+

| 1 | abc | 1 | 14 |

| 2 | abc | 2 | 13 |

| 3 | abc | 3 | 12 |

| 4 | abc | 4 | 11 |

| 5 | abc | 5 | 10 |

| 6 | abc | 6 | 9 |

| 7 | abc | 7 | 8 |

| 8 | abc | 8 | 7 |

| 9 | abc | 9 | 6 |

| 10 | abc | 10 | 5 |

| 11 | abc | 11 | 4 |

| 12 | abc | 12 | 3 |

| 13 | abc | 13 | 2 |

| 14 | abc | 14 | 1 |

+----+----------+----------+---------+

14 rows in set