11.15 Numeric Functions

[ Team LiB ]

11.15 Numeric Functions The following includes common numeric functions you can use in PL/SQL programs. ABS Returns the absolute value. The argument is any expression that evaluates to a number. var := ABS(var_1 - 100); MOD Returns the remainder following division. The following returns 3. MOD(11,4); ROUND The ROUND function rounds a number or a date. You can specify the decimal place for the rounding. Use a second parameter to indicate the degree of rounding. Positive means round that many places to the right. Negative means round that many places to the left of the decimal point. The following includes some rounding examples. ROUND(199.11); -- rounds to 200 ROUND(199.11, 1); -- rounds to 199.1 ROUND(199.125, 2); -- rounds to 199.13 ROUND(249.11, -2); -- rounds to 200 ROUND(250.11, -2); -- rounds to 300 SIGN Returns �1, 0, or +1 based on the sign of the expression. The following returns a �1. SIGN(2-5+4*20-100) SQRT Returns the square root of a number. The following returns: 1.4142136. SQRT(2) TRUNC Truncates a number or a date. You can specify the decimal position for truncation. Similar to rounding, you have an optional parameter that is negative or positive. The following are examples. TRUNC(199.99); -- returns 199 TRUNC(199.99,1); -- returns 199.9 TRUNC(199.125,2); -- returns 199.12 TRUNC(249.11,-2); -- returns 200 TRUNC(299.11,-2); -- returns 200 CEIL Returns the smallest integer greater than or equal to the argument, which is a number. A common numeric calculation is to round up nonwhole numbers. To round up a number with a fraction, you add (0.5) and truncate the number. CEIL does this for you. CEIL(3.0); -- returns 3 CEIL(3.1); -- returns 4 CEIL(3.6); -- returns 4 CEIL(-3.0); -- returns -3 CEIL(-3.1); -- returns -3 FLOOR Returns the largest integer less than or equal to the argument, which is a number. FLOOR(-3.1); -- returns -4 FLOOR(-3.0); -- returns -3 FLOOR(3.1); -- returns 3 FLOOR(3.6); -- returns 3 POWER Returns a number, raised to this power. POWER(2,10) = 1024 LOG(A,X) Common logarithm. This answers the question: A value "A," raised to the power of what is equal to "X"? The LOG functions returns the what. IF A**B=X then LOG(A,X)=B POWER(2,10) returns 1024 POWER(4,3) returns 64 LOG(2,1024) returns 10 LOG(4,64) returns 3 EXP(X) Exponential function of X. LN(X) Natural logarithm of X. EXP(1) = 2.71828183 LN(2.71828183) = 1 COS(X) Cosine of X. COSH(X) Arccosine of X. SIN(X) Sine of X. SINH(X) Arcsine of X. TAN(X) Tangent of X. TANH(X) Arctangent of X. An example of a Pythagorean relation is: For any number X: POWER(SIN(X),2) + POWER(COS(X),2) = 1

[ Team LiB ]