procedure实例2

create or replace procedure pro_synchronize_bss IS
usercount number;
cursor cur is
    select * from v_xf_bss;
BEGIN
    for curitem in cur
    loop
        --dbms_output.put_line(curitem.code);
        if(curitem.realname is not null and trim(curitem.code) is not null)
        then
        if(exist_base_user(trim(curitem.code))=0)
        then
        
          if(curitem.CZLX='D')
          then
          insert into
                base_user(ID, code, realname)
                values(trim(curitem.code), trim(curitem.code), curitem.realname);
          commit;                                             
          else 
           --dbms_output.put_line('insert...');
           insert into
                base_user(ID, code, realname)
                values(trim(curitem.code), trim(curitem.code), curitem.realname);
          commit;
          end if;
        else
        
           if(curitem.CZLX!='D')
           then
           
           --dbms_output.put_line('update...');
           select count(*) into usercount from base_user tt1
                where tt1.code=trim(curitem.code) and tt1.realname=curitem.realname ;
           
           if(usercount=0)
           then
              update base_user tt2
                   set tt2.realname=curitem.realname  where tt2.code=trim(curitem.code);
              commit;
           end if;
           
           end if;
           
        end if;
        end if;
    end loop;
    commit;
end pro_synchronize_bss;

 

create or replace procedure pro_synchronize as
begin
    pro_synchronize_bzks;
    pro_synchronize_yjs;
    pro_synchronize_bss;
    pro_synchronize_xyzg;
    pro_synchronize_jzg_ltx;
    pro_synchronize_jzg;
    commit;
end pro_synchronize;