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.
nvl
, nvl2
and lnnvl
are SQL constructs that are related to NULL handling.
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 valuenull
can be regarded as an unknown value. Therefore, the following select statement returns null:
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 operatorsand
andor
. 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 true
false true| false true
false false| false false
false null| false null
null true| null true
null false| false null
null null| null null
As can be seen, for example,falseand
null
is false. This makes sense because null, being an unknown value, could in this this context either be true or false. Bothfalse and true
andfalse and false
are false, hencefalse and null
is certainly false as well. On the other hand,falseor
null
is null because the result is true forfalse or true
and false forfalse or false
, hence the expression's value is unknown, or null.
|
分享到:
相关推荐
你是否也为在Oracle里如何实现NULL的比较而犯愁呢?
从oracle知识中的一个点考察员工对知识的掌握扎实程度,能及格的就不错了。 都是很常用的功能,但人们不一定掌握。
NULL 博文链接:https://wxz2832840.iteye.com/blog/973330
关于Oracle中NULL使用的若干实验研究.pdf
介绍oracle开发中关于null的特征、易出问题地方及其解决办法,对于从事oracle开发的成员很有帮助,也可用于培训。
oraclesql判断值为空-Oracle-sqlserver的空值(null)判断.pdf
主要介绍了mybatis使用char类型字段查询oracle数据库时结果返回null问题的解决方法,本文给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
null and not null
在我们不知道具体有什么数据的时候,也即未知,可以用NULL,我们称它为空,ORACLE中,含有空值的表列长度为零
oracle提权工具
Oracle官方SQL参考手册、oracle函数大全、Ora9iSQL参考手册、oracle函数大全(分类显示)、Oracle函数大全、Oracle函数手册、ORACLE九阴真经、oracle知识库、SQLCodes-Oracle错误代码与消息解释、SQL语言参考大全,10...
Toad for Oracle 中文使用指南(权威版),清晰版 ,涵软件安装
Oracle 数据库 11g 中的分区.pdf Oracle 数据库 11g:可管理性概述.pdf Oracle 数据库 11g:新特性概述.pdf Oracle 真正应用集群 11g .pdf Oracle高级压缩.pdf Oracle性能优化包 11g .pdf Oracle真正应用测试...
ORACLE中CLOB字段转String类型
很多朋友在操作oracle数据库的clob,blob字段时不知道怎么插入和使用,这里提供读写范例。
ORACLE直连数据库注入提权详解 linux和windows系统直连oracle数据库提权和修复详解
Oracle Exception汇总(自定义Oracle异常) 使用方法举例: Exception When no_data_found then Dbms_output.put_line(‘no_data_found’); ACCESS_INTO_NULL 为对象赋值前必需初始化对象。对应ORA-06530错误。 CASE...
在SQL Server Oracle MySQL当数据库中查出某值为NULL怎么办? 1、MSSQL: ISNULL() 语法 Java代码 代码如下: ISNULL ( check_expression , replacement_value ) ISNULL ( check_expression , replacement_value ) ...
从oracle中的BLOB类型字段中取出照片,转存到mysql数据库中。思路是现将oracle中的照片存储在本地文件夹,在将本地图片上传到mysql中。
Ora9iSQL参考手册 chm oracle函数大全 分类显示 chm Oracle函数大全 chm Oracle函数手册 chm ORACLE九阴真经 chm oracle知识库 CHM SQLCodes Oracle错误代码与消息解释 chm SQL语言参考大全 chm