Using BINARY_FLOAT and BINARY_DOUBLE for complex calculations : BINARY_INTEGER DOUBLE FLOAT « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. BINARY_INTEGER DOUBLE FLOAT

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.