业务需求:现在有表A表B,结构相同。现在需要把表B数据同步到表A,如果B表的数据在A表中已存在则更新,若B表的数据在A表中不存在则新增
表名 | 字段 |
---|---|
tem | ID, NUM(编号) NAME(名称), IS_VALID(0:已失效 1:未失效) |
tag | ID, NUM(编号) NAME(名称), IS_VALID(0:已失效 1:未失效) |
使用oracle的MERGE写法
利用B表通过A.NUM=B.NUM的条件来匹配A表,当满足条件时,可以对A表进行更新(注意这里不能进行新增操作一新增就报错,MD,不然后面我也不用写存储过程了,有解决办法的私聊),当不满足条件时,可以利用inert语句插入相关数据。
MERGE INTO tag t
USING tem tem
ON (t.NUM = tem.NUM)
WHEN MATCHED THEN
UPDATE
SET t.NUM = tem.NUM,
t.NAME = tem.NAME,
t.IS_VALID = tem.IS_VALID
WHEN NOT MATCHED THEN
INSERT
(t.ID, t.NUM, t.NAME, t.IS_VALID)
VALUES (tem.ID, tem.NUM, tem.NAME, tem.IS_VALID)
需求变更了!多开心啊
现在需要把表B数据同步到表A,但是需要把每次变更时的重复数据保存,也就是如果B表的数据在A表中已存在需要把存在数据的IS_VALID变更为0之后再新插入数据,若B表的数据在A表中不存在则直接新增
一开始在存储过程中写了个循环被主管说不行 嘎嘎嘎,好像是有点孬。思路遍历每张B表数据,一一和A表对比。存在则多一步更改IS_VALID为0操作
create PROCEDURE SP_POLICE_INSERT
AS
cursor temRows is SELECT *
FROM tem tem;
exitCount number(4);
BEGIN
FOR temRow in temRows
LOOP
SELECT count(1) into exitCount FROM tag t WHERE t.NUM = temRow.NUM AND t.IS_VALID = 1;
-- 存在则编辑IS_VALID为0
IF exitCount > 0 THEN
UPDATE tag SET IS_VALID = 0 WHERE ID = temRow.ID;
COMMIT;
END IF;
INSERT into tag (ID, NUM, NAME, IS_VALID)
VALUES (temRow.ID, NUM, temRow.NAME, temRow.IS_VALID);
COMMIT;
END LOOP;
END;
换写法先将两表中NUM的交集先设置为已失效,再将整张tem张插入tag表
CREATE PROCEDURE SP_POLICE_INSERT
AS
BEGIN
-- tag表与tem表中的交集先设置为已失效
UPDATE tag t
SET t.IS_VALID = 0
where t.NUM in (SELECT NUM
FROM tag
INTERSECT
SELECT NUM
FROM tem)
AND t.IS_VALID = 1;
COMMIT;
-- 整张tem表插入tag表
insert into tag t (t.ID, t.NUM, t.NAME, t.IS_VALID)
select tem.ID,
tem.NUM,
tem.NAME,
tem.IS_VALID
from tem tem;
commit;
END;
此时被告知在ORACLE中尽量不要用IN,遇见IN里的值太多的话那这个问题就有、滴口水了
我不信!上网查还真有大仙遇见
MD改
create PROCEDURE SP_POLICE_INSERT
AS
BEGIN
-- tag表与tem表中的交集先设置为已失效
UPDATE tag t
SET t.IS_VALID = 0
where EXISTS(SELECT 1 FROM tem tem WHERE tem.NUM = t.NUM)
AND t.IS_VALID = 1;
COMMIT;
-- 整张tem表插入tag表
insert into tag t (t.ID, t.NUM, t.NAME, t.IS_VALID)
select tem.ID,
tem.NUM,
tem.NAME,
tem.IS_VALID
from tem tem;
commit;
END;