oracle YYYYMMDDHH24MISS

Oracle中的日期格式化字符串YYYYMMDDHH24MISS可以用于将日期时间值格式化为指定格式的字符串。

例如，可以使用以下语句将当前日期时间格式化为YYYYMMDDHH24MISS格式的字符串：

SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') FROM DUAL;

这将返回类似于'20220118123456'的字符串，表示当前的年份、月份、日期、小时、分钟和秒数。

相关问题

UPDATE FPT_PRDLOTACTIVITYHISTORY t SET t.SumCycleTime = ( SELECT SUM(PH.EVENTTIME - ( SELECT /* +USE_HASH(A,PH) */ MAX(A.Eventtime) FROM MES_PRODUCTHISTORY A WHERE A.FACTORYNAME = PH.FACTORYNAME AND A.TIMEKEY < PH.TIMEKEY AND a.Productname = ph.Productname AND a.ProcessOperationName = ph.OldProcessOperationName AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework') ) ) AS SumCycleTime FROM MES_PRODUCTHISTORY PH WHERE ph.factoryname = t.factoryname AND ph.LotName = t.LotName AND ph.Timekey = t.Timekey ) --, t.CycleTime = t.SumCycleTime / t.PRODUCTQUANTITY WHERE t.factoryname = 'ARRAY' AND t.Timekey >= '20230502220000' -- YYYYMMDDHH24MISS.HH AND t.Timekey < '20230502221000' -- YYYYMMDDHH24MISS.HH AND t.MappingEventName = 'Leave' AND nvl(t.Oldsubproductquantity,0) > 0 优化这个oracle sql

这个 SQL 语句可以进行以下优化：

1. 使用 EXISTS 替代子查询

可以将子查询改写为 EXISTS 子查询，因为 EXISTS 子查询只需要判断子查询是否返回结果，而不需要返回具体的值，比子查询更高效。

UPDATE FPT_PRDLOTACTIVITYHISTORY t
SET t.SumCycleTime = (
    SELECT SUM(PH.EVENTTIME - (
            SELECT /* +USE_HASH(A,PH) */
            MAX(A.Eventtime)
            FROM MES_PRODUCTHISTORY A
            WHERE A.FACTORYNAME = PH.FACTORYNAME
                AND A.TIMEKEY < PH.TIMEKEY
                AND a.Productname = ph.Productname
                AND a.ProcessOperationName = ph.OldProcessOperationName
                AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
        )
    ) AS SumCycleTime
    FROM MES_PRODUCTHISTORY PH
    WHERE ph.factoryname = t.factoryname
        AND ph.LotName = t.LotName
        AND ph.Timekey = t.Timekey
        AND EXISTS (
            SELECT 1
            FROM MES_PRODUCTHISTORY A
            WHERE A.FACTORYNAME = PH.FACTORYNAME
                AND A.TIMEKEY < PH.TIMEKEY
                AND a.Productname = ph.Productname
                AND a.ProcessOperationName = ph.OldProcessOperationName
                AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
        )
)
WHERE t.factoryname = 'ARRAY'
    AND t.Timekey >= '20230502220000'
    AND t.Timekey < '20230502221000'
    AND t.MappingEventName = 'Leave'
    AND NVL(t.Oldsubproductquantity,0) > 0;

2. 使用 JOIN 替代子查询

可以使用 JOIN 将子查询中的 MAX 函数改写为 JOIN 操作，从而提高查询效率。

UPDATE FPT_PRDLOTACTIVITYHISTORY t
SET t.SumCycleTime = (
    SELECT SUM(PH.EVENTTIME - A.MaxEventtime) AS SumCycleTime
    FROM MES_PRODUCTHISTORY PH
        INNER JOIN (
            SELECT FACTORYNAME, Productname, OldProcessOperationName, MAX(Eventtime) AS MaxEventtime
            FROM MES_PRODUCTHISTORY
            WHERE EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
            GROUP BY FACTORYNAME, Productname, OldProcessOperationName
        ) A ON PH.FACTORYNAME = A.FACTORYNAME
            AND PH.Productname = A.Productname
            AND PH.OldProcessOperationName = A.OldProcessOperationName
            AND PH.Eventtime > A.MaxEventtime
    WHERE ph.factoryname = t.factoryname
        AND ph.LotName = t.LotName
        AND ph.Timekey = t.Timekey
)
WHERE t.factoryname = 'ARRAY'
    AND t.Timekey >= '20230502220000'
    AND t.Timekey < '20230502221000'
    AND t.MappingEventName = 'Leave'
    AND NVL(t.Oldsubproductquantity,0) > 0;

以上两种优化方式都可以有效提高 SQL 查询效率。

帮我写一个打开Oracle数据库并且执行“update esb_config t set t.val = (case when substr(t.val,0,8)=to_char(sysdate,'YYYYMMDD') then t.val + 2 else (to_number(to_char(sysdate,'YYYYMMDD')||'001')) end) where t.mdl = 'db' and name = 'msgDefVerDt'; update esb_config t set t.val = to_char(sysdate,'yyyyMMddHH24miss') where t.mdl = 'db' and name = 'versionDate'; commit;”命令的bat脚本程序

@echo off
sqlplus username/password@database_name <<-EOF
update esb_config t set t.val = (case when substr(t.val,0,8)=to_char(sysdate,'YYYYMMDD') then t.val 2 else (to_number(to_char(sysdate,'YYYYMMDD')||'001')) end) where t.mdl = 'db' and name = 'msgDefVerDt';
update esb_config t set t.val = to_char(sysdate,'yyyyMMddHH24miss') where t.mdl = 'db' and name = 'versionDate';
commit;
exit;
EOF

pause