<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
--==============================
--author:_yeeXun
--date:12/29/2010 9:59:00 AM
--address:Jau 17-304
--==============================
SQL>createtablebinary_test
2(bin_floatbinary_float,
3bin_doublebinary_double);
Tablecreated
SQL>insertintobinary_test(
2bin_float,bin_double)
3values(39.5f,15.7d) ;
1rowinserted
注意:在一个数字的末尾加上一个f或d,分别表示此数字为 binary_float或binary_double类型。
SQL>insertintobinary_test(bin_float,bin_double)
2values(binary_float_infinity,binary_double_infinity);
1rowinserted
SQL>select*frombinary_test;
BIN_FLOATBIN_DOUBLE
--------------------
39.515.7
1E1261E126
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
向表中添加 nan值
SQL>insertintobinary_test(bin_float,bin_double)
2values(binary_float_nan,binary_double_nan);
1rowinserted
当我向表中添加这两个数据 binary_float_nan,binary_double_nan 之后,执行查询操作:
SQL>select*frombinary_test;
select*frombinary_test
ORA-01722:无效数字
这里明显出了问题,明明数据表里面有3条数据的,怎么会查询不出来呢?
是我最后插入的数据影响到了这个查询吗?如果是的话,那么我先将这个nan值插入到表中,看看有什么效果。 首先声明一下,我用的是PL/SQL Developer做的测试;
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
SQL>rollback;
Rollbackcomplete
查看表中是否有数据
SQL>selectcount(*)frombinary_test;
COUNT(*)
----------
0
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
再次插入 nan值:
SQL>insertintobinary_test(bin_float,bin_double)
2values(binary_float_nan,binary_double_nan);
1rowinserted
SQL>select*frombinary_test;
select*frombinary_test
ORA-01722:无效数字
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
在插入其他数据:
SQL>insertintobinary_test(bin_float,bin_double)
2values(36.12f,564.1545d);
1rowinserted
SQL>select*frombinary_test;
select*frombinary_test
ORA-01722:无效数字
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
来看看数据表里面有几条数据 :
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
SQL>selectcount(*)frombinary_test;
COUNT(*)
----------
2
那为啥用select * 查询会显示"无效数字 "呢?
我们将其转化为字符串,看看有什么效果:
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
SQL>selectto_char(bin_float)cl,to_char(bin_double)c2frombinary_testwhererownum<=2;
CLC2
--------------------------------- --------------- -----------------
NanNan
3.61199989E+001 5.6415449999999998E+00
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
这正是刚刚才我向表里面添加的那两条数据 。
当我用oracle自带的Oracle SQL*Plus执行上面未能成功的查询时,我看到了奇迹:
SQL> select * from binary_test;
BIN_FLOAT BIN_DOUBLE
-------------- ------------------
Nan Nan
3.612E+001 5.642E+002
<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->
oracleFans(http://hi.csdn.net/Oraclefans_ ) 这样告诉我:
binary_float_nan, binary_double_nan 在数据里存储的是 ISNaN , ISINF
不要用PL/SQLDeveloper 作此实验,在 plsql 下不支持数据和 nan 同时显示,
可以用SQL*Plus。
看来 PL/SQL Developer 得改进改进。
所以,我们最好用oracle自带的 Oracle SQL*Plus来做测试!