What Is Number Data Type in Oracle?


Larry Thompson

In Oracle, the NUMBER data type is used to store numeric values. It is a versatile data type that can store both integer and decimal values with precision and scale. The precision represents the total number of digits that can be stored, while the scale represents the number of decimal places.

Precision and Scale

The precision and scale of a NUMBER data type can be specified during its declaration. For example, a NUMBER(10, 2) can store up to 10 digits in total, with 2 decimal places. If no scale is specified, the default scale is 0.

The maximum precision that can be specified for a NUMBER data type is 38. This means that it can store numbers with up to 38 digits in total.

Numeric Operations

The NUMBER data type supports various numeric operations such as addition, subtraction, multiplication, and division. These operations can be performed using arithmetic operators like + (addition), – (subtraction), * (multiplication), and / (division).

Note: When performing arithmetic operations on NUMBER data types with different precisions or scales, Oracle applies implicit conversion rules to determine the resulting precision and scale.

Numeric Functions

In addition to basic arithmetic operations, Oracle provides a wide range of built-in functions that can be used with the NUMBER data type. These functions allow you to perform advanced mathematical calculations on numeric values.

  • SQRT: Calculates the square root of a given number.
  • POWER: Raises a given number to a specified power.
  • ABS: Returns the absolute value of a given number.
  • ROUND: Rounds a given number to a specified number of decimal places.

Usage Examples

Here are some examples that demonstrate the usage of the NUMBER data type in Oracle:

CREATE TABLE Employees (
  EmployeeID NUMBER(5),
  FirstName VARCHAR2(50),
  LastName VARCHAR2(50),
  Salary NUMBER(10, 2)

In this example, we create a table called Employees, which has columns for EmployeeID, FirstName, LastName, and Salary. The EmployeeID column is defined as a NUMBER(5), which means it can store up to 5 digits. The Salary column is defined as a NUMBER(10, 2), allowing it to store up to 10 digits with 2 decimal places.

To insert values into the table, we can use SQL statements like:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (1, 'John', 'Doe', 5000.00);

This statement inserts a new row into the Employees table with an EmployeeID of 1, FirstName of ‘John’, LastName of ‘Doe’, and Salary of $5000.00.

In Conclusion

The NUMBER data type in Oracle is a powerful tool for storing and manipulating numeric values. With its support for precision and scale, as well as a variety of numeric functions, it provides flexibility and accuracy in handling numerical data.

By understanding the capabilities of the NUMBER data type, you can effectively design and implement database structures that meet your specific requirements.

Discord Server - Web Server - Private Server - DNS Server - Object-Oriented Programming - Scripting - Data Types - Data Structures

Privacy Policy