En ocasiones, es necesario agregar datos de varias filas en una sola fila, dando una lista de datos asociados con un valor específico. Utilizando la tabla SCOTT.EMP como ejemplo, podríamos querer recuperar una lista de empleados para cada departamento. A continuación se muestra una lista de los datos base y el tipo de resultado que nos gustaría obtener de una consulta agregada.
Base Data: DEPTNO ENAME ---------- ---------- 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER Desired Output: DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Este artículo se basa en un hilo de asktom.oracle.com y contiene varios métodos para lograr los resultados deseados.
- Preparar
- Función Analítica LISTAGG en 11g Release 2
- Función incorporada WM_CONCAT (Not Supported)
- Función agregada definida por el usuario
- Función específica
- Función genérica usando Ref Cursor
- Funciones ROW_NUMBER() y SYS_CONNECT_BY_PATH en Oracle 9i
- Función COLLECT en Oracle 10g
Preparar
Los ejemplos de este artículo requieren la siguiente tabla.
--DROP TABLE emp PURGE; CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
Función analítica LISTAGG en 11g versión 2
La función analítica LISTAGG se introdujo en Oracle 11g Release 2, lo que facilita la agregación de cadenas. Lo bueno de esta función es que también nos permite ordenar los elementos en la lista concatenada. Si está utilizando 11g Release 2, debería utilizar esta función para la agregación de cadenas.
COLUMN employees FORMAT A50 SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected. SQL>
Función incorporada WM_CONCAT (Not Supported)
Si no está ejecutando 11g Release 2 o superior, pero está ejecutando una versión de la base de datos donde está presente la función WM_CONCAT, entonces es una solución de esfuerzo cero, ya que realiza la agregación por usted. En realidad, es un ejemplo de una función agregada definida por el usuario que se describe a continuación, pero Oracle ha hecho todo el trabajo por usted.
COLUMN employees FORMAT A50 SELECT deptno, wm_concat(ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD 3 rows selected. SQL>
WM_CONCAT es una función no documentada y, como tal, Oracle no admite aplicaciones de usuario (MOS Note ID 1336219.1). Si esto le preocupa, utilice una función agregada definida por el usuario que se describe a continuación.
Además, WM_CONCAT se ha eliminado de 12c en adelante, por lo que no puede elegir esta opción.
Función agregada definida por el usuario
La función WM_CONCAT descrita anteriormente es un ejemplo de una función agregada definida por el usuario que Oracle ya ha creado para usted. Si no desea utilizar WM_CONCAT, puede crear su propia función agregada definida por el usuario como se describe en asktom.oracle.com. Gracias a Kim Berg Hansen por algunas correcciones en los comentarios.
CREATE OR REPLACE TYPE t_string_agg AS OBJECT ( g_string VARCHAR2(32767), STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2 ) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER ); / SHOW ERRORS CREATE OR REPLACE TYPE BODY t_string_agg IS STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER IS BEGIN sctx := t_string_agg(NULL); RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2 ) RETURN NUMBER IS BEGIN SELF.g_string := self.g_string || ',' || value; RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS BEGIN returnValue := SUBSTR(SELF.g_string, 2); RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER IS BEGIN SELF.g_string := SELF.g_string || ctx2.g_string; RETURN ODCIConst.Success; END; END; / SHOW ERRORS CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING t_string_agg; / SHOW ERRORS
La función agregada se implementa mediante un tipo y un cuerpo de tipo, y se utiliza dentro de una consulta.
COLUMN employees FORMAT A50 SELECT /*+ PARALLEL(2) */ deptno, string_agg(ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD 3 rows selected. SQL>
Función específica
Un enfoque es escribir una función específica para resolver los problemas. La función get_employees que se muestra a continuación devuelve una lista de empleados para el departamento especificado.
CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE) RETURN VARCHAR2 IS l_text VARCHAR2(32767) := NULL; BEGIN FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP l_text := l_text || ',' || cur_rec.ename; END LOOP; RETURN LTRIM(l_text, ','); END; / SHOW ERRORS Luego, la función se puede incorporar a una consulta de la siguiente manera. COLUMN employees FORMAT A50 SELECT deptno, get_employees(deptno) AS employees FROM emp GROUP by deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected. SQL>
Para reducir el número de llamadas a la función y, por lo tanto, mejorar el rendimiento, es posible que deseemos filtrar las filas por adelantado.
COLUMN employees FORMAT A50 SELECT e.deptno, get_employees(e.deptno) AS employees FROM (SELECT DISTINCT deptno FROM emp) e; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected. SQL>
Función genérica usando Ref Cursor
Un enfoque alternativo es escribir una función para concatenar los valores pasados usando un cursor de referencia. Esto es esencialmente lo mismo que el ejemplo anterior, excepto que el cursor se pasa para hacerlo genérico, como se muestra a continuación.
CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN SYS_REFCURSOR) RETURN VARCHAR2 IS l_return VARCHAR2(32767); l_temp VARCHAR2(32767); BEGIN LOOP FETCH p_cursor INTO l_temp; EXIT WHEN p_cursor%NOTFOUND; l_return := l_return || ',' || l_temp; END LOOP; RETURN LTRIM(l_return, ','); END; / SHOW ERRORS
La expresión CURSOR se usa para permitir que se pase una consulta a la función como un cursor de referencia, como se muestra a continuación.
COLUMN employees FORMAT A50 SELECT e1.deptno, concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees FROM emp e1 GROUP BY e1.deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected. SQL>
Una vez más, el número total de llamadas a funciones se puede reducir filtrando los distintos valores, en lugar de llamar a la función para cada fila.
COLUMN employees FORMAT A50 SELECT deptno, concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees FROM (SELECT DISTINCT deptno FROM emp) e1; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected. SQL>
Funciones ROW_NUMBER () y SYS_CONNECT_BY_PATH en Oracle 9i
Un ejemplo en williamrobertson.net usa las funciones ROW_NUMBER () y SYS_CONNECT_BY_PATH para lograr el mismo resultado sin el uso de PL/SQL o definiciones de tipo adicionales.
SELECT deptno, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees FROM (SELECT deptno, ename, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev FROM emp) GROUP BY deptno CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno START WITH curr = 1; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected. SQL>
Función COLLECT en Oracle 10g
Un ejemplo en oracle-developer.net usa la función COLLECT en Oracle 10g para obtener el mismo resultado. Este método requiere un tipo de tabla y una función para convertir el contenido del tipo de tabla en una cadena. He modificado ligeramente su método para adaptarlo a este artículo.
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab, p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS l_string VARCHAR2(32767); BEGIN FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP IF i != p_varchar2_tab.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_varchar2_tab(i); END LOOP; RETURN l_string; END tab_to_string; /
La consulta siguiente muestra la función COLLECT en acción.
COLUMN employees FORMAT A50 SELECT deptno, tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected. SQL>
Para más información, ver:
• User-Defined Aggregate Functions
• asktom.oracle.com
• williamrobertson.net
• oracle-developer.net – the collect function in 10g
• LISTAGG