存储过程来更新表的记录

CREATE OR REPLACE procedure DWUSER.update_DW_Amount as
 begin
  execute immediate  'truncate table DW_AMOUNT';
  insert into DW_AMOUNT
 select  login_date,
       login_amount,
       login_fail_amount,
       register_amount,
       login_fail_amount/login_amount as LOGIN_FAIL_RATE
  FROM
 
  (select login_date, count(user_id) login_amount,count(case when result='Fail' then result end ) login_fail_amount
  from (
    select  trunc(occur_date) login_date,user_id,result from T_SODW_ACTIVITY_LOG
    WHERE occur_date > to_date('20110623','yyyymmdd') and type = 'SODW_ACT_018_20'
    group by trunc(occur_date),user_id,result
    ) group by login_date
       ) LOGIN_TABLE ,
      
   (select trunc(send_date) registered_Date, count(id) register_amount from T_SODW_SUBSCRIBER_CODE
         WHERE  send_date > to_date('20110623','yyyymmdd') AND send_flag = 2  group by trunc(send_date)
      ) REGISTER_TABLE

   where login_date=registered_Date order by login_date;
   commit;
   end;