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
PLS_INTEGER for integer arithmetic (and
for loop counters) in cases in which you can
avoid conversions back and forth with the
NUMBER type.
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)
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.
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.
FLOOR and CEIL. The FLOOR function
returns the largest integer equal to or less
than the specified number.
The CEIL function returns the smallest
whereas the formula used for REMAINDER is
REMAINDER (m, n) = m - n ROUND (m/n)
Listing 4 includes a block that demonstrates the effect of and differences
between these two functions.
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 and it returns the string representation of that number, exactly long enough
to contain all of its significant digits.
Listing 5 includes a block that shows
some TO_CHAR examples. As you can see,
leading and trailing zeros are not in the
string representation of the number.
Often, when you have to convert a
number to a string, you need that number to
fit a certain format. You might, for example,
want to display the number as a currency,
so that even if there are no cents, you need
to include the “.00”—in such cases, you will
need to add a second argument in your call
to TO_CHAR: the format mask.
To specify a format for the string to which
the number is converted, provide as the
second argument to TO_CHAR a string that
contains a combination of special format
elements. Suppose, for example, that I want
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
Code Listing 5: Calls to TO_CHAR
BEGIN
DBMS_OUTPUT.put_line (TO_CHAR (100.55));
DBMS_OUTPUT.put_line (TO_CHAR (000100.5500));
DBMS_OUTPUT.put_line (TO_CHAR (10000.00));
END;
And here is the output from this block:
100.55
100.55
