What Is the Range of Number Data Type in PL SQL?
In PL/SQL, the NUMBER data type represents numeric values. It is a versatile data type that can store both integers and floating-point numbers. The range of values that can be stored in a NUMBER data type depends on the precision and scale specified for it.
Precision and Scale
The precision of a NUMBER data type determines the total number of digits that can be stored, including both integer and decimal portions. The scale represents the number of digits that can be stored after the decimal point.
To declare a NUMBER variable with a specific precision and scale, you can use the following syntax:
VARIABLE_NAME NUMBER(PRECISION, SCALE);
The maximum precision that can be specified for a NUMBER data type is 38, while the maximum scale allowed is equal to or less than the specified precision.
The range of values that can be stored in a NUMBER data type depends on its precision. Let’s explore some examples:
– Integer Values:
If you declare a NUMBER(10), it means you can store integer values ranging from -999,999,999 to 999,999,999 (inclusive).
– Floating-Point Values:
If you declare a NUMBER(10, 2), it means you can store floating-point numbers ranging from -99,999,999.99 to 99,999,999.99 (inclusive). The two-digit scale allows for two decimal places.
Overflow and Underflow
It’s important to note that if you try to assign a value outside the specified range of a NUMBER data type, an overflow or underflow error will occur. An overflow error occurs when the assigned value is greater than the maximum allowed value, while an underflow error occurs when the assigned value is smaller than the minimum allowed value.
The NUMBER data type in PL/SQL is a powerful and flexible data type for storing numeric values. By specifying the precision and scale, you can control the range of values that can be stored in a NUMBER variable. Understanding the range and limitations of this data type is essential for ensuring accurate calculations and preventing overflow or underflow errors.
Note: It’s important to consult the PL/SQL documentation for specific details about precision, scale, and range based on your Oracle version and configuration.