DROP procedure IF EXISTS MODIFY_USER_ID;
delimiter //
create procedure MODIFY_USER_ID() ## 创建存储过程
begin
declare d_login_id varchar(50) default 'system_operator';
declare to_id varchar(50) default 'system_operator_id';
declare acct_user_role_id varchar(50);
declare done int default false;
declare o_acct_user_id VARCHAR(32);
DECLARE d_wallet_id varchar(32);
DECLARE user_role_cursor CURSOR FOR select aur.id from acct_user_role as aur where aur.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);
DECLARE acct_coupon_cursor CURSOR FOR select ac.id from acct_coupon as ac where ac.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);
DECLARE acct_logistics_coupon_cursor CURSOR FOR select alcc.id from acct_logistics_coupon as alcc where alcc.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);
DECLARE acct_coupon_batch_cursor CURSOR FOR select acb.id from acct_coupon_batch as acb where acb.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);
declare continue HANDLER for not found set done = true;
start TRANSACTION;
open user_role_cursor;
open acct_coupon_cursor;
open acct_logistics_coupon_cursor;
open acct_coupon_batch_cursor;
select id into o_acct_user_id from acct_user au where au.login_id=d_login_id;
-- 保存wallet的id
select ww.id into d_wallet_id from wall_wallet as ww where ww.`user`=(select au.id from acct_user as au where au.login_id=d_login_id);
-- 将关联的userID全部都置空
update acct_user_role set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);
update acct_coupon set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);
update acct_logistics_coupon set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);
update acct_coupon_batch set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);
update wall_wallet set `user`=null where `user`= (select id from acct_user where login_id=d_login_id);
-- 开始更新
update acct_user set id=to_id where login_id=d_login_id;
select d_wallet_id;
update wall_wallet set `user`=to_id where id=d_wallet_id;
read_loop:loop
fetch user_role_cursor into acct_user_role_id;
if done then
leave read_loop;
end if;
update acct_user_role set user_id=to_id where id=acct_user_role_id;
end loop;
set done = false;
read_loop:loop
fetch acct_coupon_cursor into acct_user_role_id;
if done then
leave read_loop;
end if;
update acct_coupon set user_id=to_id where id=acct_user_role_id;
end loop;
set done = false;
read_loop:loop
fetch acct_logistics_coupon_cursor into acct_user_role_id;
if done then
leave read_loop;
end if;
update acct_logistics_coupon set user_id=to_id where id=acct_user_role_id;
end loop;
set done = false;
read_loop:loop
fetch acct_coupon_batch_cursor into acct_user_role_id;
if done then
leave read_loop;
end if;
update acct_coupon_batch set user_id=to_id where id=acct_user_role_id;
end loop;
CLOSE user_role_cursor;
CLOSE acct_coupon_cursor;
CLOSE acct_logistics_coupon_cursor;
CLOSE acct_coupon_batch_cursor;
select * from acct_user_role where user_id = (select id from acct_user where login_id=d_login_id);
select * from wall_wallet where `user`= (select id from acct_user where login_id=d_login_id);
-- 最后更新customer
update balidrop_product set customer_id=to_id where customer_id= ( select id from cust_customer where user_id = o_acct_user_id);
update balidrop_prdouct_category set customer_id=to_id ,customer_name=to_id where customer_id= ( select id from cust_customer where user_id = o_acct_user_id);
update cust_customer set id=to_id, user_id=to_id where user_id = o_acct_user_id;
COMMIT;
end;//
delimiter;
call MODIFY_USER_ID();