Oracle PL/SQL Programming, Third Edition

Name

ABS

Synopsis

The ABS function returns the absolute value of the input. The specification for the ABS function is:

FUNCTION ABS (n NUMBER) RETURN NUMBER;

The ABS function can help simplify your code logic. For example, in one program we reviewed, line items and amounts for a profit and loss statement were footed or balanced. If the variance on the line amount was greater than $100, either positive or negative, that line item was flagged as “in error.” The first version of the code that implemented this requirement looked like this (variance_table is a PL/SQL table holding the variance for each line item):

IF variance_table (line_item_nu) BETWEEN 1 AND 100 OR
   variance_table (line_item_nu) BETWEEN -100 AND -1 
THEN
   apply_variance (statement_id);
ELSE
   flag_error (statement_id, line_item_nu);
END IF;

There are two ways to express this logic. First, instead of hardcoding the maximum allowable variance, we put the value in a named constant. Second, we use ABS so that we perform the range check only once. With these changes, the above code can be rewritten as follows:

IF ABS (variance_table (line_item_nu)) 
   BETWEEN min_variance AND max_variance 
THEN
   apply_variance (statement_id);
ELSE
   flag_error (statement_id, line_item_nu);
END IF;