Working with Numbers in PL/SQL（在PL/SQL中使用数字）

Working with Numbers in PL/SQL（在PL/SQL中使用数字）

 

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 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;

 

 

This range of values is demonstrated by the code block in Listing 1. (TO_CHAR and format masks are described later in this article.)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.

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.