合并操作遵循常规的null处理方式

7 浏览
0 Comments

合并操作遵循常规的null处理方式

在SQL中，对null的常规处理和语言规范是，如果表达式的任何部分为null，则整个表达式为null。

然而在Oracle中，文本连接将null转换为空白字符，例如：

select concat(concat('foo', null), 'bar') from dual;  --> 返回"foobar"
select 'foo' || null || 'bar' from dual;              --> 返回"foobar"

我想要常规的行为，即如果任何一个术语为空，结果应为null。

Oracle是否提供了一种方法或函数，可以使用单个表达式连接文本，而不需要对任何术语进行重新编码，从而使得如果任何术语为空，结果就为null？

注意：

  • 我不想重复任何术语，这将需要使用case等语句，因为这些术语非常冗长且复杂，而且重复代码是不好的实践
  • 我不能定义任何函数。我必须只使用单个SQL查询，仅使用标准语法和纯Oracle提供的函数/操作符
  • 通过使用子查询或CTE来绕过不重复要求并不是回答问题，而是避免问题：我想知道Oracle是否可以像我所知道的其他数据库一样使用单个表达式连接字符串
0

3 答案

0 Comments

在Oracle中，字符串连接操作符（||）在处理NULL值时会将其视为空字符串。这与其他数据库的行为不同，因为其他数据库会将NULL保留为NULL。这种行为可能会导致程序员在使用字符串连接时得到意外的结果。为了解决这个问题，可以使用一些方法来处理NULL值。

首先，可以使用子查询来避免重复表达式。通过使用子查询，可以在子查询中对表达式进行求值，然后在主查询中进行字符串连接操作。这样可以确保表达式只被计算一次，而不会重复计算。以下是一个示例查询：

with t1 as (
select 'foo' col1, null col2, 'bar' col3 from dual union all
select null col1, null col2, null col3 from dual union all
select 'foo' col1, 'baz' col2, 'bar' col3 from dual
) 
select  col1,col2,col3,
case when col1 is not NULL and col2 is not NULL and col3 is not NULL then
  col1||col2||col3 end as concat
from t1;

其次，可以使用“Group Comparison Conditions”来更紧凑地写入条件语句。这些条件允许对多个值进行比较，并返回一个结果。然而，这些条件不能直接用于判断NULL值，需要使用length函数进行处理。以下是一个使用Group Comparison Conditions的示例查询：

select  
case when 0 < ALL(length(col1),length(col2),length(col3)) then
  col1||col2||col3 end as concat
from t1;

第三种方法是使用NVL函数处理NULL值。可以在进行字符串连接之前使用NVL函数将所有字符串转换为特殊字符串（如'#§$%'），然后再排除由NVL函数映射的字符串。以下是一个示例查询：

with t2 as 
(select  nvl(col1,'#§$%')||nvl(col2,'#§$%')||nvl(col3,'#§$%') as concat
from t1)
select  
case when concat not like '%#§$\%%' escape'\' then concat end as concat
from t2;

需要注意的是，这些方法只是一种解决方案，而不是修复Oracle处理NULL值的方法。如果希望Oracle在字符串连接操作中按照预期的方式处理NULL值，可以向Oracle提交一个SR（Service Request）以获得官方支持。

0
0 Comments

在这段内容中，提到了一个名为"Concatenation respecting conventional null handling"的问题。这个问题的出现原因是在SQL中，没有一个单一的表达式能够实现所需的功能，即没有与concat()函数或连接运算符等效的标准兼容方法。

接下来，作者提供了一个解决方法，使用了一个名为"XMLDB"的工具进行了一种绕过/黑客/杂物的操作。通过设置null表达式为"(null)"，然后使用XMLQUERY函数和if语句来判断输入的参数是否为空，如果为空则返回空字符串，否则将参数连接起来。在示例中，作者将'foo'作为"x"参数，null作为"y"参数，'bar'作为"z"参数进行了测试，最后返回了"(null)"作为结果。

此外，还有这种方法的局限性，即无法处理可变数量的参数。作者表示自己尚未想出一种传递任意数量参数的方法。

"Concatenation respecting conventional null handling"问题的出现原因是SQL中缺乏与concat()函数或连接运算符等效的标准兼容方法。解决方法是使用XMLDB工具进行绕过/黑客/杂物的操作，通过设置null表达式和使用XMLQUERY函数来判断参数是否为空，并将参数连接起来。然而，这种方法存在参数数量固定和无法处理可变数量参数的局限性。

0
0 Comments

在Oracle数据库中，使用字符串连接运算符（concat）或者双竖线运算符（||）时，存在一些问题。这是因为在Oracle数据库中，空字符串和null值没有明确的区别。

为了解决这个问题，可以创建一个用户定义的函数，并在SQL中使用它。下面是创建这个函数的代码：

CREATE OR REPLACE FUNCTION standard_concat (
    a VARCHAR2,
    b VARCHAR2
) RETURN VARCHAR2
    AS
BEGIN
    IF
        a IS NULL OR b IS NULL
    THEN
        RETURN NULL;
    ELSE
        RETURN a || b;
    END IF;
END;
/

使用这个函数可以得到以下结果：

select standard_concat(standard_concat('foo', ''), 'bar') from dual; -- 返回null
select standard_concat(standard_concat('foo', null), 'bar') from dual; -- 返回null
select standard_concat(standard_concat('foo', 'foo'), 'bar') from dual; -- 返回"foofoobar"

可以看到，空字符串被视为null值，因为这是Oracle数据库处理字符串的方式，无法区分空字符串和null值。

如果这个函数只在一个查询中使用，可以将函数定义嵌入到SQL中，如下所示：

WITH
    FUNCTION standard_concat (
        a VARCHAR2,
        b VARCHAR2
    ) RETURN VARCHAR2
        AS
    BEGIN
        IF
            a IS NULL OR b IS NULL
        THEN
            RETURN NULL;
        ELSE
            RETURN a || b;
        END IF;
    END;
SELECT
    standard_concat(standard_concat('foo',''),'bar')
FROM
    dual;

希望这能有所帮助。

当然，也可以编写一个存储过程来实现这种行为，但是我想知道Oracle是否提供了相应的方法。Oracle数据库的标准函数不能有这种行为，因为这在Oracle中是没有意义的。历史上，曾经做出了一个决定，即空字符串和null是相同的东西，我想大多数人如果将空字符串与非空字符串连接起来会得到一个未定义的（null）值，他们是不会满意的。此外，从历史的角度来看，这个答案（stackoverflow.com/a/1268219/6090993）提供了更多的见解。

总之，Oracle中的大多数函数都会将null和空字符串视为null值，但是字符串连接运算符对待它们都是空字符串（违反了标准）。Oracle文档中还提到，这在将来的版本中可能不再是真实情况，不过我预计这将会发生在他们最终重新定义varchar类型的时候... *8-)

0