SCALAR function in SQL

SQL Scalar Functions are the user-defined functions that allow you to perform different calculations on data values. These functions operate on single rows only and produce one result per row. There are different types of Scalar Functions. They are as follows:

Except for aggregate functions, all the scalar function uses the table called DUAL table. The DUAL table is a table that is automatically created by Oracle Database with the data dictionary. This table is in the user SYS schema but it is available by the 'DUAL' name to all users.

The DUAL table has one column and one row known as DUMMY which is defined to be VARCHAR2(1). This is in the 1*1 format matrix to represent the value of x in the output.

Let us have a look on the DUAL table.

SELECT * FROM DUAL;

DUAL-TABLE

NUMERIC functions in Oracle SQL

Numeric functions manipulate the numeric input values. The return type for numeric functions is NUMBER.

Some of the most commonly used NUMERIC functions are as follows.

  • TRUNC
  • ROUND
  • CEIL
  • POWER
  • SQRT

Now let us understand the above NUMERIC functions one by one.

This function is used to truncate or reduce the decimal place.

Syntax of TRUNC() function :

SELECT TRUNC(n1 [, n2 ]) FROM DUAL;
  • n1: A number to be truncated up to n2 decimal places.
  • n2: A number indicating up to how many decimal places, n1 is to be truncated.

Example of TRUNC() function :

SELECT TRUNC(10.375,1) FROM DUAL;

trunc()-func
In the above query, the n1 is truncated up to the first decimal.

Example of the TRUNC() function where n2 is omitted :

SELECT TRUNC(12.96) FROM DUAL;

trunc()-n2-omit
In the above query, the n2 is omitted. So, n1 is truncated to 0 places of its decimal place.

Example of the TRUNC() function where n2 is negative :

SELECT TRUNC(12.96,-2.7) FROM DUAL;

TRUNC()-NEGATIVE
In the above query, the n1 is omitted because the n2 is negative. So the result is 0.

Example of the TRUNC() function where n1 and n2 are negative :

SELECT TRUNC(-12.96,-2.7) FROM DUAL;

In the below query, the n1 and n2 are in the negative integer. If the n2 is negative, the output is 0.

trunc-func-negative

ROUND() function in Oracle SQL:

The ROUND() returns n(number) rounded value to an integer place to the right of its decimal point. If you neglect the integer before the decimal point, then n is rounded to 0 places.

Syntax of the ROUND() function :

SELECT ROUND( number [, decimal_places] ) FROM DUAL;

Example of the ROUND() function :

SELECT ROUND(10.4733,1) FROM dual;

round()-2
In the above query, the statement will round the given number 10.4733 up to 1 decimal place.

Example of the ROUND() function when the n2 is more than the number of decimal place of n1 :

SELECT ROUND(10.4733,7) FROM dual;

round()-1
In the above query, the value of the decimals doest change when the n2 value is higher than the decimal place of n1.

Example of the ROUND() function when n2 omitted and also decimal value is higher than .5 :

SELECT ROUND(10.7) FROM dual;

ROUND()
In the above example, the output is retrieved when the decimal value is .5 or more than .5.

Example of the ROUND() function when n2 omitted and also decimal value is lesser than .5 :

SELECT ROUND(10.4) FROM dual;

round()-func
In the above example, the output is retrieved when the decimal value is .4 or less than .4.

CEIL() function in Oracle SQL:

The CEIL() function always returns the immediate next integer for the number specified in an argument. This function takes any numeric or non-numeric data type as an argument.

The CEIL() function always returns in the whole number.

Syntax of CEIL() function :

SELECT CEIL(n) FROM DUAL;

where n is the value of the number.

Example of CEIL() function :

SELECT CEIL (10.0001) FROM dual;

ceil-func
In the above query, the statement returns 11 which is the immediate value of the specified number (10.0001) in the argument.

Example of CEIL() function when the n value is negative :

SELECT CEIL (-10.0001) FROM dual;

CEIL-NEGATIVE
In the above query, the statement returns -10 which doesn't return the decimal value (-10.0001) in the argument.

POWER () returns the first number, raised to the power of the second number.

Syntax of POWER() function :

SELECT POWER(n,p) FROM DUAL;
  • n: An integer number.
  • p: The value of the power value.

Example of POWER() function :

SELECT POWER(5,2) FROM DUAL;

POWER()

SQRT() function in Oracle SQL:

This function gives the square root of the given number.

Syntax of SQRT() function :

SELECT SQRT(n) FROM DUAL;

where n is the number that to be square rooted.

Example of SQRT() function :

SELECT SQRT(9) FROM DUAL;

SQRT()

    TO_NUMBER() conversion function in Oracle SQL:

    Conversions functions are used to convert from one data type to another type. The TO_NUMBER function converts a string value to a number in order to the specified format.

    Syntax of TO_NUMBER() function in SQL:

    TO_NUMBER( input_value, expr_format)

    Some of the examples of the TO_NUMBER() function:

    • Example-1:
      SELECT TO_NUMBER('175.12')
      FROM DUAL;

    to-number
    The above query converts a simple string to a number value.

    • Example 2:
      SELECT TO_NUMBER('1234', '99999')
      FROM DUAL;

      to-number-2

    The above query converts a number that has no decimal places in it.

    • Example 3:
      SELECT TO_NUMBER('$123.44', 'L999.99')
      FROM DUAL;

    to-number-3
    The above query converts the number from the dollar sign as a currency figure.

    0 results
    Comment / Suggestion Section
    Point our Mistakes and Post Your Suggestions