关闭

存储过程

标签: 存储insertdate
517人阅读 评论(0) 收藏 举报

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;
 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:119375次
    • 积分:1642
    • 等级:
    • 排名:千里之外
    • 原创:24篇
    • 转载:102篇
    • 译文:0篇
    • 评论:16条
    文章分类
    最新评论