mysql存储过程实现数据同步
数据库表发⽣变化，需要把2.0的表数据同步到3.0库中去；
-- 同步数据存储过程执⾏
-- 更新留⾔旧表数据到新表数据中
/*DEFINER：Vector*/
drop procedure if exists p_for_feedback_message;
create procedure p_for_feedback_message()
begin
DECLARE v_done INT DEFAULT 0;
DECLARE v_id INT DEFAULT 0;
DECLARE v_feedback VARCHAR(50);
DECLARE v_user_phone VARCHAR(50);
DECLARE v_system_time VARCHAR(50);
DECLARE v_status VARCHAR(50);
DECLARE v_requDatas CURSOR FOR (SELECT f.id as id,f.feedback as feedback,f.user_phone as user_phone,f.system_time as system_time,f.`status` as status FROM feedback_message as f ORDER BY system_time DESC);
/*游标异常后捕捉并设置变量，done 为 1 跳出循环*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
START TRANSACTION;
/*打开游标*/
OPEN v_requDatas;
out_loop:
LOOP
/*游标向下⾛⼀步，将查询出来的值付给定义的变量*/
FETCH v_requDatas into v_id,v_feedback,v_user_phone,v_system_time,v_status;
IF v_done = 1 THEN
LEAVE out_loop;
END IF;
mysql视图和存储过程BEGIN
DECLARE v_user_id VARCHAR(50);
SELECT user_id into v_user_id FROM user_info WHERE user_phone = v_user_phone LIMIT 0,1 ;
INSERT INTO feedback_message2
(id,user_id,feedback,system_time,`status`)VALUES(v_id,v_user_id,v_feedback,v_system_time,v_status);
if(v_user_phone is not null) then
select v_user_phone,v_user_id;
end if;
END;
SET v_done = 0;
END LOOP out_loop;
/*关闭游标*/
CLOSE v_requDatas;
/*事务提交*/
COMMIT;
END;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。