Single Row Function-Numeric Function

All Numeric Function accept numeric value and return numeric value.

Some Important Numeric Function are
1. ABS function
2. POWER function
3. SQRT function
4. SIGN function
5. ROUND function
6. TRUNC function
7. FLOOR function

8. CEIL function
9. MOD function
10. REMAINDER function
etc.

Oracle SQL/PLSQL : ABS function
Syntax : ABS(n)

Purpose
To find absolute value of a numeric data type or non numeric data type that can implicitly converted to numeric data type.

Parameter
ABS function take single Numeric input parameter.

Return Data Type
ABS function return same data type as input parameter(n).

Example
```
SELECT 'The absolute value of -19 is '||ABS(-19)
FROM dual
'THEABSOLUTEVALUEOF-19IS'||ABS(
-------------------------------
The absolute value of -19 is 19
1 row selected. 
```
Oracle SQL/PLSQL : POWER function
Syntax : POWER(n,m)

Purpose
Power function is used to find n^m .

Parameter
Power function take two Numeric input parameter n base and m exponent. n and m can be any numeric data type.
But if base i.e n is negative numeric value then m must be Integer.
Return Data Type
POWER function return NUMBER , But if any argument i.e. either n or m is BINARY_FLOAT or BINARY_DOUBLE then POWER function return BINARY_DOUBLE

Example
```
SELECT POWER(4,2) "4^2"
  FROM DUAL;

    4^2
----------
         16
```
```
1 row selected. 
```
Oracle SQL/PLSQL : SQRT function
Syntax : SQRT(n)

Purpose
SQRT funtion find square root of n.

Parameter
SQRT function take single Numeric input parameter or non numeric parameter which is implicitly converted to numeric data type.

Return Data Type
SQRT function return same data type as input parameter(n).
If n resolves to a NUMBER, then the value n cannot be negative. SQRT returns a real number.

If n resolves to a binary floating-point number (BINARY_FLOAT or BINARY_DOUBLE):

If n >= 0, then the result is positive.
If n = -0, then the result is -0.
If n < 0, then the result is NaN. Example
```
SELECT SQRT(26) "Square root" FROM DUAL;

Square root
-----------
5.09901951 
```
Oracle SQL/PLSQL : SIGN function
Syntax : SIGN(n)

Purpose
SIGN function is used to find that the input parameter is Positive or negative.

Parameter
SIGN function take single Numeric or Non-Numeric parameter which is implicitly converted to Numeric data type.

Return Data Type

Sign Function Return -1 if n<0Sign Function Return 0 if n=0Sign Function Return 1 if n>0
For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), SIGN function returns the sign bit of the number. The sign bit is:

-1 if n<0
+1 if n>=0 or n=NaN.

Example

The following example indicates that the argument of the function (-5) is is less than 0:
```
SELECT SIGN(-5) "Sign" FROM DUAL;

      Sign
----------
        -1
```
.

Oracle SQL/PLSQL : ROUND function

Syntax : ROUND(value,[precision])

Purpose

ROUND function round the value according to precision.

For Example

SELECT ROUND(105.59356,2) "round", ROUND(105.59356,3) "round1",ROUND(105.59356,4) "round2",

ROUND(105.59356,1) "round3" from dual;

round     round1     round2     round3
---------- ---------- ---------- ----------
    105.59    105.594   105.5936      105.6

If precision is not present then oracle consider precision value is 0 and it round the number before decimal place.

 For Example

 SELECT ROUND(105.59356,0) "round", ROUND(104.59356,0) "round1",

ROUND(105.49356,0) "round2" from dual;

 round     round1     round2     
---------- ---------- ---------- 
       106        105        105

if precision is negative then it round number before decimal places.

For Example

SELECT ROUND(105.59,-1) "round", ROUND(104.59,-2) "round1",

ROUND(145.49356,-2) "round2",ROUND(155.59,-2) "round3" from dual;

      round     round1     round2     round3
---------- ---------- ---------- ----------
       110        100        100        200

If n is negative then Round function return -Round(value,[precision])

SELECT ROUND(-105.59,1) "round", ROUND(-104.59,-2) "round1" from dual;

round     round1
---------- ----------
    -105.6       -100

Oracle SQL/PLSQL : TRUNC function

Syntax : TRUNC(n,[precision])

Purpose
TRUNC function truncate column expression or value (n) according to given precision.
If Precision is omitted then default Precision will be 0.

For Example

SELECT TRUNC(105.5945,2) "t1",TRUNC(105.5945,0) "t2",
TRUNC(105.5945,-1) "t3",TRUNC(-105.5945,2) "t4"  from DUAL;

 t1         t2         t3         t4
---------- ---------- ---------- ----------
    105.59        105        100    -105.59

Oracle SQL/PLSQL : FLOOR function
Syntax : FLOOR(n)

Purpose
FLOOR function return greatest Integer which is smaller than or equal to number.

Parameter
FLOOR function take single Numeric DATA Type.

Return Data Type
FLOOR function return same data type as input parameter(n).

Example
```
SELECT FLOOR(15.84) "f1", FLOOR(14.45) "f2" from DUAL;
```
```
  
```
```
        f1         f2
---------- ----------
        15         14
  
```
Oracle SQL/PLSQL : CEIL function
Syntax : CEIL(n)

Purpose
CEIL function return Smallest Integer which is greater than or equal to number.

Parameter
CEIL function take single Numeric DATA Type.

Return Data Type
CEIL function return same data type as input parameter(n).

Example
```
SELECT CEIL(15.84) "C1", CEIL(14.45) "C2" from DUAL;
```
```
 
```
```
          C1         C2
---------- ----------
        16         15
```
Oracle SQL/PLSQL : MOD function
Syntax : MOD(n,m)

Purpose
MOD function return remainder of n divided by m.

Parameter
MOD function take Numeric DATA Type of parameter.

Return Data Type
MOD function return same data type as input parameter

Example
```
SELECT MOD(13,2) from DUAL;
```
```
 
```
```
 MOD(13,2)
----------
         1
```
Oracle SQL/PLSQL : REMAINDER function
Syntax : REMAINDER(n,m)

Purpose
Remainder function return remainder of n divided by m.

Parameter
REMAINDER function take Numeric Data type of parameter.

Return Data Type
REMAINDER function return same data type as input parameter.

Example
SELECT REMAINDER(13,2), MOD(13,2) from DUAL;

REMAINDER(13,2) MOD(13,2)
--------------------------------------- ----------
1 1

Free tutorial | Learn Core Java, Advance Java, Struts 2.X, Spring, Hibernate, Oracle Sql/PLSQL FREE

Pages

Monday, January 4, 2016