PL/SQL table in Oracle

Topic Introduction: In PL/SQL, a PL/SQL table is a composite data type that can hold an indexed collection of data elements. It is similar to an array or a list in other programming languages. PL/SQL tables are useful when you need to store and manipulate a dynamic set of data elements without knowing the exact number of elements in advance.

PL/SQL tables characteristics:

Indexed Collection: Each element in a PL/SQL table is associated with an index, starting from 1. You can access, retrieve, and modify elements in the table using their respective indexes.

Dynamic Size: PL/SQL tables can grow or shrink in size at runtime, allowing you to add or remove elements as needed.

Homogeneous Data Type: All elements in a PL/SQL table must be of the same data type, such as integers, strings, or custom record types.

Local to a Block: PL/SQL tables are typically declared within a PL/SQL block and are not directly stored in the database like database tables.

Here's an example of declaring and using a simple PL/SQL table:

DECLARE

    -- Declare a PL/SQL table of integers

    TYPE number_table IS TABLE OF NUMBER;

    my_table   number_table;

BEGIN

    -- Populate the table

    my_table :=

        number_table (10,

                      20,

                      30,

                      40);

    -- Access and modify elements using indexes

    my_table (2) := 25;                     -- Change the second element to 25

    -- Loop through the elements

    FOR i IN 1 .. my_table.COUNT

    LOOP

        DBMS_OUTPUT.PUT_LINE ('Element ' || i || ': ' || my_table (i));

    END LOOP;

END;

In this example, we declared a PL/SQL table of integers called my_table, populated it with four elements, modified the second element, and then looped through the elements to display their values.