与 NULL 值相加 [英] Addition with NULL values

问题描述

在存储过程(在我的例子中是 Oracle)中，我想向现有记录添加一些值.问题是现有值和要添加的值都可以为空.当两个操作数都为空时，我只希望结果为 NULL.如果其中只有一个为空，我希望结果是另一个操作数.如果两者都不为空，我希望结果是正常"加法.

这是我目前使用的:

SELECT column INTO anz_old FROM aTable Where ;如果 anz_old 为空然后anz_new := panzahl;别的anz_new := anz_new + NVL (panzahl, 0);万一;UPATE aTabel set column = anz_new Where ;

有没有更优雅的方法(最好完全在 SQL 中，即只是在一个更新语句中缺少一个长 CASE-Statement，其逻辑与上述代码基本相同)?

解决方案

如果你想添加 a 和 b 并且其中一个可能为空，你可以使用coalesce，它返回你传递给它的第一个非空参数:

>

coalesce(a+b, a, b)

所以在这种情况下，如果两个参数都不为空，它将返回总和.如果只有 b 为空，它将跳过 a+b 并返回 a.如果 a 为空，它将跳过 a+b 和 a 并返回 b，只有当它们都为空时才会为空.

如果你希望答案是 0 而不是 null 如果 a 和 b 都为 null，你可以传递 0 作为最后一个参数:

coalesce(a+b, a, b, 0)

请考虑 @erwins 答案 - null 可能不适合使用.

In a stored procedure (Oracle in my case), I want to add some values to an existing record. Problem is that both the existing value and the value to be added can be null. I only want the result to be NULL when both operands are null. If only one of them is null, I want the result to be the other operand. If both are non-null, I want the result to be "normal" addition.

Here's what I am using so far:

SELECT column INTO anz_old FROM aTable Where <someKeyCondition>;
IF anz_old IS NULL
THEN
    anz_new := panzahl;
ELSE
    anz_new := anz_new + NVL (panzahl, 0);
END IF;
UPATE aTabel set column = anz_new Where <someKeyCondition>;

Is there a more elegant way (pereferably completely in SQL, i.e. just in an update statement short of a long CASE-Statement with basically the same logic as the above code)?

解决方案

If you want to add a and b and either may be null, you could use coalesce, which returns the first non-null parameter you pass it:

coalesce(a+b, a, b)

So in this case, if neither parameter is null, it will return the sum. If only b is null, it will skip a+b and return a. If a is null, it will skip a+b and a and return b, which will only be null if they are both null.

If you want the answer to be 0 rather than null if both a and b are null, you can pass 0 as the last parameter:

coalesce(a+b, a, b, 0)

Do consider @erwins answer - null might not be the right thing to be using.

这篇关于与 NULL 值相加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！