mysql存储过程例子cursor

CREATE DEFINER = 'test'@'%'
PROCEDURE DB.mv_parentsToContact()
begin
declare custid bigint(20);
declare name1 varchar(50);
declare mobile1 varchar(20);
declare name2 varchar(50);
declare mobile2 varchar(20);
  declare tempStr varchar(200);
  DECLARE done INT DEFAULT FALSE;


DECLARE cur1 CURSOR FOR
select id from p2p_customer t1 where 
not exists (
select t2.CUST_ID from p2p_cust_top_contactor t2 where t2.CUST_ID=t1.id limit 0,1
)
and   exists (
select t3.CUST_ID from p2p_cust_addr_list t3 where t3.CUST_ID=t1.id limit 0,1
)  order by id asc;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
custIDs_Loop:LOOP  
fetch cur1 into custid;


    IF done THEN
      set done=FALSE;
      LEAVE custIDs_Loop;
    END IF;
  


select t.MOBILE,t.COMPANY_NAME into name1,mobile1 from p2p_customer_contactor t where t.CUST_ID=custid
and t.CONNECT_TYPE='FATHER' and DEL_FLAG='N';
    
   IF done THEN 
     set done=FALSE;
     ITERATE custIDs_Loop;    
   END IF;


select t.MOBILE,t.COMPANY_NAME into name2,mobile2 from p2p_customer_contactor t where t.CUST_ID=custid
and t.CONNECT_TYPE='MOTHER' and DEL_FLAG='N';


    IF done THEN
      set done=FALSE; 
     ITERATE custIDs_Loop;    
   END IF;


if name1 is not null and mobile1 is not null and name2 is not null and mobile2 is not null THEN
if name1 !='' and mobile1 !=''  and name2 !=''  and mobile2 !=''  THEN

            insert into p2p_cust_top_contactor ( CUST_ID, CON_NAME, MOBILE, RELATION_TYPE, CREATE_TIME, UPDATE_TIME ) 
values ( custid, name1, mobile1, 'parents', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP );
insert into p2p_cust_top_contactor ( CUST_ID, CON_NAME, MOBILE, RELATION_TYPE, CREATE_TIME, UPDATE_TIME ) 
values ( custid, name2, mobile2, 'parents', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP );
COMMIT;
END IF;
END IF;
    

END LOOP custIDs_Loop; 
close cur1;


end