PLSQL datatypes
In this article, we will understand briefly PLSQL datatypes. Each value in PL/SQL such as constant, variable, or parameter has a data type that defines their storage and calculation behavior. PL/SQL has basically two types of data types e.g scalar and composite.
Types of sql data types
Scalar data types
In scalar data type are those data type that stores a single value. Scalar data type has further four data subtypes
Composite data types
Whereas composite data type stores multiple values. There are two data sub types of composite data types are available.
- Record
- Collection
In this article, we will be discussing only scalar data type
Oracle number data type
The numeric data type represents real numbers, integer, and floating numbers. It can store numbers in fixed and floating-point format. It provides precision up to 38 decimals. It cal also stores positive and negative numbers along with zero.
In sql while creating a table we can assign column as below
For numeric columns we can specify the column as a floating-point number:
column_name NUMBER
Also, we can specify a precision (total number of digits) and scale (number of digits to right of decimal point):
column_name NUMBER (precision, scale)
Oracle boolean data type
The BOOLEAN datatype has three data values: TRUE, FALSE, and NULL. Boolean values are typically used in control flow structures such as IF-THEN, CASE, and loop statements like LOOP, FOR LOOP, and WHILE LOOP. As SQL doesn’t have boolean data type so we can’t use it in SQL column or SELECT statement or in SQL function
Character data types
The SQL character data types represent alphanumeric text. PL/SQL uses the SQL character data types such as CHAR, VARCHAR2, LONG, RAW, LONG RAW, ROWID, and UROWID.
- CHAR(n) is a fixed-length character type whose length is from 1 to 32,767 bytes.
- VARCHAR2(n) is varying length character data from 1 to 32,767 bytes.
- LONG variable for each row in the table, up to 2^31 – 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
- RAW variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.
- LONG RAW Variable for each row in the table, up to 2^31 – 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
- ROWID Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.
Datetime data types
The datetime data types represent dates, timestamp with or without time zone, and intervals. PL/SQL DateTime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.
- Date format : For input and output of dates, the standard Oracle default date format is
DD-MON-YY. To chnage date format we can use TO_DATE function. - Time format : Time is stored in 24-hour format#HH:MM:SS.By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered.
You can read more about SQL data types from this link.
To know about how to install oracle database click here.