number数据类型的疑问

Question

create table a ( b number(5,2));
create table a ( b number);所占用的空间会一样吗？

Answer 1

--应该是不一样的  不过number应该是变长的 跟你插入的值的大小有关
scott@YPCOST> create table a(b number(5,2));Table created.scott@YPCOST> insert into a values(999.99);1 row created.scott@YPCOST> insert into a values(1000.01);
insert into a values(1000.01)
                     *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
scott@YPCOST> rollback;Rollback complete.scott@YPCOST> create table aa(b number);Table created.scott@YPCOST> insert into aa values(1000.01);1 row created.

Answer 2

我知道它是有定长的，如果类似number(20)这种 如果我没有具体的长度限制，是不是直接就定义为number类型了？

Answer 3

scott@YPCOST> desc a;
 Name                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------- -------- 
 B                                                                                                  NUMBER(5,2)scott@YPCOST> desc aa;
 Name                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------- -------- 
 B                                                                                                  NUMBER

Answer 4

如char与varchar的区别，因为varchar是变长的，所以我们在数据库定义中必须用varchar以减少磁盘空间占用,是不是在number使用过程中也该遵循此原则呢？

Answer 5

-- *(06) NUMBER：这种数据类型能存储精度最多达38位的数字。这些数介于1.0x10(-130)~(但不包括)1.0x10(126)之间。每个数字存储在一个变长字段中，
--     其长度在0(尾部的NULL列就是0字节)~22字节之间。Oracle的NUMBER类型精度很高，远远高于许多编程语言中常规的FLOAT和DOUBLE类型。

Answer 6

-- 12.4 数值类型 ( P506 )create table t
( num_col number,
  float_col binary_float,
  dbl_col binary_double
);insert into t ( num_col, float_col, dbl_col )
values ( 1234567890.0987654321,
         1234567890.0987654321,
         1234567890.0987654321 );set numformat 99999999999.99999999999scott@SZTYORA> select * from t;                 NUM_COL                FLOAT_COL                  DBL_COL
------------------------ ------------------------ ------------------------
  1234567890.09876543210   1234567940.00000000000   1234567890.09876540000delete from t;insert into t ( num_col, float_col, dbl_col )
values ( 9999999999.9999999999,
         9999999999.9999999999,
         9999999999.9999999999 );eygle@SZTYORA> select * from t;                 NUM_COL                FLOAT_COL                  DBL_COL
------------------------ ------------------------ ------------------------
  9999999999.99999999990  10000000000.00000000000  10000000000.00000000000delete from t;insert into t ( num_col )
values ( 123*1e20 + 123*1e-20 );set numformat 999999999999999999999999.999999999999999999999999
eygle@SZTYORA> select num_col, 123*1e20, 123*1e-20 from t;                                           NUM_COL                                           123*1E20                                  123*1E-20
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
  12300000000000000000000.000000000000000000000000   12300000000000000000000.000000000000000000000000                          .000000000000000001230000select num_col from t where num_col = 123*1e20;create table t ( num_col number(5,0) );insert into t ( num_col ) values ( 12345 );eygle@SZTYORA> insert into t ( num_col ) values ( 123456 );
insert into t ( num_col ) values ( 123456 )
                                   *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度create table t ( msg varchar2(10), num_col number(5,2));insert into t (msg,num_col) values ( '123.45', 123.45 );
insert into t (msg,num_col) values ( '123.456', 123.456 );eygle@SZTYORA> select * from t;MSG                     NUM_COL
-------------------- ----------
123.45                   123.45
123.456                  123.46eygle@SZTYORA> insert into t (msg, num_col) values ( '1234', 1234 );
insert into t (msg, num_col) values ( '1234', 1234 )
                                              *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度create table t ( msg varchar2(10), num_col number(5,-2));insert into t ( msg, num_col) values ( '123.45', 123.45 );insert into t ( msg, num_col) values ( '123.456', 123.456 );eygle@SZTYORA> select * from t;MSG                     NUM_COL
-------------------- ----------
123.45                      100
123.456                     100insert into t(msg, num_col) values('1234567', 1234567);eygle@SZTYORA> select * from t;MSG                     NUM_COL
-------------------- ----------
123.45                      100
123.456                     100
1234567                 1234600eygle@SZTYORA> insert into t (msg, num_col) values ( '12345678', 12345678 );
insert into t (msg, num_col) values ( '12345678', 12345678 )
                                                  *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度create table t ( x number, y number );insert into t ( x )
select to_number(rpad('9', rownum*2,'9'))
from all_objects
where rownum <= 14;update t set y = x+1;set numformat 99999999999999999999999999999
col v1 format 99
col v2 format 99
select x, y, vsize(x) v1, vsize(y) v2
from t order by x;                             X                              Y  V1  V2
------------------------------ ------------------------------ --- ---
                            99                            100   2   2
                          9999                          10000   3   2
                        999999                        1000000   4   2
                      99999999                      100000000   5   2
                    9999999999                    10000000000   6   2
                  999999999999                  1000000000000   7   2
                99999999999999                100000000000000   8   2
              9999999999999999              10000000000000000   9   2
            999999999999999999            1000000000000000000  10   2
          99999999999999999999          100000000000000000000  11   2
        9999999999999999999999        10000000000000000000000  12   2
      999999999999999999999999      1000000000000000000000000  13   2
    99999999999999999999999999    100000000000000000000000000  14   2
  9999999999999999999999999999  10000000000000000000000000000  15   2已选择14行。-- 12.4.4 性能考虑 ( P514 )
create table t
( num_type number,
  float_type binary_float,
  double_type binary_double
)
/insert /*+ APPEND */ into t
select rownum, rownum, rownum
from all_objects
/commit;select sum(ln(num_type)) from t;select sum(ln(float_type)) from t;eygle@SZTYORA> set timing on
eygle@SZTYORA> select sum(ln(num_type)) from t;             SUM(LN(NUM_TYPE))
------------------------------
                        391412已用时间:  00: 00: 00.92
eygle@SZTYORA> select sum(ln(float_type)) from t;           SUM(LN(FLOAT_TYPE))
------------------------------
                        391412已用时间:  00: 00: 00.03
eygle@SZTYORA> select sum(ln(double_type)) from t;          SUM(LN(DOUBLE_TYPE))
------------------------------
                        391412已用时间:  00: 00: 00.01eygle@SZTYORA> select sum(ln(cast( num_type as binary_double ) )) from t;SUM(LN(CAST(NUM_TYPEASBINARY_DOUBLE)))
--------------------------------------
                                391412已用时间:  00: 00: 00.04

Answer 7

-- 看一下这一段代码，楼主就明白啦：
create table t ( x number, y number );insert into t ( x )
select to_number(rpad('9', rownum*2,'9'))
from all_objects
where rownum <= 14;update t set y = x+1;set numformat 99999999999999999999999999999
col v1 format 99
col v2 format 99
select x, y, vsize(x) v1, vsize(y) v2
from t order by x;                             X                              Y  V1  V2
------------------------------ ------------------------------ --- ---
                            99                            100   2   2
                          9999                          10000   3   2
                        999999                        1000000   4   2
                      99999999                      100000000   5   2
                    9999999999                    10000000000   6   2
                  999999999999                  1000000000000   7   2
                99999999999999                100000000000000   8   2
              9999999999999999              10000000000000000   9   2
            999999999999999999            1000000000000000000  10   2
          99999999999999999999          100000000000000000000  11   2
        9999999999999999999999        10000000000000000000000  12   2
      999999999999999999999999      1000000000000000000000000  13   2
    99999999999999999999999999    100000000000000000000000000  14   2
  9999999999999999999999999999  10000000000000000000000000000  15   2已选择14行。

Answer 8

Numeric datatypes

Internal representation of the NUMBER datatype

http://topic.csdn.net/u/20110311/10/73fa722c-d119-4af4-8b6a-41fa3d9b2f06.html