[20150503]关于oracle的number类型.txt

[20150503]关于oracle的number类型.txt

--节前的事情,别人建表使用number类型,本来想定义成number(10,2),结果少输入0,变成number(1,2).
--在我的记忆里,好像前面的数值应该大于后面的精度的,没想到这样竟然可以通过,自己感到很奇怪!
--测试下来,才知道自己oracle基本的东西都不是很清楚.

1.首先提到我以前写的一篇blog:
[20140823]在sqlplus使用copy注意.txt
http://blog.itpub.net/267265/viewspace-1257036/

在这篇blog中我提到定义number类型最好包含精度和小数点位数.

2.接着看看上面的定义会如何?

--看看oracle 文档:

NUMBER Data Type
The NUMBER data type stores zero as well as positive and negative fixed numbers with
absolute values from 1.0 x 10^-130 to but not including 1.0 x 10^126
. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10^126
, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.
Specify a fixed-point number using the following form:
NUMBER(p,s)

■ p is the precision, or the maximum number of significant decimal digits, where the
most significant digit is the left-most nonzero digit, and the least significant digit is
the right-most known digit. Oracle guarantees the portability of numbers with
precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits
depending on the position of the decimal point.

■ s is the scale, or the number of digits from the decimal point to the least significant
digit. The scale can range from -84 to 127.

– Positive scale is the number of significant digits to the right of the decimal
point to and including the least significant digit.

– Negative scale is the number of significant digits to the left of the decimal
point, to but not including the least significant digit. For negative scale the
least significant digit is on the left side of the decimal point, because the actual
data is rounded to the specified number of places to the left of the decimal
point. For example, a specification of (10,-2) means to round to hundreds.

Scale can be greater than precision, most commonly when e notation is used. When
scale is greater than precision, the precision specifies the maximum number of
significant digits to the right of the decimal point. For example, a column defined as
NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all
values past the fifth digit after the decimal point.

--文档明确提到Scale can be greater than precision.

It is good practice to specify the scale and precision of a fixed-point number column
for extra integrity checking on input. Specifying scale and precision does not force all
values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If
a value exceeds the scale, then Oracle rounds it.
Specify an integer using the following form:
NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to
NUMBER(p,0).
Specify a floating-point number using the following form:
NUMBER
The absence of precision and scale designators specifies the maximum range and
precision for an Oracle number.

--测试:
SCOTT@test01p> create table t1 ( a number(1,2));
Table created.

SCOTT@test01p> insert into t1 values(0.10);
insert into t1 values(0.10)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SCOTT@test01p> insert into t1 values(0.01);
1 row created.

SCOTT@test01p> insert into t1 values(0.091);
1 row created.

SCOTT@test01p> select * from t1;
         A
----------
       .01
       .09

--很明显这样定义仅仅能插入0.01-0.09(当然没有包括负数),再过来看就很容易里面前面的定义.精度1表示仅仅最后1位有数值(把值*100).
--假如我定义number(2,2)表示的范围就是0.01-0.99 (不包括负数).

SCOTT@test01p> create table t2 ( a number(2,2));
Table created.

SCOTT@test01p> insert into t2 values(0.99);
1 row created.

SCOTT@test01p> insert into t2 values(0.01);
1 row created.

SCOTT@test01p> insert into t2 values(1.00);
insert into t2 values(1.00)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SCOTT@test01p> insert into t2 values(-0.99);
1 row created.

SCOTT@test01p> select * from t2;
         A
----------
       .99
       .01
      -.99

时间: 2024-10-23 20:05:41

[20150503]关于oracle的number类型.txt的相关文章

[20160828]number类型.txt

[20160828]number类型.txt --昨天看了一个链接http://www.cnblogs.com/kerrycode/p/4427352.html,感觉有点不对,上班测试看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------

[20161123]oracle数据块类型.txt

[20161123]oracle数据块类型.txt --oracle 数据块有许多类型,自己平时很少关注与记忆,自己做一个归纳总结: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------------

ORACLE 中NUMBER类型默认的精度和Scale问题

在ORACLE数据库中,NUMBER(P,S)是最常见的数字类型,可以存放数据范围为10^-130~10^126(不包含此值),需要1~22字节(BYTE)不等的存储空间.P 是Precison的英文缩写,即精度缩写,表示有效数字的位数,最多不能超过38个有效数字.S是Scale的英文缩写,表示从小数点到最低有效数字的位数,它为负数时,表示从最大有效数字到小数点的位数.有时候,我们在创建表的时候,NUMBER往往没有指定P,S的值,那么默认情况下,NUMBER的P.S的值分别是多少呢?相信这个问

ORACLE NUMBER类型详解

1>.NUMBER类型细讲:Oracle number datatype 语法:NUMBER[(precision [, scale])]简称:precision --> p      scale     --> s NUMBER(p, s)范围: 1 <= p <=38, -84 <= s <= 127保存数据范围:-1.0e-130 <= number value < 1.0e+126   保存在机器内部的范围: 1 ~ 22 bytes 有效为:

ORACLE NUMBER类型Scale为0引发的问题

今天遇到了一个很有意思的NUMBER类型Scale引发的问题,我用一个简单的测试用例来展示一下这个案例.假如有个TEST的表,有个字段类型为NUMBER,我插入下面两条数据 CREATE TABLE TEST (      Category VARCHAR(12),      QTY  NUMBER )   INSERT INTO TEST SELECT 'M', 12 FROM DUAL UNION ALL SELECT 'C', 0.99999999999999999 FROM DUAL;

Oracle中的number类型

number类型用于定义固定长度的数字,可以使整数,也可以是实数.number(p,s)是完整的定义形式. p必须是整数,取值范围是1-38,用于指定数字的总的位数.s必须是整数,取值范围是-84~127,用于指定小数点之后的位数. NUMBER 赋值:1234.56 实际:1234.56 NUMBER(3) 赋值:-123 实际:-123 NUMBER(3) 赋值:1234 实际:错误(ORA-06502:PL/SQL:数字或值错误:数字精度太高) NUMBER(4,3) 赋值:1.23456

ORACLE NUMBER类型内部实现

先来研究NUMBER类型的数字回推算法 类型 <[长度]>,符号/指数位 [数字1,数字2,数字3,......,数字20] 1.长度类型没什么号说的 2.符号位,这个需要说一下 The sign bit, which is the high order bit (128) 按照文档的说法这个判断方法是和128进行最高位按位与出来的,如果 这位小于128则是负数,我们使用127试试吧 128的二进制为1000 0000  127的二进制为0111 1111 最高位1&0=0 则代表他是

[20171203]关于raw类型.txt

[20171203]关于raw类型.txt --//从来没有关注raw类型,昨天看https://blog.dbi-services.com/doag-2017-avg_row_len-with-virtual-columns/ --//我第一次接触一个应用使用sys_guid()函数生成键值,不过对方使用-分割,36位字符串长度.一个表有6个这样的字段.昏.. --//还真的没有反过来想利用raw类型可以减少磁盘空间占用. 1.环境: SCOTT@test01p> @ ver1 PORT_ST

[20120712]10g下Oracle Index rebuild online.txt

[20120712]10g下Oracle Index rebuild online.txt oracle rebuild online时要建立一张IOT表,为了保证事务依旧能操作,需要记录索引rebuild期间的DML操作.但是10g与11g下rebuild的机制有一些不同. index online rebuild 前先建立一张IOT表跟踪后续DML操作,然后merge全部的改变到索引中. 下面通过例子来说明: BANNER ----------------------------------