[SOLVED] Aggregation into collection in Oracle PL/SQL

Aggregation into collection in Oracle PL/SQL

Is there a neat way to aggregate table data in PL/SQL and get a single value and a collection in one query? In other words, can we easily combine the two following queries

SELECT AVG(sal)
INTO v_sal
FROM emp;

SELECT DISTINCT deptno
BULK COLLECT INTO v_deptnos
FROM emp;

into something like this:

SELECT AVG(sal), ARRAY_AGG(DISTINCT deptno)
INTO v_sal, v_deptnos
FROM emp;

Solution

Use CAST and COLLECT:

DECLARE
  v_sal EMP.SAL%TYPE;
  v_deptnos number_list;
BEGIN
  SELECT AVG(sal)
       , CAST(COLLECT(DISTINCT deptno) AS number_list)
  INTO   v_sal
       , v_deptnos
  FROM   emp;

  DBMS_OUTPUT.PUT_LINE(v_sal);
  FOR i IN 1 .. v_deptnos.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_deptnos(i));
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE emp (deptno, sal) AS
SELECT 1, 100 FROM DUAL UNION ALL
SELECT 1, 200 FROM DUAL UNION ALL
SELECT 1, 300 FROM DUAL UNION ALL
SELECT 2, 100 FROM DUAL UNION ALL
SELECT 2, 200 FROM DUAL UNION ALL
SELECT 3, 300 FROM DUAL;  

CREATE TYPE number_list IS TABLE OF NUMBER;

Outputs:

fiddle