Page 175
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.
Listing 7.26 contains 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.
Listing 7.26. Determining the storage required by a number.
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. Listing 7.27
demonstrates how Oracle converts a specified number to a character value if it is being stored in
a VARCHAR2 column.
Page 176
Listing 7.27. Automatic conversion of a number to a string.
SQL> insert into Course
2 (Course_ID, Department_ID, Title, Additional_Fees)
3 values
4 (782, `BIO', `INVERTEBRATE CLONING LAB', 275);
1 row created.
SQL> select Course_ID, Department_ID, Title
2 from Course
3 where
4 Course_ID = `782';
COURS DEPARTMENT_ID TITLE
----- -------------------- --------------------------------------------
782 BIO INVERTEBRATE CLONING LAB
If you look at the row that was inserted in Listing 7.27, you can see that the number 782, which was used to specify a value for Course_ID, has been converted to a VARCHAR2 value of 782, which is stored in the Course_ID column.
The TO_CHAR function is used to explicitly convert a number to a string.
Its syntax is
TO_CHAR (number [,format])
The variables are defined as follows:
number is the numeric expression to be converted. format is the optional format model to be used by TO_CHAR.
Listing 7.28 provides an example of the use of 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.
Listing 7.28. Using the TO_CHAR function without a format to convert a number to a string.
SQL> select Real_Value, to_char(Real_Value)
2 from Number_Demo;
REAL_VALUE TO_CHAR(REAL_VALUE)
---------- ----------------------------------------
3.1 3.1
Page 177
This is an appropriate point to discuss the Oracle number format model. The code segments contained in Listings 7.29 through 7.32 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. Please refer to Listing 7.29 for an example.
Listing 7.29. Using a numeric format mask.
SQL> select Course_ID, Title, to_char(Additional_Fees, `9,999.99')
2 from Course
3 where
4 Department_ID = `BIO'
5 order by Course_ID;
COURS TITLE TO_CHAR(A
----- ------------------------------------------------------ ---------
101 INTRO TO BIOLOGY 55.00
177 INVERTEBRATE ANATOMY 65.00
178 MAMMALIAN ANATOMY 70.00
To display a number with leading zeros, use 0 at the beginning of the format, as shown in Listing 7.30.
Listing 7.30. Using a leading zero in a numeric format mask.
SQL> select Course_ID, Title, to_char(Additional_Fees, `099.99') 2 from Course 3 where 4 Department_ID = `ECON' 5 order by Course_ID; COURS TITLE TO_CHAR ----- -------------------------------------------------------- ------- 101 INTRO TO ECONOMICS 025.00 189 MONETARY POLICY 750.00 199 WORKSHOP ON MARX 000.00
To display a leading dollar sign, begin the format with a
$ (see Listing 7.31).
Page 178
Listing 7.31. Specifying a dollar sign in a numeric format mask.
SQL> select Course_ID, Title, to_char(Additional_Fees, `$999.99') 2 from Course 3 where 4 Department_ID = `ECON' 5 order by Course_ID; COURS TITLE TO_CHAR( ----- ------------------------------------------------------ -------- 101 INTRO TO ECONOMICS $25.00 189 MONETARY POLICY $750.00 199 WORKSHOP ON MARX $.00
If you want a number to appear in scientific notation, follow the specified precision with EEEE (see Listing 7.32).
Listing 7.32. Specifying scientific notation in a format mask.
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])
The variables are defined as follows:
string is the character expression to be converted.
format is the optional format model to be used by TO_NUMBER.
TO_NUMBER uses the same format model as TO_CHAR. Listing 7.33 shows how you would
convert a string value, representing earnings per share, to a number.