DIFFRENCE BETWEEN PLS-INTEGER, BINARY_INTEGER AND SIMPLE_INTEGER

DIFF.BETWEEN PLS-INTEGER, BINARY_INTEGER AND SIMPLE_INTEGER

PLS_INTEGER:

• PLS_INTEGER stores data  in the hardware arithmetic format. So It is faster then NUMBER and its subtypes.
• PLS_INTEGER requires less storage.
• PLS_INTEGER  has range of -2,147,483,648 through 2,147,483,647, represented in 32 bits.
• Use PLS_INTEGER  when more calculations are in use.
• A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception, even if you assign the result to a NUMBER data typ
• PLS_INTEGER and its subtypes can be implicitly converted to these data types:

• CHAR

-  VARCHAR2

• NUMBER
• LONG

SIMPLE_INTEGER:

• It is new feature of the 11g.
• It has same range as PLS_INTEGER and NOT NULL constraint.
• We can not pass null value to procedure , if the procedure have parameter as SIMPLE_INTEGER.
• We can declare SIMPLE_INTEGER with null values in declarative section.
• If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.

For Example:
        The speed improvements are a result of two fundamental differences between the two         datatypes. First, SIMPLE_INTEGER and PLS_INTEGER have the same range (-2,147,483,648 through 2,147,483,647), but SIMPLE_INTEGER wraps round when it exceeds its bounds, rather than throwing an error like PLS_INTEGER.

SET SERVEROUTPUT ON

DECLARE

 l_simple_integer SIMPLE_INTEGER := 2147483645;

BEGIN

 FOR i IN 1 .. 4 LOOP

   l_simple_integer := l_simple_integer + 1;

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999'));

 END LOOP;

 FOR i IN 1 .. 4 LOOP

   l_simple_integer := l_simple_integer - 1;

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999'));

 END LOOP;

END;

/

+2147483646

+2147483647

-2147483648

-2147483647

-2147483648

+2147483647

+2147483646

+2147483645

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.