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;
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
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:
- Convert the PL/SQL associative array to a collection in the SQL scope and use an SQL statement to sum the values; or
- Iterate over the associative array in PL/SQL.
This is the second option and will use
.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; /