ORCALE中的 MERGE INTO UPDATE、INSERT实践

1、看到一段代码如下：

JPA与MyBatis混用；

大致的逻辑是：在异步里，计算当前时间与上一状态记录的TimeStamp创建时间的时间差（转分钟，保留毫秒数）作为上一状态的持续时间。并记录当前状态。

 

@Async
@Override
public void logAgentStatus(AgentStatus status){
    
    Integer queryStatus = CommonUtils.getQueryStatusForUserAction(status.getStatus());

    UserAction userAction = userActionRepository.finTopByUserIdAndStatusAndActionAndNewValueIsNullOrderByCreateDateDesc(status.getUserId(),queryStatus,status.getUserAction().name());

    if(!Objects.isNull(userAction) && StringUtils.isEmpty(userAction.getNewValue())){

        Double begin = new Double(userAction.getCreatedDate() != null ? userAction.getCreatedDate().getTime():System.currentTimeMillis());

        Double end = new Double(System.currentTimeMillis());

        Double min = (end - beign)/60/1000.0;

        userActionMapper.updateNewValue(Double.toString(min),userAction.getId());
    }

    UserAction userActionAvailable = userActionRepository.finTopByUserIdAndStatusAndActionAndNewValueIsNullOrderByCreateDateDesc(status.getUserId(),status.getStatus,statys.GetUserAction().name());

    if(Objects.isNull(userActionAvailable)){
        
        userActionRepository.saveAndFlush(UserAction.Builder().userId(status.getUserId())
        .action(status.getUserAction().name()).status(status.getStatus()).build());
    }

}

2、ORACLE通过查询更新表中数据

ORCALE日期、时间的处理，转：https://www.cnblogs.com/hijushen/p/4223557.html

转：https://blog.csdn.net/iteye_14659/article/details/82331422

使用一条sql代替上面的查询和更新代码，执行逻辑 ：计算当前时间与上一状态记录的TimeStamp创建时间的时间差（转分钟，保留毫秒数）作为上一状态的持续时间

MERGE INTO IM_USER_ACTION_LOG A
USING(  SELECT ID,
            EXTRACT(DAY FROM (SYSTIMESTAMP - CREATED_DATE))*24*60*60 +
            EXTRACT(HOUR FROM (SYSTIMESTAMP - CREATED_DATE))*60*60 +
            EXTRACT(MINUTE FROM (SYSTIMESTAMP - CREATED_DATE)) +
            EXTRACT(SECOND FROM (SYSTIMESTAMP - CREATED_DATE))/60 AS MIN
        FROM (SELECT T.ID,T.CREATED_DATE
                FROM IM_USER_ACTION_LOG T
                WHERE   T.USER_ID = 'AA'
                    AND T.STATUS =  1
                    AND T.ACTION = 'ACTION'
                    AND T.NEW_VALUE IS NULL
                ORDER BY T.CREATED_DATE DESC)
        WHERE ROWNUM = 1) B
ON (A.ID = B.ID)
WHEN MATCHED THEN
    UPDATE SET A.NEW_VALUE = B.MIN ;     

3、自测时，发现新问题：当持续时间较短时，db字段new_value存储的数值出现类似 .14 丢失个位为0的问题，以及小数点后的位数长度太长的问题。

解决方法，转 https://blog.csdn.net/qq_34909807/article/details/76270987   

MERGE INTO IM_USER_ACTION_LOG A
USING(  SELECT ID,
            EXTRACT(DAY FROM (SYSTIMESTAMP - CREATED_DATE))*24*60*60 +
            EXTRACT(HOUR FROM (SYSTIMESTAMP - CREATED_DATE))*60*60 +
            EXTRACT(MINUTE FROM (SYSTIMESTAMP - CREATED_DATE)) +
            EXTRACT(SECOND FROM (SYSTIMESTAMP - CREATED_DATE))/60 AS MIN
        FROM (SELECT T.ID,T.CREATED_DATE
                FROM IM_USER_ACTION_LOG T
                WHERE   T.USER_ID = 'AA'
                    AND T.STATUS =  1
                    AND T.ACTION = 'ACTION'
                    AND T.NEW_VALUE IS NULL
                ORDER BY T.CREATED_DATE DESC)
        WHERE ROWNUM = 1) B
ON (A.ID = B.ID)
WHEN MATCHED THEN
    UPDATE SET A.NEW_VALUE = TO_CHAR(B.MIN,'fm9999990.9999999999') ;     

4、为避免重复插入当前状态的记录，所以JAVA代码中的逻辑是先查询，查询不到再插入新的值。此处不能使用 MERGE INTO（它是使用USING中的数据按WHERE MATCHED 或者 NOT MATCHED 执行THEN的逻辑）。

尝试 （这种一般用在表复制场景）INSERT INTO TB(...) SELECT ...FROM TB WHERE NOT EXISTS(SELECT ... FROM TB WHERE ) 转 https://blog.csdn.net/sun5769675/article/details/50158531?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

但是很不幸，NOT EXISTS子句判断条件没有唯一性，会导致插入多条。