A numeric <data type> is defined by a descriptor that contains four pieces of information:

  1. The <data type>'s name: either INTEGER, SMALLINT, NUMERIC, DECIMAL, FLOAT, REAL or DOUBLE PRECISION.
  2. The <data type>'s precision.
  3. The <data type>'s scale (for exact numeric types).
  4. Whether the <data type>'s precision and scale are expressed in decimal or binary terms.

INTEGER:

The required syntax for an INTEGER <data type> specification is:

INTEGER <data type> ::=
INTEGER

INTEGER may be abbreviated as INT; it defines a set of possibly signed whole numbers that have a scale of zero.

[NON-PORTABLE] INT's precision must be greater than or equal to the precision of SMALLINT but is non-standard because the SQL Standard requires implementors to define INT's precision. FIPS says that INT should have a precision of at least 9 digits. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines INT as a 32-bit, signed binary numeric, i.e.: INT corresponds to the C long int data type. Thus, INT defines a set of values that are possibly signed whole numbers with a precision of 31 bits and a scale of zero, e.g.: -6500 or 476673.

[NON-PORTABLE] INT's radix must be the same as the radix chosen for SMALLINT but is non-standard because the SQL Standard requires implementors to define whether INT and SMALLINT have a binary radix or a decimal radix. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines INT and SMALLINT with a binary radix, i.e.: 2. This gives INT a valid range of -2,147,483,647 to +2,147,483,647.

SMALLINT:

The required syntax for a SMALLINT <data type> specification is:

SMALLINT <data type> ::=
SMALLINT

SMALLINT defines a set of possibly signed whole numbers that have a scale of zero.

[NON-PORTABLE] SMALLINT's precision must be less than or equal to the precision of INT but is non-standard because the SQL Standard requires implementors to define SMALLINT's precision. FIPS says that SMALLINT should have a precision of at least 4 digits. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines SMALLINT as a 16-bit signed binary numeric, i.e.: SMALLINT corresponds to the C int data type. Thus, SMALLINT defines a set of values that are possibly signed whole numbers with a precision of 15 bits and a scale of zero, e.g.: -65 or 476.

[NON-PORTABLE] SMALLINT's radix must be the same as the radix chosen for INT but is non-standard because the SQL Standard requires implementors to define whether SMALLINT and INT have a binary radix or a decimal radix. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines SMALLINT and INT with a binary radix, i.e.: 2. This gives SMALLINT a range of -32,767 to +32,767.

NUMERIC:

The required syntax for a NUMERIC <data type> specification is:

NUMERIC <data type> ::=
NUMERIC [ (precision[,scale]) ]

NUMERIC is a fixed-point numeric with a decimal precision and decimal scale that are equal to the explicit precision and the explicit scale given; it defines a set of values that are possibly signed decimal numbers with an optionally defined precision and optionally defined scale, e.g.: 65.73 or .6 or -819.3 or -.25.

The optional precision, if specified, is an unsigned integer that defines the maximum precision of acceptable values. The minimum precision is 1.

[NON-PORTABLE] The default precision and the maximum precision for NUMERIC are non-standard because the SQL Standard requires implementors to define NUMERIC's default and maximum precisions. Typically, the maximum precision is 15 (the FIPS requirement); it may be as high as 38 (the DB2 maximum). [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of NUMERIC to range from 1 to 38, with a default precision of 1. For example, this <data type> specification defines a set of values that may range from -9999 to +9999 (4 digits defined):

NUMERIC(4)

and these two equivalent <data type> specifications define a set of values that may range from -9 to +9 (1 digit defined or default):

NUMERIC(1)
NUMERIC

The optional scale, if specified, is an unsigned integer, greater than zero, that defines the maximum number of digits in the scale of acceptable values. It must be less than the precision and defaults to zero if omitted. You may define a scale for NUMERIC only if you also define a precision: if no precision is defined, the scale must default to zero.

[NON-PORTABLE] The maximum scale for NUMERIC must always be less than the defined precision but is non-standard because the SQL Standard requires implementors to define NUMERIC's maximum scale. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a scale ranging from 1 to 38 for NUMERIC. For example, this <data type> specification defines a set of values that may range from -999.9 to +999.9 (3 digits before the decimal point and 1 digit after the decimal point, for a total of 4 digits):

NUMERIC(4,1)

DECIMAL:

The required syntax for a DECIMAL <data type> specification is:

DECIMAL <data type> ::=
DECIMAL [ (precision[,scale]) ]

DECIMAL may be abbreviated as DEC and is a fixed-point numeric with a decimal scale that is equal to the explicit scale given; it defines a set of values that are possibly signed decimal numbers with an optionally defined precision and optionally defined scale, e.g.: 65.73 or .6 or -819.3 or -.25.

The optional precision, if specified, is an unsigned integer that defines the maximum precision of acceptable values. DEC's decimal precision must be at least equal to the precision you define -- compare COBOL, which allows "PIC S9(3) COMP-1" but might allot a full-word "PIC S9(5)" for internal storage. The minimum precision is 1.

[NON-PORTABLE] The default precision, maximum precision and exact precision for DEC are non-standard because the SQL Standard requires implementors to define DEC's default, maximum and exact precisions. Typically, the maximum precision is 15 (the FIPS requirement); it may be as high as 38 (the DB2 maximum). [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of DEC to range from 1 to 38, with a default precision of 1. DEC's decimal precision is equal to the precision you define, i.e.: OCELOT treats DEC and NUMERIC as synonyms. For example, this <data type> specification defines a set of values that may range from -9999 to +9999 (4 digits defined):

DEC(4)

and these two equivalent <data type> specifications define a set of values that may range from -9 to +9 (1 digit defined or default):

DEC(1)
DECIMAL

The optional scale, if specified, is an unsigned integer, greater than zero, that defines the maximum number of digits in the scale of acceptable values. It must be less than the precision and defaults to zero if omitted. You may define a scale for DEC only if you also define a precision: if no precision is defined, the scale must default to zero.

[NON-PORTABLE] The maximum scale for DEC must always be less than the defined precision but is non-standard because the SQL Standard requires implementors to define DEC's maximum scale. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a scale ranging from 1 to 38 for DEC. For example, this <data type> specification defines a set of values that may range from -999.9 to +999.9 (3 digits before the decimal point and 1 digit after the decimal point, for a total of 4 digits):

DEC(4,1)

FLOAT:

The required syntax for a FLOAT <data type> specification is:

FLOAT <data type> ::=
FLOAT [ (precision) ]

FLOAT is a floating-point numeric with a binary precision; it defines a set of values that are possibly signed floating point numbers.

The optional precision, if specified, is an unsigned integer that defines the maximum number of bits (including the hidden bit) in the mantissa of acceptable values. FLOAT's binary precision must be at least equal to the precision you define. The minimum precision is 1.

[NON-PORTABLE] The default precision, maximum precision and binary precision for FLOAT are non-standard because the SQL Standard requires implementors to define FLOAT's default, maximum and exact precisions. FIPS says that FLOAT should have a binary precision of at least 20 digits. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of FLOAT to range from 1 to 53, with a default precision of 53. Thus, FLOAT defines a set of values that are possibly signed floating point numbers with this format:

[sign]+digit+period+ up to 14 digits+E+[sign]+ up to 3 digits

For example: -1.27982E+015 or .465E-007. The IEEE Standard for Binary Floating-Point Arithmetic (IEEE Standard 754-1985) specifies two usual mantissa sizes: 24 and 53. OCELOT supports both: regardless of the actual precision specified for FLOAT, there are really only two possible results. If you define FLOAT with a precision that is less than or equal to 24, the actual binary precision will equal 24 bits in the mantissa. For example, these two <data type> specifications are equivalent: they both define a set of floating point values whose mantissa may range up to a precision of 24 bits:

   FLOAT(12)
   FLOAT(24)

If you define FLOAT with a precision between 25 and 53, the actual binary precision will equal 53 bits in the mantissa. For example, these three <data type> specifications are equivalent: they all define a set of floating point values whose mantissa may range up to a precision of 53 bits:

FLOAT
FLOAT(27)
FLOAT(53)

[NON-PORTABLE] The minimum exponent and the maximum exponent for FLOAT are non-standard because the SQL Standard requires implementors to define FLOAT's minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define an exponent ranging from -038 to +038 for FLOAT.

REAL:

The required syntax for a REAL <data type> specification is:

REAL <data type> ::=
REAL

REAL is a floating-point numeric with a binary precision, i.e.: REAL defines a set of values that are possibly signed floating point numbers.

[NON-PORTABLE] The binary precision of REAL must be less than the precision defined for DOUBLE PRECISION but is non-standard because the SQL Standard requires implementors to define REAL's exact precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book treats REAL as a synonym for FLOAT(24). Thus, REAL defines a set of values that are possibly signed floating point numbers with this format:

[sign]+digit+period+up to 6 digits+E+[sign]+ up to 2 digits

For example: -1.27982E+15 or .465E-07.

[NON-PORTABLE] The minimum exponent and the maximum exponent for REAL are non-standard because the SQL Standard requires implementors to define REAL's minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define an exponent ranging from -38 to +38 for REAL.

DOUBLE PRECISION:

The required syntax for a DOUBLE PRECISION <data type> specification is:

DOUBLE PRECISION <data type> ::=
DOUBLE PRECISION

DOUBLE PRECISION is a floating-point numeric with a binary precision, i.e.: DOUBLE PRECISION defines a set of values that are possibly signed floating point numbers.

[NON-PORTABLE] The binary precision of DOUBLE PRECISION must be greater than the precision defined for REAL but is non-standard because the SQL Standard requires implementors to define DOUBLE PRECISION's exact precision. FIPS says that DOUBLE PRECISION should have a binary precision of at least 30 digits. [OCELOT Implementation] The OCELOT DBMS that comes with this book treats DOUBLE PRECISION as a synonym for FLOAT(53). Thus, DOUBLE PRECISION defines a set of values that are possibly signed floating point numbers with this format:

[sign]+digit+period+up to 14 digits+E+[sign]+up to 3 digits

For example: -1.27982E+015 or .465E-007.

[NON-PORTABLE] The minimum exponent and the maximum exponent for DOUBLE PRECISION are non-standard because the SQL Standard requires implementors to define DOUBLE PRECISION's minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define an exponent ranging from -038 to +038 for DOUBLE PRECISION.

Now that we've described SQL's numeric <data type>s, let's look at some example SQL statements that put them to use.

These SQL statements make a Table with four exact numeric Columns, insert a row, then search for any number less than -1.

CREATE TABLE Exact_Examples (
     occurrence_decimal DECIMAL(5),
     occurrence_numeric NUMERIC(7,2),
     occurrence_integer INTEGER,
     occurrence_smallint SMALLINT);

INSERT INTO Exact_Examples (
     occurrence_decimal,
     occurrence_numeric,
     occurrence_integer,
     occurrence_smallint)
     VALUES (12345, 12345, 12345, 12345);

SELECT occurrence_decimal,
       occurrence_numeric,
       occurrence_integer,
       occurrence_smallint
FROM   Exact_Examples
WHERE  occurrence_decimal < -1;

These SQL statements make a Table with three approximate numeric Columns, insert a row, then search for any number less than 50000.

CREATE TABLE Approximate_Examples (
     occurrence_float FLOAT(53),
     occurrence_real REAL,
     occurrence_double DOUBLE PRECISION);

INSERT INTO Approximate_Examples (
     occurrence_float,
     occurrence_real,
     occurrence_double)
     VALUES (5E+2, 5E+2, 5E+2);

SELECT occurrence_float,
       occurrence_real,
       occurrence_double
FROM   Approximate_Examples
WHERE  occurrence_float < 5E+4;

Note:

Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.

Comments

Comments loading...