Oracle PL/SQL Tutorial/PL SQL Data Types/BINARY INTEGER DOUBLE FLOAT

BINARY_INTEGER

The BINARY_INTEGER datatype is used for declaring signed integer variables.

BINARY_INTEGER variables are stored in binary format, which takes less space.

Calculations on binary integers can also run slightly faster because the values are already in a binary format.

The Syntax for the BINARY_INTEGER Datatype

   <source lang="sql">

variable_name BINARY_INTEGER;</source>

BINARY_INTEGER in action

Both of these datatypes exist only in PL/SQL, and you cannot create a column of these types.

   <source lang="sql">

SQL> SQL> declare

 2      variable1_nr BINARY_INTEGER;
 3      variable2_nr PLS_INTEGER;
 4  begin
 5     NULL;
 6
 7  end;
 8  /

PL/SQL procedure successfully completed. SQL> SQL> -- SQL></source>

BINARY_INTEGER Subtypes

Subtype Usage POSITIVE Allows only positive integers to be stored, up to the maximum of 2,147,483,647. Zero is not considered a positive number, and so is not an allowed value. NATURAL Allows only natural numbers to be stored, which includes zero. Allowed values are 0, 1, 2, 3, and so on up to the maximum of 2,147,483,647. POSITIVEn Like POSITIVE but cannot be null. NATURALn Like NATURAL but cannot be null. SIGNTYPE Restricts a variable to only the values -1, 0, and 1.

NUMBER and BINARY_INTEGER Data types

   <source lang="sql">

SQL> SQL> set timing on SQL> declare

 2      v_nr number;
 3  begin
 4      for i in 1..1000000 loop
 5          v_nr:=v_nr+i-i+i*2-i*2;
 6      end loop;
 7      DBMS_OUTPUT.put_line(v_nr);
 8  end;
 9  /

PL/SQL procedure successfully completed. Elapsed: 00:00:00.93 SQL> SQL> declare

 2      v_nr binary_integer;
 3  begin
 4      for i in 1..1000000 loop
 5          v_nr:=v_nr+i-i+i*2-i*2;
 6      end loop;
 7      DBMS_OUTPUT.put_line(v_nr);
 8  end;
 9  /

PL/SQL procedure successfully completed. Elapsed: 00:00:00.25 SQL> SQL> set timing off</source>

Using BINARY_FLOAT and BINARY_DOUBLE for complex calculations

The BINARY_FLOAT and BINARY_DOUBLE datatypes offer better performance over other datatypes.

   <source lang="sql">

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.ru.</source>