PL/SQL Data Types

Data types define the kind of data that can be stored and manipulated in a PL/SQL program. Oracle PL/SQL provides a wide range of data types, categorized into scalar, composite, reference, and LOB (Large Object) data types. Understanding these data types is crucial for writing efficient and optimized PL/SQL code.

1. Scalar Data Types

Scalar data types hold a single value and do not have internal components. These are the most basic data types in PL/SQL.

Common Scalar Data Types

Data Type	Description	Example
`VARCHAR2(size)`	Variable-length character string (1 to 32,767 bytes)	`VARCHAR2(50)`
`CHAR(size)`	Fixed-length character string (1 to 32,767 bytes)	`CHAR(10)`
`NUMBER(p,s)`	Numeric value (p = precision, s = scale)	`NUMBER(10,2)`
`BOOLEAN`	Stores `TRUE`, `FALSE`, or `NULL` (Only in PL/SQL, not in SQL)	`BOOLEAN`
`DATE`	Stores date and time (YYYY-MM-DD HH:MI:SS)	`DATE`
`TIMESTAMP`	Stores date and time with fractional seconds	`TIMESTAMP`
`BINARY_INTEGER`	Integer value optimized for performance	`BINARY_INTEGER`
`PLS_INTEGER`	Similar to `BINARY_INTEGER` but faster	`PLS_INTEGER`

Example of Scalar Data Type Usage

DECLARE
    v_name VARCHAR2(50);
    v_age NUMBER(3);
    v_is_active BOOLEAN;
    v_join_date DATE;
BEGIN
    v_name := 'John Doe';
    v_age := 30;
    v_is_active := TRUE;
    v_join_date := SYSDATE;
    
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Age: ' || v_age);
    DBMS_OUTPUT.PUT_LINE('Joining Date: ' || v_join_date);
END;
/

2. Composite Data Types

Composite data types can hold multiple values, making them useful for storing complex structures. These include RECORDS, PL/SQL TABLES, VARRAYs, and NESTED TABLES.

2.1 RECORD Data Type

A RECORD is similar to a struct in C or object in other languages, containing multiple fields of different types.

Example of RECORD Usage

DECLARE
    TYPE emp_record IS RECORD (
        emp_id NUMBER(5),
        emp_name VARCHAR2(50),
        salary NUMBER(10,2)
    );
    v_emp emp_record;
BEGIN
    v_emp.emp_id := 101;
    v_emp.emp_name := 'Alice';
    v_emp.salary := 5000;

    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.emp_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp.emp_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_emp.salary);
END;
/

2.2 PL/SQL TABLE (Associative Array)

A one-dimensional, sparse collection indexed by either BINARY_INTEGER or VARCHAR2.
Useful for in-memory data storage.

Example of PL/SQL Table

DECLARE
    TYPE emp_table IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
    v_employees emp_table;
BEGIN
    v_employees(1) := 'John';
    v_employees(2) := 'Alice';
    
    DBMS_OUTPUT.PUT_LINE('Employee 1: ' || v_employees(1));
    DBMS_OUTPUT.PUT_LINE('Employee 2: ' || v_employees(2));
END;
/

2.3 VARRAY (Variable-Size Array)

A fixed-size collection of elements of the same type.
Requires a size limit at declaration.

Example of VARRAY Usage

DECLARE
    TYPE arr_names IS VARRAY(3) OF VARCHAR2(50);
    v_names arr_names := arr_names('John', 'Alice', 'Bob');
BEGIN
    FOR i IN 1..v_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_names(i));
    END LOOP;
END;
/

2.4 NESTED TABLE

Similar to a VARRAY but does not have a predefined limit and supports DML operations.

Example of Nested Table Usage

DECLARE
    TYPE emp_nested IS TABLE OF VARCHAR2(50);
    v_emps emp_nested := emp_nested('John', 'Alice', 'Bob');
BEGIN
    v_emps.EXTEND;
    v_emps(4) := 'Charlie';

    FOR i IN 1..v_emps.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emps(i));
    END LOOP;
END;
/

3. Reference Data Types (REF CURSOR)

A REF CURSOR is a pointer to a result set, allowing dynamic SQL queries. It is useful for returning query results to applications.

Example of REF CURSOR

DECLARE
    TYPE emp_cursor IS REF CURSOR;
    v_cursor emp_cursor;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN v_cursor FOR SELECT first_name FROM employees WHERE department_id = 10;
    FETCH v_cursor INTO v_name;

    WHILE v_cursor%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
        FETCH v_cursor INTO v_name;
    END LOOP;
    
    CLOSE v_cursor;
END;
/

4. LOB (Large Object) Data Types

LOB types store large amounts of data, such as images, documents, and multimedia files.

LOB Data Types

Data Type	Description
`BLOB`	Stores binary large objects (e.g., images, audio, video)
`CLOB`	Stores large character data (e.g., documents, XML)
`NCLOB`	Stores Unicode character data
`BFILE`	Stores a file reference to external OS files

Example of Using CLOB

DECLARE
    v_large_text CLOB;
BEGIN
    v_large_text := 'This is a long text stored in a CLOB column.';
    DBMS_OUTPUT.PUT_LINE(v_large_text);
END;
/

Conclusion

PL/SQL provides a rich set of data types to handle different data storage and processing requirements:

Scalar types store single values (e.g., VARCHAR2, NUMBER, BOOLEAN).
Composite types store multiple values in structured formats (RECORD, VARRAY, NESTED TABLE).
Reference types (REF CURSOR) allow dynamic queries.
LOB types handle large binary and character data (BLOB, CLOB).

Choosing the right data type is crucial for performance optimization and efficient database operations.