MOD and REMAINDER in SQL and PL/SQL

View All Scripts Login to Run Script

Script Name MOD and REMAINDER in SQL and PL/SQL
Description MOD uses FLOOR and REMAINDER uses ROUND in their formulas. FLOOR returns the largest integer equal to or less than the number provided. ROUND could return an integer that is greater than the number provided (it could round up). The formulas used by these two functions are: MOD (m, n) = m - n * FLOOR (m/n); REMAINDER (m, n) = m - n * ROUND (m/n); Thus, if ROUND rounds up, a larger number will be subtracted from m, and so the value returned by REMAINDER could be less than that returned by MOD, but never greater. Doc link: http://docs.oracle.com/database/121/SQLRF/functions002.htm#CJAIBHGG
Area SQL General / Functions
Contributor Steven Feuerstein
Created Monday June 13, 2016

Statement 1

BEGIN
   DBMS_OUTPUT.put_line (MOD (15, 4));
   DBMS_OUTPUT.put_line (REMAINDER (15, 4));
   DBMS_OUTPUT.put_line (MOD (15, 9));
   DBMS_OUTPUT.put_line (REMAINDER (15, 9));
   DBMS_OUTPUT.put_line (MOD (15.55, 9));
   DBMS_OUTPUT.put_line (REMAINDER (15.55, 9));
END;

3
-1
6
-3
6.55
-2.45

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database