-- 創(chuàng)建測試表
-- drop table test_a;
create  table test_a(
id VARCHAR (16),
name VARCHAR (16),
Operatime datetime
)
-- drop table test_b;
create table test_b(
id VARCHAR (16),
name VARCHAR (16),
Operatime datetime
)

-- 插入模擬數(shù)據(jù)
INSERT into test_b values(1,"1",now()),(2,"2",now());
INSERT into test_a values(1,"1",now()),(3,"3",now());

-- 查詢數(shù)據(jù)
SELECT * FROM test_b;
SELECT * FROM test_a;
 

delimiter $
CREATE PROCEDURE merge_a_to_b () BEGIN
-- 定義需要插入從a表插入b表的過程變量
DECLARE _ID VARCHAR (16);
DECLARE _NAME VARCHAR (16);
-- 游標遍歷數(shù)據(jù)結(jié)束標志 
DECLARE done INT DEFAULT FALSE;
-- 游標指向a表結(jié)果集第一條-1位置
DECLARE cur_account CURSOR FOR SELECT ID, NAME FROM test_a;
-- 游標指向a表結(jié)果集最后一條加1位置 設置結(jié)束標志
DECLARE CONTINUE HANDLER FOR NOT FOUND  SET done = TRUE;
-- 打開游標
OPEN cur_account;
-- 遍歷游標
read_loop :
LOOP
--  取值a表當前位置數(shù)據(jù)到臨時變量
	FETCH NEXT FROM cur_account INTO _ID,_NAME;

-- 如果取值結(jié)束 跳出循環(huán)
IF done THEN LEAVE read_loop; 
END IF;

-- 當前數(shù)據(jù)做 對比 如果b表存在則更新時間 不存在則插入
IF NOT EXISTS ( SELECT 1 FROM TEST_B WHERE ID = _ID AND NAME=_NAME ) 
	THEN
		INSERT INTO TEST_B (ID, NAME,operatime) VALUES (_ID,_NAME,now());
	ELSE 
	UPDATE TEST_B  set operatime = now() WHERE ID = _ID AND NAME=_NAME;
END IF;

END LOOP;
CLOSE cur_account;

END $

-- 驗證語句
SELECT * FROM test_b;
SELECT * FROM test_a;
call merge_a_to_b();
SELECT * FROM test_b;
SELECT * FROM test_a;