Oracle SQL MERGE语句报ORA-00905缺失关键字问题求助

问题描述 #

输入参数 #

workflowId='CHILD1'
processName='TEST4'
sectionId=''
dependsOnWorkflowId='PARENT1'
dependsOnProcessName='TEST4'
dependsOnSectionId=''

执行的MERGE语句 #

MERGE INTO WF_CMN_A_PROCESS_DEPENDENCY_TREE_TEST3 USING DUAL 
ON (    
    "WORKFLOW_ID"             = :workflowId
    AND "PROCESS_NAME"            = :processName
    AND NVL("SECTION_ID", '-')    = NVL(:sectionId, '-')
)
-- Root node exists, create a parent-child link and delete the root node
WHEN MATCHED THEN UPDATE SET 
    "DEPENDS_ON_WORKFLOW_ID" = :dependsOnWorkflowId,
    "DEPENDS_ON_PROCESS_NAME"= :dependsOnProcessName,
    "DEPENDS_ON_SECTION_ID"  = :dependsOnSectionId,
    "LAST_UPDT_DT_TM"        = SYSDATE
WHERE 
        "WORKFLOW_ID"             = :workflowId
    AND "PROCESS_NAME"            = :processName
    AND NVL("SECTION_ID", '-')    = NVL(:sectionId, '-')
    AND "DEPENDS_ON_WORKFLOW_ID"  IS NULL 
    AND "DEPENDS_ON_PROCESS_NAME" IS NULL 
    AND "DEPENDS_ON_SECTION_ID"   IS NULL 

WHEN NOT MATCHED THEN 
    MERGE INTO WF_CMN_A_PROCESS_DEPENDENCY_TREE_TEST3 USING DUAL 
    ON (  
        "WORKFLOW_ID"                      = :workflowId
        AND "PROCESS_NAME"                     = :processName
        AND NVL("SECTION_ID", '-')             = NVL(:sectionId, '-')
        AND NVL("DEPENDS_ON_WORKFLOW_ID" ,'-') = NVL(:dependsOnWorkflowId , '-')
        AND NVL("DEPENDS_ON_PROCESS_NAME",'-') = NVL(:dependsOnProcessName, '-')
        AND NVL("DEPENDS_ON_SECTION_ID"  ,'-') = NVL(:dependsOnSectionId  , '-')
    )
    WHEN NOT MATCHED THEN 
    INSERT (
        "WORKFLOW_ID",
        "PROCESS_NAME",
        "SECTION_ID",
        "DEPENDS_ON_WORKFLOW_ID",
        "DEPENDS_ON_PROCESS_NAME",
        "DEPENDS_ON_SECTION_ID",
        "LAST_UPDT_DT_TM"
    ) 
    VALUES (        
        :workflowId,
        :processName,
        :sectionId,
        :dependsOnWorkflowId,
        :dependsOnProcessName,
        :dependsOnSectionId,
        SYSDATE
    )

错误信息 #

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [905] [42000]: ORA-00905: missing keyword

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:544)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:451)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:458)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:847)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3515)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4852)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword

错误原因 #

Oracle不支持在MERGE语句的WHEN NOT MATCHED分支中嵌套另一个MERGE语句。根据Oracle语法规范，WHEN NOT MATCHED分支仅允许包含INSERT操作，嵌套其他DML语句（包括MERGE）属于语法违规，直接触发ORA-00905缺失关键字错误。

修正方案 #

将原嵌套MERGE的逻辑拆分为两个独立的MERGE语句：

1. 第一个MERGE负责更新已存在的无依赖根节点，设置其依赖关系
2. 第二个MERGE负责检查带依赖关系的目标记录是否存在，不存在则插入新记录

修正后的SQL代码 #

步骤1：更新根节点的依赖关系 #

MERGE INTO WF_CMN_A_PROCESS_DEPENDENCY_TREE_TEST3 USING DUAL 
ON (
    "WORKFLOW_ID"             = :workflowId
    AND "PROCESS_NAME"            = :processName
    AND NVL("SECTION_ID", '-')    = NVL(:sectionId, '-')
)
WHEN MATCHED THEN UPDATE SET 
    "DEPENDS_ON_WORKFLOW_ID" = :dependsOnWorkflowId,
    "DEPENDS_ON_PROCESS_NAME"= :dependsOnProcessName,
    "DEPENDS_ON_SECTION_ID"  = :dependsOnSectionId,
    "LAST_UPDT_DT_TM"        = SYSDATE
WHERE 
    "DEPENDS_ON_WORKFLOW_ID"  IS NULL 
    AND "DEPENDS_ON_PROCESS_NAME" IS NULL 
    AND "DEPENDS_ON_SECTION_ID"   IS NULL;

步骤2：插入不存在的带依赖节点 #

MERGE INTO WF_CMN_A_PROCESS_DEPENDENCY_TREE_TEST3 USING DUAL 
ON (
    "WORKFLOW_ID"                      = :workflowId
    AND "PROCESS_NAME"                     = :processName
    AND NVL("SECTION_ID", '-')             = NVL(:sectionId, '-')
    AND NVL("DEPENDS_ON_WORKFLOW_ID" ,'-') = NVL(:dependsOnWorkflowId , '-')
    AND NVL("DEPENDS_ON_PROCESS_NAME",'-') = NVL(:dependsOnProcessName, '-')
    AND NVL("DEPENDS_ON_SECTION_ID"  ,'-') = NVL(:dependsOnSectionId  , '-')
)
WHEN NOT MATCHED THEN 
INSERT (
    "WORKFLOW_ID",
    "PROCESS_NAME",
    "SECTION_ID",
    "DEPENDS_ON_WORKFLOW_ID",
    "DEPENDS_ON_PROCESS_NAME",
    "DEPENDS_ON_SECTION_ID",
    "LAST_UPDT_DT_TM"
) 
VALUES (        
    :workflowId,
    :processName,
    :sectionId,
    :dependsOnWorkflowId,
    :dependsOnProcessName,
    :dependsOnSectionId,
    SYSDATE
);

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