Oracle10g的新数据类型：本地浮点数据类型

导读：
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
据类型都是基于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之后写一些偏数字科学运行量巨大的存储过程、函数时,对于浮点数字类型要优先考虑使用本地浮点数据类型

本文转自

http://yaanzy.itpub.net/post/1263/200971