为什么 Oracle 在 NVL 比较中不被视为 HOL。

Question

我有这个查询，没有填写任何数据作为变量。应为一行；我一无所获。

select 1 from dual
where  SYSDATE BETWEEN NVL(:START_DATE, SYSDATE) AND NVL(:END_DATE, SYSDATE)

我试着这样做，期待着一场争吵，但我得到了一场争吵。

select 1 from dual
where  SYSDATE BETWEEN SYSDATE  AND  SYSDATE

有些东西没有按预期工作。NVL应将"视为NULL。

我试着这样做，期待着一场争吵，但我得到了一场争吵。

select 1 from dual
where '' is null

如果"为NULL(我们现在已经证明了这一点)，则应该返回一行。但事实并非如此。

select 1 from dual
where SYSDATE BETWEEN NVL('', SYSDATE) AND NVL('', SYSDATE)

Answer 1

这里有一些有趣的答案，但我不确定是否有人解释过为什么您的查询没有返回行。(如果他们有道歉，而我错过了——那就忽略我吧！)

这是因为NVL('', SYSDATE)正在返回一个字符串，因为第一个参数"是一个字符串。因此CCD_ 2等价于CCD_ 3。由于我们还没有指定TO_CHAR的格式，Oracle将使用默认格式，该格式通常不包括时间组件。

所以这个：

where SYSDATE BETWEEN NVL(:START_DATE, SYSDATE) AND NVL(:END_DATE, SYSDATE)

正在被这样对待：

where SYSDATE BETWEEN '25-APR-2020' AND '25-APR-2020'

然后，Oracle将把这些字符串转换回日期，以执行BETWEEN，因此将假定一天中的时间为00:00:00。因此，除非您在午夜运行此操作，否则它不会返回一行。

但是，如果您将默认日期格式设置为包括这样的时间：

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

您会发现它现在起作用了，因为日期的时间分量在隐式转换中不再丢失。

也许处理这个问题的最好方法是确保你处理的是日期，而不是字符串：

select 1 from dual
where  SYSDATE BETWEEN NVL(TO_DATE(:START_DATE), SYSDATE)
AND NVL(TO_DATE(:END_DATE), SYSDATE)

Answer 2

不是你不知道的东西会杀死你。这是"你所知道的"，只是不是那么…

Oracle不将''视为NULL的原因是，''不是NULL——它是一个零长度的字符串常量。现在，就在这里，人们会跳出来告诉我我错了，我不知道自己在说什么，质疑我的父母身份，可能会发起请愿，拒绝我的社会保障，把我赶出老法尔斯之家，但我是对的。这里有一个演示：

DECLARE
vStr  VARCHAR2(10) := '';
cStr  CHAR(10) := '';
vNULL VARCHAR2(10) := NULL;
cNULL CHAR(10) := NULL;
nStr  NUMBER := '';
nNULL NUMBER := NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('LENGTH(vStr)  = ' || LENGTH(vStr));
DBMS_OUTPUT.PUT_LINE('LENGTH(cStr)  = ' || LENGTH(cStr));
DBMS_OUTPUT.PUT_LINE('LENGTH(vNULL) = ' || LENGTH(vNULL));
DBMS_OUTPUT.PUT_LINE('LENGTH(cNULL) = ' || LENGTH(cNULL));
DBMS_OUTPUT.PUT_LINE('LENGTH(nStr)  = ' || LENGTH(nStr));
DBMS_OUTPUT.PUT_LINE('LENGTH(nNULL) = ' || LENGTH(nNULL));
END;

现在而不向下看(是的，我知道-这很难。试试…：-)您希望从上面的代码中得到什么输出？如果你和大多数人一样(包括我，直到几年前我被这件事绊倒)，你会期望它是：

LENGTH(vStr)  = 
LENGTH(cStr)  = 
LENGTH(vNULL) = 
LENGTH(cNULL) = 
LENGTH(nStr)  = 
LENGTH(nNULL) = 

也就是说，您希望LENGTH函数在应用于所有这些变量时返回NULL，因为它们都应该是NULL，对吧？

但这并不是你得到的(请参阅这个db<>fiddle)。您实际得到的是：

LENGTH(vStr)  = 
LENGTH(cStr)  = 10
LENGTH(vNULL) = 
LENGTH(cNULL) = 
LENGTH(nStr)  = 
LENGTH(nNULL) = 

哇！等待10在里面干什么？！？

这很简单。考虑一下PL/SQL中CHAR数据类型的语义。如果将字符串分配给CHAR变量或字段，并且分配的字符串长度短于变量或字段的定义长度，则分配给变量或域的值将填充在变量/字段的完全定义宽度的右侧，在本例中为10个字符。因此，当''(即零长度字符串常数)被分配给变量cStr时，分配给变量的值被填充在变量定义宽度的右侧，因此cStr最终填充了10个空格。但是，当NULL被分配给同一个字符变量时，它最终会被设置为NULL，正如预期的那样，LENGTH函数会返回NULL。

VARCHAR2的语义(以及目前的VARCHAR——至少在Oracle开始支持VARCHAR的ANSI语义之前——他们将在Real Soon Now(tm)中这样做)在Oracle中是不同的。当VARCHAR2类型的变量/字段被赋值时，它不进行任何填充；相反，它只将源字符串的有效字符分配给变量或字段，如果分配给变量的字符串的结果长度为零，则根据Oracle规则"零长度字符串值与NULL相同"，将变量或字段设置为NULL。但这种情况发生在将值分配给变量时。''本身仍然是一个零长度的字符串常量。

只要记住——不是你不知道的事情会杀死你。这是"你所知道的"，只是不是那么…：-)

Answer 3

问题是一些用户界面(令人恼火的是，包括SQL*Plus和SQLDeveloper)不支持绑定变量的DATE数据类型。

这是对这些用户界面的限制。OracleSQL完全能够处理DATE绑定变量，如果您能够将数据类型为DATE的NULL传递给它，它将按预期进行处理。请参见下图。

已经发布的一些答案处理了次要问题——如果传入数据类型为CHAR或VARCHAR2的NULL，会发生什么？我认为理解确切的原因并不重要；特别重要的是要理解前端程序的这种限制，如果您被迫使用它们与数据库交互，请确保绑定变量是字符串数据类型，并且查询反映了这一点(通过使用具有正确日期格式模型的to_DATE)。

如果您使用PL/SQL和/或其他调用环境(例如，不确定ApEx，因为我没有使用它)，则可以直接传入DATE数据类型的NULL，并获得预期的行为。这里有一个示例：我将您的查询构建为具有绑定变量的动态查询，然后调用它并将数据类型为DATE的NULL传递给它。看看发生了什么：

declare
l_sql clob;
l_n   number;
begin
l_sql := 'select 1 
from   dual 
where  sysdate between nvl(:start_date, sysdate)
and nvl(:end_date  , sysdate)';
execute immediate l_sql into l_n using cast(null as date), cast(null as date);
dbms_output.put_line(l_n);
end;
/

1
PL/SQL procedure successfully completed.

Answer 4

您必须像下面这样使用将null与null匹配以获得行

select 1 from dual
where nvl('',SYSDATE) BETWEEN NVL('', SYSDATE) AND NVL('', SYSDATE)