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.
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 . jav a2 s.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
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>-- w w w . j ava2 s. c o m
Using the POWER and MOD Functions
SQL> SQL> select power(2,3), power(-2,3) 2 , mod(8,3), mod(13,0)-- from www . j av a2 s . co 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>