create or replace function fun_score_rank(

p_in_stuid in number,--学号

p_in_courseid in  number --课程ID

)

return number

is

ls_pm number:=0;

ls_score number:=0;

begin

  --获取该学生的成绩

  select t.score into ls_score from score_ys t

   where t.stuid = p_in_stuid

     and t.courseid = p_in_courseid;

  --获取成绩比该学生高的人数

  select count(1) into ls_pm from score_ys t

   where t.courseid = p_in_courseid

   and  t.score>ls_score;

   --得到该学生的成绩排名

   ls_pm:=ls_pm+1;

   return ls_pm;

exception

  when no_data_found  then

     dbms_output.put_line('该学生的课程：'||p_in_courseid|| '的成绩在成绩表中找不到');

end;

select fun_score_rank('','') 排名1, fun_score_rank('','') 排名2 from dual;