No results found

Your search did not match any results.

We suggest you try the following to help find what you’re looking for:

  • Check the spelling of your keyword search.
  • Use synonyms for the keyword you typed, for example, try “application” instead of “software.”
  • Try one of the popular searches shown below.
  • Start a new search.
Trending Questions
 

Working with numbers in PL/SQL

Part 4 in a series of articles on understanding and using PL/SQL for accessing Oracle Database

By Steven Feuerstein | October 2020


PL/SQL 101 Series

PL/SQL is one of the core technologies at Oracle and is essential to leveraging the full potential of Oracle Database. PL/SQL combines the relational data access capabilities of the Structured Query Language with a flexible embedded procedural language, and it executes complex queries and programmatic logic run inside the database engine itself. This enhances the agility, efficiency, and performance of database-driven applications.

Steven Feuerstein, one of the industry’s best-respected and most prolific experts in PL/SQL, wrote a 12-part tutorial series on the language. Those articles, first published in 2011, have been among the most popular ever published on the Oracle website and continue to find new readers and enthusiasts in the database community. Beginning with the first installment, the entire series is being updated and republished; please enjoy!

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 you’ll work with, but it is certainly a very rare application that does not also rely on numbers, such as to keep track of the quantities of items, their prices, and so on.

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 to begin working with numbers in your PL/SQL programs.

A wide variety of numeric data types

PL/SQL offers a variety of numeric data types to suit different purposes. Here are the main ones:

  • NUMBER: A true decimal data type 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 data type 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 data type.
  • SIMPLE_INTEGER:The SIMPLE_INTEGER data type results in significantly shorter execution times for natively compiled code. This data type is not explored in this article.
  • BINARY_FLOAT and BINARY_DOUBLE: Single- and double-precision, IEEE-754, binary floating-point data types. These BINARY data types are highly specialized and are useful when you need to improve the performance of computation-intensive operations. These data types are not explored in this article.

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.

The NUMBER data type. The NUMBER data type is by far the most common numeric data type 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 data type 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 data type:


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 10,126 - 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 10,126 will be undefined, 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 data type 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 data types has rounding implications, so be sure to check the Oracle documentation on binary data types for details. For most uses, the chance of encountering these rounding errors will probably lead you to choose the NUMBER data type.

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 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 you declare fixed-point numbers, the value for scale is usually less than the value for precision.

The PLS_INTEGER data type. The PLS_INTEGER data type 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 data type 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 data type.

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.

The built-in numeric functions

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 the following, when both m and n have the same sign (positive or negative):

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

If the signs of m and n are different, then the formula used is this:

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

The formula used for REMAINDER is the following, 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.

n2 - (n1*N)

Listing 4 includes a block that demonstrates the effects 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.

Dates, time stamps, and intervals

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 useful date and time data types that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time as needed by your application.

I will cover dates, time stamps, and intervals in the next article.

Dig deeper

Illustration: Wes Rowell

Steven Feuerstein

Steven Feuerstein

Steven Feuerstein is an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O’Reilly Media). Steven has been developing software since 1980. He was one of the original Oracle ACE Directors and wrote regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG’s Lifetime Achievement Award (2009).