The Decimal Data Type in Oracle is a numeric data type that is used to store fixed-point numbers with precision and scale. It is commonly used for financial and monetary calculations where accuracy is of utmost importance. The precision represents the total number of digits that can be stored, while the scale represents the number of digits to the right of the decimal point.
Precision and Scale
When declaring a column with the Decimal data type, you need to specify both the precision and scale. For example:
salary DECIMAL(10, 2);
In this example, the column salary will store numbers with a maximum of 10 digits, out of which 2 digits will be reserved for decimal places.
Usage
The Decimal data type is commonly used in financial applications where accurate decimal calculations are required. It provides precise results without any loss of information. Some common examples include storing salaries, prices, tax rates, or interest rates.
Example:
CREATE TABLE employees (
id NUMBER,
name VARCHAR2(50),
salary DECIMAL(10, 2)
);
In this example, we have created a table named employees, which has three columns: id, name, and salary. The salary column is declared as Decimal with a precision of 10 and scale of 2.
Note:
- The maximum precision for Decimal data type in Oracle is 38.
- The maximum scale should be less than or equal to the precision value.
- If precision and scale are not specified, Oracle will use a default precision of 38 and scale of 0.
Operations on Decimal Data Type
Decimal data type supports all arithmetic operations such as addition, subtraction, multiplication, and division. The result of these operations will also have the same precision and scale as the operands.
Example:
DECLARE
num1 DECIMAL(5, 2) := 10.50;
num2 DECIMAL(5, 2) := 5.25;
result DECIMAL(6, 2);
BEGIN
result := num1 + num2;
DBMS_OUTPUT.PUT_LINE('Sum: ' || result);
result := num1 - num2;
DBMS_OUTPUT.PUT_LINE('Difference: ' || result);
result := num1 * num2;
DBMS_OUTPUT.PUT_LINE('Product: ' || result);
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Quotient: ' || result);
END;
In this example, we have declared two Decimal variables num1 and num2. We perform addition, subtraction, multiplication, and division operations on these variables and store the results in the result variable. Finally, we display the results using the DBMS_OUTPUT.PUT_LINE function.
The Decimal data type in Oracle provides a reliable way to store and manipulate fixed-point numbers with accuracy. It is essential for financial calculations where precision is crucial. By understanding its usage and operations, you can effectively work with Decimal data type in Oracle databases.