17,377
社区成员
发帖
与我相关
我的任务
分享
-- 看一下这一段代码,楼主就明白啦:
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 数值类型 ( 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.99999999999
scott@SZTYORA> select * from t;
NUM_COL FLOAT_COL DBL_COL
------------------------ ------------------------ ------------------------
1234567890.09876543210 1234567940.00000000000 1234567890.09876540000
delete 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.00000000000
delete 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 .000000000000000001230000
select 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.46
eygle@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 100
insert into t(msg, num_col) values('1234567', 1234567);
eygle@SZTYORA> select * from t;
MSG NUM_COL
-------------------- ----------
123.45 100
123.456 100
1234567 1234600
eygle@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.01
eygle@SZTYORA> select sum(ln(cast( num_type as binary_double ) )) from t;
SUM(LN(CAST(NUM_TYPEASBINARY_DOUBLE)))
--------------------------------------
391412
已用时间: 00: 00: 00.04
scott@YPCOST> desc a;
Name Null? Type
----------------------------------------------------------------------------------------- --------
B NUMBER(5,2)
scott@YPCOST> desc aa;
Name Null? Type
----------------------------------------------------------------------------------------- --------
B NUMBER
--应该是不一样的 不过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.