Working with Numbers in PL/SQL
By Steven Feuerstein
Part 4 in a series of articles on understanding and using PL/SQL
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
This article gives you all the information you need in order to begin working with numbers in your PL/SQL programs.
Numbers in PL/SQL
PL/SQL offers a variety of numeric datatypes to suit different purposes:
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:
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.
Numeric Built-in 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
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
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 in PL/SQL
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.
Steven Feuerstein (firstname.lastname@example.org) is Quest Software’s PL/SQL evangelist. He has published 10 books on Oracle PL/SQL (O’Reilly Media) and is an Oracle ACE director. More information is available at stevenfeuerstein.com.