Oracle在10gR1推出本地浮点数据类型(Native Floating-Point Data Types): BINARY_FLOAT(单精度32位)和BINARY_DOUBLE(双精度64位). 这些新数据类型都是基于IEEE二进制浮点运算标准,ANSI/IEEE Std 754-1985 [IEEE 754].
使用这些类型时要加上文字f(BINARY_FLOAT)或者d(BINARY_DOUBLE).比如,2.07f
、3.000094d
SQL和PL/SQL都支持这些新的数据类型：
SQL例子:
CREATE TABLE t (f BINARY_FLOAT, d BINARY_DOUBLE);
Table created.
INSERT INTO t VALUES (2.0f, 2.0d);
1 row created.
INSERT INTO t VALUES (1.5f/0.5f, 1.5d/0.5d);
1 row created.
INSERT INTO t VALUES (1.0f/0.0f, 1.0d/0.0d);
1 row created.
PL/SQL例子:
DECLARE
bf_var BINARY_FLOAT := 2.0f;
bd_var BINARY_DOUBLE := 2.0d;
BEGIN
bf_var := 1.5f/0.5f;
bd_var := 1.5d/0.5d;
END;
/
本地浮点数据类型最大的特点就是比NUMBER类型效率更高
- 硬件运算/数学运算快 5– 10 倍
- 占用更少的内存/磁盘空间（5/9 字节与 1 – 22 字节）
- BINARY_DOUBLE 值范围更大（e308 与 e125）
- 无需类型转换（使用与字节顺序无关的存储格式）
下面程序是使用欧拉级数计算圆周率∏:
∏ = sqrt ( 6 * ( 1 + 1/2*2 + 1/3*2 + ... ) )
方法一：使用NUMBER类型
create or replace procedure Euler_Pi_Number is
subtype My_Number is number;
zero constant My_Number := 0.0;
one constant My_Number := 1.0;
two constant My_Number := 2.0;
six constant My_Number := 6.0;
toler constant My_Number := 0.00000000001;
root_toler constant My_Number := toler/1000.0;
root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;
begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two;
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Number;
/
方法二：使用BINARY_DOUBLE类型
create or replace procedure Euler_Pi_Binary is
subtype My_Number is binary_double;
zero constant My_Number := 0.0d;
one constant My_Number := 1.0d;
two constant My_Number := 2.0d;
six constant My_Number := 6.0d;
toler constant My_Number := 0.00000000001d;
root_toler constant My_Number := toler/1000.0d;
root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;
begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two;
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Binary;
/
SQL> set timing on
SQL> exec Euler_Pi_Number;
PL/SQL 过程已成功完成。
已用时间: 00: 00: 11.59
SQL> exec Euler_Pi_Binary;
PL/SQL 过程已成功完成。
已用时间: 00: 00: 02.09
上面例子中近似300,000次迭代计算,NUMBER类型花费11.59秒,BINARY_DOUBLE类型花费约2.09秒,性能提高大约5.5倍.
结论：在版本10g之后写一些偏数字科学运行量巨大的存储过程、函数时,对于浮点数字类型要优先考虑使用本地浮点数据类型
打印文档
推荐好友 
返回顶部
增大字体 
减少字体