syncMemberCarNo.sql

CREATE DEFINER=`root`@`%` PROCEDURE `syncMemberCarNo`()
begin
	DECLARE s INT DEFAULT 0;
	DECLARE mid BIGINT DEFAULT 0;
	DECLARE carname varchar(255) DEFAULT null;
	DECLARE carno varchar(255) DEFAULT null;
	-- 定义游标，并将sql结果集赋值到游标中，report为游标名
	DECLARE report CURSOR FOR select user_id,name,card_number from member_user_card_auth t where t.`status`=2 and t.deleted=0 and  LENGTH(t.name) < 20 and LENGTH(t.card_number) < 20 and t.card_type=1;  -- 审核通过的会员
-- 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
	-- 打开游标
OPEN report;
-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO mid,carname,carno;
-- 当s不等于1时，也就是未遍历完时，会一直循环
WHILE
s <> 1 DO

    set @cid = (select distinct a.id from ecw_customer a inner join ecw_customer_contacts b on a.id = b.customer_id and b.deleted=0
            inner join member_user c on b.userid = c.id
            where c.id = mid order by a.id desc limit 1);

    if @cid > 0 then
update ecw_customer a set a.car_name = carname,a.car_no=carno where a.id = @cid;
end if;


    -- 当s等于1时代表遍历已完成，退出循环
FETCH report INTO mid,carname,carno;

END WHILE;-- 关闭游标
CLOSE report;
end