诚心求教，嵌套的动态游标更不了数据，怎么办？

Question

写了下面两个动态游标，想丛临时表t_score中取出数据，计算各类课程的总学分，以及计算ABCD类课的学分积，
更新到临时表t_stdcode中，但数据更新不进去，即仍为空。
ps：t_stdcode表除了学生标识外其他列原始值为null，之后每搜索到相应学生标识则取出学分计算后回填。open v_CursorStd for
select col1
from t_stdcode;
--for update of credit,a_credit,b_credit,c_credit,d_credit,e_credit,re_credit;
loop
fetch v_CursorStd into s_stdcode;
EXIT WHEN v_CursorStd % NOTFOUND;open v_CursorScore for
select hundredscore,coursetypecode,t_credit
from t_score
where stdcode=s_stdcode;loop
fetch v_CursorScore into s_hundredscore,s_coursetype,s_credit;
EXIT WHEN v_CursorScore % NOTFOUND; 
case s_coursetype
       when 'A' then       
          update t_stdcode
          set a_credit=a_credit+s_credit;
                   
          total_credit:=total_credit+s_credit;
          total_score:=total_score+s_hundredscore*s_credit;          
       when'B' then
          update t_stdcode
          set b_credit=b_credit+s_credit;
          total_credit:=total_credit+s_credit;
          total_score:=total_score+s_hundredscore*s_credit;         
       when  'C' then
          update t_stdcode
          set c_credit=c_credit+s_credit;
         -- where col1=s_stdcode;
          total_credit:=total_credit+s_credit;
          total_score:=total_score+s_hundredscore*s_credit;          
       when  'D' then
          update t_stdcode
          set d_credit=d_credit+s_credit;
          total_credit:=total_credit+s_credit;
          total_score:=total_score+s_hundredscore*s_credit;
        else 
          update t_stdcode
          set e_credit=e_credit+s_credit;end case;if s_hundredscore is null then
    update t_stdcode
    set re_credit=re_credit+s_credit;
end if;end loop;  
 
update t_stdcode
set credit=total_score/total_credit;
close v_CursorScore;
 
end loop;
close v_CursorStd;写了上面两个动态游标，想丛临时表t_score中取出数据，计算各类课程的总学分，以及计算ABCD类课的学分积，
更新到临时表t_stdcode中，但数据更新不进去，即仍为空。
ps：t_stdcode表除了学生标识外其他列原始值为null，之后每搜索到相应学生标识则取出学分计算后回填。谢谢各位了。

Answer 1

t_stdcode的结构：COL1                 COL2 CREDIT   A_CREDIT   B_CREDIT   C_CREDIT   D_CREDIT   E_CREDIT  RE_CREDIT
-------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0110707              刘伟
0211214              蒯宇豪
0210092              邹剑
0210131              马骅
0210183              谢佳琪
0210196              郭全乐
0210223              孙会业
0111127              吕威
0210265              谢玲
0210289              王建英t_score的表结构及数据
STDCODE              COURSEINCODE HUNDREDSCORE COURSETY   T_CREDIT
-------------------- ------------ ------------ -------- ----------
0110707                     32258           74                   1
0110707                     32463           77                 4.5
0110707                     32464           74                 4.5
0110707                     32544           65                   4
0110707                     32675           69                   4
0110707                     32705           77                   2
0110707                     32711           82                   2
0110707                     32731           73                   2
0110707                     32733           83                   2
0110707                     34109           92                   1
0110707                     34108           86                   2
0110707                     34107           71                   2
0110707                     34104            0 B                 3
0110707                     34102           68                   2
0110707                     34101           77                   2
0110707                     32744           75                 1.5
0110707                     32741           78                   2
0110707                     32734           82                   2
0110707                     39836           72                 3.5
0110707                     39422           44                 2.5
0110707                     34114           84                   2
0110707                     34113           92                   2
0110707                     34112           98                   1
0110707                     34111           86                   1
0110707                     34110          100                   1
0110707                     40762           66 C                 3
0110707                     40761           76 C                 4
0110707                     40340           77                   2
0110707                     40334           85                   1
0110707                     40306           78                   2
0110707                     40305           86                   1
0110707                     39839           79                 3.5
0110707                     39837           64                 3.5
0110707                     40839           60                 1.5
0110707                     40777              D               4.5
0110707                     40776              D               4.5
0110707                     40770              C               4.5
0110707                     40769              C                 4
0110707                     40768           71 C               2.5
0110707                     40766              C               4.5
0110707                     40764           70 C               4.5
0110707                     32732           60                   4谢谢了。

Answer 2

更新语句之后加上commit试试，你没有提交。

Answer 3

create or replace procedure calc_credit
is
  total_credit number(12,2);
  total_score number(12,2);  cursor v_CursorStd is
    select col1 
      from t_stdcode
     order by col1;
  cursor v_CursorScore(s_stdcode) is
    select coursetypecode,nvl(sum(t_credit),0) credit,nvl(hundredscore*t_credit,0) score 
      from t_score 
     where stdcode=s_stdcode
     group by coursetypecode;
begin
  For t_CursorStd in v_CursorStd LOOP
    FOR t_CursorScore in v_CursorScore(t_CursorStd.col1) LOOP
     case 
      when t_CursorScore.coursetypecode is null then
         update t_stdcode 
          set re_credit = re_credit + t_CursorScore.credit
           where t_stdcode.col1 = t_CursorStd.col1;      when t_CursorScore.coursetypecode = 'A' then      
          update t_stdcode 
             set a_credit = a_credit + t_CursorScore.credit
           where t_stdcode.col1 = t_CursorStd.col1;                
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score;  
        
      when t_CursorScore.coursetypecode = 'B' then 
          update t_stdcode 
             set b_credit = b_credit + t_CursorScore.credit
           where t_stdcode.col1 = t_CursorStd.col1;                         
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score; 
         
      when  t_CursorScore.coursetypecode = 'C' then 
          update t_stdcode 
             set c_credit = c_credit + t_CursorScore.credit
           where t_stdcode.col1 = t_CursorStd.col1;                         
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score; 
         
      when  t_CursorScore.coursetypecode = 'D' then 
          update t_stdcode 
             set d_credit = d_credit + t_CursorScore.credit
           where t_stdcode.col1 = t_CursorStd.col1;                         
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score;  
        
      else 
          update t_stdcode 
             set e_credit = e_credit + t_CursorScore.credit
           where t_stdcode.col1 = t_CursorStd.col1; 
     end case;   
    end LOOP;
    if total_credit <> 0 then
      update t_stdcode 
         set credit = total_score / total_credit
       where t_stdcode.col1 = t_CursorStd.col1;
    end if;
  end loop;
end calc_credit;

Answer 4

第二个游标定义下少了个sum，改成  cursor v_CursorScore(s_stdcode) is 
    select coursetypecode,nvl(sum(t_credit),0) credit,nvl(sum(hundredscore*t_credit),0) score 
      from t_score 
    where stdcode=s_stdcode 
    group by coursetypecode; 先计算，再更新，速度会快些。

Answer 5

更正下：
create or replace procedure calc_credit 
is 
  total_credit number(12,2); 
  total_score number(12,2);   cursor v_CursorStd is 
    select col1 
      from t_stdcode 
    order by col1; 
  cursor v_CursorScore(s_stdcode) is 
    select coursetypecode,nvl(sum(t_credit),0) credit,nvl(sum(hundredscore*t_credit),0) score 
      from t_score 
    where stdcode=s_stdcode 
    group by coursetypecode; 
begin 
  For t_CursorStd in v_CursorStd LOOP
    total_credit := 0;
    total_score := 0;
    FOR t_CursorScore in v_CursorScore(t_CursorStd.col1) LOOP 
    case 
      when t_CursorScore.coursetypecode is null then 
        update t_stdcode 
          set re_credit = re_credit + t_CursorScore.credit 
          where t_stdcode.col1 = t_CursorStd.col1;       when t_CursorScore.coursetypecode = 'A' then      
          update t_stdcode 
            set a_credit = a_credit + t_CursorScore.credit 
          where t_stdcode.col1 = t_CursorStd.col1;                
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score;  
        
      when t_CursorScore.coursetypecode = 'B' then 
          update t_stdcode 
            set b_credit = b_credit + t_CursorScore.credit 
          where t_stdcode.col1 = t_CursorStd.col1;                        
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score; 
        
      when  t_CursorScore.coursetypecode = 'C' then 
          update t_stdcode 
            set c_credit = c_credit + t_CursorScore.credit 
          where t_stdcode.col1 = t_CursorStd.col1;                        
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score; 
        
      when  t_CursorScore.coursetypecode = 'D' then 
          update t_stdcode 
            set d_credit = d_credit + t_CursorScore.credit 
          where t_stdcode.col1 = t_CursorStd.col1;                        
          total_credit := total_credit + t_CursorScore.credit; 
          total_score := total_score + t_CursorScore.score;  
        
      else 
          update t_stdcode 
            set e_credit = e_credit + t_CursorScore.credit 
          where t_stdcode.col1 = t_CursorStd.col1; 
    end case;  
    end LOOP; 
    if total_credit <> 0 then 
      update t_stdcode 
        set credit = total_score / total_credit 
      where t_stdcode.col1 = t_CursorStd.col1; 
    end if; 
  end loop; 
end calc_credit; 

Answer 6

第二个游标定义还有点问题，改成   cursor v_CursorScore(s_stdcode varchar) is 
    select coursetypecode,nvl(sum(t_credit),0) credit,nvl(sum(hundredscore*t_credit),0) score 
      from t_score 
    where stdcode=s_stdcode 
    group by coursetypecode; 

Answer 7

所有的update都没有commit;第二个游标定义有问题，可以参考楼上的修改下

Answer 8

 给你个提示
when 'A' then      
          update t_stdcode 
          set a_credit=a_credit+s_credit; 这个地方有问题。
首先t_stdcode这个事空的没有值，所以+s_credit还是空的
所以应该该为:set a_credit=nvl(a_credit,0)+s_credit
另外还要加条件：update set where。。
 要不会把所有的都更新了。