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

最新推荐文章于 2023-12-19 14:51:00 发布

cp26520

最新推荐文章于 2023-12-19 14:51:00 发布

阅读量202

点赞数

文章标签：数据库

Oracle在10gR1推出本地浮点数据类型(Native Floating-Point Data Types): BINARY_FLOAT(单精度32位)和BINARY_DOUBLE(双精度64位). 这些新数据类型都是基于IEEE二进制浮点运算标准,ANSI/IEEE Std 754-1985 [IEEE 754].

[@more@]

使用这些类型时要加上文字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;