PLSql -- 练习2 cursor 显式/隐式游标

问题

统计列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程号

解决此问题的SQL语句见  Sql – 练习2 统计列出矩阵类型各分数段人数

解决1

分析

--Sql  select distinct sc.course_no from hand_student_core sc  ; -- cursor cno -> exit when nofound select sc.core from hand_student_core sc where sc.course_no='c001'; -- cursor core(no)  --> exit when  nofound-- using variablecnum1,cnum2,cnum3,cnum4cno_tmp

declare  cnum1 number;  cnum2 number;  cnum3 number;  cnum4 number;  cursor cno is    select distinct sc.course_no from hand_student_core sc;  cursor sccore(cno_tmp hand_student_core.course_no%type) is    select sc.core from hand_student_core sc where sc.course_no = cno_tmp;  c_no    hand_student_core.course_no%type;  sc_core hand_student_core.core%type;begin  dbms_output.put_line('course_no  [100-85]   [85-70]   [70-60]   [<60]');  open cno;  loop    fetch cno      into c_no;    exit when cno%Notfound;    cnum1 := 0;    cnum2 := 0;    cnum3 := 0;    cnum4 := 0;    open sccore(c_no);    loop      fetch sccore        into sc_core;      exit when sccore%NOTFOUND;      if sc_core >= 85 and sc_core < 100 then        cnum1 := cnum1 + 1;      elsif sc_core >= 70 and sc_core < 85 then        cnum2 := cnum2 + 1;      elsif sc_core >= 60 and sc_core < 75 then        cnum3 := cnum3 + 1;      else        cnum4 := cnum4 + 1;      end if;    end loop;    close sccore;    dbms_output.put_line('    ' || c_no || '             ' || cnum1 ||                         '              ' || cnum2 || '            ' ||                         cnum3 || '            ' || cnum4);  end loop;  close cno;exception  when no_data_found then    dbms_output.put_line('no data found');  when others then    dbms_output.put_line('others');end;

解决2

隐式游标

declare  cnum1 number;  cnum2 number;  cnum3 number;  cnum4 number;  c_no  hand_student_core.course_no%type;begin  dbms_output.put_line('course_no  [100-85]   [85-70]   [70-60]   [<60]');  for result_cno in (select distinct course_no from hand_student_core) loop    cnum1 := 0;    cnum2 := 0;    cnum3 := 0;    cnum4 := 0;    c_no  := result_cno.course_no;    for result_core in (select sc.core                          from hand_student_core sc                         where sc.course_no = c_no) loop      if result_core.core >= 85 and result_core.core < 100 then        cnum1 := cnum1 + 1;      elsif result_core.core >= 70 and result_core.core < 85 then        cnum2 := cnum2 + 1;      elsif result_core.core >= 60 and result_core.core < 75 then        cnum3 := cnum3 + 1;      else        cnum4 := cnum4 + 1;      end if;    end loop;    dbms_output.put_line('    ' || c_no || '             ' || cnum1 ||                         '              ' || cnum2 || '            ' ||                         cnum3 || '            ' || cnum4);  end loop;exception  when no_data_found then    dbms_output.put_line('no_data_found');  when others then    dbms_output.put_line('others');end;

结果