Oracle 中的动态更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2320310/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic Update Query in Oracle
提问by Matt McCormick
I'm trying to create a standard UPDATE query for a table. However, if certain criteria are met, some columns should be included/excluded from the UPDATE statement.
我正在尝试为表创建标准的 UPDATE 查询。但是，如果满足某些条件，则应从 UPDATE 语句中包含/排除某些列。
For example:
例如：
UPDATE TBL_PROJECT SET
REVISION_COUNT = V_REVISION_COUNT
,PRIMARY_BRANCH = IN_PRIMARY_BRANCH
,PROJECT_STATUS = IN_PROJECT_STATUS
...
WHERE PROJECT_ID = IO_PROJECT_ID
AND REVISION_COUNT = IO_REVISION_COUNT
RETURNING REVISION_COUNT INTO IO_REVISION_COUNT';
However, the table has two columns for submitted by and approved by. So if the status is set to submitted or approved, I want those columns to be updated. eg.
但是，该表有两列用于提交者和批准者。因此，如果状态设置为已提交或已批准，我希望更新这些列。例如。
IF IN_PROJECT_STATUS = 'SUB'
UPDATE TBL_PROJECT SET
SUBMITTED_DATE = SYSDATE
ELSIF IN_PROJECT_STATUS = 'APP'
UPDATE TBL_PROJECT SET
APPROVED_DATE = SYSDATE
END;
I also need to return the REVISION_COUNT and number of rows affected (rowcount) to check if the update was successful or not.
我还需要返回 REVISION_COUNT 和受影响的行数（rowcount）来检查更新是否成功。
What is the best way to write this query? I'm assuming a Dynamic query is better than having an if-elsif-else statement with the whole query nearly duplicated in each block.
编写此查询的最佳方法是什么？我假设动态查询比使用 if-elsif-else 语句更好，整个查询几乎在每个块中重复。
回答by Peter Lang
You could write a single UPDATE, and use DECODE(or CASE) to only update the dates when in_project_statusmatches:
您可以编写一个UPDATE, 并使用DECODE(或CASE) 仅在in_project_status匹配时更新日期：
...
, submitted_date = DECODE( in_project_status, 'SUB', SYSDATE, submitted_date )
, approved_date = DECODE( in_project_status, 'APP', SYSDATE, approved_date )
...
This would avoid the duplicate UPDATE.
这将避免重复UPDATE。
回答by OMG Ponies
Case example:
案例示例：
UPDATE TBL_PROJECT
SET REVISION_COUNT = v_revision_count,
PRIMARY_BRANCH = IN_PRIMARY_BRANCH,
PROJECT_STATUS = IN_PROJECT_STATUS
...
SUBMITTED_DATE = CASE WHEN IN_PROJECT_STATUS = 'APP' THEN SYSDATE ELSE SUBMITTED_DATE END,
APPROVED_DATE = CASE WHEN IN_PROJECT_STATUS = 'SUB' THEN SYSDATE ELSE APPROVED_DATE END,
WHERE PROJECT_ID = IO_PROJECT_ID
AND REVISION_COUNT = IO_REVISION_COUNT
RETURNING REVISION_COUNT INTO IO_REVISION_COUNT
