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.
- So how are the rows getting inserted when I write
select last_name into emp_table(i*3) from employees
. - 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. - 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;
CodePudding user response:
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
CodePudding user response:
Never loop through an associative array using a FOR
loop unless you know that:
- the items are going to be sequential; and
- there are not going to be any deleted items.
(If these conditions hold then you also need to ask yourself why you are using an associative array and not a nested table collection type.)
If you want to loop through an associative array then use FIRST
and NEXT
:
DECLARE
TYPE emptype IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;
emp_table emptype;
idx PLS_INTEGER;
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');
idx := emp_table.FIRST;
WHILE idx IS NOT NULL LOOP
dbms_output.put_line(emp_table(idx));
idx := emp_table.NEXT(idx);
END LOOP;
END;
/
Which, for the sample data:
CREATE TABLE employees (employee_id, last_name) AS
SELECT 100, 'AAA' FROM DUAL UNION ALL
SELECT 101, 'BBB' FROM DUAL UNION ALL
SELECT 102, 'CCC' FROM DUAL UNION ALL
SELECT 103, 'DDD' FROM DUAL UNION ALL
SELECT 104, 'EEE' FROM DUAL UNION ALL
SELECT 105, 'FFF' FROM DUAL;
Outputs:
AAA CCC DDD EEE FFF
db<>fiddle here