2.1_3 Oracle float number类型详解 + 测试实例（测试小数发现BUG）

GroupiesM

已于 2022-09-10 01:20:30 修改

阅读量7.4k

点赞数 2

分类专栏： # 2.1 Oracle 文章标签： oracle bug 数据库

于 2020-08-18 18:12:03 首次发布

本文链接：https://blog.csdn.net/qq_43529621/article/details/108074319

版权

2.1 Oracle 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

前言

本文针对于数仓中心采集时(Teradata)，对于Oracle数据库数值类型的研究
针对上游Oralce数据库采集时,Oralce数值字段类型对应Teardata中的字段类型，精度，标度
Teradata自身支持FLOAT字段类型，但由于处理数据脚本Dsql中没有FLOAT类型，所以需要将FLOAT类型统一转为DECIMAL处理

本文涉及名词

【precision】 n. the quality of being exact 精密，精确性在本文中指精度
【scale】 n. 规模；比例；鳞；刻度；天平；数值范围在本文中指标度

【长度】CHAR类型指字符长度，数值类型测试建表值为22，具体含义不明 => ALL_TAB_COLUMNS.DATA_LENGTH
【精度】NUMBER类型精度表示数值总的位数 , FLOAT类型精度指二进制的精度 => ALL_TAB_COLUMNS.DATA_PRECISION
【标度】NUMBER类型标度表示小数点后位数，目前已知仅ORALCE的标度值可以为负值 => ALL_TAB_COLUMNS.DATA_SCALE

【小数】小数，是实数的一种特殊的表现形式。所有分数都可以表示成小数，小数中的圆点叫做小数点，它是一个小数的整数部分和小数部分的分界号。
【纯小数】其中整数部分是零的小数叫做纯小数
【带小数】整数部分不是零的小数叫做带小数。

【科学计数法】在科学计数法中，为了使公式简便，可以用带“E”的格式表示。当用该格式表示时，E前面的数字和“E+”后面要精确到十分位，（位数不够末尾补0）,例如7.8乘10的7次方，正常写法为：7.8x10^7,简写为“7.8E+07”的形式

1 定义

1.1 FLOAT 类型

Oracle float datatype 语法：FLOAT(b)
Oracle Online Help 说：
- FLOAT(b) specifies a floating-point number with binary precision b. The precision
  b can range from 1 to 126. To convert from binary to decimal precision, multiply b by 0.30103。
- 根据这段话，我们可以看到，float(2)中的2是一个binary precision，而不是我们常用的decimal precision。
- 他们之间的换算关系是：binary precision＝int(b*0.30103) (向下取整)，因此我们这里实际上的标度应该等于int(2*0.30103)=0，即标度为0。
- 建表时 FLOAT类型可以不指定参数b建表，此时建表默认为FLOAT(126) => 即 FLOAT = FLOAT(126)
  - FLOAT(126) binary precision = 126 时，标度 = int(b*0.30103) = 126 * 0.30103 = 37.92978 ≈ 37
例如：
- float(7)：此时标度为 int(7*0.30103)=2.10721 ，向下取整 ≈ 2
- 那么插入2111.111(2.111111 * 10³)到Oracle数据库表中，值会被转变为 2.11 * 10³=2110
- 那么插入234567.891(2.34567891 * 10⁵)到Oracle数据库表中，值会被转变为 2.35 * 10⁵=235000
插入值最大位数
- FLOAT类型允许长度小数点前 126 位，小数点后300+位没有具体测试

1.2 NUMBER 类型

Oracle number datatype 语法：NUMBER(precision,scale)
参数 precision
- Precision表示有效位数，有效数位：从左边第一个不为0的数算起，小数点和负号不计入有效位数；
- Precision 的取值范围是[1,38];
- 表示精度（数字中的有效位），如果没有指定precision的话，此时比较特殊，采用科学计数法，下面会详细解释
参数 scale
- scale表示精确到多少位，指精确到小数点左边还是右边多少位（由±决定）。
- scale的取值范围是[-84,127]
- scale 与 0
  - scale > 0
    精确到小数点右边s位，第s+1为采用四舍五入。
    - 如果precision = scale，表示存储的是带小数。
    - 如果precision = scale，表示存储的是纯小数。
    - 如果precision < scale，表示存储的是纯小数。且小数点向右数scale位都为0。
  - scale < 0
    精确到小数点左边s位，第s+1位采用四舍五入。
    - precision 与 scale 大小关系不影响
    - 表示存储的是整数。且小数点向左数|scale|位都为0。
  - scale = 0
    - 表示存储的是整数。
- 不指定参数 scale ，此时建表默认 scale = 0
  - 如 NUMBER(5) = NUMBER(5,0)
- 总结
  - 如果scale大于零，表示数字精度到小数点右边的位数；
  - scale默认设置为0；
  - 如果scale小于零，oracle将把该数字取舍到小数点左边的指定位数。
允许插入值长度
- Number整数部分允许的长度为(precision –scale),无论scale是正数还是负数。
- 设允许插入最大位数为 x, 则 x = p - s
- 当 scale > 0 ,例 NUMBER(2,2) ， x = p - s = 2 - 2 = 0
  - 说明整数部分有效位为0位，考虑最后一位要四舍五入,取值范围(-0.995,0.995)
  - 允许插入最大值为 0.99499… ，实际存储值保留2位小数 => 0.99
- 当 scale = 0 ,例 NUMBER(2,0) 或 NUMBER(2) , x = p - s = 2 - 0 = 2
  - 说明整数部分有效位为2位，考虑最后一位要四舍五入,取值范围为(-95,95)
  - 允许插入最大值为 99.499…, 实际存储值值保留0位小数 => 99
- 当 scale < 0 , 例 NUMBER(2,-2) ,** x = p - s = 2 - (-2) = 4**
  - 说明整数部分有效位为4位, 考虑最后一位要四舍五入，取值范围为(-9950,9950)
  - 允许插入最大值为 9949.99… , 实际存储值保留-2位小数,所以实际存储值为9900

NUMBER无参数
当 NUMBER类型没有指定参数，情况比较特殊
并不像其他NUMBER类型，小数点固定精确到某一位，而是根据数值情况变化的，具体情况会在 3.2 NUMBER 无参数中详细分析
- 总的来说，此时NUMBER采用科学计数法，能够录入值符合以下公式：a.b * 10ⁿ
  - 其中a 为整数，取值范围为 [1,9]
  - 其中b为小数，不限制
  - 其中n 为整数，取值范围为 [-40,125]
  - 有效数字
    - 整数 40位
    - 带小数 40位
      - 对于纯小数插入值不能小于 1 * 10 ^-129
        如果小于这个值依然可以插入成功，但表的查询性能会大幅度下降
        小于这个值会导致CAST函数报错，如果有下游脚本中使用了CAST函数处理这个字段，会导致报错且很难定位问题
    - 纯小数小数点前0不算，小数点后有效数值40位

有效位数40位，第41位四舍五入
在这里插入图片描述

总长126位
在这里插入图片描述

2 测试实例

2.1 测试环境

SELECT * FROM product_component_version;

PRODUCT                                |VERSION   |STATUS          |
---------------------------------------|----------|----------------|
NLSRTL                                 |11.2.0.4.0|Production      |
Oracle Database 11g Enterprise Edition |11.2.0.4.0|64bit Production|
PL/SQL                                 |11.2.0.4.0|Production      |
TNS for Linux:                         |11.2.0.4.0|Production      |

2.2 DDL测试表 SQL

CREATE TABLE "SCOTT"."20200818_TEST" 
   (	
   "COL1" FLOAT,
   "COL2" FLOAT(7),
   "COL3" number(5, 2),
   "COL4" number(5,-2),
   "COL5" number(5)
   );

2.3 查看表结构 SQL

查看表结构SQL

略

查询结果

库名 |表名    |表名中文|序号|字段名|字段中文|SQL_COMBINE |允许为空|是否主键|长文本检查|数据量|ORACLE_TYPE|长度|精度|标度|
-----|-------------|---|---|----|--------|-------------|------|-------|---------|-----|--------------|--|---|--|
SCOTT|20200818_TEST|   |  1|COL1|        |             |Y     |       |         |    0|FLOAT         |22|126|  |
SCOTT|20200818_TEST|   |  2|COL2|        |             |Y     |       |         |    0|FLOAT         |22|  7|  |
SCOTT|20200818_TEST|   |  3|COL3|        |DECIMAL(5,2) |Y     |       |         |    0|NUMBER        |22|  5| 2|
SCOTT|20200818_TEST|   |  4|COL4|        |DECIMAL(5,0) |Y     |       |         |    0|NUMBER        |22|  5|-2|
SCOTT|20200818_TEST|   |  5|COL5|        |DECIMAL(5,0) |Y     |       |         |    0|NUMBER        |22|  5| 0|

在这里插入图片描述

结论
- FLOAT(b) ：没有指定FLOAT参数b时，默认取126
- NUMBER(precision,scale) ：没有指定第二个参数scale时，默认取0

2.4 插入测试值

COL1 : 测试 FLOAT(126) 最大值，精度，标度
COL2 : 测试 FLOAT(7) 最大值，精度，标度
COL3 : 测试 NUMBER(5,2) 精度，标度
COL4 : 测试 NUMBER(5,-2) 精度，标度
COL5 : 测试 NUMBER(5) 精度，标度
在这里插入图片描述

--插入测试值
INSERT ALL 
INTO "SCOTT"."20200818_TEST"(COL1, COL2, COL3, COL4, COL5)VALUES(
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456,
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456,
222,
222,
222)
INTO "SCOTT"."20200818_TEST"(COL1, COL2, COL3, COL4, COL5)VALUES(
0.123456789012345678901234567890123456789123456789,
0.123456789012345678901234567890123456789123456789,
222.2222,
222.2222,
222.2222)
INTO "SCOTT"."20200818_TEST"(COL1, COL2, COL3, COL4, COL5)VALUES(
1.2345, 
1.2345,
0.2222,
0.2222,
0.2222)
SELECT 1 FROM DUAL;

--回顾建表SQL
CREATE TABLE "SCOTT"."20200818_TEST" 
   (	
   "COL1" FLOAT,
   "COL2" FLOAT(7),
   "COL3" number(5, 2),
   "COL4" number(5,-2),
   "COL5" number(5)
   );

COL1 FLOAT	COL2 FLOAT(7)	COL3 Number（5,2）	COL4 Number（5,-2）	COL5Number（5）
过长，略	过长，略	222	200	222
过长，略	0.123	222.22	200	222
1.2345	1.23	0.22	0	0

SELECT * FROM "SCOTT"."20200818_TEST" 
--结果
COL1                                                                                                                          |COL2                                                                                                                          |COL3  |COL4|COL5|
------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------|------|----|----|
123456789012345678901234567890123456790000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|123000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|   222| 200| 222|
                                                                                      0.12345678901234567890123456789012345679|                                                                                                                         0.123|222.22| 200| 222|
                                                                                                                        1.2345|                                                                                                                          1.23|  0.22|   0|   0|

2.5 分类讨论

2.5.1 FLOAT 类型

计算理论标度
- FLOAT(126) ：COL1
  - binary precision = 126 时，标度 = int(b*0.30103) = 126 * 0.30103 = 37.92978 ≈ 37
  - 理论值：科学计数法小数点后最多37位有效数字
- FLOAT(7) ：COL2
  - binary precision = 7时，标度 = int(b*0.30103) = 7 * 0.30103 = 2.10721 ≈ 2
  - 理论值：科学计数法小数点后最多2位有效数字

2.5.1.1 整数

长度
经测试，INSERT纯整数时最多126位数字
插入值
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
标度
- 预期计算值
  - FLOAT(126)：COL1 科学计数法时取37位小数 = 1.2345678901234567890123456789012345678 * 10³⁷ = 123456789012345678901234567890123456790000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  - FLOAT(7) ：COL2 取3位有效数字** = 1.23 * 10³⁷ = 123000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
- 查询结果
  - FLOAT(126) : COL1 123456789012345678901234567890123456790000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  - FLOAT(7) : COL2 123000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
- 结论
  - 符合计算结果，并且可以看到最后一位8四舍五入变成9

2.5.1.2 纯小数（40位）

长度
经测试，INSERT纯小数时(0.123…)最多约300+位数值，过长没有价值没有测试
插入值
0.123456789012345678901234567890123456789123456789
标度
- 预期计算值
  - FLOAT(126) ：COL1科学计数法时取37位小数 = 1.2345678901234567890123456789012345678 * 10^-1 = 0.12345678901234567890123456789012345679
  - FLOAT(7) ：COL2 取3位有效数字** = 1.23 * 10^-1 = 0.123
- 查询结果
  - FLOAT(126) : COL1 0.12345678901234567890123456789012345679
  - FLOAT(7) : COL2 0.123
- 结论
  - 符合预期计算结果，并且可以看到最后一位8四舍五入变成9

2.5.1.3 带小数（5位）

插入值
1.2345
标度
- 预期计算值
  - FLOAT(126) ：COL1 科学计数法时取37位小数 = 1.2345* 10⁰ = 1.2345
  - FLOAT(7) ：COL2 取3位有效数字 = 1.23 * 10 ⁰ = 1.23
- 查询结果
  - FLOAT(126) : COL1 1.2345
  - FLOAT(7) : COL2 1.23
- 结论
  - 符合预期计算结果

2.5.2 NUMBER 类型

NUMBER(5, 2) ：COL3
- 理论值：最多5位有效数字，精确到小数点后2位
NUMBER(5, -2) ：COL4
- 理论值：最多5位有效数字，精确到小数点前2位
NUMBER(5) ：COL5
- 理论值：最多5位有效数字，精确到个位数

2.5.2.1 整数（3位）

插入值
- 222
- 预期计算值
  - NUMBER(5, 2) ：COL3 5位有效数字，精确到个位 222 => 222
  - NUMBER(5, -2) ：COL4 5位有效数字，精确到百位 222 => 200
  - NUMBER(5) ：COL5 5位有效数字，精确到小数点后2位 222 => 222
- 查询结果
  - NUMBER(5, 2) ：COL3 222
  - NUMBER(5, -2) ：COL4 200
  - NUMBER(5) ：COL5 222
- 结论
  - 符合预期计算结果

2.5.2.2 带小数（6位）

插入值
- 222.2222
- 预期计算值
  - NUMBER(5, 2) ：COL3 5位有效数字，精确到小数点后2位 222.2222=> 222.22
  - NUMBER(5, -2) ：COL4 5位有效数字，精确到百位 222.2222=> 200
  - NUMBER(5) ：COL5 5位有效数字，精确到个位 222.2222=> 222
- 查询结果
  - NUMBER(5, 2) ：COL3 222.22
  - NUMBER(5, -2) ：COL4 200
  - NUMBER(5) ：COL5 222
- 结论
  - 符合预期计算结果

2.5.2.3 纯小数（4位）

插入值
- 0.2222
- 预期计算值
  - NUMBER(5, 2) ：COL3 5位有效数字，精确到小数点后2位 0.2222=> 0.22
  - NUMBER(5, -2) ：COL4 5位有效数字，精确到百位 0.2222=> 0
  - NUMBER(5) ：COL5 5位有效数字，精确到个位 0.2222=> 0
- 查询结果
  - NUMBER(5, 2) ：COL3 222.22
  - NUMBER(5, -2) ：COL4 200
  - NUMBER(5) ：COL5 222
- 结论
  - 符合预期计算结果

3 特殊类型

3.1 NUMBER类型 p < |s|

3.1.1 NUMER(1,2)

NUMBER(1,-2) 精确到小数点后2位，一位有效数字(四舍五入后)
- 实际插入值的绝对值要小于0.1，由于后面一位四舍五入，则实际插入值绝对值小于0.095
- 四舍五入后插入值范围：开区间 (-0.1,0.1)
- 四舍五入前插入值范围：开区间 (-0.095,0.095)
建表 sql

CREATE TABLE "SCOTT"."20200819_TEST" (
	"COL1" NUMBER (1,2)
)

测试插入

--插入SQL
	--符合范围 (-0.1,0.1) 插入成功
INSERT INTO "SCOTT"."20200819_TEST"(COL1)VALUES(-0.094999); 	--精确到小数点后2位，四舍五入结果 -0.09
INSERT INTO "SCOTT"."20200819_TEST"(COL1)VALUES(0.0049999);		--精确到小数点后2位，四舍五入结果 0
	--超出范围 (-0.1,0.1) 插入失败
INSERT INTO "SCOTT"."20200819_TEST"(COL1)VALUES(0.095);			--精确到小数点后2位，四舍五入结果 0.10 
INSERT INTO "SCOTT"."20200819_TEST"(COL1)VALUES(-0.01);			--精确到小数点后2位，四舍五入结果 -0.1 
	--插入失败返回结果
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1438] [22003]: ORA-01438: 值大于为此列指定的允许精度

--查询SQL
SELECT * FROM "SCOTT"."20200819_TEST"

--返回结果
COL1|
----|
-0.9|
   0|

3.1.2 NUMBER(1,-2)

NUMBER(1,-2) 精确到百位，一位有效数字(四舍五入后)
- 实际插入值的绝对值要小于1000，由于后面一位四舍五入，则实际插入值绝对值小于950
- 四舍五入后插入值范围：开区间 (-1000,1000)
- 四舍五入前插入值范围：开区间 (-950,950)
建表 sql

CREATE TABLE "SCOTT"."20200820_TEST" (
	"COL1" NUMBER (1,-2)
)

测试插入

--插入SQL
	--符合范围 (-1000,1000) 插入成功
INSERT INTO "SCOTT"."20200820_TEST"(COL1)VALUES(-949.9999); 	--精确到百位，四舍五入结果 -900
INSERT INTO "SCOTT"."20200820_TEST"(COL1)VALUES(0.1234568);		--精确到百位，四舍五入结果 0

	--超出范围 (-1000,1000)  插入失败
INSERT INTO "SCOTT"."20200820_TEST"(COL1)VALUES(950);			--精确到百位，四舍五入结果 1000 				-
INSERT INTO "SCOTT"."20200820_TEST"(COL1)VALUES(-1000);			--精确到百位，四舍五入结果 -1000 
	--插入失败返回结果
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1438] [22003]: ORA-01438: 值大于为此列指定的允许精度

--查询SQL
SELECT * FROM "SCOTT"."20200820_TEST"

--返回结果
COL1|
----|
-900|
   0|

结论
建表时，当NUMBER(p,s) 中 |s| > p 时可以建表成功，但插入值时需要注意不要超出精度允许范围。

3.2 NUMBER 无参数

NUMBER无参数
当 NUMBER类型没有指定参数，情况比较特殊
- 此时NUMBER采用科学计数法，能够录入值符合以下公式：a.b * 10ⁿ
  - 其中a 取值范围为 [1,9] 取整数
  - 其中b 取值范围为 总长度不超过125位 (-99999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000,99999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000)
  - 其中n 为整数，取值范围为 [-1,125]
  - 有效数字
    - 整数 40位
    - 带小数
      - 整数37位
      - 小数3位
    - 纯小数 3位
建表sql

CREATE TABLE "SCOTT"."20200821_TEST" (
	"COL1" NUMBER
)

查看表结构SQL

略

查询结果
精度标度值都为NULL，可以查询 ALL_TAB_COLUMNS 的精度DATA_PRECISION 和标度DATA_SCALE 字段查看

在这里插入图片描述
下面分类测试允许插入最大值和插入后保留有效位数

3.2.1 整数

测试插入

--成功插入，共10位数字,插入后保留有效位数10位
--查询结果：1234567891
--科学计数法：1.234567891* 10^10  （小数点后39位有效数字）
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(1234567890123456789012345678901234567891);

--成功插入，共40位数字,插入后保留有效位数40位
--查询结果：1234567890123456789012345678901234567891
--科学计数法：1.234567890123456789012345678901234567891* 10^39  （小数点后39位有效数字）
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(1234567890123456789012345678901234567891);

--成功插入，共126位数字,插入后保留有效位数40位
--查询结果：123456789012345678901234567890123456789100000000000000000000000000000000000000000000000000000000000000000000000000000000000000
--科学计数法：1.234567890123456789012345678901234567891 * 10^125 （小数点后39位有效数字）
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(123456789012345678901234567890123456789112345678901234567890123456789012345678901234567890123456789012345678901234567890123456);	

--成功插入，共126位数字，插入后有效位数40位
--查询结果：999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000
-- 科学计数法：9.999999999999999999999999999999999999999 * 10^125
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(999999999999999999999999999999999999999949999999999999999999999999999999999999999999999999999999999999999999999999999999999999);	

--失败，共126位数字，第41位数字为5
--由于只保留40位有效数字，第41位四舍五入到40位后进一,
--导致9.999... * 10^125 变为 1 * 10^126 超出126位数值长度限制
--科学计数法：1 * 10^126
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);	
--返回结果
SQL 错误 [1426] [22003]: ORA-01426: 数字溢出

查看数据

SELECT COL1 FROM  "SCOTT"."20200821_TEST"

COL1                                                                                                                           |
-------------------------------------------------------------------------------------------------------------------------------|
                                                                                       1234567890123456789012345678901234567891|
                                                                                       1234567890123456789012345678901234567891|
 123456789012345678901234567890123456789100000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
 999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
-999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|

清空表

--清空表
TRUNCATE TABLE "SCOTT"."20200821_TEST"

结论整数最多插入126位数字，保留有效数字为从前向后数40位，且第41位四舍五入后不会导致长度超过126位数字，即
- a.b * 10ⁿ
  - 其中a 为整数，取值范围为 [1,9]
  - 其中b为小数，不限制
  - 其中n 为整数，取值范围为 [-1,125]

3.2.3 带小数

~~这个不想测了太麻烦了,GG~~

3.2.3 纯小数

对于小数不需要测试最大值，因为小数最大值不会超过1，需要测试有效位数，和最小值

同时在测试时发现了一个ORACLE的BUG，这个BUG还比较严重，可能导致整个表的数据都不能使用

首先需要明确查小数方法，这里我使用的是DBEAVER，查看数据时【网格】选项会自动截取小数点3位以后的数值，使用【文本】查看才是原始值

在这里插入图片描述

想使用网格也能正确查看数值可以通过sql将数值转为文本类型

SELECT 
CASE 
	WHEN COL1 < 1 
		THEN '0'||CAST(COL1 AS VARCHAR2(250))  
	ELSE CAST(COL1 AS VARCHAR2(250))
END AS COL1
FROM "SCOTT"."20200821_TEST"

在这里插入图片描述

测试插入

--成功插入共40位有效数字位数字,插入后保留有效位数10位
--查询结果：0.1234567890123456789012345678901234567891
--科学计数法：1.234567890123456789012345678901234567891 * 10^-1  （小数点后39位有效数字）
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(0.1234567890123456789012345678901234567891123456789);


--成功插入，共40位有效数字,小数点后128个0
--查询结果：0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001234567890123456789012345678901234567891
--科学计数法：1234567890123456789012345678901234567891 * 10^-129
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001234567890123456789012345678901234567891123456789);

--成功插入，共1位有效数字，小数点后128个0
--查询结果：0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
--科学计数法：1* 10^-129
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001);

这里的1* 10^-129 是我测试出来的NUMBER类型能插入的最小值，当小于这个值依然插入成功，但会引发ORACLE(11G)很严重的BUG

--成功插入，共1位有效数字，小数点后129个0
--查询结果： ？
--科学计数法：9* 10^-130
INSERT INTO "SCOTT"."20200821_TEST"(COL1)VALUES(0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009);

下面直接上截图

插入数值成功

在这里插入图片描述

查询SQL报错，猜测可能是CAST转不了这么小的数，那就直接查询

在这里插入图片描述

这时这个表已经基本不能查询了，原来几毫秒查出来的四条记录现在需要将近70秒，而这张表仅仅只有4条数据

在这里插入图片描述

网格

在这里插入图片描述

文本
这里可以看到 9* 10^-130的有效数字9已经丢失精度

在这里插入图片描述

小结
- 对于纯小数插入值不能小于 1 * 10 ^-129
  - 如果小于这个值依然可以插入成功，但表的查询性能会大幅度下降
  - 小于这个值会导致CAST函数报错，如果有下游脚本中使用了CAST函数处理这个字段，会导致报错且很难定位问题
- 有效数字为40位，第41位依然采取四舍五入进一位的方式

采集到TERADATA贴源(S)层建议

建议在TD中使用VARCHAR(200)接收ORACLE的NUMBER和FLOAT类型

TD中的数值类型

类型	字节长度	说明
DATE	4	YYYYMMDD,特殊的INTEGER类型
DECIMAL(n,m)	1,2,4,8	n:1~18 m:0~n,可缩写成DEC 如256.78可写表示为DEC(5,2) ，可省略参数，DICIMAL= DICIMAL(5,0)
NUMERIC(n,m)	1,2,4,8	n:1~18 m:0~n
BYTEINT	1	128~+127
SMALLINT	2	32768~+32767
INTEGER	4	可缩写成INT
REAL	8	2 * 10 ^-307 ~ 2 * 10 ³⁰⁸
FLOAT	8	同上
DOUBLE PRECISION	8	同上

TERADATA中DICIMAL(m,n)类型
- 最长支持38位数字
- 精度m ∈[1,38]
- 标度n ∈[0,m]
TERADATA中FLOAT类型
- 最长支持38位数字(39位数字没有超过理论值2 * 10³⁰⁸ ，但是不能插入，不清楚具体原因)
- 精度测试结果大概如下图所示
- 肯定不能支持上游ORACLE的FLOAT类型

根据上面对于FLOAT和NUMBER类型测试结果，使用TD的DICIMAL和FLOAT类型接收都可能导致数值溢出报错，或数值丢失精度
ORACLE,FLOAT类型长度最长约126位数字，NUMBER类型测试插入最长179位，只有使用VARCHAR才能保证采集数据不会出现数值溢出或丢失精度问题，
S层（贴源层）作为数仓最底层，需要保证数据与源库一致，使用DECIMAL(38,8)有溢出风险，建议使用VARCHAR(200)