MYSQL – Sentencias SQL dinámicas en procedimientos almacenados

Una sentencia SQL Dinámica es un conjunto de cláusulas armadas en tiempo de ejecución que pueden ser almacenadas en variables de tipo Varchar e interpretadas y ejecutadas por el Manejador de Base de datos. Esto supone una ventaja para aquellas situaciones dónde no se conocen de manera predeterminada las condiciones de lo que deseamos ejecutar. El uso de este tipo de sentencias lo encontramos muy extendido cuando programamos del lado del cliente, es decir, de un programa que consume recursos del servidor de base de datos, pero, ¿qué sucede si por x razón debemos usarlas dentro del código de nuestros procedimientos almacenados?. Bueno en esta entrada aprenderemos cómo hacerlo.

*** Importante: Usar SQL dinámico dentro de procedimientos almacenados desperdicia las ventajas de un plan de ejecución generado.

Mysql nos  proporciona un conjunto de sentencias que nos permiten ejecutar sentencias a partir del contenido de una variable varchar, la documentación puede encontrarse en: SQL Syntax for Prepared Statements. Para ejemplificar su uso crearemos un procedimiento almacenado que dado un schema, nos devuelva el número de filas en cada tabla contenida en él, para conseguirlo ejecutaremos una sentencia dinámica que incruste en una tabla temporal el nombre de la tabla y el número de registros que esta contiene.  Para crear este ejemplo iremos paso por paso.

Crearemos primero la definición de nuestro procedimiento almacenado.

DROP procedure if exists cnc_sp_cuentafilastablas;

CREATE PROCEDURE cnc_sp_cuentafilastablas( 
         pv_nomb_schema varchar(100) 
        )
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
BEGIN

END;

Declaramos las variables que usaremos
  DECLARE vb_termina BOOL DEFAULT FALSE; -- Bandera para terminar las iteraciones
  DECLARE VV_NombTabla varchar(250) default '';  -- Nombre de la tabla

Declaramos el handler que se ejecutará al llegar al final del cursor

 DECLARE CONTINUE HANDLER 
     FOR SQLSTATE '02000'
     SET vb_termina = TRUE;

Declaramos un cursor que nos permita obtener los nombres de tablas de un esquema en particular:

  DECLARE CURTABLAS CURSOR 
      FOR SELECT table_name
            FROM information_schema.tables
           where table_schema = pv_nomb_schema;

Creamos la tabla temporal que usaremos para almacenar los datos:

CREATE TEMPORARY TABLE sakila.FilasPorTabla (
     Tabla VARCHAR(100) NOT NULL,
     registros INT UNSIGNED NOT NULL DEFAULT 0);

En seguida agregaremos el código necesario para abrir el cursor, obtener el nombre de tabla, armar la sentencia dinámica  y ejecutarla:

OPEN CURTABLAS;
     Recorre_Cursor: LOOP
        FETCH CURTABLAS INTO VV_NombTabla;

        -- Se arma la sentencia dinámica que insertará los datos en la tabla temporal 
        SET @VV_CONSDINAM = CONCAT('INSERT INTO sakila.FilasPorTabla  select "',VV_NombTabla,'" NombTabla, COUNT(*) from ',VV_NombTabla);
        -- Preparamos la sentencia
        PREPARE SENTENCIA FROM @VV_CONSDINAM;
        -- La ejecutamos
        EXECUTE SENTENCIA;
        -- Liberamos la sentencia preparada.
        DEALLOCATE PREPARE SENTENCIA;
 
        IF vb_termina THEN
            LEAVE Recorre_Cursor;
        END IF;
 
    END LOOP;
  CLOSE CURTABLAS;

Y por último devolveremos el contenido de la tabla temporal.

SELECT Tabla, registros  FROM sakila.FilasPorTabla;

Ejecutar este procedimiento almacenado nos devuelve la siguiente salida:

 

 

 

 

Por último, aquí el código completo del procedimiento almacenado:

 

DROP procedure if exists cnc_sp_cuentafilastablas;

CREATE PROCEDURE cnc_sp_cuentafilastablas( 
         pv_nomb_schema varchar(100) 
        )
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
BEGIN

  DECLARE vb_termina BOOL DEFAULT FALSE; -- Bandera para terminar las iteraciones
  DECLARE VV_NombTabla varchar(250) default '';  -- Nombre de la tabla

  DECLARE CURTABLAS CURSOR 
      FOR SELECT table_name
            FROM information_schema.tables
           where table_schema = pv_nomb_schema;
           
 DECLARE CONTINUE HANDLER 
     FOR SQLSTATE '02000'
     SET vb_termina = TRUE;           
           

  CREATE TEMPORARY TABLE sakila.FilasPorTabla (
     Tabla VARCHAR(100) NOT NULL,
     registros INT UNSIGNED NOT NULL DEFAULT 0);
     
  OPEN CURTABLAS;
     Recorre_Cursor: LOOP
        FETCH CURTABLAS INTO VV_NombTabla;

        SET @VV_CONSDINAM = CONCAT('INSERT INTO sakila.FilasPorTabla  select "',VV_NombTabla,'" NombTabla, COUNT(*) from ',VV_NombTabla);
        PREPARE SENTENCIA FROM @VV_CONSDINAM;
        EXECUTE SENTENCIA;
        DEALLOCATE PREPARE SENTENCIA;
 
        IF vb_termina THEN
            LEAVE Recorre_Cursor;
        END IF;
 
    END LOOP;
  CLOSE CURTABLAS;     
  
  SELECT Tabla, registros  FROM sakila.FilasPorTabla;                                                
  
END;


                 

call cnc_sp_cuentafilastablas('sakila')

 


 

Estas otras entradas del blog te pueden interesar:

MYSQL – CURSORES EN PROCEDIMIENTOS ALMACENADOS.

4 Comments

  1. Genial Amigo .. ¡¡¡
    Eres un Genio .. Indiscretamente … Solucionas el problema entradas de variables como tablas y concatenas sentencias con variables, con esto mantienes en perfecto estado los modelos MVC a un buen estandar de desarrollo y encapsulamiento por separado …

Deja un comentario