The NUMBER data type in Oracle SQL is used to store numeric data with high precision and scale. It is a versatile data type that can handle both positive and negative values, as well as integers and decimal numbers.
Defining a NUMBER Data Type:
To define a column with the NUMBER data type in Oracle SQL, you can use the following syntax:
CREATE TABLE table_name (
column_name NUMBER(precision, scale)
);
The precision parameter specifies the total number of digits that can be stored in the column, while the scale parameter determines the number of digits to the right of the decimal point. For example, if you define a column as NUMBER(8, 2), it can store numbers up to 999999.99.
Working with NUMBER Data Type:
Once you have defined a column with the NUMBER data type, you can perform various mathematical operations on it, such as addition, subtraction, multiplication, and division. Here are some examples:
Addition:
To add two values from columns with the NUMBER data type:
SELECT column1 + column2 FROM table_name;
Subtraction:
To subtract one value from another:
SELECT column1 – column2 FROM table_name;
Multiplication:
To multiply two values:
SELECT column1 * column2 FROM table_name;
Division:
To divide one value by another:
SELECT column1 / column2 FROM table_name;
Rounding Numbers:
Sometimes you may need to round numbers to a specific decimal place. Oracle SQL provides several functions for rounding numbers:
- ROUND(): Rounds a number to a specified decimal place.
- TRUNC(): Truncates a number to a specified decimal place.
- CEIL(): Rounds a number up to the nearest integer.
- FLOOR(): Rounds a number down to the nearest integer.
Example:
SELECT ROUND(45.678, 2) FROM dual;
This will return 45.68, rounding the number to two decimal places.
Conclusion:
The NUMBER data type in Oracle SQL is essential for storing and manipulating numeric data with precision and scale. By understanding how to define and work with this data type, you can perform complex calculations and store accurate numerical information in your database.