New Document
Computer Science
Computer Catlog
Oracle - TOC
  1   2   3   4   5   page(3/5)


Arithmetic Functions


    Many of the uses you have for the data you retrieve involve mathematics. Most implementations of SQL provide arithmetic functions similar to the functions covered here. The examples in this section use the NUMBERS table:

ABS


    The ABS function returns the absolute value of the number you point to. ABS changes all the negative numbers to positive and leaves positive numbers alone. For example:

INPUT:

SQL> SELECT ABS (A) ABSOLUTE-VALUE FROM NUMBERS;


CEIL and FLOOR


    CEIL returns the smallest integer greater than or equal to its argument. FLOOR does just the reverse, returning the largest integer equal to or less than its argument. For example:

INPUT:

SQL> SELECT B, CEIL (B) CEILING FROM NUMBERS;

INPUT:

SQL> SELECT A, FLOOR (A) FLOOR FROM NUMBERS;

COS, COSH, SIN, SINH, TAN, and TANH


    The COS, SIN, and TAN functions provide support for various trigonometric concepts. They all work on the assumption that n is in radians. The following statement returns some unexpected values if you don't realize COS expects A to be in radians.

INPUT:

SQL> SELECT A, COS (A), COSH (A*0.017453), SIN (A*0.017453),
SINH (A*0.017453), TAN (A*0.017453), TANH (A*0.017453)
FROM NUMBERS;

ANALYSIS:

    You would expect the COS of 45 degrees to be in the neighborhood of .707, not .525. To make this function work the way you would expect it to in a degree-oriented world, you need to convert degrees to radians. (When was the last time you heard news broadcast report that a politician had done a pi-radian turn? You hear about a 180-degree turn.). Because 360 degrees = 2 pi radians.

EXP


    EXP enables you to raise e (e is a mathematical constant used in various formulas) to a power. Here's how EXP raises e by the values in column A:

INPUT:

SQL> SELECT A, EXP (A) FROM NUMBERS;

LN and LOG


    These two functions center on logarithms. LN returns the natural logarithm of its argument which should be positive input. For example:

INPUT:

SQL> SELECT A, LN (ABS (A)) FROM NUMBERS;

ANALYSIS:

    Notice how you can embed the function ABS inside the LN call. The other logarithmic function, LOG, takes two arguments, returning the logarithm of the first argument in the base of the second. The following query returns the logarithms of column B in base 10.

INPUT:

SQL> SELECT B, LOG (B, 10) FROM NUMBERS;


MOD


    Here's a query that returns a table showing the remainder of A divided by B:

INPUT:

SQL> SELECT A, B, MOD (A, B) FROM NUMBERS;

POWER


    To raise one number to the power of another, use POWER. In this function the first argument is raised to the power of the second: Therefore, if the first number in the POWER function is negative, the second must be an integer. You can work around this problem by using CEIL (or

INPUT:

SQL> SELECT A, CEIL (B), POWER (A, CEIL(B)) FROM NUMBERS;

SIGN


    SIGN returns -1 if its argument is less than 0, 0 if its argument is equal to 0 and 1 if its argument is greater than 0. You could also use SIGN in a SELECT WHERE clause.

INPUT:

SQL> SELECT A, SIGN (A) FROM NUMBERS;

INPUT:

SQL> SELECT A FROM NUMBERS WHERE SIGN (A) = 1;

SQRT


    The function SQRT returns the square root of an argument. Because the square root of a negative number is undefined, you cannot use SQRT on negative numbers.

INPUT:

SQL> SELECT ABS (A), SQRT (ABS (A)) FROM NUMBERS;
  1   2   3   4   5   page(3/5)



Google