Datatypes for SQL and PL/SQL, Numbers

Original Text: http://it.toolbox.com/blogs/oracle-guide/learn-oracle-datatypes-for-sql-and-plsql-numbers-10673

Datatypes are a subject that confuses many people. There are many types and many sub-types. A type is a basic datatype like NUMBER. A sub-type is derived from a type. An INTEGER is a sub-type of NUMBER. A sub-type usually add a constraint to a type and that creates the new sub-type; for INTEGER, only whole numbers are allowed.

In 10g, the five numeric types that you will most commonly use are: NUMBER, PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT and BINARY_DOUBLE. You would declare and use any of these in the same manner, although the BINARY_FLOAT and BINARY_DOUBLE add a few extra features that I cover below.


NUMBER

NUMBER, as you might have guessed, stores numeric data. There aren't any differences between the SQL type NUMBER and the PL/SQL type NUMBER. A NUMBER can store 38 digits of precision. A fully populated NUMBER requires 22 bytes to store. That's a very large number. You can constrain a NUMBER to limit the size of the values it can store.

NUMBER(precision,scale) defines how precise the number can be (think of it as how many digits it can be ) and what the scale can be (think of scale as how small it can be).

Both precision and scale are optional in a NUMBER declaration. You can declare a NUMBER with a precision and no scale but if you define a scale, you must define a precision.

You can also declare a negative scale. A negative scale will round up to the number of digits specified.

Let's see some examples:

DECLARE
a NUMBER;
b NUMBER(5);
c NUMBER(5,0);
d NUMBER(2,3);
e NUMBER (5,3);
f NUMBER(5,-2);
BEGIN
a := 1; -- No error, no rounding
a := 1000; -- No error, no rounding
a := 10000000; -- No error, no rounding
a := 100000000000000000000000000; -- No error, no rounding
a := 100000000000000000000000000.1; -- No error, no rounding

b := 1; -- No error, no rounding
b := 1.1; -- rounded to 1
b := 123.1; -- rounded to 123
b := 123.1234; -- rounded to 123
b := 123.9999; -- rounded to 124
b := 12345.123456; -- rounded to 12345
-- Precision is 5,
-- It's ok to be bigger than 5 on the right
b := 123456.1111; -- This gets an error
-- Can't be bigger than 5 digits to the left

-- c is implicitly defined exactly the same as b
c := 1; -- No error, no rounding
c := 1.1; -- rounded to 1
c := 123.1; -- rounded to 123
c := 123.1234; -- rounded to 123
c := 12345.123456; -- rounded to 12345
c := 12345.999999; -- rounded to 12346
c := 123456.1111; -- This gets an error
-- Can't be bigger than 5 digits to the left

-- d the scale is larger than the precision
-- it can only hold a number smaller than 2 significant digits

d := .012345; -- rounded to .012
d := 1.012345; -- This is an error, scale less than precision
-- means less than 1
d := .112345; -- This is an error
-- Precision is less

e := 1; -- No error, no rounding
e := 1.1; -- No error, no rounding
e := 12.1; -- No error, no rounding
e := 12.12; -- No error, no rounding
e := 12.123; -- No error, no rounding
e := 123.1234; -- Error, .1234 to large
e := 12345.123; -- Error, 12345 too large

f := 1; -- rounded to 0
f := 10; -- rounded to 0
f := 100; -- No error, no rounding
f := 100.001; -- rounded to 100
f := 9999.9999; -- rounded to 10000
f := 9999999; -- Error, too large

END;
You can use NUMBER as a table datatype and in your programs. Constrain your number with a precision and scale when your data rules call for it.

PLS_INTEGER and BINARY_INTEGER

PLS_INTEGER and BINARY_INTEGER are identical data types and are only available in PL/SQL. You cannot create a column in a table with either of these data types. PLS_INTEGER is a highly efficient integer 32-bit data type. You will most commonly see PLS_INETGER (and BINARY_INETGER) in PL/SQL routines as an index variable. An associative array (INDEX BY TABLE) index. Both PLS_INTEGER and BINARY_INTEGER allow whole numbers only. Decimal fractions are rounded to the nearest whole number.

There performance benefits to using PLS_INTEGER or BINARY_INTEGER is some places (as opposed to a NUMBER). Those performance issues are beyond the scope of this article, but if you would like to read about it, do a web search on "performance pls_integer oracle" and read some of the articles.

There's not much more to say about these two types. In 10g, they are interchangeable and the primary place to use them is in computing intensive PL/SQL code.

BINARY_FLOAT and BINARY_DOUBLE

These two data types are provided to allow very efficient floating point operations. BINARY_FLOAT is 32 bit and BINARY_DOUBLE is 64 bit.

There are two special cases (three including a negative) for these datatypes can be tested: Not a Number (NaN) and infinity (INF) (and negative infinity -INF). You can test for INF and NaN using IS or IS NOT, as in: SELECT * FROM TAB WHERE float_field IS NaN.

While these two data types are more efficient, they are less precise than a regular NUMBER datatype.

Sub-Types

Here are the STANDARD sub-types defined in Oracle 10g (taken from the STANDARD package):


type NUMBER is NUMBER_BASE;
subtype FLOAT is NUMBER; -- NUMBER(126)
subtype REAL is FLOAT; -- FLOAT(63)
subtype "DOUBLE PRECISION" is FLOAT;
subtype INTEGER is NUMBER(38,0);
subtype INT is INTEGER;
subtype SMALLINT is NUMBER(38,0);
subtype DECIMAL is NUMBER(38,0);
subtype NUMERIC is DECIMAL;
subtype DEC is DECIMAL;

subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
subtype NATURAL is BINARY_INTEGER range 0..2147483647;
subtype NATURALN is NATURAL not null;
subtype POSITIVE is BINARY_INTEGER range 1..2147483647;
subtype POSITIVEN is POSITIVE not null;
subtype SIGNTYPE is BINARY_INTEGER range '-1'..1;
That's about it for the numeric data types. If you understand NUMBER you're pretty much covered. It's important to understand the scale and precision parameters to a NUMBER declaration. Play with the code above.

There is no right or wrong about setting your scale or precision. If a number must be limited (due to business requirements), set the limits at the database level.

If you are tuning a PL/SQL program, remember PLS_INTEGER and BINARY_INTEGER are potentially faster than a regular NUMBER. Also remember that the Binary Float types may also be faster but are not as precise.

I will cover the Oralce provided numeric functions, including SQL, conversions & format masks, in a future article.

Take care,

LewisC

Listen to my latest podcast where I discuss RAC and Grid with experts Philip Newlan and Scott Jesse of Oracle - download it here.