Oracle SQL MERGE查询缺少关键字问题

Question

不确定这里缺少什么关键字。。从互联网上的各种例子和文档来看，它看起来是正确的。。非常感谢您的帮助。可能是因为Oracle不支持嵌套的MERGE语句吗？

输入为：

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

查询是

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

Answer 1

在MERGE语句中不能有MERGE。您只能选择WHEN MATCHED THEN UPDATE和WHEN NOT MATCHED THEN INSERT。我怀疑你想要这样的东西：

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 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
)

尽管我确信我错过了你所做的一些细微之处。

此处为合并报表文档