PL/SQL vs SQL

FUNCTION getenames(deptno_in IN emp.deptno%type) RETURN VARCHAR2

IS

  TYPE enames_aa IS TABLE OF emp.ename%type INDEX BY pls_integer;

  l_returnvalue VARCHAR2(32767) := '';

  l_enames enames_aa;

BEGIN

  SELECT e.ename

    BULK COLLECT INTO l_enames

    FROM emp e

   WHERE e.deptno = deptno_in

   ORDER BY e.ename ASC NULLS FIRST;

  IF l_enames.count > 0 THEN

    FOR indx IN 1 .. l_enames.count LOOP

      l_returnvalue := l_returnvalue || l_enames(indx) || ',';

    END LOOP;

  END IF;

  l_returnvalue := rtrim(l_returnvalue, ',');

  RETURN l_returnvalue;

END;

 

 

SQL:

 

SELECT d.dname, getenames(d.deptno) enames

  FROM dept d

 

Result:

 

DNAME ENAMES

-------------- ----------------------------------------------------------------------

ACCOUNTING CLARK,KING,MILLER

RESEARCH ADAMS,FORD,JONES,SCOTT,SMITH

SALES ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

OPERATIONS

 

 

New SQL:

SELECT d.dname, (SELECT listagg(e.ename, ',') WITHIN GROUP(ORDER BY e.ename)

                   FROM emp e

                  WHERE 1=1

                    AND e.deptno = d.deptno

                ) enames

  FROM dept d