The following sections describe each of the PL/SQL numeric functions.
| ABS |
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;| ACOS |
The ACOS function returns the inverse cosine. The specification for the ACOS function is:
FUNCTION ACOS ( n NUMBER) RETURN NUMBER;where the number n must be between -1 and 1, and the value returned by ACOS is between 0 and 
.
| ASIN |
The ASIN function returns the inverse sine. The specification for the ASIN function is:
FUNCTION ASIN ( n NUMBER) RETURN NUMBER;where the number n must be between -1 and 1, and the value returned by ASIN is between - /2 and /2.
| ATAN |
The ATAN function returns the inverse tangent. The specification for the ATAN function is:
FUNCTION ATAN ( n NUMBER) RETURN NUMBER;where the number n must be between -infinity and infinity, and the value returned by ATAN is between - /2 and /2.
| ATAN2 |
The ATAN2 function returns the inverse tangent of n/m. The specification for the ATAN2 function is:
FUNCTION ATAN ( n NUMBER, m NUMBER) RETURN NUMBER;where the numbers n and m must be between -infinity and infinity, and the value returned by ATAN is between - and . The result of ATAN2(n,m) is defined to be identical to ATAN(n/m).
| BITAND |
The BITAND function performs a logical AND between two positive, integer arguments. The specification for BITAND is:
FUNCTION BITAND ( n NUMBER, m NUMBER) RETURN NUMBER;The following example illustrates how BITAND might be used to read the bits in an integer used to contain on/off-type flags.
DECLARE --Declare a flag variable, the bits of which are interpreted --as follows: --- 0000 --- |--0=male, 1=female --- |---0=part-time, 1=full-time --- |----0=hourly, 1=salaried --- |-----0=office, 1=factory bit_flags PLS_INTEGER;BEGIN --Initialize the flag variable to indicate a female, --full-time employee. Note that 3 is represented in --binary as 11. bit_flags:= 3;... --Do some things if the employee is full-time IF BITAND(bit_flags, 2) <> 0 THEN DBMS_OUTPUT.PUT_LINE('Employee is full-time.');...To set an individual bit in an integer value, you can use an incantation such as the following. The first resets the full-time bit in the flag variable used in the previous example, while the second sets the same bit:
|
|
|
These incantations work by using BITAND with a mask that returns the current state of all bits except the one that we want to set (or reset, as the case may be). The value 13 is represented in binary as 1101. Consequently, the value returned by BITAND in this case is guaranteed to have the full-time bit set to zero. If we want to leave it that way, we can. Otherwise, to set the bit, we add the value 2. The binary representation of 2 is 10, causing the second bit to be set. These incantations work regardless of the bit's original value.
If you limit yourself to positive numbers, the largest power of 2 that you can store in a PLS_INTEGER variable is 230. This effectively gives you 30 bits to play with.
| CEIL |
The CEIL ("ceiling") function returns the smallest integer greater than or equal to the specified number. The specification for the CEIL function is:
FUNCTION CEIL ( n NUMBER) RETURN NUMBER;Here are some examples of the effect of CEIL:
CEIL (6) --> 6CEIL (119.1) --> 120CEIL (-17.2) --> -17For a comparison of CEIL with several other numeric functions, see Table 9-7 and Figure 9-5 earlier in Section 9.3.1.
| COS |
The COS trigonometric function returns the cosine of the specified angle. The specification for the COS function is:
FUNCTION COS ( angle NUMBER) RETURN NUMBER;where angle must be expressed in radians. If your angle is specified in degrees, then you should call COS as follows:
my_cosine:= COS (ACOS(-1)*angle_in_degrees/180);| COSH |
The COSH trigonometric function returns the hyperbolic cosine of the specified number. The specification for the COSH function is:
FUNCTION COSH ( n NUMBER) RETURN NUMBER;If n is a real number and i is the imaginary square root of -1, then the relationship between COS and COSH can be expressed as follows:
COS (i * n) = COSH (n)| EXP |
The EXP function returns the value e raised to the nth power, where n is the input argument. The specification for the EXP function is:
FUNCTION EXP ( n NUMBER) RETURN NUMBER;The number e (approximately equal to 2.71828) is the base of the system of natural logarithms.
| FLOOR |
The FLOOR function, the opposite of the CEIL function, returns the largest integer that is less than or equal to the input number. The specification for the FLOOR function is:
FUNCTION FLOOR ( n NUMBER) RETURN NUMBER;Here are some examples of the values returned by FLOOR:
FLOOR (6.2) --> 6FLOOR (-89.4) --> -90For a comparison of FLOOR with several other numeric functions, see Table 9-7 and Figure 9-5 earlier in Section 9.3.1.
|
|
|
| LN |
The LN function returns the natural logarithm of the input. The specification for the LN function is:
FUNCTION LN ( n NUMBER) RETURN NUMBER;The argument n must be greater than or equal to 0. If you pass LN a negative argument, you will receive the following error:
ORA-01428: argument '-1' is out of range| LOG |
The LOG function returns the base b logarithm of the input value. The specification for the LOG function is:
FUNCTION LOG ( b NUMBER, n NUMBER) RETURN NUMBER;The argument n must be greater than or equal to 0. The base b must be greater than 1. If you pass LOG an argument that violates either of these rules, you will receive the following error:
ORA-01428: argument '-1' is out of range| MOD |
The MOD function returns the remainder of one number when divided by a second number. The specification for the MOD function is:
FUNCTION MOD ( dividend NUMBER, divisor NUMBER) RETURN NUMBER;If the divisor is zero, then the dividend is returned unchanged. Here are some examples of MOD:
MOD (10, 5) --> 0MOD (2, 1) --> 0MOD (3,2) --> 1You can use MOD to determine quickly if a number is odd or even:
FUNCTION is_odd (num_in IN NUMBER) RETURN BOOLEANISBEGIN RETURN MOD (num_in, 2) = 1;END; FUNCTION is_even (num_in IN NUMBER) RETURN BOOLEANISBEGIN RETURN MOD (num_in, 2) = 0;END;| POWER |
The POWER function raises the first argument to the power indicated by the second argument. The specification for the POWER function is:
FUNCTION POWER ( base NUMBER, power NUMBER) RETURN NUMBER;If base is negative, then power must be an integer. The following expression calculates the range of valid values for a BINARY_INTEGER variable (-231 -1 through 231 -1):
POWER (-2, 31) - 1.. POWER (2, 31) - 1or:
-2147483649.. 2147483647| ROUND |
The ROUND function returns the first argument rounded to the number of decimal places specified in the second argument. The specification for the ROUND function is:
FUNCTION ROUND ( n NUMBER, [ decimal_places NUMBER]) RETURN NUMBER;The decimal_places argument is optional and defaults to 0, which means that n will be rounded to zero decimal places, a whole number. The value of decimal_ places can be less than zero. A negative value for this argument directs ROUND to round digits to the left of the decimal point rather than to the right. Here are some examples:
ROUND (153.46) --> 153ROUND (153.46, 1) --> 153.5ROUND (153, -1) --> 150For a comparison of ROUND with several other numeric functions, see Table 9-7 and Figure 9-5 earlier in Section 9.3.1.
| SIGN |
The SIGN function returns the sign of the input number. The specification for the SIGN function is:
FUNCTION SIGN ( n NUMBER) RETURN NUMBER;This function returns one of these three values:
| -1 | n is less than zero |
| n is equal to zero | |
| +1 | n is greater than zero |
| SIN |
The SIN trigonometric function returns the sine of the specified angle. The specification for the SIN function is:
FUNCTION SIN ( angle NUMBER) RETURN NUMBER;where angle must be expressed in radians. If your angle is specified in degrees, then you should call SIN as follows:
my_sine:= SIN (ACOS(-1)*angle_in_degrees/180);| SINH |
The SINH trigonometric function returns the hyperbolic sine of the specified number. The specification for the SINH function is:
FUNCTION SINH ( n NUMBER) RETURN NUMBER;If n is a real number and i is the imaginary square root of -1, then the relationship between SIN and SINH can be expressed as follows:
SIN (i * n) = i * SINH (n)| SQRT |
The SQRT function returns the square root of the input number. The specification for the SQRT function is:
|
|
|
where n must be greater than or equal to 0. If n is negative, you will receive the following error:
ORA-01428: argument '-1' is out of range| TAN |
The TAN trigonometric function returns the tangent of the specified angle. The specification for the TAN function is:
FUNCTION TAN ( angle NUMBER) RETURN NUMBER;where angle must be expressed in radians. If your angle is specified in degrees, then you should call TAN as follows:
my_tane:= TAN (ACOS(-1)*angle_in_degrees/180);| TANH |
The TANH trigonometric function returns the hyperbolic tangent of the specified number. The specification for the TANH function is:
FUNCTION TANH ( n NUMBER) RETURN NUMBER;If n is a real number and i is the imaginary square root of -1, then the relationship between TAN and TANH can be expressed as follows:
TAN (i * n) = i * TANH (n)| TRUNC |
The TRUNC function truncates the first argument to the number of decimal places specified by the second argument. The specification for the TRUNC function is:
FUNCTION TRUNC ( n NUMBER, [ decimal_places NUMBER]) RETURN NUMBER;The decimal_places argument is optional and defaults to 0, which means that n will be truncated to zero decimal places, a whole number. The value of decimal_ places can be less than zero. A negative value for this argument directs TRUNC to truncate or zero-out digits to the left of the decimal point rather than to the right. Here are some examples:
TRUNC (153.46) --> 153TRUNC (153.46, 1) --> 153.4TRUNC (-2003.16, -1) --> -2000For a comparison of ROUND with several other numeric functions, see Table 9-7 and Figure 9-5 earlier in Section 9.3.1.
Chapter 10. Dates and Timestamps
Most of our applications require the storage and manipulation of dates and times. Dates are quite complicated: not only are they highly formatted data, but there are myriad rules for determining valid values and valid calculations (leap days and years, national and company holidays, date ranges, etc.). Fortunately, the Oracle RDBMS and PL/SQL offer us lots of help in handling date information.
First of all, both the RDBMS and PL/SQL provide you with a set of true datetime datatypes that store both date and time information using a standard, internal format. No matter how you choose to represent datetime values when you enter them or display them, such values are represented in a consistent manner within PL/SQL and the RDBMS.
For any datetime value, Oracle stores some or all of the following information:
Year
Month
Day
Hour
Minute
Second
Fractional second
Time zone hour displacement
Time zone minute displacement
Time zone region name
Time zone abbreviation
Support for true datetime datatypes is only half the battle. You also need a language that can manipulate those values in a natural and intelligent manneras actual dates and times. Oracle provides us with a comprehensive suite of functions with which to manipulate date and time information. Need toconvert a character string to a date? No problem. Oracle has you covered with the TO_DATE function, which can interpret and validate a variety of different date formats. Need to convert times between time zones? Again, Oracle has you covereduse the NEW_TIME function.
Not only do you have a rich set of built-in datetime functions to work with, you can perform certain types of datetime arithmetic directly on any datetime value. For example, computing the number of days between two dates is as simple as subtracting one from another.
|
|
|
With all this datetime power at your disposal, it's important to understand your choices. To that end, the next section discusses the various datetime datatypes available from PL/SQL. Next we talk about conversions, and show you how to get datetime values into and out of your datetime variables. In the remaining sections of the chapter, we describe the datetime functions that are available and show you how to work with and manipulate the various datetime datatypes that Oracle supports.
