存储过程--查询考生成绩排名

delimiter //
-- 查询考生成绩排名。
drop procedure if exists gk2011.rank;
create procedure gk2011.rank(my_zkzh varchar(10))
begin
declare str_cj int;
declare str_kmdm varchar(10);
declare str_rank int;
select kmdm from cj where zkzh=my_zkzh and substring(kmdm,1,1)='6' into str_kmdm;
select max(cj) from cj where zkzh=my_zkzh and substring(kmdm,1,1)='6' into str_cj;
select count(zkzh)+1 from cj where cj>str_cj and kmdm=str_kmdm into str_rank;
select zkzh,xm,kmdm,kmmc,cj,str_rank from cj where zkzh=my_zkzh and kmdm=str_kmdm;
end;
//