Working with Numbers in PL/SQL

Part 4 in a series of articles on understanding and using PL/SQL

By Steven Feuerstein

November/December 2011

The previous article in this introductory PL/SQL series focused on working with strings in PL/SQL-based applications. Without a doubt, strings are the most common type of data with which PL/SQL developers will work, but it is certainly a very rare application that does not also rely on numbers. You need to keep track of the quantities of items, and much more.

As a result, you will quite often need to

• Declare variables and constants for numbers

• Use built-in functions to modify values, such as the rounding of numbers

This article gives you all the information you need in order to begin working with numbers in your PL/SQL programs.

PL/SQL offers a variety of numeric datatypes to suit different purposes:

• NUMBER. A true decimal datatype that is ideal for working with monetary amounts. NUMBER is the only one of PL/SQL’s numeric types to be implemented in a platform-independent fashion.

• PLS_INTEGER. Integer datatype conforming to your hardware’s underlying integer representation. Arithmetic is performed with your hardware’s native machine instructions. You cannot store values of this type in tables; it is a PL/SQL-specific datatype.

• SIMPLE_INTEGER. Introduced as of Oracle Database 11g Release 1. The SIMPLE_INTEGER datatype results in significantly shorter execution times for natively compiled code. This datatype is not explored in this article.

• BINARY_FLOAT and BINARY_DOUBLE. Single- and double-precision, IEEE-754, binary floating-point datatypes. These BINARY datatypes are highly specialized and are useful when you need to improve the performance of computation-intensive operations. These datatypes are not explored in this article.

In practice, you may encounter other numeric types, such as FLOAT, INTEGER, and DECIMAL. These are subtypes of the four core numeric types in the preceding list.

Now let’s take a closer look at NUMBER and PLS_INTEGER.

The NUMBER datatype. The NUMBER data-type is by far the most common numeric datatype you’ll encounter in the world of Oracle and PL/SQL programming. Use it to store integer, fixed-point, or floating-point numbers of just about any size. Prior to Oracle Database 10g, NUMBER was the only numeric datatype supported directly by the Oracle Database engine; now you can use BINARY_FLOAT and BINARY_DOUBLE as well. NUMBER is implemented in a platform-independent manner, and arithmetic on NUMBER values yields the same result no matter what hardware platform you run on.

To work with numbers in PL/SQL programs, you declare variables to hold the number values. The following declares a variable using the NUMBER datatype:

DECLARE
   l_salary NUMBER;

Such a declaration results in a floating-point number. Oracle Database will allocate space for a maximum of 40 digits, and the decimal point will float to best accommodate whatever values you assign to the variable. NUMBER variables can hold values as small as 10-130 (1.0E - 130) and as large as 10126 - 1 (1.0E126 - 1). Values smaller than 10-130 will get rounded down to 0, and calculations resulting in values larger than or equal to 10126 will be un-defined, causing runtime problems but not raising an exception.

This range of values is demonstrated by the code block in Listing 1. (TO_CHAR and format masks are described later in this article.)

Code Listing 1: Demonstration of the range of NUMBER datatype values

DECLARE
   tiny_nbr NUMBER := 1e-130;
   test_nbr NUMBER;

   --                              1111111111222222222233333333334
   --                     1234567890123456789012345678901234567890
   big_nbr      NUMBER := 9.999999999999999999999999999999999999999e125;

   --                                 1111111111222222222233333333334444444
   --                        1234567890123456789012345678901234567890123456
   fmt_nbr VARCHAR2(50) := '9.99999999999999999999999999999999999999999EEEE';
BEGIN
   DBMS_OUTPUT.PUT_LINE(
      'tiny_nbr          =' || TO_CHAR(tiny_nbr, '9.9999EEEE'));

   /* NUMBERs that are too small round down to zero. */
   test_nbr := tiny_nbr / 1.0001;
   DBMS_OUTPUT.PUT_LINE(
      'tiny made smaller =' || TO_CHAR(test_nbr, fmt_nbr));

   /* NUMBERs that are too large throw an error: */
   DBMS_OUTPUT.PUT_LINE(
      'big_nbr           =' || TO_CHAR(big_nbr, fmt_nbr));
   test_nbr := big_nbr * 1.0001;        -- too big
   DBMS_OUTPUT.PUT_LINE(
      'big made bigger   =' || TO_CHAR(test_nbr, fmt_nbr));
END;

And here is the output from this block:

tiny_nbr          = 1.0000E-130
tiny made smaller =   .00000000000000000000000000000000000000000E+00
big_nbr           = 9.99999999999999999999999999999999999999900E+125
big made bigger   =#################################################

If you try to explicitly assign a number that is too large to your NUMBER variable, you’ll raise a numeric overflow or underflow exception, but if you assign calculation results that exceed the largest legal value, no exception will be raised. If your application really needs to work with such large numbers, you will need to write validation routines that anticipate out-of-range values or consider using BINARY_DOUBLE. Using binary datatypes has rounding implications, so be sure to check the Oracle documentation on binary datatypes for details. For most uses, the chance of encountering these rounding errors will probably lead you to choose the NUMBER datatype.

Often when you declare a variable of type NUMBER, you will want to constrain its precision and scale, which you can do as follows:

NUMBER (precision, scale)

For example, I want to declare a variable to hold a monetary amount of up to $999,999 and that consists of dollars and cents (that is, just two digits to the right of the decimal point). This declaration does the trick:

NUMBER (8,2)

Such a declaration results in a fixed-point number. The precision is the total number of significant digits in the number. The scale dictates the number of digits to the right (positive scale) or left (negative scale) of the decimal point and also affects the point at which rounding occurs. Both the precision and scale values must be literal integer values; you cannot use variables or constants in the declaration. Legal values for precision range from 1 to 38, and legal values for scale range from -84 to 127.

When declaring fixed-point numbers, the value for scale is usually less than the value for precision.

The PLS_INTEGER datatype. The PLS_INTEGER datatype stores signed integers in the range of -2,147,483,648 through 2,147,483,647. Values are represented in your hardware platform’s native integer format.

Here is an example of declaring a variable of type PLS_INTEGER:

DECLARE
   loop_counter PLS_INTEGER; 

The PLS_INTEGER datatype was designed for speed. When you perform arithmetic with PLS_INTEGER values, the Oracle software uses native machine arithmetic. As a result, it’s faster to manipulate PLS_INTEGER values than it is to manipulate integers in the NUMBER datatype.

Consider using PLS_INTEGER whenever your program is compute-intensive and involves integer arithmetic (and the values will never fall outside of this type’s range of valid integers). Bear in mind, however, that if your use of PLS_INTEGER results in frequent conversions to and from the NUMBER type, you may be better off using NUMBER to begin with. You’ll gain the greatest efficiency when you use PLS_INTEGER for integer arithmetic (and for loop counters) in cases in which you can avoid conversions back and forth with the NUMBER type.

Oracle Database includes an extensive set of built-in functions for manipulating numbers and for converting between numbers and strings. The following are some of the most commonly needed functions.

ROUND. The ROUND function accepts a number and returns another number rounded to the specified number of places to the right of the decimal point. If you do not specify that number, ROUND will return a number rounded to the nearest integer.

Listing 2 includes some examples of calls to ROUND.

Code Listing 2: Calls to ROUND

BEGIN
   DBMS_OUTPUT.put_line (ROUND (10.25));
   DBMS_OUTPUT.put_line (ROUND (10.25, 1));
   DBMS_OUTPUT.put_line (ROUND (10.23, 1));
   DBMS_OUTPUT.put_line (ROUND (10.25, 2));
   DBMS_OUTPUT.put_line (ROUND (10.25, -2));
   DBMS_OUTPUT.put_line (ROUND (125, -2));
END;

And here is the output from this block:

10
10.3
10.2
10.25
0
100

Note that a negative value for the second argument rounds to the nearest 10 (to the left of the decimal point).

TRUNC. TRUNC is similar to round, in that you can specify the number of digits to the right or left of the decimal point. The difference is that TRUNC simply removes or truncates digits. And, like ROUND, you can specify a negative number, which truncates digits (makes them zero) to the left of the decimal point.

Listing 3 includes some examples of calls to TRUNC.

Code Listing 3: Calls to TRUNC

BEGIN
   DBMS_OUTPUT.put_line (TRUNC (10.23, 1));
   DBMS_OUTPUT.put_line (TRUNC (10.25, 1));
   DBMS_OUTPUT.put_line (TRUNC (10.27, 1));
   DBMS_OUTPUT.put_line (TRUNC (123.456, -1));
   DBMS_OUTPUT.put_line (TRUNC (123.456, -2));
END;

And here is the output from this block:

10.2
10.2
10.2
120
100

FLOOR and CEIL. The FLOOR function returns the largest integer equal to or less than the specified number.

The CEIL function returns the smallest integer equal to or greater than the specified number.

The following block and its output demonstrate these two functions:

BEGIN
   DBMS_OUTPUT.put_line (FLOOR (1.5));
   DBMS_OUTPUT.put_line (CEIL (1.5));
END;
/
1
2

MOD and REMAINDER. MOD and REMAINDER both return the remainder of one number divided by another, but that remainder is calculated differently for each function.

The formula used by Oracle Database for MOD is

MOD (m, n) = m - n * FLOOR (m/n)

when both m and n have the same sign (positive or negative). If the signs of m and n are different, then the formula used is:

MOD (m,n) = ( m - n * CEIL(m/n) )

whereas the formula used for REMAINDER is

n2 - (n1*N)

where n1 is not zero and where N is the integer nearest n2/n1. If n2/n1 equals x.5, then N is the nearest even integer.

Listing 4 includes a block that demonstrates the effect of and differences between these two functions.

Code Listing 4: Calls to MOD and REMAINDER

BEGIN
   DBMS_OUTPUT.put_line (MOD (15, 4));
   DBMS_OUTPUT.put_line (REMAINDER (15, 4));
   DBMS_OUTPUT.put_line (MOD (15, 6));
   DBMS_OUTPUT.put_line (REMAINDER (15, 6));
END;
/

And here is the output from this block:

3
-1
3
3

TO_CHAR. Use TO_CHAR to convert a number to a string. In its simplest form, you pass a single argument (the number) to TO_CHAR and it returns the string representation of that number, exactly long enough to contain all of its significant digits.

Listing 5 includes a block that shows some TO_CHAR examples. As you can see, leading and trailing zeros are not in the string representation of the number.

Code Listing 5: Calls to TO_CHAR

BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (100.55));
   DBMS_OUTPUT.put_line (TO_CHAR (000100.5500));
   DBMS_OUTPUT.put_line (TO_CHAR (10000.00));
END;

And here is the output from this block:

100.55
100.55
10000

Often, when you have to convert a number to a string, you need that number to fit a certain format. You might, for example, want to display the number as a currency, so that even if there are no cents, you need to include the “.00”—in such cases, you will need to add a second argument in your call to TO_CHAR: the format mask.

To specify a format for the string to which the number is converted, provide as the second argument to TO_CHAR a string that contains a combination of special format elements. Suppose, for example, that I want to display large numbers with a “1000s” delimiter. In other words, I want to display “10,000” instead of “10000” so I would use the following format:

BEGIN
   DBMS_OUTPUT.put_line (
      'Amount='||
      TO_CHAR (
        10000
      , '9G999G999'));
END;

The G element indicates where in the string I would like to place the group separator. (The character used for the separator is determined by the National Language Settings database parameter NLS_NUMERIC_CHARACTERS.) The 9 element tells Oracle Database to put a significant digit or a blank space in that location. As a result, the output from this block is

Amount=    10,000

If I want to have zeros appear instead of blanks, I can use 0 instead of 9, as in

BEGIN
   DBMS_OUTPUT.put_line (
      'Amount=' ||
      TO_CHAR (
         10000
       , '0G000G999'));
END;
Amount= 0,010,000

If I do not want any leading zeros, extra blanks, or white space appearing in my converted number, I will use the FM element, as in

BEGIN
   DBMS_OUTPUT.put_line (
      'Amount=' ||
      TO_CHAR (
         10000
      , 'FM9G999G999'));
END;
Amount=10,000

Suppose that my number is actually a monetary unit consisting of dollars (or euros) and cents and I want to show the currency symbol as well as the cents portion. I can use the following format:

BEGIN
   DBMS_OUTPUT.put_line (
     'Salary=' ||
     TO_CHAR (
        14500.77
     , 'FML999G999D99'));
END;
Salary=$14,500.77

The L element specifies the location of the local currency symbol (such as $ or €) in the return value (the NLS_CURRENCY parameter specifies the local currency symbol). The D element indicates the location of the decimal point. (The character used for the decimal point is specified by the database parameter NLS_NUMERIC_CHARACTERS.)

It is outside the scope of this article to explain all of the many elements available for use in number formats (there are, for example, at least four elements just for denoting monetary units). Check  Oracle Database SQL Language Reference 11g Release 2 (11.2) , for a complete description.

Most applications require the storage and manipulation of dates and times. Unlike strings and numbers, dates are quite complicated: not only are they highly formatted data, but there are also many rules for determining valid values and valid calculations (leap days and years, daylight saving time changes, national and company holidays, date ranges, and so on).

Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format and also have an extensive set of built-in functions for manipulating the date and time.

I will cover dates, time stamps, and intervals in the next PL/SQL 101 article.

CREATE OR REPLACE FUNCTION
plch_ceil_and_floor (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RETURN CEIL (number_in) - FLOOR (number_in);
END;

REPLACE=stvn furstin
TRANSLATE=

LEARN more about
PL/SQL datatypes
built-in functions

DOWNLOAD Oracle Database 11g

TEST your PL/SQL knowledge

READ PL/SQL 101, parts 1-12