ORACLE NUMBER数据类型.docVIP

ORACLE NUMBER数据类型 ORACLE NUMBER数据类型！ 网上关于number的资料很多了，学习总结了下，如果问题及不足，欢迎指正。 一、oracle的number类型精度、刻度范围 number(p,s) p:1---38 s:-84---127 有效数位：从左边第一个不为0的数算起，小数点和负号不计入有效位数。 p0，对s分2种情况： 1. s0 精确到小数点右边s位，并四舍五入。然后检验有效数位是否=p； ZWF.YUDONGcreate table t_n(id number(5,2)); Table created. ZWF.YUDONGinsert into t_n values(123.45); 1 row created. ZWF.YUDONGinsert into t_n values(123.455); 1 row created. ZWF.YUDONGselect * from t_n; ID ---------- 123.45 123.46 2 rows selected. ZWF.YUDONGinsert into t_n values(1.234); 1 row created. ZWF.YUDONGselect * from t_n; ID ---------- 123.45 123.46 1.23 3 rows selected. ZWF.YUDONGinsert into t_n values(.001); 1 row created. ZWF.YUDONGselect * from t_n; ID ---------- 123.45 123.46 1.23 0 4 rows selected. ZWF.YUDONGinsert into t_n values(1234.56); insert into t_n values(1234.56) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column 如果sp，小数点右边至少有s-p个0填充。 ZWF.YUDONGcreate table t_n(id number(4,5)); Table created. ZWF.YUDONGinsert into t_n values(1); insert into t_n values(1) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column ZWF.YUDONGinsert into t_n values(.1); insert into t_n values(.1) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column ZWF.YUDONGinsert into t_n values(.01); 1 row created. ZWF.YUDONGcommit; Commit complete. ZWF.YUDONGselect * from t_n; ID ---------- .01 1 row selected. ZWF.YUDONGinsert into t_n values(.001); 1 row created. ZWF.YUDONGinsert into t_n values(.0001); 1 row created. ZWF.YUDONGinsert into t_n values(.00001); 1 row created. ZWF.YUDONGinsert into t_n values(.000001); --超过刻度存储0 1 row created. ZWF.YUDONGselect * from t_n; ID ---------- .01 .001 .0001 .00001 0 10 rows selected. ZWF.YUDONGcol dp for a50 ZWF.YUDONGselect id,dump(id) dp,length(id),vsize(id) from t_n; --vsize和du