Hi Tom,
Just would like to understand your view on this.
Can use FLOAT instead for NUMBER (x,y) in £ Amount field? If they are same, will there be same
round off error?
What is recommended for monetory columns please?
Followup June 17, 2011 - 12pm UTC:
You cannot use floats or doubles for money, you'll lose (or gain magically) money that way.
floats cannot accurately hold money data.
Use the NUMBER type for money. You get 38 digits of precision.
ops$tkyte%ORA11GR2> create table t ( x number, y binary_float, z binary_double );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t (x) select 100.00+ rownum/100 from all_users;
44 rows created.
ops$tkyte%ORA11GR2> update t set y = x, z = x;
44 rows updated.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set numformat 99999999.9999999999
ops$tkyte%ORA11GR2> select x, y, z,
2 case when x <> cast(y as number) then '*' end bad_y,
3 case when x <> cast(z as number) then '*' end bad_z
4 from t
5 where x <> cast(y as number)
6 or x <> cast(z as number);
X Y Z B B
-------------------- -------------------- -------------------- - -
100.0100000000 100.0100020000 100.0100000000 * *
100.0200000000 100.0199970000 100.0200000000 *
100.0300000000 100.0299990000 100.0300000000 *
100.0400000000 100.0400010000 100.0400000000 * *
100.0500000000 100.0500030000 100.0500000000 *
100.0600000000 100.0599980000 100.0600000000 *
100.0700000000 100.0700000000 100.0700000000 *
100.0800000000 100.0800020000 100.0800000000 *
100.0900000000 100.0899960000 100.0900000000 *
100.1000000000 100.0999980000 100.1000000000 * *
100.1100000000 100.1100010000 100.1100000000 *
100.1200000000 100.1200030000 100.1200000000 *
100.1300000000 100.1299970000 100.1300000000 *
100.1400000000 100.1399990000 100.1400000000 *
100.1500000000 100.1500020000 100.1500000000 * *
100.1600000000 100.1600040000 100.1600000000 *
100.1700000000 100.1699980000 100.1700000000 *
100.1800000000 100.1800000000 100.1800000000 *
100.1900000000 100.1900020000 100.1900000000 *
100.2000000000 100.1999970000 100.2000000000 *
100.2100000000 100.2099990000 100.2100000000 * *
100.2200000000 100.2200010000 100.2200000000 *
100.2300000000 100.2300030000 100.2300000000 *
100.2400000000 100.2399980000 100.2400000000 * *
100.2600000000 100.2600020000 100.2600000000 * *
100.2700000000 100.2699970000 100.2700000000 *
100.2800000000 100.2799990000 100.2800000000 *
100.2900000000 100.2900010000 100.2900000000 * *
100.3000000000 100.3000030000 100.3000000000 *
100.3100000000 100.3099980000 100.3100000000 *
100.3200000000 100.3200000000 100.3200000000 *
100.3300000000 100.3300020000 100.3300000000 *
100.3400000000 100.3399960000 100.3400000000 *
100.3500000000 100.3499980000 100.3500000000 * *
100.3600000000 100.3600010000 100.3600000000 *
100.3700000000 100.3700030000 100.3700000000 *
100.3800000000 100.3799970000 100.3800000000 *
100.3900000000 100.3899990000 100.3900000000 *
100.4000000000 100.4000020000 100.4000000000 * *
100.4100000000 100.4100040000 100.4100000000 *
100.4200000000 100.4199980000 100.4200000000 *
100.4300000000 100.4300000000 100.4300000000 *
100.4400000000 100.4400020000 100.4400000000 *
43 rows selected.
看到了吧,小数点5位,肯定丢精度了,这东西基本没有啥用,10G的新玩意,那9i的咋办??
类型也就是NUMBER,VARCHAR2,DATE,TIMESTAMP这几个用用,其他的很少用