使用临时表的Oracle查询报错（无法识别临时表），请求技术协助

你现在的问题是把SQL Server的临时表写法直接搬到Oracle里了，Oracle不支持SQL Server的#临时表和SELECT INTO语法，还有OUTER APPLY在Oracle里要用LEFT JOIN LATERAL替代。下面是修正后的完整代码，以及关键改动说明：

关键改动点 #

• 去掉SQL Server风格的#Test写法，Oracle全局临时表创建后直接用表名
• 用INSERT INTO ... SELECT ...代替SELECT INTO来填充临时表
• 替换TOP(1000)为Oracle的FETCH FIRST 1000 ROWS ONLY，并修正原SQL里的语法错误（TOP后面多了逗号）
• 把OUTER APPLY替换为Oracle支持的LEFT JOIN LATERAL
• 修正Timestamp字段的默认值（Oracle不允许给Timestamp设默认0，这里去掉不合理的默认）

修正后的完整代码 #

-- 创建全局临时表（会话级，会话结束才清空数据，若要事务级改成ON COMMIT DELETE ROWS）
CREATE GLOBAL TEMPORARY TABLE Test
(
    EVENTTIME Timestamp,
    LCIDENTIFIER nvarchar2(50),
    MESSAGETYPE nvarchar2(4),
    ADDRIDENTIFIER nvarchar(50)
) ON COMMIT PRESERVE ROWS;

-- 向临时表插入数据（替代SQL Server的SELECT INTO）
INSERT INTO Test (EVENTTIME, LCIDENTIFIER, MESSAGETYPE, ADDRIDENTIFIER)
SELECT EVENTTIME, LCIDENTIFIER, MESSAGETYPE, ADDRIDENTIFIER
FROM TR_COMMUNICATION
FETCH FIRST 1000 ROWS ONLY;

-- 主查询，替换OUTER APPLY为LEFT JOIN LATERAL
SELECT DELAYED.CREATEEVENTTIME,
       DELAYED.LREP_TIME,
       DELAYED.DLST_TIME,
       DELAYED.SOURCEADDRESS,
       NEXT_LREP.ADDRIDENTIFIER AS NEXT_LREP 
FROM (
    SELECT tv.CREATEEVENTTIME,
           lrep_cc.EVENTTIME AS LREP_TIME,
           dlst_cc.EVENTTIME AS DLST_TIME,
           tv.SOURCEADDRESS,
           tr.FROM_POS,
           EXTRACT(SECOND FROM dlst_cc.EVENTTIME - lrep_cc.EventTime) * 1000 AS ms_diff,
           tv.LC_NAME  -- 这里要把LC_NAME选出来，后面关联NEXT_LREP要用
    FROM TR_OVERVIEW_V tv
    INNER JOIN TR_RELATED tr 
        ON tr.LC_NAME = tv.LC_NAME 
        AND tr.FROM_LOC = tv.SOURCEADDRESS
        AND tr.EVENT_NAME = 'TaskStarted'
    INNER JOIN Test lrep_cc 
        ON lrep_cc.LCIDENTIFIER = tv.LC_NAME
        AND lrep_cc.MESSAGETYPE = 'LREP'
        AND lrep_cc.ADDRIDENTIFIER = tr.FROM_POS
        AND lrep_cc.EVENTTIME <= tv.CREATEEVENTTIME + INTERVAL '1' SECOND
        AND lrep_cc.EVENTTIME >= tv.CREATEEVENTTIME - INTERVAL '1' SECOND
    INNER JOIN Test dlst_cc 
        ON dlst_cc.LCIDENTIFIER = tv.LC_NAME
        AND dlst_cc.MESSAGETYPE = 'DLST'
        AND dlst_cc.EVENTTIME <= tv.CREATEEVENTTIME + INTERVAL '2' SECOND
        AND dlst_cc.EVENTTIME >= tv.CREATEEVENTTIME - INTERVAL '2' SECOND                                              
    WHERE tv.EVENT_NAME = 'CompletedWithError'
      AND tv.SOURCEADDRESS IS NOT NULL
      AND EXTRACT(SECOND FROM dlst_cc.EVENTTIME - lrep_cc.EventTime) * 1000 > 250
    ORDER BY tv.EVENTTIME DESC
    FETCH FIRST 500 ROWS ONLY
) DELAYED
LEFT JOIN LATERAL (
    SELECT ADDRIDENTIFIER
    FROM Test
    WHERE MESSAGETYPE = 'LREP'
      AND LCIDENTIFIER = DELAYED.LC_NAME
      AND EVENTTIME > DELAYED.DLST_TIME
    ORDER BY EVENTTIME ASC
    FETCH FIRST 1 ROW ONLY
) NEXT_LREP ON 1=1;

额外优化建议 #

• 如果临时表数据量较大，建议给临时表添加索引，比如：

  CREATE INDEX idx_test_lc_msg_time ON Test(LCIDENTIFIER, MESSAGETYPE, EVENTTIME);
  

• 全局临时表的生命周期根据需求选择：ON COMMIT PRESERVE ROWS是会话级，ON COMMIT DELETE ROWS是事务级，按需选择

内容的提问来源于stack exchange，提问作者user616076