Page 179
Listing 7.33. Using the TO_NUMBER function.
SQL> update Security_Price
2 set Last_Qtr_EPS = to_number(`$2.81','$999.99')
3 where
4 Symbol = `ZGEGE';
1 row updated.
SQL> select Symbol, Last_Qtr_EPS
2 from Security_Price
3 where
4 Symbol = `ZGEGE';
SYMBO LAST_QTR_EPS
----- ------------
ZGEGE 2.81
Oracle furnishes the following statistical functions that are actually group functions:
Because these group functions are more complex, their use is described in detail on Day 8, "More Sophisticated Queries with SQL."
Oracle provides four built-in functions related to rounding and truncating fractional numbers.
ROUND(value,[scale]) TRUNC(value,[scale]) FLOOR(value) CEIL(value)
In these functions value is a numeric expression, and scale is an optional argument indicating the number of digits that the function should use for rounding or truncating. (The default is 0.)
The following examples show how you can use each function.
The ROUND function has two arguments: the numeric expression and an optional number of digits to be used for rounding. If the second argument isn't supplied, ROUND returns the value of its numeric argument rounded to the nearest integer. If the second argument is supplied,
Page 180
ROUND returns the value of its numeric argument rounded to the nearest fractional number with the specified number of digits to the right of the decimal point. ROUND can be used with literal values, as shown in Listing 7.34.
Listing 7.34. Using the ROUND function.
SQL> select round(123.2) from dual;
ROUND(123.2)
------------
123
SQL> select round(123.27,1) from dual;
ROUND(123.27,1)
---------------
123.3
SQL> select round(101.8) from dual;
ROUND(101.8)
------------
102
The TRUNC function is similar to the ROUND function. However, instead of rounding to the nearest integer, TRUNC removes the fractional portion of its numeric argument. You can supply a literal number to TRUNC as seen in Listing 7.35.
Listing 7.35. Truncating a number.
SQL> select trunc(123.33), trunc(123.567,2)
2 from dual;
TRUNC(123.33) TRUNC(123.567,2)
------------- ----------------
123 123.56
The FLOOR function is almost identical to the TRUNC function except that FLOOR cannot truncate to a fractional number. The FLOOR function returns the integer that is less than or equal to its numeric argument as you can see in Listing 7.36.
Listing 7.36. Using the FLOOR function.
SQL> select floor(128.3), floor(129.8)
2 from dual;
FLOOR(128.3) FLOOR(129.8)
------------ ------------
128 129
Page 181
The CEIL function returns a ceiling integer for its numeric argument—the smallest integer that is greater than or equal to its argument. CEIL can accept constants. See the example in Listing 7.37.
Listing 7.37. Using the CEIL function.
SQL> select ceil(128.3), ceil(129.8)
2 from dual;
CEIL(128.3) CEIL(129.8)
----------- -----------
129 130
You can use the MAX and MIN functions to retrieve the largest and smallest values for a particular column in a table. Technically, MAX and MIN are group functions. However, you aren't required to specify the SELECT statement's GROUP BY clause to use these functions. As an example, Listing 7.38 demonstrates how you would retrieve the largest and smallest estimates for Additional_Fees from the Course table.
Listing 7.38. Using MIN and MAX.
SQL> select min(Additional_Fees), max(Additional_Fees)
2 from Course;
MIN(ADDITIONAL_FEES) MAX(ADDITIONAL_FEES)
-------------------- --------------------
0 750
When developing an Oracle application, you are bound to encounter situations in which a screen or report will return information about a column that can be null. If you want to return a specific value in place of a null value, you can use Oracle's NVL function to make the replacement.
Here is the syntax:
NVL (column-value, substitute-value)
The variables are defined as follows:
column-value is the column value to evaluate.
substitute-value is the value that the NVL function will return if column-value is null.
Page 182
Listing 7.39 provides an example. Let's imagine we modify the Course table so that Additional_Fees is set to null if it is zero. Next, you query the Course table and see that many of the rows don't have a value for Additional_Fees. Next, you query the Course table using the NVL function to return a zero if Additional_Fees is null.
Listing 7.39. Using the NVL function.
SQL> update course 2 set additional_fees = null 3 where additional_fees = 0; 15 rows updated. SQL> select Department_ID, Course_ID, Additional_Fees 2 from Course 3 order by Department_ID, Course_ID; DEPART COURS ADDITIONAL_FEES ------ ----- --------------- ANTHRO 101 ANTHRO 174 55 ANTHRO 189 7.5 BIO 101 55 BIO 177 65 BIO 178 70 ECON 101 25 ECON 189 750 ECON 199 ENG 101 75 ENG 102 45 ENG 103 35 ENG 199 45 ENGL 101 ENGL 189 ENGL 192 ENGL 193 HIST 115 HIST 184 HIST 199 MATH 101 MATH 189 MATH 50 MATH 51 10 PHILO 101 PHILO 174 PHILO 198 PSYCH 101 25 PSYCH 181 75 PSYCH 183 45 PSYCH 185 20 PSYCH 501 75 32 rows selected.
Page 183
SQL> select Department_ID, Course_ID, nvl(Additional_Fees,0) 2 from Course 3 order by Department_ID, Course_ID; DEPART COURS NVL(ADDITIONAL_FEES,0) ------ ----- ---------------------- ANTHRO 101 0 ANTHRO 174 55 ANTHRO 189 7.5 BIO 101 55 BIO 177 65 BIO 178 70 ECON 101 25 ECON 189 750 ECON 199 0 ENG 101 75 ENG 102 45 ENG 103 35 ENG 199 45 ENGL 101 0 ENGL 189 0 ENGL 192 0 ENGL 193 0 HIST 115 0 HIST 184 0 HIST 199 0 MATH 101 0 MATH 189 0 MATH 50 0 MATH 51 10 PHILO 101 0 PHILO 174 0 PHILO 198 0 PSYCH 101 25 PSYCH 181 75 PSYCH 183 45 PSYCH 185 20 PSYCH 501 75 32 rows selected.
This lesson demonstrated a variety of operators and built-in functions that can be used with string, numeric, and date/time values. Some of the string built-in functions included: