Oracle SQL - Select Arithmetic Functions

Introduction

The most popular arithmetic functions of Oracle are listed in the following table.

Function Description
ROUND(n[,m]) Round n on m decimal positions
TRUNC(n[,m]) Truncate n on m decimal positions
CEIL(n) Round n upward to an integer
FLOOR(n) Round n downward to an integer
ABS(n) Absolute value of n
SIGN(n) -1, 0, or 1 if n is negative, zero, or positive
SQRT(n) Square root of n
EXP(n) e ( = 2,7182813. . .) raised to the nth power
LN(n), LOG(m,n) Natural logarithm, and logarithm base m
POWER(n,m) n raised to the mth power
MOD(n,m) Remainder of n divided by m
SIN(n), COS(n), TAN(n) Sine, cosine, and tangent of n (n expressed in radians)
ASIN(n), ACOS(n), ATAN(n)Arcsine, arccosine, and arctangent of n
SINH(n), COSH(n), TANH(n)Hyperbolic sine, hyperbolic cosine, and hyperbolic tangent of n

The ROUND and TRUNC functions have an optional argument m; the default value for m is zero.

The following code shows some examples of using the following arithmetic functions: ROUND, CEIL, FLOOR, ABS, SIGN, POWER, and MOD.

Demo

SQL>
SQL> select round(345.678, 0), ceil(345.678), floor(345.678)
  2  from   dual;

ROUND(345.678,0) | CEIL(345.678) | FLOOR(345.678)
---------------- | ------------- | --------------
        00346.00 |      00346.00 |       00345.00

SQL>--  w ww. ja va2s  . co m
SQL> select round(345.678, 2)
  2  ,      round(345.678,-1)
  3  ,      round(345.678,-2)
  4  from   dual;

ROUND(345.678,2) | ROUND(345.678,-1) | ROUND(345.678,-2)
---------------- | ----------------- | -----------------
        00345.68 |          00350.00 |          00300.00

SQL>

Using the ABS and SIGN Functions

Demo

SQL>
SQL> select  abs(-123),  abs(0),  abs(456)
  2  ,       sign(-123), sign(0), sign(456)
  3  from   dual;

ABS(-123) |    ABS(0) |  ABS(456) | SIGN(-123) |   SIGN(0) | SIGN(456)
--------- | --------- | --------- | ---------- | --------- | ---------
 00123.00 |  00000.00 |  00456.00 |  -00001.00 |  00000.00 |  00001.00

SQL>-- from   ww  w. ja  va2  s. co  m

Using the POWER and MOD Functions

Demo

SQL>
SQL> select power(2,3), power(-2,3)
  2  ,        mod(8,3),   mod(13,0)-- from w  ww  .j a  v  a  2 s .c  o m
  3  from   dual;

POWER(2,3) | POWER(-2,3) |  MOD(8,3) | MOD(13,0)
---------- | ----------- | --------- | ---------
  00008.00 |   -00008.00 |  00002.00 |  00013.00

SQL>

Related Topics