PLSQL mod

Introduction to PLSQL mod

MOD function is an in-built function in PL/SQL that is used to perform the MOD operation and returns the remainder obtained by dividing the num1 with num2. These numbers (num1 and num2) are passed as parameters in the MOD function. It takes both numeric and non-numeric values which are implicitly calculated as numeric values. MOD function in PL/SQL internally performs the FLOOR function in order to obtain the exact result. It returns the result as the same data type as the data type of parameters passed in the MOD function. It is quite useful in performing mathematical calculations.

Syntax:

Below given is the basic syntax of using the mod function in PL/SQL :

MOD (num1, num2)

where,

Parameters: MOD function takes 2 parameters which are num1 and num2 and returns the remainder as an output of num1 dividing by num2.

num1: Numeric value used for calculation. It is basically a dividend.

num2: Numeric value used for calculation. It is basically a divisor.

Return Value: Return value of the MOD function is the remainder obtained on dividing the num1 with num2.

How does PL/SQL mod work?

Below given are some of the points showing how the mod function works in PL/SQL:

Internally MOD of two numbers is calculated as follows:

num1- num2 * floor (num1/ num2)

MOD function in PL/SQL uses the Floor function internally.

MOD function returns the num1 if num2 is input as 0.

MOD function always returns the numerical value.

MOD functions can take the numeric as well as non-numeric data types if they are internally calculated as numeric types only.

The MOD function in PL/SQL behaves differently from the classical modulus function in the case of negative values of num1 and num2.

MOD function in PL/SQL returns the negative value only if the num1 is negative. Otherwise, it does not care if num2 is negative or positive.

FLOOR and ROUND functions in PL/SQL are almost similar to the MOD function but they use the ROUND instead of the FLOOR function internally.

The MOD function in PL/SQL also works on the fractional values and returns the exact remainder.

The MOD function returns the same data type as the result of the argument. But if the argument is BINARY_FLOAT, it returns the BINARY_DOUBLE data type.

Some of the version of Oracle supporting the MOD function in PL/SQL are:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Examples

Some of the examples showing the usage of mod function in PL/SQL are given below:

Example #1

Code:

DECLARE
Mod_number num1 := 33;
Mod_number num2 := 2;
BEGIN
dbms_output.put_line(MOD(Mod_number num1,
Mod_number num2));
END;

Output:

Explanation:

In the above PL/SQL code, Mod_number num1 and Mod_number num2 whose MOD needs to be calculated are given as integer values whose values are 33 and 2 respectively. In the above code, num1 (dividend) and num2 (divisor) is a non-negative number, so calculating the MOD by dividing the 33 with 2, returns the value 1 which is printed as an output to the user on the console.

Example #2

Code:

DECLARE
Mod_number num1 := -33;
Mod_number num2 := 2;
BEGIN
dbms_output.put_line(MOD(Mod_number num1,
Mod_number num2));
END;

Output:

Explanation:

In the above PLSQL code, Mod_number num1 and Mod_number num2 whose MOD needs to be calculated are given as integer values in which num1 is a negative number. The values of the numbers are -33 and 2 respectively. As mentioned above, the MOD function returns the result of negative and positive values on the basis of num1 (Which is a dividend). So in the above code, num1 (dividend) is a negative number, so calculating the MOD by dividing the -33 with 2, returns the value 1 which is printed as an output to the user on the console.

Example #3

Code:

DECLARE
Mod_number num1 := 13;
Mod_number num2 := 0;
BEGIN
dbms_output.put_line(MOD(Mod_number num1,
Mod_number num2));
END;

Output:

Explanation:

In the above PLSQL code, Mod_number num1 and Mod_number num2 whose MOD needs to be calculated are given as integer values in which the values of num1 and num2 are 13 and 0 respectively. As mentioned above, the MOD function returns the result as num1 if the value of num2 is 0. So in the above code, the value of num2 (divisor) is 0, so calculating the MOD by dividing the 13 with 0, returns the value 13 which is printed as an output to the user on the console.

Example #4

Code:

DECLARE
Mod_number num1 := -30;
Mod_number num2 := -7;
BEGIN
dbms_output.put_line(MOD(Mod_number num1,
Mod_number num2));
END;

Output:

Explanation:

In the above PLSQL code, Mod_number num1 and Mod_number num2 whose MOD needs to be calculated are given as integer values in which both the num1 and num2 is a negative number. The values of the numbers are -30 and -7 respectively. As mentioned above, the MOD function returns the result of negative and positive values on the basis of num1 (Which is a dividend). So in the above code, num1 (dividend) is a negative number, so calculating the MOD by dividing the -30 with -7, returns the value -2 which is printed as an output to the user on the console.

Example #5

Code:

DECLARE
Mod_number num1 := 30;
Mod_number num2 := -7;
BEGIN
dbms_output.put_line(MOD(Mod_number num1,
Mod_number num2));
END;

Output:

Explanation:

In the above PLSQL code, Mod_number num1 and Mod_number num2 whose MOD needs to be calculated are given as integer values in which num2 is a negative number. The values of the numbers are 30 and -7 respectively. As mentioned above, the MOD function returns the result of negative and positive values on the basis of num1 (Which is a dividend). So in the above code, num1 (dividend) is a non-negative number, so calculating the MOD by dividing the 30 with -7, returns the value 2 which is printed as an output to the user on the console.

Example #6

Code:

DECLARE
Mod_number num1 := 10.5;
Mod_number num2 := 3.3;
BEGIN
dbms_output.put_line(MOD(Mod_number num1,
Mod_number num2));
END;

Output:

Explanation:

In the above PLSQL code, Mod_number num1 and Mod_number num2 whose MOD needs to be calculated is given as float values which are 10.5 and 3.3 respectively. As mentioned above, the MOD function works on the float value as well and gives the exact remainder. So MOD is calculated by dividing the 10.5 with 3.3, and the float value 0.6 is printed as an output to the user on the console.

Conclusion – PLSQL mod

The above description clearly explains what the mod function is and how it works in PLSQL. Mod is an inbuilt function and is widely used in programs in order to perform mathematical calculations. It is important for a programmer to understand it clearly in order to use it well in the programs.

Recommended Articles

This is a guide to PLSQL mod. Here we discuss How does PL/SQL mod work and Examples along with the codes and outputs. You may also have a look at the following articles to learn more –

1. Loops in PL/SQL
2. PL/SQL Data Types
3. Triggers in PL/SQL
4. PLSQL Interview Questions