-- 創(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);
-- 游標(biāo)遍歷數(shù)據(jù)結(jié)束標(biāo)志 
DECLARE done INT DEFAULT FALSE;
-- 游標(biāo)指向a表結(jié)果集第一條-1位置
DECLARE cur_account CURSOR FOR SELECT ID, NAME FROM test_a;
-- 游標(biāo)指向a表結(jié)果集最后一條加1位置 設(shè)置結(jié)束標(biāo)志
DECLARE CONTINUE HANDLER FOR NOT FOUND  SET done = TRUE;
-- 打開游標(biāo)
OPEN cur_account;
-- 遍歷游標(biāo)
read_loop :
LOOP
--  取值a表當(dāng)前位置數(shù)據(jù)到臨時(shí)變量
	FETCH NEXT FROM cur_account INTO _ID,_NAME;

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

-- 當(dāng)前數(shù)據(jù)做 對(duì)比 如果b表存在則更新時(shí)間 不存在則插入
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 $

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