db2 decimal转oracle,DB2计算当中遇到了decimal如何转换的？

最新推荐文章于 2022-11-02 21:31:30 发布

明明如灼

最新推荐文章于 2022-11-02 21:31:30 发布

阅读量478

点赞数

文章标签： db2 decimal转oracle

本文讨论了在DB2数据库中遇到的浮点数运算导致的溢出问题，通过示例展示了不同运算顺序如何影响结果的精度，并探讨了DB2 DECIMAL类型的精度计算规则。内容包括对除法运算和乘法运算精度的分析，以及如何根据运算规则预测结果的精度。

摘要由CSDN通过智能技术生成

先说下起因，在论坛当中有人问[db2inst1@localhost ~]$ db2 values "0.12/100*(1-0.055)-3.95"1 ---------------------------------SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003这个是溢出的[db2inst1@localhost ~]$ db2 values "(1-0.055)*0.12/100-3.95"1 --------------------------------- -3.948866000000000000 1 record(s) selected.第二个正常输出结果

关于结果我查了下资料[db2inst1@localhost ~]$ db2 describe "values 0.12/100*(1-0.055)" Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 31,31 1 1[db2inst1@localhost ~]$ db2 describe "values (1-0.055)*0.12/100" Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 31,18 1 1[db2inst1@localhost ~]$ db2 describe "values -3.95" Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 3, 2 1 1DB2 decimal类型做算术运算的结果精度遵循下面规则

---------------------------------------------------------------------

操作数1 操作数2 结果

DECIMAL(w, x) DECIMAL(y, z) DECIMAL(p, s)

p = max(x,z)+max(w-x,y-z)

s = max(x,z)

但除法的小数位计算遵循下面规则: 31-p+s-s'

(The symbols p and s denote the precision and scale of the first operand, and the symbols p' and s' denote the precision and scale of the second operand.)

这样按照公式计算，第一种算法精度是溢出了

但是接下来的疑问是db2inst1@localhost ~]$ db2 describe values "dec(0.1,31,30)*dec(0.1,15,3)" Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 31,31 1 1按照公式，如何算出结果是DECIMAL(31,31)的[db2inst1@localhost ~]$ db2 describe values 0.12 Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 3, 2 1 1[db2inst1@localhost ~]$ db2 describe values 100 Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 496 INTEGER 4 1 1[db2inst1@localhost ~]$ db2 describe values 0.12/100 Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 31,30 1[db2inst1@localhost ~]$ db2 describe values 1 Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 496 INTEGER 4 1 1[db2inst1@localhost ~]$ db2 describe values 0.055 Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 4, 3 1 1[db2inst1@localhost ~]$ db2 describe "values 1-0.005" Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 484 DECIMAL 15, 3 1 1以上这两段的DECIMAL的精度和小数位分别是如何计算出的