此实现是否符合SQL-92? [英] Is this implementation SQL-92 conformant?
问题描述
Tony Andrews in another question gave an example of:
IF p_c_courtesies_cd
|| p_c_language_cd
|| v_c_name
|| v_c_firstname
|| v_c_function
|| p_c_phone
|| p_c_mobile p_c_fax
|| v_c_email is not null
THEN
-- Do something
END IF;
作为Oracle COALESCE函数的聪明替代(如果不是有点晦涩).果然，它可以工作，如果任何参数不为null，则IF测试为true.我的问题:上述连接操作SQL-92的Oracle实现是否符合要求?包含NULL的表达式不应该计算为NULL吗?如果您不这样认为，那么为什么表达式1 + NULL求值为NULL?
as a clever (if not a tad obscure) alternative to the Oracle COALESCE function. Sure enough, it works, if any argument is not null, the IF test is true. My question: Is Oracle's implementation of the above concatenation operation SQL-92 conforming? Shouldn't an expression involving a NULL evaluate to NULL? If you don't think so, then why should the expression 1 + NULL evaluate to NULL?
推荐答案
否，Oracle对null的处理是特质的，不同于其他所有人，并且与ANSI标准不一致.但是，在Oracle的辩护中，它很可能早就决定并致力于这种处理，直到有与之相符的ANSI标准为止.
No, Oracle's treatment of nulls is idiosyncratic, different from everyone else's, and inconsistent with the ANSI standards. In Oracle's defence however, it probably settled on and was committed to this treatment long before there was an ANSI standard to be consistent with!
这一切都始于Oracle存储带有字符计数和字符串数据的字符串. NULL由零的字符计数表示，没有后续的字符串数据-与空字符串('')完全相同. Oracle根本没有办法区分它们.
It all starts from the fact that Oracle stores strings with a character count followed by the string data. A NULL is represented by a character count of zero with no following string data - which is exactly the same as an empty string (''). Oracle simply doesn't have a way to distinguish them.
这会导致一些古怪的行为，例如这种串联情况. Oracle还具有一个LENGTH函数来返回字符串的长度，但这是以相反的方式定义的，因此LENGTH('')返回NULL而不是零.所以:
This leads to some quirky behaviour, such as this concatenation case. Oracle also has a function LENGTH to return the length of a string, but this has been defined in an opposite manner, so that LENGTH('') returns NULL not zero. So:
LENGTH('abc') + LENGTH('') IS NULL
LENGTH('abc' || '') = 3
在我看来这违反了基本的数学原理.
which seems to me to violate basic mathematical principles.
当然，Oracle开发人员已经习惯了这一点，以至于我们许多人甚至看不到任何错误或奇怪之处-实际上，有些人会争辩说世界其他地方是错误的，并且空字符串和NULL 是同一件事！
Of course, Oracle developers become so used to this that many of us can't even see anything wrong or odd about it - some will in fact argue that the rest of the world is wrong and that an empty string and a NULL are the same thing!
这篇关于此实现是否符合SQL-92?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
