PLSQL | SQRT Function

In PL/SQL, the SQRT function is used to find the square root of a number. This function is really handy for various tasks that involve mathematical calculations, such as analyzing statistics, solving geometry problems, or handling financial data.

The SQRT function is easy to use and can simplify complex calculations right within your SQL queries and PL/SQL code. By using the SQRT function, developers can make their data processing more efficient and perform accurate mathematical operations directly in their database applications.

In this article, we will explore the SQRT function in PL/SQL with examples and discuss its advantages.

SQRT Function in PL/SQL

In PL/SQL, the SQRT function is used to compute the square root of a number. This mathematical function is helpful when you need to perform calculations that involve the square root, such as in statistical analysis, geometry, or financial calculations.

Syntax:

SQRT( number )

• number: The numeric value for which you want to calculate the square root. It must be a non-negative number.

Return Value:

This function returns a numeric number which is the square root of the given input number.

Supported Versions of Oracle/PLSQL are given below:

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

Examples of PLSQL SQRT Function

Suppose you want to calculate the square root of a number, say 16. You can use the SQRT function as follows:

Example 1

DECLARE
    result NUMBER;
BEGIN
    result := SQRT(16);
    DBMS_OUTPUT.PUT_LINE('The square root of 16 is: ' || result);
END;

Output:

The square root of 16 is: 4

Explanation: In this example, the square root of 16 is calculated to be 4, which is printed to the console.

Example 2

DECLARE 
   Test_Number number := 5.617;
   
BEGIN 
   dbms_output.put_line(SQRT(Test_Number number)); 
   
END; 

Output:

2.37002109695251

Explanation: In this example, the square root of 5.617 is calculated as approximately 2.37002109695251.

Example with Table Data

Let's say you have a table named 'numbers' with a column 'value' that contains various numbers. You want to calculate the square root for each number in the table.

Table Structure:

CREATE TABLE numbers (
    id NUMBER,
    value NUMBER
);

Inserting Data:

INSERT INTO numbers (id, value) VALUES (1, 25);
INSERT INTO numbers (id, value) VALUES (2, 9);
INSERT INTO numbers (id, value) VALUES (3, 4);

Query to Calculate Square Roots:

SELECT id, value, SQRT(value) AS square_root
FROM numbers;

Output:

ID | VALUE | SQUARE_ROOT
---|-------|------------
 1 | 25    | 5
 2 | 9     | 3
 3 | 4     | 2

Explanation: Here, the SQRT function is applied to each value in the numbers table, returning the square root for each entry. For example, the square root of 25 is 5, the square root of 9 is 3, and the square root of 4 is 2.

Handling Negative Numbers

The SQRT function in PL/SQL does not handle negative numbers and will return NULL for such values. If you attempt to compute the square root of a negative number, it will result in a NULL value.

Example

DECLARE
    result NUMBER;
BEGIN
    result := SQRT(-9);
    DBMS_OUTPUT.PUT_LINE('The square root of -9 is: ' || result);
END;

Output:

The square root of -9 is: 

Explanation: Since the square root of a negative number is not defined in the set of real numbers, the function returns 'NULL'.

Advantages of Using the SQRT Function

• Ease of Use: Simplifies the process of calculating square roots directly in SQL and PL/SQL.
  
• Integration: Can be seamlessly integrated into SQL queries and PL/SQL blocks, enhancing data processing capabilities.
  
• Versatility: Useful in a wide range of applications, from basic arithmetic to complex mathematical computations.
  
• Error Handling: Returns 'NULL' for negative inputs, which can be handled gracefully in your application logic.

Conclusion

The SQRT function in PL/SQL is a straightforward yet powerful tool for performing square root calculations. It is used to find the principal square root of a non-negative number. When working with real-world data, ensure that the input to the SQRT function is non-negative to avoid unexpected results. This function is beneficial in various scenarios, from basic arithmetic operations to more complex mathematical computations in database applications.