How to get the updated count record wise using FORALL using PLSQL

Need help on getting the updated count while using the FORALL

Steps to replicate Create table:

    create table TEST_UPDATE (name_id number(9), name_name varchar2(40));

Insert data

insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (3,'VVV');
insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (3,'VVV');
insert into TEST_UPDATE values (1,'VVV');
insert into TEST_UPDATE values (2,'VVV');
insert into TEST_UPDATE values (1,'VVV');

execute the FORALL update

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    
    l_ids t_ids := t_ids(1,2,3); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ'); -- New salaries
    
    l_updated_ids t_ids;
    l_updated_name t_name;
BEGIN
    FORALL i IN INDICES OF l_ids
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i)
        RETURNING name_id, name_name BULK COLLECT INTO l_updated_ids, l_updated_name;
    
    -- Output the results
    FOR i IN 1..l_updated_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Updated Employee ID: ' || l_updated_ids(i) || ', name: ' || l_updated_name(i));
    END LOOP;

      
END;

With this getting below output

Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 1, name: XXX
Updated Employee ID: 2, name: YYY
Updated Employee ID: 2, name: YYY
Updated Employee ID: 2, name: YYY
Updated Employee ID: 3, name: ZZZ
Updated Employee ID: 3, name: ZZZ


I need to get output like 
Total update for Id 1: 4
Total update for Id 2 :3
Total update for Id 3 :2

With sql%count, it gives the total count as 9. But I am not able to find a way to get the output for each entry wise.

Solution

You can use aggregates in the RETURNING clause (though that doesn't seem to be documented clearly). But you can't have a group-by clause, so you need aggregate all of the returned columns. In this case that's OK, you can just do a max or min of the ID; so instead of:

        RETURNING name_id, name_name BULK COLLECT INTO l_updated_ids, l_updated_name;

you can aggregate both:

        RETURNING max(name_id), count(name_name) BULK COLLECT INTO l_updated_ids, l_updated_name;

giving you:

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    
    l_ids t_ids := t_ids(1,2,3); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ'); -- New salaries
    
    l_updated_ids t_ids;
    l_updated_name t_name;
BEGIN
    FORALL i IN INDICES OF l_ids
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i)
        RETURNING max(name_id), count(name_name) BULK COLLECT INTO l_updated_ids, l_updated_name;
    
    -- Output the results
    FOR i IN 1..l_updated_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Total updates for Employee ID ' || l_updated_ids(i) || ': ' || l_updated_name(i));
    END LOOP;     
END;
/

Total updates for Employee ID 1: 4
Total updates for Employee ID 2: 3
Total updates for Employee ID 3: 2

fiddle

However, if one if the IDs has no matches, the output looks a little odd; but you can refer to the indexed value without aggregating that:

        RETURNING l_ids(i), count(name_name) BULK COLLECT INTO l_updated_ids, l_updated_name;

Then with another pair of values without an ID that doesn't match any rows:

    l_ids t_ids := t_ids(1,2,3,4); -- Example employee IDs
    l_name t_name := t_name('XXX','YYY','ZZZ', 'AAA'); -- New salaries

You see:

Total updates for Employee ID 1: 4
Total updates for Employee ID 2: 3
Total updates for Employee ID 3: 2
Total updates for Employee ID 4: 0

fiddle