Procedimiento almacenado mysql para crear registros a partir de listas separadas por comas

A veces, la información que se almacena en la base de datos son campos que contienen una lista de valores separados por comas.

PROVINCIAS
---------------------------------
Madrid, Cadiz, Sevilla, Cáceres

Pero, ¿Cómo podemos convertir el String separado por comas en una lista de filas? ¿Cómo se puede pasar de una lista de valores separados por comas en una lista de registros?

PROVINCIAS
--------------------------------
Madrid
Cádiz
Sevilla
Cáceres

Para separar cada elemento de una lista en una fila se debe usar un procedimiento almacenado. En el procedimiento almacenado se usarán cursores y la función SPLIT_STR, ya comentada en un post anterior (Ver entrada)

CÓDIGO MYSQL QUE CONVIERTE ELEMENTOS SEPARADOS POR COMA EN FILAS DE UNA TABLA

A continuación escribo el código y procedo a explicarlo:

DELIMITER $$
DROP PROCEDURE IF EXISTS extraerLista$$
CREATE PROCEDURE `extraerLista`(IN `lista` VARCHAR(250))
BEGIN

/*  DECLARACIÓN E INICIALIZACIÓN DE VARIABLES */
DECLARE singleprovince text;
DECLARE cont INT DEFAULT 0;
DECLARE longitud INT DEFAULT 1;
DECLARE str varchar(50) DEFAULT '';
DECLARE provincia varchar(50);

/* DECLARACIÓN DEL CURSOR Y CONDICIÓN DE FINALIZACIÓN */
DECLARE provincia_cur CURSOR FOR SELECT provincias FROM `tabla_provincias` WHERE provincias=lista limit 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @hecho = TRUE;

/* CREACIÓN DE TABLA TEMPORAL */
DROP TABLE if EXISTS my_temp_table; 
CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table (provincias varchar(50));

/* ABRIMOS CURSOR */
OPEN provincia_cur;

/*  LEEMOS CADA ELEMENTO DEL CURSOR HASTA FIN */
do_loop:LOOP

    FETCH provincia_cur INTO singleprovince;

    IF @hecho THEN
    LEAVE do_loop;
    END IF;

    /* extraigo usuarios de la variable singlemember */
    
    SET cont= 0;
    simple_loop: LOOP
            SET cont=cont+1;
            SET str=SPLIT_STR(singleprovince,",",cont);
            IF str='' THEN
                LEAVE simple_loop;
            END IF;
            set provincia=str;
            insert into my_temp_table values (provincia); 
           
    END LOOP simple_loop;


END LOOP do_loop;
CLOSE provincia_cur;

/* SE LLAMA A OTRO PROCEDIMIENTO ALMACENADO QUE CONTIENE LA CONSULTA */
select * from my_temp_table;
END$$
DELIMITER ;

Dado que el propósito de esta entrada es explicar como convertir una cadena de texto separada por comas en un conjunto de registros, he simplificado el procedimiento almacenado y la creación de tablas. La tabla sobre la que trabajamos no tiene un campo clave definido y en términos de normalización de base de datos esto es erróneo por ser una mala práctica. Dicho esto, todos los procedimientos almacenados constan de dos partes:

• Definición y declaración de variables
• Parte lógica del procedimiento

Nuestro procedimiento presenta la misma estructura. Las líneas donde aparece la palabra reservada DECLARE, permiten definir e inicializar la lista de variables que vamos a utilizar a lo largo de la ejecución del procedimiento:

/*  DECLARACIÓN E INICIALIZACIÓN DE VARIABLES */
DECLARE singleprovince text;
DECLARE cont INT DEFAULT 0;
DECLARE longitud INT DEFAULT 1;
DECLARE str varchar(50) DEFAULT '';
DECLARE provincia varchar(50);

Normalmente, cuando hacemos consultas en MySQL nos arroja la lista de filas que coincidan con los parámetros de consulta. En nuestro caso necesitamos que cada fila que nos devuelva nuestra consulta, sea tratada de forma independiente o dicho de otra forma, necesitamos separar elementos de cada fila de texto que obtengamos de la consulta.

Para llevar a cabo operaciones sobre los registros que una consulta SQL devuelve, necesitamos usar cursores, y eso es lo que definimos con las siguientes líneas:

/* DECLARACIÓN DEL CURSOR Y CONDICIÓN DE FINALIZACIÓN */
DECLARE provincia_cur CURSOR FOR SELECT provincias FROM tabla_provincias WHERE provincias=lista limit 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @hecho = TRUE;

/* CREACIÓN DE TABLA TEMPORAL */
DROP TABLE if EXISTS my_temp_table;
CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table (provincias varchar(50));

/* ABRIMOS CURSOR */
OPEN provincia_cur;

La primera línea define que consulta define al cursor. Dado que la tabla no dispone de campo clave se aseguramos que el resultado de la consulta siempre devuelva un único resultado.

La segunda linea indica que el cursor finaliza cuando no haya más registros para leer.

Antes de empezar a trabajar con nuestro cursor, procedemos a crear una tabla temporal. La línea DROP TABLE IF EXISTS, podría omitirse ya que por definición las tablas temporales solo permanecen activas mientras dure la sesión.

Todo el trabajo sucio de nuestro procedimiento almacenado se realiza en el bucle que está encerrado entre las líneas: do_loop:LOOP …. END LOOP: do_loop

La línea siguiente:

FETCH provincia_cur INTO singleprovince;

Guarda la cadena devuelta en el cursor, que en nuestro caso, aunque esté en un bucle, aseguramos que solo devuelva un único resultado. A partir de ahí, mediante otro bucle encadenado leemos los elementos de la cadena ayudándonos de la función SPLIT_STR ya explicada en una entrada anterior. (ver entrada)

El resultado de ejecutar el procedimiento almacenado sería como aparece en la imagen: