In this Oracle tutorial, we will learn about the decimal datatype in the oracle database. Also, we will illustrate how to use it to create decimals columns for a table in oracle.
Introduction to Oracle Decimal datatype
A decimal data type with fixed precision and scale is called DECIMAL in Oracle 21c. It is used to hold decimal numbers. It is comparable to the NUMBER data type but has a lower range and higher precision.
Oracle Decimal datatype Syntax
Here is the syntax for declaring a DECIMAL column in a table is as follows:
DECIMAL (precision, scale)
- It consists of two main parameters
- precision: The total number of digits that the number can contain, including the digits to the right of the decimal point. The precision can range from 1 to 38.
- scale: The number of digits to the right of the decimal point. The scale can range from -84 to 127.
Also, check: How to check database status in Oracle
Oracle Decimal datatype Example
Let’s take an example and creates a table with a DECIMAL column named price with a precision of 6 and a scale of 2.
CREATE TABLE sales ( product_id NUMBER, price DECIMAL (6, 2) );
The “price” column in this example has a DECIMAL data type. It has a scale of 2 and a precision of 6. This indicates that it can store a total of 6 digits: 2 to the right and 4 to the left of the decimal.
Now we will insert the values in a given table
INSERT INTO sales VALUES (67, 2.45);
Now, we will discuss how to use the float datatype in the SQL developer tool. First, we will create a table of students and use the decimal datatype for it.
Now we will insert the values into a students name table
INSERT INTO students VALUES (45, 87.23);
Read: Oracle Create Sequence Tutorial
Storage and performance:
The DECIMAL data type utilizes a fixed-point binary representation, therefore the precision determines how many bytes are needed to hold a DECIMAL value. More bytes will be needed to store the value the more precision you need.
In terms of performance, the DECIMAL data type may be slower than the NUMBER data type because it requires more storage and has a more complex internal representation.
Read: How to add a column to a table in Oracle
So, in this Oracle tutorial, we understood how to define and use the Decimal Datatype in Oracle Database. And we have also covered a few sample examples related to it.
Also, take a look at some more Oracle tutorials.
- Oracle Varchar2 Datatype
- Char Datatype in Oracle Database
- Nchar Datatype in Oracle Database
- NVARCHAR2 Datatype in Oracle Database
- Float Datatype in Oracle Database
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.