Math Functions in PL/SQL

Last Updated : 01 Oct, 2024

In PL/SQL, mathematical functions play a important role in performing calculations and manipulating numeric data. These functions allow us to execute a wide range of mathematical operations from basic arithmetic to complex computations within our PL/SQL code.

In this article, we will learn about Math Functions in PL/SQL by performing examples from basic arithmetic to complex computations and so on.

PL/SQL Math Functions

PL/SQL provides various built-in mathematical functions that can be utilized for various purposes including data analysis, reporting and application logic. Let's learn some of the most commonly used math functions in PL/SQL.

Key Points:

Versatility: Math functions can handle integers, decimals, and other numeric data types.
Use Cases: These functions are useful in calculations involving statistics, financial operations, and scientific computations.
Precision: Some functions allow us to specify precision and scale, ensuring accuracy in results.

Common Math Functions

1. ABS Function

The ABS function returns the absolute value of a number.

SELECT ABS(-15) AS absolute_value FROM dual;

Output:

ABSOLUTE_VALUE
--------------
15

Explanation: The ABS(-15) function returns 15, which is the absolute value of -15.

2. CEIL Function

The CEIL function rounds a number up to the nearest integer.

SELECT CEIL(15.2) AS ceiling_value FROM dual;

Output:

CEILING_VALUE
-------------
16

Explanation: CEIL(15.2) rounds 15.2 up to the next integer, which is 16.

3. FLOOR Function

The FLOOR function rounds a number down to the nearest integer.

SELECT FLOOR(15.8) AS floor_value FROM dual;

Output:

FLOOR_VALUE
-----------
15

Explanation: FLOOR(15.8) rounds 15.8 down to 15.

4. ROUND Function

The ROUND function rounds a number to a specified number of decimal places.

SELECT ROUND(15.678, 2) AS rounded_value FROM dual;

Output:

ROUNDED_VALUE
-------------
15.68

Explanation: ROUND(15.678, 2) rounds the number to two decimal places, resulting in 15.68.

5. MOD Function

The MOD function returns the remainder of a division operation.

SELECT MOD(10, 3) AS remainder FROM dual;

Output:

REMAINDER
---------
1

Explanation: MOD(10, 3) performs the division of 10 by 3 and returns the remainder, which is 1.

6. POWER Function

The POWER function raises a number to the power of another number.

SELECT POWER(2, 3) AS power_value FROM dual;

Output:

POWER_VALUE
-----------
8

Explanation: POWER(2, 3) raises 2 to the power of 3, resulting in 8.

7. SQRT Function

The SQRT function returns the square root of a number.

SELECT SQRT(16) AS square_root FROM dual;

Output:

SQUARE_ROOT
-----------
4

Explanation: SQRT(16) returns the square root of 16, which is 4.

8. EXP Function

The EXP function returns e raised to the power of a given number.

SELECT EXP(1) AS exp_value FROM dual;

Output:

EXP_VALUE
---------
2.71828183

Explanation: EXP(1) returns the value of e (approximately 2.7183) raised to the power of 1.

9. LN Function

The LN function returns the natural logarithm of a number.

SELECT LN(10) AS natural_log FROM dual;

Output:

NATURAL_LOG
-----------
2.30258509

Explanation: LN(10) calculates the natural logarithm of 10, which is approximately 2.3026.

10. LOG Function

The LOG function returns the logarithm of a number to a specified base.

SELECT LOG(100, 10) AS log_value FROM dual;

Output:

LOG_VALUE
---------
2

Explanation: LOG(100, 10) returns 2, because 10^2 equals 100.

Example using Math Functions in a PL/SQL Block

Let's say we have a scenario where we need to calculate the average score of students from a Scores table. We'll use various math functions to achieve this.

DECLARE
    v_average_score NUMBER;          -- Variable to hold the average score
    v_max_score NUMBER;              -- Variable to hold the maximum score
    v_min_score NUMBER;              -- Variable to hold the minimum score
BEGIN
    -- Calculate average, max, and min scores using math functions
    SELECT AVG(score), MAX(score), MIN(score)
    INTO v_average_score, v_max_score, v_min_score
    FROM Scores;

    -- Display the results
    DBMS_OUTPUT.PUT_LINE('Average Score: ' || ROUND(v_average_score, 2));
    DBMS_OUTPUT.PUT_LINE('Maximum Score: ' || v_max_score);
    DBMS_OUTPUT.PUT_LINE('Minimum Score: ' || v_min_score);
END;

Explanation:

The above block declares variables to hold average, maximum, and minimum scores.
It uses the AVG, MAX, and MIN aggregate functions to compute scores from the Scores table.
Finally, it outputs the results using DBMS_OUTPUT.

Conclusion

In this article, we have explored various math functions available in PL/SQL that allow us to perform arithmetic and statistical operations on numeric data. Understanding these functions can significantly enhance our ability to manipulate and analyze data within our Oracle databases.

Mathematical functions in MySQL

mishraaabcf9

Improve

Article Tags :

Math Functions in PL/SQL

PL/SQL Math Functions

Common Math Functions

1. ABS Function

2. CEIL Function

3. FLOOR Function

4. ROUND Function

5. MOD Function

6. POWER Function

7. SQRT Function

8. EXP Function

9. LN Function

10. LOG Function

Example using Math Functions in a PL/SQL Block

Conclusion

Similar Reads