Previous | Table of Contents | Next

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

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.TXT—Example 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

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.TXT—Example 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

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.TXT—Example 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.

Understanding Date Functions

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

The Add_Months Function

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.TXT—Example 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.

The Months_Between Function

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.TXT—Example 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

The Next_Day Function

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.TXT—Example 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.

Using Date Pictures

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)

Previous | Table of Contents | Next