如何在 Oracle 中组合多个更新语句？

How can I combine multiple update statements in Oracle?

提问by dee

I've been working on improving the performance of a stored procedure and there is a big slow down due to having to run four very similar updates.

我一直致力于提高存储过程的性能，但由于必须运行四个非常相似的更新，速度大大降低。

I've found information on similar troubles in MySql which was helpful but I could not implement what I had learned, perhaps Oracle operates differently.

我在 MySql 中找到了有关类似问题的信息，这很有帮助，但我无法实现我所学到的，也许 Oracle 的运行方式不同。

Among the things I've tried are; a batch insert through merge, creating a secondary temp_table and working with no temp tables at all but there has been no improvement.

我尝试过的事情包括；通过合并进行批量插入，创建辅助 temp_table 并且根本不使用临时表，但没有任何改进。

Being new to Oracle it's entirely possible I'm just going about it the wrong way.

作为 Oracle 的新手，我完全有可能只是以错误的方式处理它。

Any advice or answers or directions to answers would be greatly apreciated as I've run out of ideas and working now with nothing but brute force and ignorance.

任何建议或答案或答案的方向都将受到极大的赞赏，因为我已经没有想法并且现在除了蛮力和无知之外什么都没有。

UPDATE TEMP_TABLE TI SET T.ItemPrice_One =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_One'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );

UPDATE TEMP_TABLE TI SET T.ItemPrice_Two =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Two'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );

UPDATE TEMP_TABLE TI SET T.ItemPrice_Three =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Three'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );

采纳答案by Dave Costa

What you're trying to do is essentially a pivot operation -- turning multiple row values into multiple column values in a single row. Below is one method using a common technique in Oracle for doing pivot queries (untested but I think it should work as-is). As of version 11g there is a built-in PIVOT operation, but I have not really looked at it yet -- it may be a more direct method of doing what you need.

您要做的本质上是一个数据透视操作——将多行值转换为单行中的多列值。下面是一种使用 Oracle 中的常用技术进行数据透视查询的方法（未经测试，但我认为它应该按原样工作）。从版本 11g 开始，有一个内置的 PIVOT 操作，但我还没有真正看过它——它可能是一种更直接的方法来做你需要的事情。

UPDATE TEMP_TABLE TI SET ( T.ItemPrice_One, T.ItemPrice_Two,T.ItemPrice_Three )  =
    (
     SELECT
       MAX( CASE WHEN V.Item_Name='Item_Name_One' THEN ItemPrice ELSE NULL END ) Item_Price_One,
       MAX( CASE WHEN V.Item_Name='Item_Name_Two' THEN ItemPrice ELSE NULL END ) Item_Price_Two,
       MAX( CASE WHEN V.Item_Name='Item_Name_Three' THEN ItemPrice ELSE NULL END ) Item_Price_Three
     FROM   Temp.View_Items V
     WHERE  V.ID                           =          T.ID
     AND    V.Date                         =          T.Date
    );

回答by Glenn

Maybe you can try exploring the use of the MERGE statement. It may allow you to avoid doing three scans of the view table. I don't have an instance to experiment with, but here is a rough guess of the idea:

也许您可以尝试探索 MERGE 语句的使用。它可以让您避免对视图表进行三次扫描。我没有可以试验的实例，但这是对这个想法的粗略猜测：

MERGE INTO TEMP_TABLE t
   USING (SELECT Item_name, ItemPrice
            FROM Temp.View_Items V
            WHERE V.Item_Name IN ('Item_Name_One', 'Item_Name_Two', 'Item_Name_Three')
              AND V.ID = T.ID
              AND V.Date = T.Date) s
   ON (...join condition here... t.item_name = v.item_name??)
   WHEN MATCHED THEN
     UPDATE SET t.ItemPrice_One = DECODE(s.ItemPrice, 'Item_Name_One', v.ItemPrice, t.ItemPrice_One)
               ,t.ItemPrice_Two = DECODE(s.ItemPrice, 'Item_Name_Two', v.ItemPrice, t.ItemPrice_Two)
               ,t.ItemPrice_Three = DECODE(s.ItemPrice, 'Item_Name_Three', v.ItemPrice, t.ItemPrice_Three);

回答by AnBisw

A merge is a best bet in this scenario.

在这种情况下，合并是最好的选择。

回答by Roger Cornejo

UPDATE TEMP_TABLE TI SET T.ItemPrice_One =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_One'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        )
,       T.ItemPrice_Two =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Two'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        )
,       T.ItemPrice_Three =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Three'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );