1. Introducción
Las funciones acumulativas (aggregte functions) son funciones que permiten obtener un valor a través de la acumulación de registros según un patrón definido. Para ser más específicos me refiero a funciones como , AVG, COUNT, SUM, etc. Con estas funcione puedes devolver el promedio, conteo o suma agrupando los registros con la cláusula group by.
En este pequeño artículo veremos la creación de funciones acumulativas en Oracle, haciendo uso de las API's ODCI.
1.1. Contenido
- Definición de funciones acumulativas.
- Oracle Data Cartridge Interface - ODCI
2. Funciones acumulativas - aggregate functions
Oracle tiene funciones de acumulación predefinidas como MAX, MIN, AVG. Éstas funciones permiten hacer cálculos con datos escalares, es decir, tipos de datos primarios como números, texto. Las funciones de acumulación permiten agrupar el resultado, por ejemplo, mostraremos el salario mínimo por departamento:
select department_id as "Departamento", MIN(salary) as "Sueldo Mínimo" from employees group by department_id order by department_id;
También podemos tener el promedio de salarios de empleados por cada id de departamento:
select department_id as "Departamento", AVG(salary) as "Promedio de sueldos" from employees group by department_id order by department_id;
Las funciones acumulativas permiten una agrupación fácil, pero no se pueden crear de cualquier manera, a diferencia de una función o procedimiento almacenado, las funciones acumulativas(aggregate functions) necesitan tener una especie de búfer independiente por cada iteración (agrupación), sólo se pueden definir funciones acumulativas haciendo uso de la API ODCI.
3. Oracle Data Cartridge Interface - ODCI
Oracle Data Cartridge Interface o simplemente ODCI, es una API que permite extender la funcionalidad de un servidor Oracle, estas funcionalidades adicionadas al servidor son denominadas data cartridge. El uso de ésta API se puede hacer a través de los lenguajes de programación soportados por el servidor, entre ellos, Java y PL/SQL.
Al poder ser usados con PL/SQL nos proporciona la ventaja de simplemente agregar funcionalidad al servidor haciendo uso de éste lenguaje, esta API está implementada en el paquete ODCI de Oracle, para poder crear funciones acumulativas deberemos hacer uso de él.
Para la explicación de esta API, crearemos una función que permita concatenar los valores de una columna en una cadena de texto separada por comas, por ejemplo deseamos mostrar los nombres de los empleados por cada departamento:
select department_id as "Departamento", first_name as "Empleado" from employees order by department_id;
Esta consulta nos mostrará el department_id y el nombre de trabajador, pero el department_id se repetirá tantas veces como empleados haya en el departamento, pero que tal si queremos mostrar el department_id con un campo donde aparezcan los nombres de todos sus empleados separados por comas, habría varias maneras de solucionarlo, entre ellas, pero en nuestro caso crearemos una función acumulativa, ésta función será capaz de recibir la columna first_name y agrupará los nombres separados por comas. El nombre de la función que implementaremos será COLUMN_CONCAT y tendrá las siguientes especificaciones:
- Recibirá como parámetro una cadena del tipo VARCHAR2.
- Devolverá un valor del tipo VARCHAR2
- Debemos tener una variable del tipo VARCHAR2, esta variable almacenará los valores de las columnas agrupadas y separadas por coma, ésta variable se llamará cadena.
Primero deberemos crear la implementación de la función para nuestra función, ésta implementación no es más que la creación de un objeto y su cuerpo, añadiéndole la referencia a las funciones ODCI para que sea un objeto de función acumulativa además de colocar la lógica de nuestra función:
create or replace type ColumnConcatImpl as object ( cadena VARCHAR2(2000), STATIC FUNCTION ODCIAggregateInitialize( ini IN OUT ColumnConcatImpl) return number, MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ColumnConcatImpl, value IN VARCHAR2) return number, MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ColumnConcatImpl,ante IN ColumnConcatImpl) return number, MEMBER FUNCTION ODCIAggregateTerminate(self IN ColumnConcatImpl,returnValue OUT VARCHAR2,flags IN number) return number );
En nuestra declaración de objeto debemos agregar nuestra variable cadena VARCHAR2(2000), además de las 4 funciones ODCI que permiten la implementación:
ODCIAggregateInitialize: ésta función inicializa el contexto de acumulación además de crear un objeto que será usado para dicha iteración, es como una función constructora, aquí deberemos inicializar nuestras variables. Parámetros:
- ini IN OUT ColumnConcatImpl: es el objeto utilizado en la agregación del mismo tipo que nuestra implementación, debemos inicializarlo al momento de implementar nuestra función.
ODCIAggregateIterate: ésta función debe procesar los valores de entrada. Aquí deberemos almacenar el valor de entrada en nuestra variable cadena. Parámetros:
- self IN OUT ColumnConcatImpl: es el objeto utilizado en la agregación del mismo tipo que nuestra implementación, debemos usar éste objeto para acceder a las variables de nuestra implementación.
- value IN VARCHAR2 : es el valor de la columna que va a ser acumulada.
ODCIAggregateMerge: ésta función debe fusionar dos objetos de interacciones paralelas en uno solo, no he visto un caso dónde se aplique, por ahora sólo juntaremos el valor de la variable cadena de ambos objetos . Parámetros:
- self IN OUT ColumnConcatImpl: objeto de interacción.
- ante IN ColumnConcatImpl: objeto de interacción.
ODCIAggregateTerminate: ésta función debe calcular el resultado final de la acumulación así como hacer la limpieza respectiva de variables. Aquí deberemos debolver la cadena acumulada.
El cuerpo de nuestro implementación ColumnConcatImpl sería:
create or replace type body ColumnConcatImpl is
STATIC FUNCTION ODCIAggregateInitialize( ini IN OUT ColumnConcatImpl) return number is
begin
ini := ColumnConcatImpl('');
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ColumnConcatImpl, value IN VARCHAR2) return number is
begin
self.cadena:= self.cadena || ',' || value;
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT ColumnConcatImpl,ante IN ColumnConcatImpl)return number is
begin
self.cadena := ante.cadena || ',' || self.cadena;
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateTerminate(self IN ColumnConcatImpl,returnValue OUT VARCHAR2,flags IN number) return number is
begin
returnValue:= SUBSTR(cadena,2);
return SYS.ODCICONST.Success;
end;
end;Finalmente crearemos nuestra función COLUMN_CONCAT hará referencia a nuestra implementación ColumnConcatImpl;
create OR REPLACE function COLUMN_CONCAT(input VARCHAR2) return VARCHAR2 PARALLEL_ENABLE AGGREGATE USING ColumnConcatImpl;
Ahora probemos nuestra función acumulativa, mostrar los nombres de los empleados agrupados por departamentos:
select department_id as "Departamento", COLUMN_CONCAT(first_name) as "Empleado" from employees group by department_id order by department_id;
4. Conclusiones
La API ODCI usada para crear funciones acumulativas es muy sencilla de usar además de que provee un mecanismo poderoso para crear dichas funciones de manera práctica haciendo uso de PL/SQL.
