Oracle 中的动态更新查询

发布于 2024-08-23 01:29:33 字数 774 浏览 5 评论 0原文

我正在尝试为表创建标准 UPDATE 查询。但是，如果满足某些条件，则应在 UPDATE 语句中包含/排除某些列。

例如：

 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';

但是，该表有两列，分别是“提交者”和“批准者”。因此，如果状态设置为已提交或已批准，我希望更新这些列。例如。

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;

我还需要返回 REVISION_COUNT 和受影响的行数 (rowcount) 以检查更新是否成功。

编写此查询的最佳方式是什么？我假设动态查询比使用 if-elsif-else 语句更好，并且整个查询在每个块中几乎重复。

原文

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 successful 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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

善良天后 2024-08-30 01:29:33

您可以编写一个 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 )
...

这将避免重复的UPDATE。

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.

回复收藏 0 原文

只想待在家 2024-08-30 01:29:33

案例示例：

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

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

回复收藏 0 原文

~没有更多了~