Oracle - 根据条件更新一列或另一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2898118/
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
Oracle - Updating one column or another based on a condition
提问by z-dan
I want to update a record in a table but based on a condition I will either update one column or another but I do not want to have 2 separate statements because the statements are very long and detailed.
我想更新表中的记录，但根据条件，我将更新一列或另一列，但我不想有 2 个单独的语句，因为这些语句非常长且详细。
Here is the basic idea with over simplification to get to the point.
这是基本思想，过度简化以达到目的。
PROCEDURE Animal_something(p_updater VARCHAR2)
begin
if p_updater = 'person' then
-- I want to update the modified_by
else
-- if p_updater = 'a process' I want to update modified_by_process
Update table_creatures
set animal_type = 'Dog ,
**modified_by** = 'Bob'
**or do this**
**modified_by_process =** 'creature_package'
where animal_legs = '4'
I don't want:
我不想：
if p_updater = 'person' then
Update table_creatures
set animal_type = 'Dog ,
modified_by = 'Bob'
where animal_legs = '4';
else
Update table_creatures
set animal_type = 'Dog ,
modified_by_process = 'creature_package'
where animal_legs = '4';
end;
回答by Quassnoi
UPDATE table_creatures
SET animal_type = 'Dog',
modified_by = CASE p_updater WHEN 'person' THEN 'Bob' ELSE modified_by END,
modified_by_process = CASE p_updater WHEN 'process' THEN 'creature_package' ELSE modified_by_process END
WHERE animal_legs = 4
回答by Dave Costa
You could use dynamic SQL, e.g.:
您可以使用动态 SQL，例如：
PROCEDURE Animal_something(p_updater VARCHAR2)
sql_string_pt1 VARCHAR2(2000) := 'UPDATE table_creatures SET animal_type = :1';
sql_string_pt2 VARCHAR2(2000) := NULL;
sql_string_pt3 VARCHAR2(2000) := ' WHERE animal_legs = :3';
begin
if p_updater = 'person' then
sql_string_pt2 := ', modified_by = :2';
else
sql_string_pt2 := ', modified_by_process = :2';
end if;
EXECUTE IMMEDIATE sql_string_pt1 || sql_string_pt2 || sql_string_pt3
USING 'Dog', 'Bob', '4';
end;
This has two advantages over Quassnoi's answer: use of bind variables, and not needing to update both columns on every execution, which would generate redo even though the actual value is not changed.
与 Quassnoi 的答案相比，这有两个优点：使用绑定变量，并且不需要在每次执行时更新两列，即使实际值未更改，也会生成重做。
On the downside, the statement is not validated at all at compile time.
不利的一面是，该语句在编译时根本没有经过验证。
回答by Bharat
UPDATE table_creatures
SET animal_type = 'Dog',
modified_by = DECODE(p_updater , 'person' , 'BOB' ,
'proces' , 'creature_package' ,
'GIVE DEFAULT VALUE')
WHERE animal_legs = 4;
You can try this.
你可以试试这个。
