Oracle 5.0, Справочное руководство

Страница: 1025 / 3232

Rounding Behavior
1005
• If strict mode is enabled, inserts and updates involving division by zero are prohibited, and an error
occurs.
In other words, inserts and updates involving expressions that perform division by zero can be treated
as errors, but this requires
ERROR_FOR_DIVISION_BY_ZERO
[536] in addition to strict mode.
Suppose that we have this statement:
INSERT INTO t SET i = 1/0;
This is what happens for combinations of strict and
ERROR_FOR_DIVISION_BY_ZERO
[536] modes.
sql_mode
[495] Value Result
''
(Default) No warning, no error;
i
is set to
NULL
.
strict No warning, no error;
i
is set to
NULL
.
ERROR_FOR_DIVISION_BY_ZERO
[536] Warning, no error;
i
is set to
NULL
.
strict,
ERROR_FOR_DIVISION_BY_ZERO
[536] Error condition; no row is inserted.
For inserts of strings into numeric columns, conversion from string to number is handled as follows if
the string has nonnumeric contents:
• A string that does not begin with a number cannot be used as a number and produces an error in
strict mode, or a warning otherwise. This includes the empty string.
• A string that begins with a number can be converted, but the trailing nonnumeric portion is truncated.
If the truncated portion contains anything other than spaces, this produces an error in strict mode, or
a warning otherwise.
12.17.4. Rounding Behavior
This section discusses precision math rounding for the
ROUND()
[913] function and for inserts into
columns with exact-value types (
DECIMAL
and integer).
The
ROUND()
[913] function rounds differently depending on whether its argument is exact or
approximate:
• For exact-value numbers,
ROUND()
[913] uses the “round half up” rule: A value with a fractional
part of .5 or greater is rounded up to the next integer if positive or down to the next integer if
negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is
rounded down to the next integer if positive or up to the next integer if negative.
• For approximate-value numbers, the result depends on the C library. On many systems, this means
that
ROUND()
[913] uses the “round to nearest even” rule: A value with any fractional part is
rounded to the nearest even integer.
The following example shows how rounding differs for exact and approximate values:
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+
For inserts into a
DECIMAL
or integer column, the target is an exact data type, so rounding uses “round
half away from zero,” regardless of whether the value to be inserted is exact or approximate:
mysql> CREATE TABLE t (d DECIMAL(10,0));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES(2.5),(2.5E0);