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