COMPOSITE DATATYPES

In PL/SQL, composite datatypes allow you to group multiple variables under a single name. There are two main types of composite datatypes:
  • Records
  • Collections
1. Records :
A record is a composite datatype that groups different variables under a single name. Each variable in the record is called a field.

    DECLARE
    -- Declare a record
    TYPE employee_record IS RECORD (
        employee_id NUMBER,
        employee_name VARCHAR2(50),
        employee_salary NUMBER);

    -- Declare a variable of the record type
    emp_info employee_record;

    BEGIN
    -- Assign values to the fields of the record
    emp_info.employee_id := 101;
    emp_info.employee_name := 'John Doe';
    emp_info.employee_salary := 50000;

    -- Display record information
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_info.employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_info.employee_name);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || TO_CHAR(emp_info.employee_salary));

    END;
    /
Output:
Employee ID: 101
Employee Name: John Doe
Employee Salary: 50000
PL/SQL procedure successfully completed.

2. Collections :
  • Collections are another form of composite datatype that can hold multiple values.
  • The two main types of collections are associative arrays (also known as index-by tables) and nested tables.
    • Example - Associative Array:
    
    DECLARE
    TYPE SalaryList IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
    salaries SalaryList;
    BEGIN
    salaries('John') := 50000;
    salaries('Jane') := 60000;
    salaries('Bob') := 55000;

    DBMS_OUTPUT.PUT_LINE('John''s Salary: ' || salaries('John'));
    END;
    Output :
    John's Salary: 50000
    PL/SQL procedure successfully completed.

    Example - Nested Table: 
    
    DECLARE
    TYPE NameList IS TABLE OF VARCHAR2(50);

    names NameList := NameList('John', 'Jane', 'Bob');
    BEGIN
    FOR i IN 1..names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Name: ' || names(i));
    END LOOP;
    END;
    /
    Output :
    Name: John
    Name: Jane
    Name: Bob
    PL/SQL procedure successfully completed.


    (PL/SQL - Explicit Cursors)