存储过程

create or replace procedure B_SHINCHOKU_S3
is

cursor cur_SHINCHOKU3 is
select T1.pref_code as pref_code ,T1.soshikicode as soshikicode,T1.A as kakunin_end_count,T2.B as kakunin_count,round(T2.B/T1.A*100,1) as kakunin_radio
from (select pref_code,soshikicode,count(LND_T_HOUJINNEW.ID) A  from LND_T_HOUJINNEW where LND_T_HOUJINNEW.conferm_flg = 1 group by pref_code,soshikicode) T1,
     (select pref_code,soshikicode,count(LND_T_HOUJINNEW.ID) B from LND_T_HOUJINNEW where LND_T_Houjinnew.checkedflg = 1 group by pref_code,soshikicode) T2    
where
 T1.pref_code = T2.pref_code and
 T1.soshikicode = T2.soshikicode; 

  O_GK_RTN_CODE varchar2(100);
  houjinkaku_code varchar2(100);
   pref_code varchar2(100);
   ncount NUMBER(10);
begin

 FOR cur_result in cur_SHINCHOKU3 LOOP

   BEGIN
       houjinkaku_code := cur_result.soshikicode;
        pref_code := cur_result.pref_code;
       select Count(*) into ncount 
       From LND_T_Sinchoku_S
       where LND_T_Sinchoku_S.Country_Code = pref_code and LND_T_Sinchoku_S.houjinkaku_code = houjinkaku_code;
        if (ncount > 0 ) then
              update LND_T_Sinchoku_S
       set kakunin_end_count = cur_result.kakunin_end_count,kakunin_count = cur_result.kakunin_count,kakunin_radio = cur_result.kakunin_radio,insert_date = SYSDATE, update_date = SYSDATE; 
 
 else
 
 insert into LND_T_Sinchoku_S(country_code,houjinkaku_code,kakunin_radio,kakunin_end_count,kakunin_count,insert_date,update_date)
 values(cur_result.pref_code,cur_result.soshikicode,cur_result.kakunin_radio,cur_result.kakunin_end_count,
        cur_result.kakunin_count,SYSDATE,SYSDATE);
  
        end if;
  end;

 END LOOP;
 --end;
 COMMIT;
--EXCEPTION
-- WHEN OTHERS THEN
 -- ROLLBACK;
 -- O_GK_RTN_CODE := sqlerrm;

end;