In Chapter 11, "Manipulating Strings," you learned about the use of strings and string functions in Personal Oracle7. In Chapter 12, "Dealing with Dates," you explored the advantages of the DATE datatype and the relevant Oracle built-in functions. The topic of this chapter is the use of numeric datatypes in Personal Oracle7.
A column's datatype is specified in the CREATE TABLE and ALTER TABLE statements. The general syntax for specifying a numeric datatype is
NUMBER ([precision [, scale]])
where precision is an optional argument that specifies the number of digits of precision that Oracle should store for column values and scale is an optional argument indicating the number of digits to the right of the decimal point that Oracle should store for column values.
If you don't specify precision or scale, Oracle accepts a number of up to 38 digits of precision --;the maximum precision that Oracle offers. When you specify a column, consider limiting the width of numeric values by using an appropriate precision. For example, if a column stores a patient's body temperature in degrees Fahrenheit, you would specify the column as
Body_Temp_F Number(4,1)
A precision of 4 and a scale of 1 allow Body_Temp_F to store a total of four digits, including one digit to the right of the decimal point. As a result, Oracle accepts the following values:
SQL> update Patient 2 set Body_Temp_F = 99.2 3 where 4 Patient_ID = 'A2002'; 1 row updated. SQL> update Patient 2 set Body_Temp_F = 103.8 3 where 4 Patient_ID = 'E3893'; 1 row updated.
The column definition prevents a bad value from accidentally being stored in the table.
SQL> update Patient
2 set Body_Temp_F = 1003.8
3 where
4 Patient_ID = 'N3393';
set Body_Temp_F = 1003.8
*
ERROR at line 2:
ORA-01438: value larger than specified precision allows for this column
Of course, this definition for Body_Temp_F allows values up to 999.9 degrees Fahrenheit--;an impossible value for humans. In addition to specifying the precision and scale, you also need to specify a CHECK constraint for this column to restrict its values to a range. I explain how to use constraints in Chapter 14, "Defining Table and Column Constraints."
If you specify a value for precision but not for scale, Oracle truncates the fractional value of a real number before storing the value in the column. Consider the following examples:
SQL> create table Number_Demo (
2 Int_Value number(3),
3 Real_Value number(3,1),
4 Num_Value number);
Table created.
SQL> insert into Number_Demo
2 (Int_Value)
3 values
4 (1234);
(1234)
*
ERROR at line 4:
ORA-01438: value larger than specified precision allows for this column
SQL> insert into Number_Demo
2 (Int_Value)
3 values
4 (12.2);
1 row created.
SQL> select Int_Value
2 from Number_Demo;
INT_VALUE
---------
12
If values for precision and scale have been furnished and you store a numeric value whose scale exceeds the column's scale, Oracle truncates the fractional value to the column's scale.
SQL> insert into Number_Demo
2 (Real_Value)
3 values
4 (3.144);
1 row created.
SQL> select Real_Value
2 from Number_Demo;
REAL_VALUE
----------
3.1
In addition to the NUMBER datatype, Oracle accepts the following keywords that describe a numeric column.
Oracle supports these other datatypes to provide compatibility with ANSI SQL and other relational database systems such as IBM SQL/DS and DB2. The NUMERIC, DECIMAL, and DEC datatypes are identical to the NUMBER datatype. INTEGER, INT, and SMALLINT are translated to NUMBER(38). FLOAT, DOUBLE PRECISION, and REAL are all translated to NUMBER. Unless you are converting a database schema from some non-Oracle database, you should generally use the NUMBER datatype when specifying columns.
Oracle doesn't store numbers in the manner used by programming languages such as C and FORTRAN. For example, in C a floating-point variable requires the same amount of storage regardless of its value, whereas in Oracle the number of bytes used to store a number depends on the number's precision. To illustrate this fact, you can use an Oracle built-in function called VSIZE, which returns the number of bytes used by its argument.
The following code fragment is a query of a table that contains a number column named Num_Value. To the right of the column value is VSIZE(Num_Value)--;which returns the number of bytes used to store Num_Value. Oracle can store two digits of precision in one byte. Another byte is used for storing the sign and exponent.
SQL> select Num_Value, vsize(Num_Value)
2 from Number_Demo;
NUM_VALUE VSIZE(NUM_VALUE)
--------- ----------------
123 3
1234 3
12345 4
123456 4
1234567 5
12345678 5
123456789 6
12345.679 6
You will typically want to convert a numeric value to a string value for two reasons:
In some situations SQL automatically converts a number to a string. For example, if you perform an INSERT or UPDATE, Oracle converts a specified number to a character value if it is being stored in a VARCHAR2 column.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Initial_Retail_Value)
3 values
4 ('D1001',1001,500);
1 row created.
If you look at the row that was just inserted, you can see that the number 1001, which was used to specify a value for Manufacturer_ID, has been converted to a VARCHAR2 value of 1001, which is stored in the Manufacturer_ID column.
SQL> select Product_ID, Manufacturer_ID, Initial_Retail_Value 2 from Product 3 where 4 Product_ID = '&Product_ID'; Enter value for product_id: D1001 old 4: Product_ID = '&Product_ID' new 4: Product_ID = 'D1001' PRODUCT_ MANUFA INITIAL_RETAIL_VALUE -------- ------ -------------------- D1001 1001 500
The TO_CHAR function is used to explicitly convert a number to a string. Its syntax is
TO_CHAR (number [,format])
where number is the numeric expression to be converted and format is the optional format model to be used by TO_CHAR.
Here's an example of using TO_CHAR without a format. Notice that the first column--;Real_Value--;is right-justified by SQL*Plus, whereas the second column--;to_char(Real_Value)--;is left-justified by SQL*Plus because it is a character column.
SQL> select Real_Value, to_char(Real_Value)
2 from Number_Demo;
REAL_VALUE TO_CHAR(REAL_VALUE)
---------- ----------------------------------------
3.1 3.1
This is an appropriate point to discuss the Oracle number format model. The following code segments show the most important format model elements--;the ones that you'll rely on most often.
To specify the number of digits to display, use 9 for each digit. You also can add a comma and decimal point to the specified format.
SQL> select Product_ID, to_char(Initial_Retail_Value,'9,999.99') 2 from Product 3 order by Product_ID; PRODUCT_ID TO_CHAR(I ------------ --------- A2001 350.00 A504 585.00 A509 850.00 A903 1,050.00 B311 185.00
To display a number with leading zeros, use 0 at the beginning of the format.
SQL> select Product_ID, to_char(Initial_Retail_Value,'0,999.99') 2 from Product 3 order by Product_ID; PRODUCT_ID TO_CHAR(I ------------ --------- A2001 0,350.00 A504 0,585.00 A509 0,850.00 A903 1,050.00 B311 0,185.00
To display a leading dollar sign, begin the format with a $.
SQL> select Product_ID, to_char(Initial_Retail_Value,'$9,999.99') 2 from Product 3 order by Product_ID; PRODUCT_ID TO_CHAR(IN ------------ ---------- A2001 $350.00 A504 $585.00 A509 $850.00 A903 $1,050.00 B311 $185.00
To enclose a negative value in angle brackets, add PR to the format.
SQL> select Symbol, to_char(Last_qtr_EPS,'$99.99PR') 2 from Security_Price 3 order by Symbol; SYMBO TO_CHAR( ----- -------- ABC <$.58> ACME $1.81 ZGEGE <$.18>
To return the sign of each value, add S to the format.
SQL> select Symbol, to_char(Last_qtr_EPS,'S99.99') 2 from Security_Price 3 order by Symbol; SYMBO TO_CHA ----- ------ ABC -.58 ACME +1.81 ZGEGE -.18
Finally, if you want a number to appear in scientific notation, follow the specified precision with EEEE.
SQL> select Num_Value, to_char(Num_Value,'9.9999EEEE')
2 from Number_Demo
3 order by Num_Value;
NUM_VALUE TO_CHAR(NUM_
--------- ------------
123 1.2300E+02
1234 1.2340E+03
12345 1.2345E+04
12345.679 1.2346E+04
The TO_NUMBER function is the converse of TO_CHAR: It converts a character expression to a number by specifying a format. The syntax for TO_NUMBER is
TO_NUMBER (string [,format])
where string is the character expression to be converted and format is the optional format model to be used by TO_NUMBER.
TO_NUMBER uses the same format model as TO_CHAR. As an example, here is how you would convert a string value, representing earnings per share, to a number.
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 are group functions, their use is described in detail in Chapter 15, "More Sophisticated Queries."
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, 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 the following example:
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 ROUND function also accepts numeric expressions.
SQL> select Current_Used_Value*0.87, Round(Current_Used_Value*0.87)
2 from Product;
CURRENT_USED_VALUE*0.87 ROUND(CURRENT_USED_VALUE*0.87)
----------------------- ------------------------------
217.5 218
339.3 339
587.25 587
717.75 718
330.6 331
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.
SQL> select trunc(123.33), trunc(123.567,2)
2 from dual;
TRUNC(123.33) TRUNC(123.567,2)
------------- ----------------
123 123.56
TRUNC also accepts numeric expressions.
SQL> select Current_Used_Value*0.87, Trunc(Current_Used_Value*0.87)
2 from Product;
CURRENT_USED_VALUE*0.87 TRUNC(CURRENT_USED_VALUE*0.87)
----------------------- ------------------------------
217.5 217
339.3 339
587.25 587
717.75 717
330.6 330
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.
SQL> select floor(128.3), floor(129.8) 2 from dual; FLOOR(128.3) FLOOR(129.8) ------------ ------------
128 129
The argument for FLOOR can also be a numeric expression.
SQL> select Current_Used_Value*0.87, Floor(Current_Used_Value*0.87)
2 from Product;
CURRENT_USED_VALUE*0.87 FLOOR(CURRENT_USED_VALUE*0.87)
----------------------- ------------------------------
217.5 217
339.3 339
587.25 587
717.75 717
330.6 330
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.
SQL> select ceil(128.3), ceil(129.8)
2 from dual;
CEIL(128.3) CEIL(129.8)
----------- -----------
129 130
The argument for CEIL can also be a numeric expression.
SQL> select Current_Used_Value*0.87, Ceil(Current_Used_Value*0.87)
2 from Product;
CURRENT_USED_VALUE*0.87 CEIL(CURRENT_USED_VALUE*0.87)
----------------------- -----------------------------
217.5 218
339.3 340
587.25 588
717.75 718
330.6 331
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, here is how you would retrieve the largest and smallest estimates for labor costs from the Depot Estimate table:
SQL> select min(Labor_Cost), max(Labor_Cost)
2 from Depot_Estimate;
MIN(LABOR_COST) MAX(LABOR_COST)
--------------- ---------------
56 202
The preceding query retrieves the smallest and largest values for the Labor_Cost column found in the entire Depot_Estimate table. But suppose you want to compare the value of two or more columns. Specifically, suppose that you need to find the larger cost--;labor or parts. In this case the MAX and MIN functions won't work because they each can accept only a single argument. However, two functions can serve this purpose: GREATEST and LEAST. The syntax for these functions is quite simple.
GREATEST(expression1, ... ,expressionN)
LEAST(expression1, ... ,expressionN)
where expression1 through expressionN are valid SQL expressions.
Using the GREATEST function, you submit the following query to retrieve whichever is larger--;Labor_Cost or Parts_Cost.
SQL> select greatest(Labor_Cost,Parts_Cost)
2 from Depot_Estimate;
GREATEST(LABOR_COST,PARTS_COST)
-------------------------------
202
123
183
103
104
151
6 rows selected.
Oracle returns an error message if the datatypes of the expressions don't match.
SQL> select greatest(Labor_Cost,Estimated_Date_for_Completion)
2 from Depot_Estimate;
select greatest(Labor_Cost,Estimated_Date_for_Completion)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
The GREATEST and LEAST functions don't indicate which value is largest or smallest. To obtain that information, you can use the DECODE function in conjunction with GREATEST and LEAST in the following way:
SQL> select decode(greatest(Labor_Cost,Parts_Cost), 2 Labor_Cost,'Labor', 3 Parts_Cost,'Parts'), 4 greatest(Labor_Cost,Parts_Cost) 5 from Depot_Estimate; DECODE( GREATEST(LABOR_COST,PARTS_COST) ------- ------------------------------- Labor 202 Labor 123 Parts 183 Labor 103 Parts 104 Parts 151 6 rows selected.
The first argument to the DECODE function is the expression that you want to decode--;in this case, GREATEST(Labor_Cost,Parts_Cost). First, you compare the value returned by GREATEST to the value of Labor_Cost, and if the values are equal, DECODE returns the string Labor. If they're not equal, DECODE compares the value returned by GREATEST to the value of Parts_Cost; if those two values are equal, then DECODE returns the string Parts.
The next SELECT statement illustrates how to use the DECODE function to return a string value that concatenates the type of cost--;Labor or Parts--;with the amount.
SQL> select decode(greatest(Labor_Cost,Parts_Cost), 2 Labor_Cost,'Labor = ' || to_char(Labor_Cost), 3 Parts_Cost,'Parts = ' || to_char(Parts_Cost), 4 'Unknown') 5 from Depot_Estimate; DECODE(GREATEST(LABOR_COST,PARTS_COST),LABOR_COS ------------------------------------------------ Labor = 202 Labor = 123 Parts = 183 Labor = 103 Parts = 104 Parts = 151 6 rows selected.
The previous query is just another example of how a function can be used as the argument to another function. In this case the TO_CHAR function, when concatenated with a literal string, is an argument to the DECODE function.
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. The syntax is
NVL (column-value, substitute-value)
where column-value is the column value to evaluate and substitute-value is the value that the NVL function will return if column-value is null.
Consider the following situation. The Repair Header table records the amount, if any, that the customer has placed on deposit toward the repair. If you select Deposit_Amount from Repair_Header, some of the values for Deposit_Amount are null.
SQL> select Repair_ID, Deposit_Amount
2 from Repair_Header
3 order by Repair_ID;
REPAIR_ID DEPOSIT_AMOUNT
--------- --------------
501 125
502
503 120
504
505 85
506
6 rows selected.
You can use the NVL function to return a zero instead of a null.
SQL> select Repair_ID, NVL(Deposit_Amount,0.0)
2 from Repair_Header
3 order by Repair_ID;
REPAIR_ID NVL(DEPOSIT_AMOUNT,0.0)
--------- -----------------------
501 125
502 0
503 120
504 0
505 85
506 0
6 rows selected.
The NVL function can be used as an argument for other Oracle built-in functions. For example, you can query the Repair_Header table for the average deposit.
SQL> select avg(Deposit_Amount)
2 from Repair_Header;
AVG(DEPOSIT_AMOUNT)
-------------------
110
When you embed the NVL function inside the AVG function, the result is different.
SQL> select avg(NVL(Deposit_Amount,0.0))
2 from Repair_Header;
AVG(NVL(DEPOSIT_AMOUNT,0.0))
----------------------------
55
The reason for the difference is that the AVG function uses the zero values returned by NVL, not the null values.
The following code segments show some additional built-in mathematical functions that Oracle provides. These functions can be used in all four DML statements: SELECT, INSERT, UPDATE, and DELETE.
The function EXP(n) returns the value of the mathematical constant e raised to the nth power.
SQL> select exp(2.1) from dual; EXP(2.1) --------- 8.1661699
LN(n) returns the natural logarithm of n, where n is greater than zero.
SQL> select ln(1.0), ln(2.781828) from dual;
LN(1.0) LN(2.781828)
--------- ------------
0 1.0231083
The function LOG(m, n) returns the logarithm of n, using a base of m.
SQL> select log(10,1), log(10,2) from dual;
LOG(10,1) LOG(10,2)
--------- ---------
0 .30103
POWER(m, n) returns the value of m raised to the nth power.
SQL> select power(10,2), power(2,16) from dual;
POWER(10,2) POWER(2,16)
----------- -----------
100 65536
The function SQRT(n) returns the square root of n. If n is less than zero, Oracle returns an error. Here is an example of its use:
SQL> select sqrt(256) from dual;
SQRT(256)
---------
16
SQL> select sqrt(-0.001) from dual;
ERROR:
ORA-01428: argument '-.001' is out of range
MOD provides the modulus function in Oracle SQL. Its syntax is
MOD(m, n)
where m is a number and n is a divided into m.
MOD returns the remainder of m divided by n.
SQL> select mod(23,5)
2 from dual;
MOD(23,5)
---------
3
The ABS function returns the absolute value of its argument, as shown in the following example:
SQL> select abs(-21.34), abs(131)
2 from dual;
ABS(-21.34) ABS(131)
----------- ---------
21.34 131
The SIGN function returns the location of its numeric argument relative to zero. SIGN(value) returns the following indicators:
Following is an example of how SIGN works:
SQL> select sign(-22), sign(0), sign(7.2)
2 from dual;
SIGN(-22) SIGN(0) SIGN(7.2)
--------- --------- ---------
-1 0 1
Another built-in function--;CHR--;converts an integer expression to its equivalent ASCII character. Here is an example:
SQL> select chr(60), chr(120) 2 from dual; C C - -
< x
Oracle provides six built-in trigonometric functions. For each function, the argument is expressed in radians.
SIN(value) returns the sine of value.
SQL> select sin(3.14159/2) from dual;
SIN(3.14159/2)
--------------
1
The function COS(value) returns the cosine of value.
SQL> select cos(0) from dual;
COS(0)
---------
1
TAN(value) returns the tangent of value.
SQL> select tan(3.14159/4) from dual;
TAN(3.14159/4)
--------------
.99999867
The function SINH(value) returns the hyperbolic sine of value.
SQL> select sinh(3.14159/6) from dual;
SINH(3.14159/6)
---------------
.54785297
COSH(value) returns the hyperbolic cosine of value.
SQL> select cosh(3.14159/6) from dual;
COSH(3.14159/6)
---------------
1.1402381
The function TANH(value) returns the hyperbolic tangent of value.
SQL> select tanh(3.14159/6) from dual;
TANH(3.14159/6)
---------------
.48047244
Keep the following concepts in mind when you are constructing SQL statements for your application: