Oracle中的Null(再提)
来源:互联网 发布:c语言编译器win10下载 编辑:程序博客网 时间:2024/06/04 18:25
http://www.adp-gmbh.ch/ora/misc/null.html
NULL in Oracle
A column in a table can be defined with the not null constraint.
See also NULLs and boolean operators
nvl, nvl2 and lnnvl are SQL constructs that are related to NULL handling.
The set null command in SQL*Plus defines how nulls are displayed in a resultset.
Empty string
Oracle treats the empty string ('') as null. This is not ansi compliant. Consequently, the length of an emtpy string is null, not 0.
Null means unknown value
The value null can be regarded as an unknown value. Therefore, the following select statement returns null:
select 5+7+null+9 from dual;
This is because five plus seven plus an unknown value plus nine is of course unknown as well, hence Oracle returns null. However, aggregate functions such as sum() disregard nulls and return the sum of all non-null values.
Truth table
In the following, I create a truth table for booleans and the operators and and or. I create a table to insert booleans.
create table booleans ( bool varchar2(5));
Because I cannot store booleans directly in a table, I use varchar2 as the column type and insert the english names for the booleans:
insert into booleans values ('true');insert into booleans values ('false');insert into booleans values ('null');
Then, I compare every boolean to every other and print the truth table:
declare bool_1 boolean; bool_2 boolean; bool_and boolean; bool_or boolean; res_and varchar2(5); res_or varchar2(5); function string_to_bool(str in varchar2) return boolean is begin return case when str = 'true' then true when str = 'false' then false when str = 'null' then null end; end; function bool_to_str(bool in boolean) return varchar2 is begin return case when bool = true then 'true' when bool = false then 'false' when bool is null then 'null' end; end;begin dbms_output.put_line('bool1 bool2| and or'); dbms_output.put_line('------------+-------------'); for b1 in (select bool from booleans) loop for b2 in (select bool from booleans) loop bool_1 := string_to_bool(b1.bool); bool_2 := string_to_bool(b2.bool); bool_and := bool_1 AND bool_2; bool_or := bool_1 OR bool_2; res_and := bool_to_str(bool_and); res_or := bool_to_str(bool_or ); dbms_output.put_line(lpad(b1.bool, 5) || ' ' || lpad(b2.bool, 5) || '| ' || lpad(res_and, 5) || ' ' || lpad(res_or , 5)); end loop; end loop;end;/
bool1 bool2| and or------------+------------- true true| true true true false| false true true null| null truefalse true| false truefalse false| false falsefalse null| false null null true| null true null false| false null null null| null null
As can be seen, for example, false and null is false. This makes sense because null, being an unknown value, could in this this context either be true or false. Both false and trueand false and false are false, hence false and null is certainly false as well. On the other hand, false or null is null because the result is true for false or true and false for false or false, hence the expression's value is unknown, or null.
<script type="text/javascript"><!--google_ad_client = "ca-pub-7104628658411459";/* wide1 */google_ad_slot = "8564482570";google_ad_width = 728;google_ad_height = 90;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
- Oracle中的Null(再提)
- Oracle中的null详解
- Oracle中的null
- ORACLE中的NULL
- Oracle中的NULL
- oracle中的''和null
- oracle 字段中的null
- oracle 中的null
- Oracle中的NULL
- Oracle中的NULL
- Oracle中的NULL
- Oracle中的NULL
- ORACLE中的NULL
- oracle中的null
- oracle中的null
- ORACLE中的NULL
- Oracle中的NULL
- ORACLE中的NULL
- Div+CSS布局居中
- Ubuntu10下Mysql-5.1.56安装
- Ubuntu10下Apache-2.2安装
- Ubuntu10下Eclipse3.6安装
- vmware虚拟机网络设置
- Oracle中的Null(再提)
- CPU卡操作系统COS文件访问安全的实现
- vmware 7+ubuntu桥接网络设置(非常适用于公司/学校网络)
- perl学习-第十章
- 如何创建数据库快照 (Transact-SQL)
- 如何将数据库恢复到数据库快照 (Transact-SQL)
- IE 8 中设置打开新网页时在新选项卡中显示。
- varchar和Nvarchar区别
- 重复定律