Working with Numbers in PL/SQL（在PL/SQL中使用数字）(三)
S_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 a