This topic describes how to change values of mathematical functions that do not conform to common mathematical conventions, such as date and time.
The following table shows the available mathematical functions. Many of these functions are provided in multiple forms with different argument types. A function in each form returns the same data type as its argument. The functions that involve DOUBLE PRECISION data are mostly implemented on the top layer of the C library of the host system. The accuracy and behavior in boundary cases may vary based on the host system.
Function name | Return type | Description | Example | Result |
---|---|---|---|---|
ABS(x) | Same as X | Absolute value | ABS(-17.4) | 17.4 |
CEIL(DOUBLE PRECISION or NUMBER) | Same as input | Smallest integer not less than argument | CEIL(-42.8) | -42 |
EXP(DOUBLE PRECISION or NUMBER) | Same as input | Exponential | EXP(1.0) | 2.71828182845904 52 |
FLOOR(DOUBLE PRECISION or NUMBER) | Same as input | Largest integer not greater than argument | FLOOR(-42.8) | 43 |
LN(DOUBLE PRECISION or NUMBER) | Same as input | Natural logarithm | LN(2.0) | 0.69314718055994 53 |
LOG(b NUMBER, X NUMBER) | NUMBER | Logarithm to base b | LOG(2.0, 64.0) | 6.00000000000000 00 |
MOD(y, X) | Same as argument types | Remainder of y/x | MOD(9, 4) | 1 |
NVL(x, y) | Same as argument types; where both arguments are of the same data type | If X is null, then NVL returns y | NVL(9, 0) | 9 |
POWER(a DOUBLE PRECISION, b DOUBLE PRECISION) | DOUBLE PRECISION | a raised to the power of b | POWER(9.0, 3.0) | 729.000000000000 0000 |
POWER(a NUMBER, b NUMBER) | NUMBER | a raised to the power of b | POWER(9.0, 3.0) | 729.000000000000 0000 |
ROUND(DOUBLE PRECISION or NUMBER) | Same as input | Round to nearest integer | ROUND(42.4) | 42 |
ROUND(v NUMBER, s INTEGER) | NUMBER | Round to s decimal places | ROUND(42.4382, 2) | 42.44 |
SIGN(DOUBLE PRECISION or NUMBER) | Same as input | Sign of the argument (-1, 0, +1) | SIGN(-8.4) | -1 |
SQRT(DOUBLE PRECISION or NUMBER) | Same as input | Square root | SQRT(2.0) | 1.41421356237309 5 |
TRUNC(DOUBLE PRECISION or NUMBER) | Same as input | Truncate toward zero | TRUNC(42.8) | 42 |
TRUNC(v NUMBER, s INTEGER) | NUMBER | Truncate to s decimal places | TRUNC(42.4382, 2) | 42.43 |
WIDTH BUCKET( op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER) | INTEGER | Return the bucket to which op would be assigned in an equidepth histogram with count buckets, in the range b1 to b2 | WIDTH BUCKET(5.35, 0.024, 10.06, 5) | 3 |
The following table shows the available trigonometric functions. The arguments and return values of all trigonometric functions are of the DOUBLE PRECISION data type.
Function name | Description |
---|---|
ACOS(x) | Inverse cosine |
ASIN(x) | Inverse sine |
ATAN(x) | Inverse tangent |
ATAN2(x, y) | Inverse tangent of x/y |
COS(x) | Cosine |
SIN(x) | Sine |
TAN(x) | Tangent |