Page 56
Listing 3.23Continued
3 where wages - 200 < 12000; LAST_NAME FIRST_NAME WAGES --------------- --------------- -------- COOLIDGE CALVIN 9500 JOHNSON LYNDON 12000 JOHNSON ANDREW 7500 KENNEDY JOHN 11500 EISENHOWER DWIGHT 0 5 records selected.
Null means the value is unknown or blank. It does not mean the value equals zero or contains white space. This feature can cause many problems because Oracle does not perform calculations when one of the values is null. In Oracle, the result of a calculation with a null value is always unknown or null. Listing 3.24 illustrates a query that contains null values in the wages column. Notice the Eisenhower and the last Roosevelt records. The calculations were not made in these records. The reason is because the two records have a null value in their wages column. The result of a calculation with a null value is a null value. To correct this problem, you can use the NVL function discussed later in this chapter in its own section.
Listing 3.24L_03_25.TXTExample of a Column Calculation when Null Values Exist
SQL> select last_name, wages, wages+500 2 from employee; LAST_NAME WAGES WAGES+500 --------------- --------- --------- NIXON 12500 13000 KENNEDY 11500 12000 EISENHOWER TRUMAN 11000 11500 ROOSEVELT 10400 10900 ROOSEVELT 8000 8500 ANTHONY 7000 7500 ROOSEVELT . 19 records selected
Date is a valid data format in SQL*PLUS. This means that the Oracle database actually stores the date as a numeric value. It is interpreted into a date that a user will understand when
Page 57
displayed. Because the date is stored as a numeric value, calculations can be performed on the column. Values can be added or subtracted from the date to produce another date, or two dates may be subtracted to determine the number of days between them. Listing 3.25 demonstrates several examples of the calculations that can occur.
Listing 3.25L_03_25.TXTExamples of Various Date Calculations
SQL> select last_name, employment_date, birth_date, 2 (employment_date-birth_date)/365 "age at hire", 3 employment_date+100 "100 days", birth_date + (365*65) retirement 4 from employee; LAST_NAME EMPLOYMEN BIRTH_DAT age at hire 100 days RETIREMEN --------------- --------- --------- ----------- -------- --------- COOLIDGE 07-AUG-21 01-JUL-72 50.93425 15-NOV-21 15-JUN-37 JOHNSON 23-NOV-63 27-AUG-08 55.276712 02-MAR-64 11-AUG-73 REAGAN 03-MAR-80 01-OCT-24 55.457534 11-JUN-80 15-SEP-89 BUSH 05-JAN-88 06-FEB-11 76.964384 14-APR-88 21-JAN-76 JOHNSON 13-APR-65 29-DEC-08 56.326027 22-JUL-65 13-DEC-73 CLINTON 01-JAN-92 03-APR-40 51.780822 10-APR-92 18-MAR-05 CARTER 10-JUL-76 14-JUL-13 63.032877 18-OCT-76 28-JUN-78 . 19 records selected
You might notice that the default date picture for a date is `01-JAN-97'. You can change this picture by using a date picture. Date pictures are discussed in the next chapter.
The time of day is also stored in the date value. One hour is equal to 1/24 of the day, and a minute is equal to 1/1440 of a date. To add 10 minutes to the time, just add 10/1440 to the date. The default time of day is midnight. The date value does not contain a fraction. The select statement in Listing 3.26 adds 6 hours and 20 minutes to the hire_date.
Listing 3.26L_03_26.TXTExample of Adding Hours and Minutes to the Date
SQL> column a format a25 SQL> column b format a25 SQL> select last_name, to_char(employment_date, `Month dd YYYY HH:MI') a, 2 to_char(employment_date + 6/24 + 20/1440, `Month dd YYYY HH:MI') b 3 from employee; LAST_NAME A B --------------- ------------------------- ------------------------- COOLIDGE August 07 1921 12:00 August 07 1921 06:20 JOHNSON November 23 1963 12:00 November 23 1963 06:20 REAGAN March 03 1980 12:00 March 03 1980 06:20 BUSH January 05 1988 12:00 January 05 1988 06:20 JOHNSON April 13 1965 12:00 April 13 1965 06:20
continues
Page 58
Listing 3.26Continued
CLINTON January 01 1992 12:00 January 01 1992 06:20 . 19 records selected
SQL*PLUS provides the developer with a wealth of functions that he or she can use to manipulate character and date values, perform multi-row calculations (such as sums or averages), and convert column formats. These functions are used in the select and where clauses of the statement. Table 3.3 contains a description of the character functions.
Table 3.3Character Functions
Function | Format | Description |
|| | `string'||'string' | Combines two strings. |
ASCII | ASCII(string) |
Returns the ASCII value of the first character of the string. |
CHR | CHR(integer) |
Returns the character equivalent to the ASCII value of the integer. |
INITCAP | INITCAP(string) |
Changes the first letter of the string to uppercase. |
INSTR | INSTR(string, set [,BR> start [ , occurrence ] ]) |
Finds the location of the beginning set of characters in the string. |
LENGTH | LENGTH(string) | Returns the length of the string. |
LOWER | LOWER(string) |
Converts the entire string to lower- case. |
LPAD |
LPAD(string, length, [, `set']) |
Makes a string a specific length by adding a specific set of characters to the left of the string. |
LTRIM | LTRIM(string [, `set']) |
Trims the occurrences of any one of a set of characters off the left side of a string. |
RPAD | RPAD(string, length, [, `set']) |
Makes a string a specific length by adding a specified set of characters to the right. |
RTRIM | RTRIM(string [, `set']) |
Trims the occurrences of any one of a set of characters off the right side of a string. |
Page 59
Function | Format | Description |
SOUNDEX | SOUNDEX(string) |
Converts a name to a code value. It is then used to compare names that might have small differences in spelling but sound alike. |
SUBSTR |
SUBSTR(string, start [, count]) |
Extracts a piece of a string beginning at start position and counting for count characters from start. |
TRANSLATE |
TRANSLATE(string, if, then) |
Changes a string, character by character, based on a positional matching of characters in the if string with characters in the then string. |
UPPER | UPPER(string) |
Converts every letter in a string into uppercase. |
NOTE |
In the following subsections, I discuss the major functions you are likely to use. You should gain an understanding of how to use all functions by these examples. Some of the functions such as chr and ascii are seldom used. These functions operate similar to any of the other functions. I don't feel it is necessary to illustrate these functions. |
The length function is used to determine the actual length of a character string. This length is not the defined length of the column, but the part of the column filled with characters. In Listing 3.27, the column last_name is defined as char(15). Notice the values computed by the length command.
Listing 3.27L_03_27.TXTExample of Using the Length Function
SQL> select first_name, length(first_name) from employee; FIRST_NAME LENGTH(FIRST_NAME) --------------- ------------------ CALVIN 6 LYNDON 6 RONALD 6 GEORGE 6 ANDREW 6 . 19 records selected.