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

/* 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 PL