简单的存储过程和游标的运用

--create procedure RelationUserBackUp for CR -----------
create or replace procedure P_RelationUserBackUp
as
   cursor mypnpncur is
         select a.senterpriseid,a.sfullnumber ,a.npnppolicyid,a.spnpn, a.nusertype from e_vpn_member a;
--where exists (select spnpn  from e_vpn_member b where b.sfullnumber =a.sfullnumber);

  begin
      for v in mypnpncur loop
     
     if(v.spnpn is not null) then
     
           insert into E_VPN_RELATION values(
                 v.senterpriseid,
                 v.sfullnumber,
           v.nPNPPolicyID,
           v.spnpn,
           v.nusertype
      );
   
   update E_VPN_RELATION set SPNPN=replace(SPNPN, '#','C');
   update E_VPN_RELATION set SPNPN=replace(SPNPN, '*','B');

  end if;
 
  if (v.spnpn is null) then
  insert into E_VPN_RELATION values(
            v.senterpriseid,
            v.sfullnumber,
      v.nPNPPolicyID,
      '000000',
      v.nusertype);
  end if;
  
  end loop;

  commit;

end P_RelationUserBackUp;
/