Working with Numbers
in PL/SQL
Part 4 in a series of articles on understanding
and using PL/SQL
ORACLE DATABASE
The previous article in this introduc- tory 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.
NUMBERS IN PL/SQL
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 11 g Release 1. The SIMPLE_
INTEGER datatype results in significantly
shorter execution times for natively compiled code. This datatype is not explored in
this article.
I-HUA CHEN
• BINAR Y_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.
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 =#################################################
