Page 64
Table 3.4Continued
Function Name | Syntax | Description |
FLOOR | FLOOR(value) |
Largest integer less than or equal to the value |
LN | LN(value) | Natural (base e) logarithm of the value |
LOG | LOG(base, value) | Base base logarithm of the value |
MOD | MOD(value, divisor) | Modulus (remainder) |
NVL |
NVL(value, substitute) | Substitutes a value for a null value |
POWER |
POWER(value, exponent) | Value raised to an exponent |
ROUND |
ROUND(value, precision) | Rounds a value to the specified precision |
SIGN | SIGN(value) |
Displays a minus one if the value is negative and a positive one if the value is positive. |
SIN | SIN(value) | Sine of the value |
SINH | SINH(value) | Hyperbolic sine of the value |
SQRT | SQRT(value) | Square root of the value |
TAN | TAN(value) | Tangent of the value |
TANH | TANH(value) | Hyperbolic tangent of the value |
TRUNC |
TRUNC(value, precision) |
Truncates the value to the specified precision |
NOTE |
The following subsections demonstrate the more common functions. You will be able to use any of the other functions by studying these sections. |
The nvl function is used to correct problems that occur when one of the values in the computation is null. When a null value exists in an argument, a null value results. This can cause a problem because it affects the value of computations. Assume that you have 20 records that total to 200, for example, and 10 of these records are null. Computing the average by using the avg function would result in a value of 20. The reason is the null records are not included into the denominator. If the nvl function were employed and the null value changed to 0, the value would be 10, which is the expected result. Listing 3.34 shows an example of averaging columns by using the nvl function:
Page 65
Listing 3.34L_03_34.TXTExample of the Nvl Function
SQL> select avg(wages), avg(nvl(wages,0)) 2 from employee; AVG(WAGES) AVG(NVL(WAGES,0)) ---------- ----------------- 10905.882 9757.8947 . 1 record selected.
The round function changes the value to the next higher unit if the value is 50 percent of that unit. It reduces the value to the lower unit if it is less than 50 percent. Listing 3.35 shows an example of rounding:
Listing 3.35L_03_35.TXTExample of the Round Function
SQL> select last_name, wages, 2 round(wages+1.05, 1), round(wages+1.04,1) 3 from employee; LAST_NAME WAGES ROUND(WAGES+1.05,1) ROUND(WAGES+1.04,1) --------------- --------- ------------------- ------------------- COOLIDGE 9500 9501.1 9501 JOHNSON 12000 12001.1 12001 REAGAN 13500 13501.1 13501 BUSH 14000 14001.1 14001 JOHNSON 7500 7501.1 7501 . 19 records selected.
The trunc function eliminates the portion of a number outside the defined precision. Precision means the number of decimal positions. Unlike the round function, trunc does not change the value. Listing 3.36 provides an example of using the trunc function:
Listing 3.36L_03_36.TXTExample of the Trunc Function
SQL> select last_name, wages/52, 2 trunc(wages/52, 2) 3 from employee; LAST_NAME WAGES/52 TRUNC(WAGES/52,2) --------------- --------- ----------------- COOLIDGE 182.69231 182.69
continues
Page 66
Listing 3.36Continued
JOHNSON 230.76923 230.76 REAGAN 259.61538 259.61 BUSH 269.23077 269.23 JOHNSON 144.23077 144.23 . 19 records selected.
An array of functions exist for dates. Dates are, in reality, a number. They are stored in the database as a numeric value and displayed in a date format. The majority of these functions perform calculations such as the number of months between two dates. Table 3.5 lists the date functions.
Table 3.5Date Functions
Function Name | Syntax | Description |
ADD_MONTHS | ADD_MONTHS(date, count) |
Adds the specified number of months to the date |
GREATEST | GREATEST(date1, date2,...) |
Picks the most recent date from a list of dates |
LAST_DAY | LAST_DAY(day) |
Returns the last day of the month that the date is in |
MONTHS_BETWEEN | MONTH_BETWEEN(date2, date1) |
Calculates the number of months between the two dates. |
NEXT_DAY | NEXT_DAY(date, `day') |
Returns the date of the specified next day of the week |
TO_CHAR | TO_CHAR(date, `format') |
Reformats the date according to the format picture |
TO_DATE | TO_DATE(string, `format') |
Converts a string in a given format to a date. |
NOTE |
The following subsections demonstrate the more common functions. You will be able to use any of the other functions by studying these sections. |
Page 67
This function is used to add a number of months to a date. The example shown in Listing 3.37 computes the retirement date of each employee.
Listing 3.37L_03_37.TXTExample of the Add_Months Function
SQL> select last_name, first_name, birth_date, 2 add_months(birth_date, (12*65)) age 3 from employee; LAST_NAME FIRST_NAME BIRTH_DAT AGE --------------- --------------- --------- --------- COOLIDGE CALVIN 01-JUL-72 01-JUL-37 JOHNSON LYNDON 27-AUG-08 27-AUG-73 REAGAN RONALD 01-OCT-24 01-OCT-89 BUSH GEORGE 06-FEB-11 06-FEB-76 JOHNSON ANDREW 29-DEC-08 29-DEC-73 CLINTON WILLIAM 03-APR-40 03-APR-05 CARTER JIMMY 14-JUL-13 14-JUL-78 . 19 records selected.
This function determines the number of months between two dates. Listing 3.38 uses this function to calculate the age of each employee when hired by dividing the number of months between the birthdate and hire date by 12.
Listing 3.38L_03_38.TXTExample of the Months_Between Function
SQL> select last_name, first_name, 2 employment_date, birth_date, 3 months_between(employment_date, birth_date)/12 age 4 from employee; LAST_NAME FIRST_NAME EMPLOYMEN BIRTH_DAT AGE --------------- --------------- --------- --------- --------- COOLIDGE CALVIN 07-AUG-21 01-JUL-72 50.90054 JOHNSON LYNDON 23-NOV-63 27-AUG-08 55.239247 REAGAN RONALD 03-MAR-80 01-OCT-24 55.422043 BUSH GEORGE 05-JAN-88 06-FEB-11 76.913978 JOHNSON ANDREW 13-APR-65 29-DEC-08 56.290323 CLINTON WILLIAM 01-JAN-92 03-APR-40 51.744624 CARTER JIMMY 10-JUL-76 14-JUL-13 62.989247 . 19 records selected.
Page 68
This function calculates the next day of the week (such as Sunday) following a specified base date. Listing 3.39 determines the date of the first Friday following the employee's retirement date:
Listing 3.39L_03_39.TXTExample of the Next_Day Function
SQL> select last_name, first_name, birth_date, 2 next_day(add_months(birth_date, (12*65)), `FRIDAY') party 3 from employee; LAST_NAME FIRST_NAME BIRTH_DAT PARTY --------------- --------------- --------- --------- COOLIDGE CALVIN 01-JUL-72 03-JUL-37 JOHNSON LYNDON 27-AUG-08 31-AUG-73 REAGAN RONALD 01-OCT-24 06-OCT-89 BUSH GEORGE 06-FEB-11 13-FEB-76 JOHNSON ANDREW 29-DEC-08 04-JAN-74 CLINTON WILLIAM 03-APR-40 08-APR-05 CARTER JIMMY 14-JUL-13 21-JUL-78 . 19 records selected.
A date picture is the format of a date. The default Oracle date picture or date format is `dd-mon-yy'. This is a military date with the date followed by the month and year. SQL*PLUS offers a number of formats that enable the developer to change this picture. Table 3.6 contains a listing of various formats that you can use:
Table 3.6Date Formats
Format | Meaning |
P.M. | Displays P.M. |
D | Numeric day of the week |
DAY | Day of the week is fully spelled out (MONDAY) |
Day | Same as DAY, but only the initial letter is capitalized (Monday) |
day | Same as DAY, letters are lower case (monday) |
DD | Numeric day of the month (for example, Feb 23 = 23) |
DDD | Numeric day of the year. (for example, Feb. 2 = 33) |
DY | Three letter abbreviation of the day (FRI) |