4.2 Numeric & Mathematical Functions

4.2 Numeric & Mathematical Functions

Quick Answer: Numeric functions accept numeric inputs (NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE) and return numeric values. The most heavily tested functions on the 1Z0-071 exam are ROUND, TRUNC, MOD, CEIL, FLOOR, ABS, and SIGN. Understanding the second precision parameter d in ROUND(n, d) and TRUNC(n, d)—where negative d operates on digits to the left of the decimal point—and knowing that CEIL(-4.2) is -4 while FLOOR(-4.2) is -5 are critical exam competencies.

---

The ROUND Function

The ROUND(n [, d]) function rounds n to d decimal places using standard mathematical half-way rounding (digits 5 through 9 round up away from zero; 0 through 4 round down).

Precision Parameter d Rules:

1. Positive d (d > 0): Rounds to d decimal places to the right of the decimal point.
2. Zero or Omitted d (d = 0 or omitted): Rounds to 0 decimal places (the nearest whole integer).
3. Negative d (d < 0): Rounds to d places to the left of the decimal point (tens, hundreds, thousands, etc.).

SELECT
  ROUND(45.926, 2)   AS r1, -- 45.93  (rounds to 2 decimal places)
  ROUND(45.926, 0)   AS r2, -- 46     (rounds to nearest whole integer)
  ROUND(45.926)      AS r3, -- 46     (default d is 0)
  ROUND(45.926, -1)  AS r4, -- 50     (rounds to nearest tens; 5 rounds up)
  ROUND(44.926, -1)  AS r5, -- 40     (rounds to nearest tens; 4 rounds down)
  ROUND(55.926, -2)  AS r6, -- 100    (rounds to nearest hundreds; 55 >= 50)
  ROUND(45.926, -2)  AS r7  -- 0      (rounds to nearest hundreds; 45 < 50)
FROM dual;

---

The TRUNC Function

The TRUNC(n [, d]) function truncates n to d decimal places without rounding (simply discarding discarded digits).

Precision Parameter d Rules:

1. Positive d (d > 0): Truncates to d decimal places to the right of the decimal point.
2. Zero or Omitted d (d = 0 or omitted): Discards all fractional digits, returning the whole integer part.
3. Negative d (d < 0): Replaces d digits to the left of the decimal point with zeros.

SELECT
  TRUNC(45.926, 2)   AS t1, -- 45.92  (truncates at 2 decimal places)
  TRUNC(45.926, 0)   AS t2, -- 45     (truncates all decimal places)
  TRUNC(45.926)      AS t3, -- 45     (default d is 0)
  TRUNC(45.926, -1)  AS t4, -- 40     (truncates units digit to 0)
  TRUNC(175.926, -2) AS t5, -- 100    (truncates tens and units digits to 0)
  TRUNC(45.926, -2)  AS t6  -- 0      (replaces tens and units with 0)
FROM dual;

---

Comprehensive Comparison: ROUND vs. TRUNC

The following table illustrates the exact behavior of ROUND and TRUNC across diverse positive, negative, and fractional inputs:

---

The MOD Function

The MOD(m, n) function returns the remainder of m divided by n.

Formal Mathematical Definition:

Oracle calculates MOD(m, n) using the formula: $\text{MOD}(m, n) = m - (n \times \text{TRUNC}(m / n))$

Note that Oracle uses TRUNC, not FLOOR. This is exactly why Oracle's MOD diverges from the classical mathematical modulus whenever the two operands have opposite signs: MOD(-11, 4) returns -3 in Oracle, whereas a FLOOR-based modulus would return 1.

Key Rules for MOD:

1. Sign Follows Dividend: The result of MOD(m, n) has the same sign as m (the dividend).
2. Zero Divisor Rule: If n = 0, MOD(m, 0) returns m. It does not raise a division by zero error (ORA-01476).
3. Decimal Support: MOD works with fractional numbers as well as integers.

SELECT
  MOD(11, 4)    AS m1, -- 3   (11 - (4 * 2) = 3)
  MOD(11, -4)   AS m2, -- 3   (Sign follows dividend 11)
  MOD(-11, 4)   AS m3, -- -3  (Sign follows dividend -11)
  MOD(-11, -4)  AS m4, -- -3  (Sign follows dividend -11)
  MOD(10.5, 3)  AS m5, -- 1.5 (10.5 - (3 * 3) = 1.5)
  MOD(25, 0)    AS m6  -- 25  (If divisor is 0, returns dividend without error)
FROM dual;

[!TIP] A classic use of MOD in queries is testing for even/odd numbers: WHERE MOD(employee_id, 2) = 0 filters for even employee IDs.

---

CEIL and FLOOR Functions

CEIL and FLOOR map real numbers to adjacent integers.

• CEIL(n): Returns the smallest integer greater than or equal to n ("ceiling" / rounding up toward positive infinity).
• FLOOR(n): Returns the largest integer less than or equal to n ("floor" / rounding down toward negative infinity).

SELECT
  CEIL(2.1)   AS c1, -- 3   (Smallest integer >= 2.1)
  CEIL(2.0)   AS c2, -- 2   (Smallest integer >= 2.0)
  CEIL(-2.1)  AS c3, -- -2  (Smallest integer >= -2.1; notice -2 is GREATER than -2.1)
  FLOOR(2.9)  AS f1, -- 2   (Largest integer <= 2.9)
  FLOOR(2.0)  AS f2, -- 2   (Largest integer <= 2.0)
  FLOOR(-2.1) AS f3  -- -3  (Largest integer <= -2.1; notice -3 is LESS than -2.1)
FROM dual;

Negative Number Traps on the Exam:

• CEIL(-4.8) is -4, NOT -5.
• FLOOR(-4.8) is -5, NOT -4.

---

ABS and SIGN Functions

ABS(n)

Returns the absolute value (magnitude) of a number n. Always returns a non-negative number.

SELECT ABS(-150.75) AS a1, ABS(150.75) AS a2, ABS(0) AS a3
FROM dual;
-- Returns: a1 = 150.75, a2 = 150.75, a3 = 0

SIGN(n)

Determines the sign of a number n. Returns:

• -1 if $n < 0$
• 0 if $n = 0$
• 1 if $n > 0$

SELECT SIGN(-450) AS s1, SIGN(0) AS s2, SIGN(892.5) AS s3
FROM dual;
-- Returns: s1 = -1, s2 = 0, s3 = 1

---

Summary of 1Z0-071 Numeric Function Traps

1. Negative d in ROUND/TRUNC: Negative precision moves to the left of the decimal point. ROUND(58.2, -1) = 60, TRUNC(58.2, -1) = 50.
2. Omitted Precision: Omitting d in ROUND(n) or TRUNC(n) defaults to d = 0 (nearest whole integer).
3. MOD Zero Divisor: MOD(n, 0) returns n and does NOT produce ORA-01476: divisor is equal to zero.
4. MOD Sign Convention: The sign of MOD(m, n) matches the sign of m, irrespective of n.
5. CEIL vs. FLOOR on Negative Numbers: CEIL moves toward zero for negative numbers (CEIL(-1.2) = -1), while FLOOR moves away from zero (FLOOR(-1.2) = -2).