PL/ SQL Data Types

PL/SQL provides data types to define the kind of data that can be stored in table columns efficiently and accurately.

• They help store different kinds of data such as numbers, text and dates.
• They improve storage efficiency and database performance.
• They help maintain data accuracy and consistency.
• They restrict invalid data entry into columns.

Scalar Data Types in PL/SQL

Scalar data types are basic types that store only one value at a time, like numbers, characters or even logical values each representing an individual value. The scalar data types are categorized into:

Subtypes are defined based on the base scalar types and are formed by placing additional constraints upon values that can be assigned.

Numeric Data Types & Subtypes in PL/SQL

Numeric data types store numbers, both integers and real numbers and allow developers to perform arithmetic operations. The main numeric types include:

For Example:

DECLARE
 num1 NUMBER(5,2) := 123.45;
 num2 PLS_INTEGER := 100;
 num3 FLOAT(10) := 45.67;
BEGIN
 DBMS_OUTPUT.PUT_LINE(num1);
 DBMS_OUTPUT.PUT_LINE(num2);
 DBMS_OUTPUT.PUT_LINE(num3);
END;
/

Character Data Types and Subtypes in PL/SQL

Character data types are used to store text, numbers and symbols in alphanumeric form. They are mainly used for storing and manipulating strings in PL/SQL.

Subtypes of Character Data Types

• STRING: A subtype of VARCHAR2, used to represent variable-length strings.
• LONG VARCHAR: A deprecated subtype of VARCHAR2, previously used to store large strings.

For Example:

DECLARE
 str1 CHAR(10) := 'Hello';
 str2 VARCHAR2(20) := 'Welcome';
 str3 LONG := 'This is a long text';
BEGIN
 DBMS_OUTPUT.PUT_LINE(str1);
 DBMS_OUTPUT.PUT_LINE(str2);
 DBMS_OUTPUT.PUT_LINE(str3);
END;
/

PL/SQL Boolean Data Types

A PL/SQL-only data type used to store logical values. It can have three values: TRUE, FALSE or NULL and is mainly used in conditions and comparisons. It helps in controlling program flow using conditional statements like IF and WHILE.

For Example:

DECLARE
 a BOOLEAN := TRUE;
BEGIN
 IF a THEN
 DBMS_OUTPUT.PUT_LINE('TRUE');
 END IF;
END;

PL/SQL Datetime & Interval Types

Datetime data types store date and time values, while interval data types store the difference between two date and time values. PL/SQL provides different datetime and interval data types:

PL/SQL Large Object (LOB) Data Types

LOB data types store large amounts of unstructured data, such as text, images, videos and audio. PL/SQL provides several LOB types: