create or replace procedure proc4(qihao in varchar2)
is
sr_id number;
cp_id varchar2(50);
kh_id varchar2(50);
gs_id varchar2(50);

type cur is ref cursor ;
cur_cpkh cur ;

begin
   open cur_cpkh for select sum(sr) ,c.col_1_1_214_14 ,
  case s.www06 
    when 12 then 'KH09010101' 
    when 13 then'KH09010102' 
    when 14 then 'KH09010103'
    when 16 then 'KH09010201'
    when 17 then 'KH09010202'
    when 18 then 'KH09010203'
    when 20 then 'KH09010301'
    when 21 then 'KH09010302'
    when 22 then 'KH09010303'
    when 23 then 'KH09010304'
    when 25 then 'KH09010401'
    when 26 then 'KH09010402'
    when 27 then 'KH09010403'
    when 29 then 'KH09010501'
    when 30 then 'KH09010502'
    when 31 then 'KH09010503'
    when 32 then 'KH09010504'
    when 33 then 'KH09010505'
    when 34 then 'KH09010506'
    when 60 then 'KH0905'
    when 99 then 'KH0903'
    else '其他' end ,
  s.gsdm from sab4 s, cpdtb c where s.qijian=qijian  and c.col_1_1_214_3=s.www02 and c.col_1_1_214_14 is not null group by s.gsdm,  case s.www06 
    when 12 then 'KH09010101' 
    when 13 then'KH09010102' 
    when 14 then 'KH09010103'
    when 16 then 'KH09010201'
    when 17 then 'KH09010202'
    when 18 then 'KH09010203'
    when 20 then 'KH09010301'
    when 21 then 'KH09010302'
    when 22 then 'KH09010303'
    when 23 then 'KH09010304'
    when 25 then 'KH09010401'
    when 26 then 'KH09010402'
    when 27 then 'KH09010403'
    when 29 then 'KH09010501'
    when 30 then 'KH09010502'
    when 31 then 'KH09010503'
    when 32 then 'KH09010504'
    when 33 then 'KH09010505'
    when 34 then 'KH09010506'
    when 60 then 'KH0905'
    when 99 then 'KH0903'
    else '其他' end ,c.col_1_1_214_14 ;
       loop
         fetch cur_cpkh into sr_id,cp_id,kh_id,gs_id;
         exit when cur_cpkh%notfound;
         
       end loop ;
end proc4;

select sum(sr) ,c.col_1_1_214_14 ,s.www06 ,s.gsdm from sab4 s, cpdtb c where s.qijian=201105  and c.col_1_1_214_3=s.www02 and c.col_1_1_214_14 is not null group by s.gsdm,s.www06,c.col_1_1_214_14;