drop procedure IF EXISTS test;
delimiter //
create procedure test()
begin
declare i integer;
declare cname varchar(32) DEFAULT "";
set i=1;
while i<=100000 do
begin
select i+10000 into cname;
insert into t_user(userid,groupid,charguid,name,level) values(i,1,i+1000,cname,1);
set i=i+1;
end;
end while;
end
//
delimiter ;
call test();
drop procedure if exists updaterank;
delimiter //
create procedure updaterank()
begin
declare done int default 0;
declare nrank int unsigned default 0;
declare nguid int unsigned;
declare nweekscore int unsigned;
declare cname varchar(32) default "";
declare cur cursor for
select guid,name,weekscore from t_user order by weekscore desc;
declare continue handler for not found set done=1;
delete from t_rank;
open cur;
repeat
set cname="";
fetch cur into nguid,cname,nweekscore;
if not done then
set nrank=nrank+1;
insert into t_rank values(nrank,nguid,cname,nweekscore);
end if;
until done end repeat;
close cur;
end
//
delimiter ;
//name里有中文,导出错误
改成以下形式就好用了
begin
declare done int default 0;
declare nrank int unsigned default 0;
declare nguid int unsigned;
declare nweekscore int unsigned;
declare cur cursor for
select guid,weekscore from t_user order by weekscore desc;
declare continue handler for not found set done=1;
delete from t_rank;
open cur;
repeat
fetch cur into nguid,nweekscore;
if not done then
set nrank=nrank+1;
select name into @cname from t_user where guid=nguid;
insert into t_rank values(nrank,nguid,@cname,nweekscore);
end if;
until done end repeat;
close cur;
end