Dynamic Update Query in Oracle
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.
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 succes开发者_JAVA技巧sful or not.
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.
You could write a single UPDATE
, and use DECODE
(or CASE
) to only update the dates when in_project_status
matches:
...
, 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
.
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
精彩评论