This topic describes the mathematical functions and operators that are supported by PolarDB for Oracle.
Mathematical operators are provided for many Oracle types. For types without standard mathematical conventions such as the date or time type, the actual behavior of these types are described in subsequent sections.
The following table describes the mathematical operators that are available for standard numeric types. Unless otherwise specified, operators shown as accepting numeric_type are available for the smallint, integer, bigint, numeric, real, and double precision types. Operators shown as accepting integral_type are available for the smallint, integer, and bigint types. Except where noted, each form of an operator returns the same data type as its argument. Calls involving multiple argument data types, such as integer + numeric, are resolved by using the type appearing later in these lists.
Mathematical operators
Addition
|
Unary plus (no operation)
|
Subtraction
|
Negation
|
Multiplication
|
Division (for integral types, division truncates the result towards zero)
|
Modulo (remainder); available for
|
Exponentiation (unlike typical mathematical practice, multiple uses of
|
Square root
|
Cube root
|
Factorial (deprecated and replaced with
|
Factorial that is used as prefix operator (deprecated and replaced with
|
Absolute value
|
Bitwise AND
|
Bitwise OR
|
Bitwise exclusive OR
|
Bitwise NOT
|
Bitwise shift left
|
Bitwise shift right
|
The following table describes the available mathematical functions. Many of these functions are provided in multiple forms with different argument types. Except where noted, any given form of a function returns the same data type as its argument. Cross-type cases are resolved in the same way as explained above for operators. The functions working with double precision data are mostly implemented on the top of the C library in the host system. Therefore, accuracy and behavior in boundary cases vary based on host system.
Mathematical functions
Absolute value
|
Cube root
|
Nearest integer greater than or equal to the argument
|
Nearest integer greater than or equal to the argument (same as
|
Convert radians to degrees
|
Integer quotient of
|
Exponential (
|
Factorial
|
Nearest integer less than or equal to the argument
|
Greatest common divisor (the largest positive number that divides both inputs without remainder). This function returns
|
Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs). This function returns
|
Natural logarithm
|
Base 10 logarithm
|
Base 10 logarithm (same as
|
Logarithm of
|
Minimum scale (number of fractional decimal digits) required to represent the given value precisely
|
Remainder of
|
Approximate value of π
|
|
Convert degrees to radians
|
Round to nearest integer
|
Round
|
Scale of the argument (number of decimal digits in the fractional part)
|
Sign of the argument (-1, 0, or +1)
|
Square root
|
Reduce the scale of the argument (number of fractional decimal digits) by removing trailing zeroes
|
Truncate to integer (towards zero)
|
Truncate
|
Return the number of the bucket in which
|
Return the number of the bucket in which
|
The following table describes the functions that are used to generate random numbers.
Random functions
Return a random value that is greater than or equal to 0.0 but less than 1.0.
|
Set the seed value for subsequent calls on the
|
The random() function uses a simple linear conjugate algorithm. It is fast but not suitable for cryptography applications. For more information about a more secure alternative, see the pgcrypto module. If the setseed() function is called, results returned for a series of subsequent calls on the random() function within the current session can be replicated by re-issuing setseed() with the same argument.
The following table describes the available trigonometric functions. Each of these functions comes in two variants. One variant measures angles in radians, and the other measures angles in degrees.
Trigonometric functions
Inverse cosine, result in radians
|
Inverse cosine, result in degrees
|
Inverse sine, result in radians
|
Inverse sine, result in degrees
|
Inverse tangent, result in radians
|
Inverse tangent, result in degrees
|
Inverse tangent of
|
Inverse tangent of
|
Cosine, argument in radians
|
Cosine, argument in degrees
|
Cotangent, argument in radians
|
Cotangent, argument in degrees
|
Sine, argument in radians
|
Sine, argument in degrees
|
Tangent, argument in radians
|
Tangent, argument in degrees
|
Another way to work with angles measured in degrees is to use the unit transformation functions shown earlier: radians() and degrees(). However, to avoid round-off error for special cases such as sind(30), we recommend that you use degree-based trigonometric functions.
The following table describes the available hyperbolic functions.
Hyperbolic functions
Hyperbolic sine
|
Hyperbolic cosine
|
Hyperbolic tangent
|
Inverse hyperbolic sine
|
Inverse hyperbolic cosine
|
Inverse hyperbolic tangent
|
PEMAINDER
Description
This function is used to return the remainder of n1/n2.
Syntax
PEMAINDER(n1,n2)Parameters
Parameter | Description |
n1 | The dividend, which is an expression for the NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE numeric types. |
n2 | The divisor, which is an expression for the NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE numeric types and cannot be 0. |
Data type of return values
The return type is the same as the data type of the argument that has higher numeric precedence.
Examples
SELECT REMAINDER(3.5,1);
remainder
-----------
-0.5