In Oracle PL/SQL, how do i iterate/access a collection containing the result of LISTAGG function

Question

Assuming table EMP has three columns (deptno, ename and salary). How would i write a for loop that would look like this and be able to access the items in the LISTAGG function

BEGIN

    FOR rec IN (SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
                 FROM   emp
                 WHERE deptNo = 'XYZ'
                 GROUP BY deptno)
    LOOP

        Update EMP
        set salary = 10000
        WHERE ename in (THE ITEMS IN THE LISTAGG)

        FOR EACH (ITEM RETURNED IN THE LISTAGG)
           DO SOMETHING
        END;

    END LOOP;
END;

Basically i want to know two things,

• How can i refer to all the items in returned by the listagg function (See UPDATE statement inside for loop)
• How can i iterate through each of the items returned by teh listagg function. See INNER FOR LOOP.
• Would the syntax for the above also work for WM_CONCAT and COLLECT functions.

Thanks in advance.

Answer 1

Using PL/SQL ListAGG Function Results in a Procedural Loop

Here is a sample application of the OP's request:

BEGIN

FOR rec IN (SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
             FROM  emp
             WHERE deptNo = 'XYZ'
             GROUP BY deptno)
 LOOP

    Update EMP
    set salary = 10000
    WHERE ename in

    ( select regexp_substr(rec.employees,'[^,]+',1,level)
        from dual
     connect by level <= length(regexp_replace(rec.employees,'[^,]+')) + 1
        )

    AND deptNo=rec.deptNo; --not to miss this filter condition

    FOR EACH (ITEM RETURNED IN THE LISTAGG)
       DO SOMETHING
    END;

 END LOOP;
END;

To see another working example of parsing a delimited string list, see Stack Overflow for a similar problem involving a delimited string parsing routine.