Sum up all values from a list PLSQL

I want to sum up all values from a INDEX BY TABLE list.

In the list there are values like this:

24000, 4500, 7890 and so on.

This is the code:

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
  emp_id NUMBER(5);
  emp_sal NUMBER(10);
  i NUMBER(10) := 100;
  counter NUMBER(10) := 0;
  counted NUMBER(10) := 0;

  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee; 
  e_id NUMBER(10);
  
BEGIN
  LOOP
    SELECT employee_id, salary INTO emp_id, emp_sal FROM employees WHERE employee_id = i;
      employeelist(emp_id) := emp_sal;
      i := i + 1; 
    EXIT WHEN emp_id = 110;
  END LOOP;
  
  
  e_id := employeelist.FIRST; 
  LOOP 
  counted := counted + employeelist(e_id); --This is where I want to sum all the things. 
  counter := counter + 1;
  EXIT WHEN employeelist.count > counter;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(counted); --The result should be displayed here but the only thing that get displayed is the last value of the list
  


END;

Answer

Lets assume that you have an associated array and you want to sum up the values within it. And, lets also assume that the associative array’s index is sparse.

So, if we have the test data:

CREATE TABLE employees ( employee_id, salary ) AS
SELECT 42, 24000 FROM DUAL UNION ALL
SELECT 93,  4500 FROM DUAL UNION ALL
SELECT 17,  7890 FROM DUAL;

(Note: the IDs do not go 1, 2, 3.)

then we populate the associative array with the code:

DECLARE
  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee;
BEGIN
  FOR r_emp IN ( SELECT employee_id, salary FROM employees )
  LOOP
    employeelist(r_emp.employee_id) := r_emp.salary;
  END LOOP;
END;
/

To perform the sum then we need to iterate over the associative array. This cannot be done in SQL as an associative array is a PL/SQL only data type so you have two options:

1. Convert the PL/SQL associative array to a collection in the SQL scope and use an SQL statement to sum the values; or
2. Iterate over the associative array in PL/SQL.

This is the second option and will use .FIRST and .NEXT(index) properties of the associative array to iterate over the sparse array (whereas, using a counter and incrementing by 1 will fail when the array is sparse):

DECLARE
  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee;
  
  employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
  counted     NUMBER(10) := 0;
BEGIN
  FOR r_emp IN ( SELECT employee_id, salary FROM employees )
  LOOP
    employeelist(r_emp.employee_id) := r_emp.salary;
  END LOOP;
  
  employee_id := employeelist.FIRST;
  LOOP
    EXIT WHEN employee_id IS NULL;
    counted := counted + employeelist(employee_id);
    employee_id := employeelist.NEXT( employee_id );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( counted );
END;
/

Which outputs:

36390

db<>fiddle here