The BINARY_FLOAT and BINARY_DOUBLE datatypes offer better performance over other datatypes.
SQL>
SQL> --
SQL>
SQL> set timing on
SQL>
SQL> create or replace function pi return number
2 as
3 last_pi number := 0;
4 delta number := 0.000001;
5 pi number := 1;
6 denom number := 3;
7 oper number := -1;
8 negone number := -1;
9 two number := 2;
10 begin
11 loop
12 last_pi := pi;
13 pi := pi + oper * 1/denom;
14 exit when (abs(last_pi-pi) <= delta );
15 denom := denom + two;
16 oper := oper * negone;
17 end loop;
18 return pi * 4;
19 end;
20 /
Function created.
Elapsed: 00:00:00.20
SQL>
SQL> select pi from dual;
PI
----------
3.14159465
Elapsed: 00:00:01.03
SQL>
SQL> create or replace function pi return number
2 as
3 last_pi BINARY_DOUBLE := 0;
4 delta BINARY_DOUBLE := 0.000001;
5 pi BINARY_DOUBLE := 1;
6 denom BINARY_DOUBLE := 3;
7 oper BINARY_DOUBLE := -1;
8 negone BINARY_DOUBLE := -1;
9 two BINARY_DOUBLE := 2;
10 begin
11 loop
12 last_pi := pi;
13 pi := pi + oper * 1/denom;
14 exit when (abs(last_pi-pi) <= delta );
15 denom := denom + two;
16 oper := oper * negone;
17 end loop;
18 return pi * 4;
19 end;
20 /
Function created.
Elapsed: 00:00:00.14
SQL>
SQL> select pi from dual;
PI
----------
3.14159465
Elapsed: 00:00:00.31
SQL>
SQL> create or replace function pi return number
2 as
3 last_pi BINARY_FLOAT := 0;
4 delta BINARY_FLOAT := 0.000001;
5 pi BINARY_FLOAT := 1;
6 denom BINARY_FLOAT := 3;
7 oper BINARY_FLOAT := -1;
8 negone BINARY_FLOAT := -1;
9 two BINARY_FLOAT := 2;
10 begin
11 loop
12 last_pi := pi;
13 pi := pi + oper * 1/denom;
14 exit when (abs(last_pi-pi) <= delta );
15 denom := denom + two;
16 oper := oper * negone;
17 end loop;
18 return pi * 4;
19 end;
20 /
Function created.
Elapsed: 00:00:00.11
SQL>
SQL> select pi from dual;
PI
----------
3.14159775
Elapsed: 00:00:00.28
SQL>
SQL>
SQL> set timing off
SQL>
SQL> --This procedure is revised from one of the discussions on http://asktom.oracle.com.