Describe the use of following oracle numeric functions with an example.
- Numeric functions accept numeric input and return numeric values.
- Most numeric functions that return NUMBER values that are accurate to 38 decimal digits.
- The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits.
- The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits.
The numeric functions are:1. ABS :Syntax:ABS(n)
ABS returns the absolute value of n.
Example:SELECT ABS(-13) "Absolute" FROM DUAL;
2. ACOS :Syntax:ACOS(n)
ACOS returns the arc cosine of n. The argument n must be in the range of -1 to 1, and the function returns a value in the range of 0 to pi,
expressed in radians.
Example:SELECT ACOS(.6)"Arc_Cos" FROM DUAL;
3. ASIN :Syntax:ASIN(n)
ASIN returns the arc sine of n. The argument n must be in the range of -1 to 1, and the function returns a value in the range of -pi/2 to pi/2, expressed in radians.
Example::SELECT ASIN(.6) "Arc_Sin" FROM DUAL;
4. ATAN :Syntax:ATAN(n)
ATAN returns the arc tangent of n. The argument n can be in an unbounded range and returns a value in the range of -pi/2 to pi/2, expressed in radians.
Example:SELECT ATAN(.4) "Arc_Tan" FROM DUAL;
5. ATAN2 :Syntax:ATAN2(n1;n2)
ATAN2 returns the arc tangent of n1 and n2. The argument n1 can be in an unbounded range and returns a value in the range of -pi to pi, depending on the signs of n1 and n2, expressed in radians. ATAN2(n1,n2) is the same as ATAN2(n1/n2).
Example:SELECT ATAN2(.6, .4) "Arc_Tan2" FROM DUAL;
6. BITAND :Syntax:BITAND(expr1,expr2)
BITAND computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative integers, and returns an integer. This function is commonly used with the DECODE function.
7. CEIL :Syntax:CEIL (n)
CEIL returns smallest integer greater than or equal to n.
Example:SELECT order_total, CEIL(total_order) FROM orders
WHERE order_id = 2564;
8. COS :Syntax:COS (n)
COS returns the cosine of n (an angle expressed in radians).
Example:SELECT COS(180 * 3.14159265359/180)
"Cosine of 180 degrees" FROM DUAL;
9. COSH :Syntax:COS (n)
COSH returns the hyperbolic cosine of n.
Example:SELECT COSH(1) "Hyperbolic cosine of 1" FROM DUAL;
10. EXP :Syntax:EXP(n)
EXP returns e raised to the nth power, where e = 2.71828183 ... The function returns a value of the same type as the argument.
Example:SELECT EXP(3) "e to the 3rd power" FROM DUAL;
11. FLOOR :Syntax:FLOOR(n)
FLOOR returns largest integer equal to or less than n.
Example:SELECT FLOOR(14.7) "Floor" FROM DUAL;
12. LN :Syntax:LN(n)
LN returns the natural logarithm of n, where n is greater than 0.
Example:SELECT LN(75) "Natural log of 75" FROM DUAL;
13. LOG:Syntax:LOG(n2,n1)
LOG returns the logarithm, base n2, of n1. The base n1 can be any positive value other than 0 or 1 and n2 can be any positive value.
Example:SELECT LOG(20,100) "Log base 20 of 100" FROM DUAL;
14. MOD :Syntax:MOD(n2,n1)
MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.
Example:SELECT MOD(12,3) "Modulus" FROM DUAL;
15. NANVL :Syntax:NANVL(n2,n1)
The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. It instructs Oracle Database to return an alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2. This function is useful for mapping NaN values to NULL.
Example:Insert INTO float_point_demo
VALUES (0,'NaN','NaN');
SELECT * FROM float_point_demo;
16. POWER :Syntax:POWER(n2,n1)
POWER returns n2 raised to the n1 power. The base n2 and the exponent n1 can be any numbers, but if n2 is negative, then n1 must be an integer.
Example:SELECT POWER(4,3) "Raised" FROM DUAL;
17. REMAINDER :Syntax:REMAINDER(n2,n1)
REMAINDER returns the remainder of n2 divided by n1.
Example:SELECT bin_float, bin_double, REMAINDER(bin_float, bin_double)
FROM float_point_demo;
18. ROUND (number) :Syntax:ROUND(n,integer)
ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.
Example:SELECT ROUND(12.196,2) "Round" FROM DUAL;
19. SIGN :Syntax:SIGN(n)
SIGN returns the sign of n. This function takes as an argument any numeric datatype, or any nonnumeric datatype that can be implicitly converted to NUMBER, and returns NUMBER.
Example:SELECT SIGN(-12) "Sign" FROM DUAL;
20. SIN :Syntax:SIN(n)
SIN returns the sine of n (an angle expressed in radians).
Example:SELECT SIN(20 * 3.14159265359/180)
"Sine of 20 degrees" FROM DUAL;
21. SINH :Syntax:SINH(n)
SINH returns the hyperbolic sine of n.
Example:SELECT SINH(3) "Hyperbolic sine of 3" FROM DUAL;
22. SQRT :Syntax:SQRT(n)
SQRT returns the square root of n.
Example:SELECT SQRT(24) "Square root" FROM DUAL;
23. TAN :Syntax:TAN(n)
TAN returns the tangent of n (an angle expressed in radians).
Example:SELECT TAN(90 * 3.14159265359/180)
"Tangent of 90 degrees" FROM DUAL;
24. TANH :Syntax:TANH(n)
TANH returns the hyperbolic tangent of n.
Example:SELECT TANH(.7) "Hyperbolic tangent of .7"
FROM DUAL;
25. TRUNC (number):Syntax:TRUNC(n1,n2)
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
Example:SELECT TRUNC(13.9,2) "Truncate" FROM DUAL;
26. WIDTH_BUCKET :Syntax:WIDTH_BUCKET(expr,min_value,max_value,num_buckets)
WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. (Compare this function with NTILE, which creates equiheight histograms.) Ideally each bucket is a closed-open interval of the real number line. For example, a bucket can be assigned to scores between 10.00 and 19.999... to indicate that 10 is included in the interval and 20 is excluded. This is sometimes denoted [10, 20]).
Example:SELECT customer_id, cust_last_name, credit_limit, WIDTH_BUCKET(credit_limit, 100, 5000, 10) "Credit Group" FROM customers WHERE nls_territory = 'SWITZERLAND'
ORDER BY "Credit Group";
