How does select column into associative array from table work in Oracle database

Question

when I am multiplying the index by 3 instead of one, three rows are getting inserted into the associative array and also it seems that the 2nd and 3rd rows have no value present.

1. So how are the rows getting inserted when I write select last_name into emp_table(i*3) from employees.
2. Could you also tell what emp_table.first..emp_table.last is doing i.e. is it returning some kind of reference to the Associative array or is it just returning the first and last index of the columns of the array. in which case how does it work if the list is not ordered and has gaps.
3. What is happening when I delete a column, is that only the value is getting deleted and the index remains.

DECLARE
    TYPE emptype IS
        TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;
    emp_table emptype;
BEGIN
    FOR i IN 100..105 LOOP
        SELECT
            last_name
        INTO
            emp_table(i * 3)
        FROM
            employees
        WHERE
            employees.employee_id = i;

    END LOOP;
--emp_table.delete('303');
    FOR i IN emp_table.first..emp_table.last LOOP
--dbms_output.put_line(emp_table(i));
        dbms_output.put_line(i);
    END LOOP;

END;

Answer 1

If i = 100, then array element (i * 3) just means element 300. Your code puts the value for employee 100 into emp_table(300), it doesn't create three entries.

first and last are collection methods that return the first and last index values respectively.

emp_table.first..emp_table.last is part of FOR loop syntax and defines a loop that will step through all integers from emp_table.first to emp_table.last inclusive. This only works if the collection index consists of consecutive integers. It looks like your loop will print values of i from 300 to 315, which will work, but it will fail with "ORA-01403: no data found" if you try to access emp_table(i) for i = 301. Up to Oracle 19c, you have to use the next collection method to find the next value within a loop indexed by non-consecutive integers, or strings.

From Oracle 21c you can use the more convenient iterator syntax:

declare
    type aa is table of number index by pls_integer;
    a aa;
    n pls_integer := 1;
begin
    a(n) := 123;
    a(n*3) := 456;
    
    dbms_output.put_line('"myArray" contains '||a.count||' elements, from '||a.first||' to '||a.last);
    dbms_output.new_line();
    dbms_output.put_line('Indices:');  
    
    for i in indices of a loop
        dbms_output.put_line(i);
    end loop;

    dbms_output.new_line();
    dbms_output.put_line('Values:');  

    for v in values of a loop
        dbms_output.put_line(v);
    end loop;

    dbms_output.new_line();
    dbms_output.put_line('Pairs:');  
    
    for i, v in pairs of a loop
        dbms_output.put_line(i||': '||v);
    end loop;
end;

"myArray" contains 2 elements, from 1 to 3

Indices:
1
3

Values:
123
456

Pairs:
1: 123
3: 456