Oracle数据库按属性成绩查询

create or replace function bb return nvarchar2
as
-----------自定义游标类型
type class_student is record(

    sname varchar2(200),
    Oracle varchar2(200),
    Java varchar2(200),
    Android varchar2(200)
);
type score_cursor is ref cursor return class_student;
-----------声明游标变量
student_cursor score_cursor;
v_student class_student;

begin
     open student_cursor for select s.name,s.oracle,s.java,s.android from (
     select name,sum(decode(cname,'Oracle',score,null))as Oracle,
                      sum(decode(cname,'Java',score,null))as Java,
                      sum(decode(cname,'Android',score,null))as Android

from(
        select student.sid,student.name,course.cname,score.score
        from course,student,score 
        where student.sid=score.sid and score.cid=course.cid
        order by student.sid)
        group by name) s;
      dbms_output.put_line('姓名'||'  '||'Oracle'||'  '||'Java'||'  '||'Android');
      fetch student_cursor into v_student;
      while student_cursor%found loop
      dbms_output.put_line(v_student.sname ||'  '||
                                                    case when v_student.Oracle is null then '没考试'
                                                    when v_student.Oracle>=90 then '优秀'
                                                    when v_student.Oracle<90 and v_student.Oracle>=60 then '良好'
                                                    else '不及格' end ||'  '||
                                                    case when v_student.Java is null then '没考试'
                                                    when v_student.Java>=90 then '优秀'
                                                    when v_student.Java<90 and v_student.Java>=60 then '良好'
                                                    else '不及格' end||'  '||
                                                    case when v_student.Android is null then '没考试'
                                                    when v_student.Android>=90 then '优秀'
                                                    when v_student.Android<90 and v_student.Android>=60 then '良好'
                                                    else '不及格' end );
       fetch student_cursor into v_student;

       end loop;
      return '';

end bb;

 

---------------------------------------------------------------------------------

---数据库有三张表：course(cname,cid);
---　　　　　　      student(sname,sid,cid);
---                       score(cid,score);

---三门课程，Oracle，Java，Android。

---根据成绩来区分优秀，良好和有没有考试。

----------------------------------------------------------------------------------