Oracle pone a disposición del usuario funciones de agrupación como las funciones MAX, MIN, AVG, ... Pero si nos fijamos, son todas de tipo numérico. Aún así, disponemos también del GROUP BY, pero no llega a solucionar el problema que vamos a plantear.
El objetivo es resolver este problema:
SQL> select deptno, ename
2 from emp
3 group by deptno
4 /
DEPTNO ENAME
---------- -----------------------------------------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 FORD
20 ADAMS
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 TURNER
30 JAMES
30 WARD
y obtener los resultados "agrupados" por el número de departamento usando una función de agrupación de manera que tengamos el resultado de la siguiente manera:
select deptno, str_concat(ename)
2 from emp
3 group by deptno
4 /
DEPTNO STR_CONCAT(ENAME)
---------- -----------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Fantástico, ¿verdad?. Bien, pues para poder hacer esto es necesario crear en Oracle los siguientes objetos:
- La función STR_CONCAT
CREATE OR REPLACE FUNCTION str_concat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/
- El "Type"
create or replace type body string_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ', ' || value; -- Separador
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/
- El "Type bodies"
CREATE OR REPLACE TYPE "STRING_AGG_TYPE" as object
(
total varchar2(4000),
static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
)
/
Una vez creados, basta con usar la función STR_CONCAT como si de una función MAX se tratara.
Esta función es bastante óptima. Cualquier intento de implementar una agrupación de columnas de tipo texto por otros medios, realizando otro tipo de sqls, o realizando la concatenación desde código Java o similar, sólo complica la implementación y reduce drásticamente el rendimiento provocando que la sql tarde demasiado en ejecutarse.
¡Hola! Soy Javier. Soy informático y me gusta documentar todo lo que aprendo. En mi blog te enseño todo lo que sé y cómo puedes aprender a crear tu propia web, mejorar el SEO y aplicar técnicas de Marketing Digital para conseguir mayor visibilidad. Suscríbete para no perderte ninguna de mis guías sobre WordPress, posicionamiento SEO o Marketing Digital.
Excelente... Me sirvió mucho. Probé otras publicaciones que tratan de resolver el mismo problema y esta es la mejor.
Muchas gracias
Excelente, gracias!!
Muy buena solución! muchas gracias!
Excelente Javier, funciona de maravilla, Muchas Gracias
hola javier a mi me sale este error
ORA-29925: no se puede ejecutar SYS.STRING_AGG_TYPE.ODCIAGGREGATEINITIALIZE
ORA-06553: PLS-306: número o tipos de argumentos erróneos al llamar a 'ODCIAGGREGATEINITIALIZE'
Sobre qué versión de Oracle estás intentando usar la función?
Prueba a ejecutar el comando "show errors;" después de la ejecución para que dé más detalles del error. Doy por supuesto que has intentado compilar previamente todos los objetos antes de usar la función.
Un saludo,
Javier.
Hola, excelente función pero al tener una cadena demasiado larga, es decir, más grande que lo que aloja un varchar2, qué se puede hacer?
Gracias
Cuando se quiere meter demasiado texto que no entre en un varchar2 se usa el tipo long. Un saludo, Javier.
Entonces el tipo long lo debo declarar en el campo y en el procedimiento como long......en el código de arriba que expones, debo realizar algún tipo de modificación?
Gracias
Sí. Usa el tipo long como si fuera un varchar, y verás que es capaz de guardar texto. Puedes hacer una prueba creándote una tabla con un campo de tipo long y ver cómo lo guarda. Un saludo.
Estoy usando esta funcion pero le hice cambio para guardar cadenas tipo clob y lo hace bien hasta la tercera cadena, la tercer cadena de caracteres la manda al final y la cuarta y la quinta la pone antes. Que podra estar pasando? Es Oracle 10g
Según veo en el ejemplo, parece que ordena las cadenas por el ID. Si quieres otro tipo de orden, creo que habrá que modificar esta línea y darle algo de lógica para que ordene por el criterío que necesites:
self.total := self.total || ‘, ‘ || value; – SeparadorEl Id te refieres a la localización fisica del registro dentro de la base de datos?, porque estoy ordenándolo por numero de linea asignado al momento de grabar.
Al ID me refiero, siguiendo el ejemplo, a la columna departamento "DEPTNO". En tu caso, será la primary key que tengas.
Excelente... soy novata y esta explicación me sirvio muchisimo,
Muy buen código ... Muchas Gracias