create or replace procedure endsoftwareprocedure
  (emac in out varchar2(64),
 eip in out varchar2(64),
 eprovince in out varchar2(64),
 ecity in out varchar2(32),
 eusername in out varchar2(64),
 eversion in out varchar2(32))
 
  consumid   number(20); 

  useid   number(20);
  
   select count(a.id) into consumid  from bm_consumer a where a.mac=emac;

   if consumid >0
     then
        update bm_consumer a set a.ip =eip,a.province=eprovince,a.city = ecity,a.username=eusername;
   else
      select bm_consumer_sequences.nextval into consumid from dual;

      insert into bm_consumer (id,mac,ip,province,city,username) 
          values(consumid,emac,eip,eprovince,ecity,eusername);
          
     insert into bm_userinfo(id,consumerid,version,starttime,totaltime,islogin)
     values(bm_userinfo_sequences.nextval,consumid,eversion,TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss'),0,1);
     end if;

     update bm_userinfo set lasttime=TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss'),
      totaltime=round(to_number(TO_DATE(sysdate,'yyyy-mm-dd hh24:mi:ss')-bm_userinfo.starttime)*1440) ,
      islogin=0;
 commit; 

exception

when others then

rollback;

end endsoftwareprocedure;