MERGE操作表的更新和插入

情景：千万用户【公共消息表】入表一条消息即全员可见，【消息状态表】记录消息状态
1.用户消息首次已读时入表消息状态已读
2.用户消息删除消息时入表消息状态删除/修改已读为删除

--1、建表
CREATE TABLE TABLE_NAME
(
  ID         VARCHAR2(32),
  STATUS     INTEGER
);

--2、修改或新增数据
  --逻辑：已读STATUS=0;删除STATUS>=100;未读LEFT JOIN该表NVL(STATUS,'1')
    --已读时操作
    MERGE INTO TABLE_NAME T1
    USING (SELECT '2019' AS ID,'0' AS STATUS FROM DUAL) T2
    ON (T1.ID = T2.ID)
    WHEN MATCHED THEN
         UPDATE SET T1.STATUS = T1.STATUS + T2.STATUS
    WHEN NOT MATCHED THEN
         INSERT VALUES('2019','0');
    --删除时操作
    MERGE INTO TABLE_NAME T1
    USING (SELECT '2019' AS ID,'100' AS STATUS FROM DUAL) T2
    ON (T1.ID = T2.ID)
    WHEN MATCHED THEN
         UPDATE SET T1.STATUS = T1.STATUS + T2.STATUS
    WHEN NOT MATCHED THEN
         INSERT VALUES('2019','100');
     
--3、查询状态数数据
S