Los procedimientos almacenados son un recurso muy útil y socorrido, pues permiten realizar complejas operaciones sobre las bases de datos sin necesidad de que intervenga el código de las aplicaciones. Por otra parte, permiten realizar ETLs simples dentro de la base de datos, es decir, recopilar información de diferentes consultas y generar un resultado resumido, producto de filtrados y operaciones matemáticas y de información.
A veces, necesitamos obtener el resultado de un procedimiento almacenado en una consulta SELECT. Un ejemplo de ello puede ser en herramientas como Pentaho, donde el diseñador de informes utiliza una consulta para genera el informe.
La forma más sencilla es cuando el resultado es un único valor (un campo y un registro). Para ello, en lugar de un procedimiento almacenado se define una función, que retorne un único valor. En tal caso, para hacer la SELECT, simplemente definimos un campo como resultado de la llamada a dicha función:
SELECT nombre_funcion([parametros]);
Sin embargo, si deseamos obtener más valores, ya sea en un único registro o en varios, obtendremos un error por parte de MySQL, ya que esta sintaxis de SELECT se refiere a un único valor.
Una posibilidad es utilizar un procedimiento almacenado, y obtener los diferentes valores a través de parámetros de tipo OUT. Esto funciona bien, pero el problema radica en que aplicaciones como Pentaho, requieren obtener los datos en una única y simple consulta SELECT. En este caso, necesitamos dos pasos: la llamada al procedimiento (para obtenerlos valores en los parámetros OUT) y la SELECT que obtiene dichos valores:
CALL nombre_procedimiento(@param1, @param2...);
SELECT @param1, param2...;
No se puede obtener de otra manera. Si utilizamos una expresión como la siguiente retornará un error:
SELECT @param1, param2... FROM (CALL nombre_procedimiento(@param1, param2...));
¿Cómo solucionar este problema?
Voy a plantear una propuesta que a mi me ha servido y funciona bien. Se trata de utilizar una función que retorna un valor (físico) que contenga todos los valores (lógicos). Es decir, retornamos una cadena de texto que contiene todos los valores separados por un token. Posteriormente, cuando se recupere con el SELECT, se procederá a extraer todos los valores contenidos, como si fueran diferentes campos de una consulta.
En mi caso, necesitaba extraer diferentes valores que eran el resultado de diferentes operaciones matemáticas, y que resumían, de forma ejecutiva, una serie de ventas clasificadas por diferentes criterios. Todos los datos eran numéricos, y el tamaño máximo era de 10 cifras (sin decimales). Por tanto, monté una cadena de texto en la que concatené diferentes cadenas de 10 caracteres, que eran los números alineados a la derecha y rellenos con espacios por la izquierda. Entre valor y valor añadí un carácter a modo de token (un pipe o un punto y coma, por ejemplo). Por tanto, el valor devuelto por la función MySQL sería algo como esto:
34550| 340575
A la hora de hacer el SELECT, podemos extraer cada uno de los valores como campos independientes, con el tipo de dato correspondiente (en este caso, numérico entero sin signo):
SELECT
CAST(SUBSTRING(funcion() FROM 1 FOR 10) AS SIGNED) AS campo1,
CAST(SUBSTRING(funcion() FROM 12 FOR 10) AS SIGNED) AS campo2;
