Page 46
Table 3.2. Subtypes of the NUMBER datatype.
| Subtype | Usage |
| DECIMAL | Same as NUMBER. |
| DEC | Same as DECIMAL. |
| DOUBLE PRECISION | Same as NUMBER. |
| NUMERIC | Same as NUMBER. |
| REAL | Same as NUMBER. |
| INTEGER | Equivalent to NUMBER(38). |
| INT | Same as INTEGER. |
| SMALLINT | Same as NUMBER(38). |
| FLOAT | Same as NUMBER. |
| FLOAT(prec) | Same as NUMBER(prec), but the precision is expressed in terms of binary bits, not decimal digits. Binary precision can range from 1 through 126. |
Subtypes of NUMBER are not considered to be constraining. The same underlying datatype is used, and subject to the precision and scale you specify, all the subtypes can store the same range of values. Strange as it may seem, even an integer can be defined to hold noninteger, decimal values. Take a look at Listing 3.2, which shows all the NUMBER subtypes being used to declare identical variables.
INPUT/OUTPUT
Listing 3.2. Identical declarations using
NUMBER subtypes.
1: --Remember to execute: SET SERVEROUTPUT ON 2: DECLARE 3: --all these declarations are identical. 4: num_dec DECIMAL(5,2); 5: num_int INTEGER(5,2); 6: num_dbl DOUBLE PRECISION(5,2); 7: num_num NUMERIC(5,2); 8: num_real REAL(5,2); 9: num_sint SMALLINT(5,2); 10: 11: --decimal precision / 0.30103 = binary precision, 12: --so 5/.30103 = 16.6. Round it up to 17. 13: num_flt FLOAT(17); 14: 15: BEGIN 16: --Assign the same value to each variable, and make it one 17: --that needs to be rounded. 18: num_dec := 123.456; 19: num_int := 123.456; 20: num_dbl := 123.456;
Page 47
21: num_num := 123.456; 22: num_real := 123.456; 23: num_sint := 123.456; 24: num_flt := 123.456; 25: 26: --Now display each value to demonstrate that they are all the same. 27: DBMS_OUTPUT.PUT_LINE(num_dec); 28: DBMS_OUTPUT.PUT_LINE(num_int); 29: DBMS_OUTPUT.PUT_LINE(num_dbl); 30: DBMS_OUTPUT.PUT_LINE(num_num); 31: DBMS_OUTPUT.PUT_LINE(num_real); 32: DBMS_OUTPUT.PUT_LINE(num_sint); 33: DBMS_OUTPUT.PUT_LINE(num_flt); 34: END; 35: / 36: 123.46 37: 123.46 38: 123.46 39: 123.46 40: 123.46 41: 123.46 42: 123.456 43: 44: PL/SQL procedure successfully completed.
ANALYSIS
Notice that even the variables defined as
INTEGER and SMALLINT still can hold noninteger values. The underlying datatype allows it, and the use of
INTEGER(5,2) in the declaration overrides the integer subtype definition of
NUMBER(38).
Notice also that the last value printed was 123.456. Why was this not rounded? Because that value came from the variable declared as FLOAT(17). Variables of subtype FLOAT store floating-point values, so rounding might not occur. There is no exact correspondence between binary and decimal precision either. Seventeen bits is slightly more than enough to hold any value that a NUMBER(5,2) variable could hold.
| TIP |
Please don't take advantage of the fact that you can declare an INTEGER variable with a precision and scale, for example INTEGER(5,2), and assign a value to it. Knowing that this can be done is interesting, but actually doing it will be confusing to other programmers. |
The BINARY_INTEGER datatype is used for declaring signed integer variables. Compared to the NUMBER datatype, BINARY_INTEGER variables are stored in binary format, which takes less space. Calculations on binary integers can also run slightly faster because the values are already in a binary format.
Page 48
The Syntax for the BINARY_INTEGER Datatype
variable_name BINARY_INTEGER;
In this syntax, variable_name is whatever you want to name the variable.
Here is a sample declaration:
my_integer BINARY_INTEGER;
A BINARY_INTEGER variable can store any integer value in the range _2,147,483,647 through 2,147,483,647.
| TIP |
If you are running PL/SQL version 2.3 or later, you have access to the new PLS_INTEGER datatype, which is optimized for fast calculations. For new applications I recommend using it instead of BINARY_INTEGER. |
BINARY_INTEGER Subtypes
Oracle has defined five subtypes for the
BINARY_INTEGER datatype, as explained in Table 3.3.
Table 3.3. Subtypes of BINARY_INTEGER.
| Subtype | Usage |
| POSITIVE | Allows only positive integers to be stored, up to the maximum of 2,147,483,647. Zero is not considered a positive number, and so is not an allowed value. |
| NATURAL | Allows only natural numbers to be stored, which includes zero. Allowed values are 0, 1, 2, 3, and so on up to the maximum of 2,147,483,647. |
| POSITIVEn | Like POSITIVE but cannot be null. |
| NATURALn | Like NATURAL but cannot be null. |
| SIGNTYPE | Restricts a variable to only the values -1, 0, and 1. Oracle's built-in sign() function returns values in this range depending on whether its argument is negative, zero, or positive. (New for Oracle8.) |
Unlike the subtypes defined for NUMBER, these subtypes are constraining. There is no way, for example, to define a POSITIVE in such a way as to still allow negative values.
Why would you want to use these subtypes? One reason might be for purposes of documentation. A subtype might be more descriptive of the type of data you intend to store
Page 49
in a variable, which can help prevent mistakes by other programmers who later work on the code. Another reason might be for error detection. If the code is later modified to assign the wrong type of value to a variable, a VALUE_ERROR exception will be generated, alerting the programmer to the mistake. Listing 3.3 shows an example of this.
INPUT/OUTPUT
Listing 3.3. An attempt to assign a negative value to a
POSITIVE variable.
1: --Remember to execute: SET SERVEROUTPUT ON
2: DECLARE
3: age POSITIVE;
4:
5: current_year NATURAL; --a year of 00 is valid.
6: current_month POSITIVE;
7: current_day POSITIVE;
8:
9: birth_year NATURAL; --a year of 00 is valid.
10: birth_month POSITIVE;
11: birth_day POSITIVE;
12:
13: birth_date DATE := TO_DATE('11-15-1961','mm-dd-yyyy');
14: current_date DATE;
15: BEGIN
16: --Set the current date. Normally we would do "current_date := sysdate",
17: --but LET'S pretend it's the year 2000.
18: current_date := TO_DATE ('12-1-2000','mm-dd-yyyy');
19:
20: --Show the effect of trying to set a negative age.
21: --Pretend it's the year 2000 and we forgot to convert this code.
22: --Note that only the two digit year is retrieved.
23: current_year := TO_NUMBER(TO_CHAR(current_date,'yy'));
24: current_month := TO_NUMBER(TO_CHAR(current_date,'mm'));
25: current_day := TO_NUMBER(TO_CHAR(current_date,'dd'));
26:
27: --Oops! Only two digits allowed for birth year.
28: birth_year := TO_NUMBER(TO_CHAR(birth_date,'yy'));
29: birth_month := TO_NUMBER(TO_CHAR(birth_date,'mm'));
30: birth_day := TO_NUMBER(TO_CHAR(birth_date,'dd'));
31:
32: --Now make the actual computation.
33: IF current_month > birth_month THEN
34: age := current_year - birth_year;
35: ELSIF (current_month = birth_month) and (current_day >= birth_day) THEN
36: age := current_year - birth_year;
37: ELSE
38: age := current_year - birth_year - 1;
39: END IF;
40: END;
41: /
42: DECLARE
43: *
44: ERROR at line 1:
45: ORA-06502: PL/SQL: numeric or value error
46: ORA-06512: at line 25