BD UD7 Sentencias Prepared

De MediaWiki
Ir a la navegación Ir a la búsqueda

Sentencias preparadas

Nota: Todos los ejemplos están basados en esta base de datos.


  • Este tipo de sentencias se basan en el concepto de que cuando un programa cliente (aplicación de escritorio, página web con PHP, ASP.NET, JSP,...) va a realizar una operación SQL (SELECT, INSERT,....) normalmente siempre va a ejecutar la misma sentencia cambiando los datos que se le envían al servidor.
Imaginemos que damos de alta a 10 empleados. Tendríamos que enviar 10 sentencias INSERT con los datos de cada uno de los empleados. El gestor tendrá que analizar esa sentencia por cada empleado, comprobar si está bien escrita y ejecutarla...
Con las sentencias 'prepared', lo que hacemos en enviar al servidor la sentencia SQL sin datos, enviamos la sentencia con parámetros. El gestor la analiza una sola vez para comprobar que no tiene problemas sintácticos y una vez hecho, el cliente sólo tiene que enviar los datos que van a ser ejecutados por la sentencia.


  • El uso de sentencias 'prepared' nos va a permitir:
  • Una mayor velocidad de ejecución (en el caso de usar procedimientos almacenados, ya no tenemos esta ventaja) porque las sentencias están pre-compiladas.
  • Una mayor seguridad, ya que impide la inyección de código (en el caso de usar procedimientos almacenados, ya no tenemos esta ventaja, ya comenté antes este tipo de ataque).
  • La posibilidad de crear sentencias SQL dinámicas, es decir, que podemos construir la sentencia como si fuera una cadena a la que vamos añadiendo partes hasta tenerla completa y ejecutarla (esta es la mayor ventaja si estamos usando procedimientos almacenados y necesitamos este tipo de sentencias).
Por lo tanto, si estamos haciendo uso de procedimientos, el único sentido de utilizar sentencias prepared es el de crear sentencias dinámicas dentro del mismo.
La sentencia PREPARE es muy recomendada si estamos programando un programa cliente y llamamos múltiples veces al servidor enviando la misma sentencia SQL, cambiando sólo los datos entre cada llamada o si queremos, desde el lado cliente, crear una sentencia SQL dinámicamente.


  • El proceso para poder emplear este tipo de sentencias es el siguiente (tanto si lo hacemos desde el cliente (php, java,...) como desde un procedimiento almacenado:
  • PREPARE: Donde definimos la sentencia SQL con parámetros que queremos ejecutar (por ejemplo, una sentencia INSERT lleva como parámetros los valores a añadir, o una sentencia SELECT lleva parámetros en la parte WHERE).
  • Paso de datos a los parámetros que están definidos en la sentencia anterior.
  • EXECUTE: Para ejecutar la sentencia.
  • DEALLOCATE PREPARE: Para liberar la memoria del servidor de la sentencia preparada.


  • Veamos un ejemplo genérico:
1 SET @sentencia = 'SELECT * FROM TABLA WHERE col1=? AND col2=?';
2 PREPARE stPrepared FROM @sentencia;     -- Preparamos la sentencia. El nombre stPrepared se lo damos nosotros. Podemos poner lo que queramos.
3 SET @valorParam1 = 2;                    -- Estamos usando una variable para establecer el valor que se le va a enviar al parámetro. Podríamos pasarlo directamente en la siguiente intrucción.
4 SET @valorParam2 = 'Angel';                    -- Estamos usando una variable para establecer el valor que se le va a enviar al parámetro. Podríamos pasarlo directamente en la siguiente intrucción.
5 EXECUTE stPrepared USING @valorParam1,@valorParam2;
6 DEALLOCATE PREPARE stPrepared;     -- Liberamos la memoria


  • Importante:
  • En la sentencia PREPARE sólo podemos emplear variables de sesión o literales (es decir, entre comillas si son cadenas o directamente el valor si son numéricos). No podemos emplear variables locales si estamos dentro de un procedimiento. En la sentencia EXECUTE ... USING sólo podremos emplear variables de sesión.
  • La sentencia 'PREPARE' es visible a nivel de sesión. Quiere esto decir que si no hacemos un DEALLOCATE, podríamos emplearla en cualquier pestaña SQL de la conexión activa.


  • Veamos un ejemplo aplicado a nuestra base de datos.
Crea un procedimiento de nombre animales_getListPorFiltro que devuelva la lista de animales filtrados por columnas dinámicamente. Los datos a enviar será el nombre de la columna, el tipo de operación y el valor que debe cumplir. Por ejemplo, 'anhos','=','2'.
 1 USE CIRCO;
 2 
 3 DELIMITER $$
 4 CREATE PROCEDURE animales_getListPorFiltro (p_nombreColum varchar(20),p_operacion char(2),p_valorParam varchar(10))		
 5     COMMENT 'Devuelve todos los animales que cumplan la condición que se la va pasar como parámetro.'
 6 BEGIN
 7     
 8     SET @v_consulta = CONCAT('SELECT * FROM ANIMALES WHERE ',p_nombreColum,p_operacion,'?');	-- Podríamos poner como tercer parámetro directamente el parámetro p_valorParam
 9     SET @v_valor = p_valorParam;
10     PREPARE prepConsulta FROM @v_consulta;
11     EXECUTE prepConsulta USING @v_valor;
12     DEALLOCATE PREPARE prepConsulta;
13 
14 
15 END $$
16 DELIMITER ;
Ejemplo de uso:
1 CALL animales_getListPorFiltro('tipo','=','Jirafa');
2 
3 CALL animales_getListPorFiltro('anhos','>=','3');



Ejercicios propuestos Sentencias preparadas

  • Ejercicio 1: Crea un procedimiento de nombre animales_getListFiltroNumeroColum que devuelva el número de animales indicado por el parámetro (emplear LIMIT). También se le pasará en otro parámetro las columnas que debe mostrar de la forma 'col1,col3,col5'.
Nota: Este procedimiento sería un buen candidato para aplicar el control de excepciones de Mysql y controlar si enviamos una columna que no existe.


  • Ejercicio 2: Crea un procedimiento de nombre animales_getListFiltroPista que devuelva la lista de animales que trabajan en la pista que se envía como parámetro. En caso de que no se envíe dato (valor null) debe mostrar todos los animales.
Atención: En este caso, la sentencia SQL a emplear es IN ('pista1,'pista2',...). Si o fijáis, la sentencia IN espera recibir cada uno de los datos entre comillas.
Por lo tanto, nosotros no podemos enviar como dato: 'pista1,pista2,...'. Si quisiéramos enviar así los datos, tendríamos que descomponerlos dentro del procedimiento con un bucle y guardar cada una de las pistas en una tabla temporal (CREATE TEMPORARY TABLE IF NOT EXISTS) y después hacer una consulta IN (Subconsulta a la tabla temporal).
Para poder enviar cada dato separado por comilla simple necesitamos emplear el carácter 'escape' que en Mysql es \ de la forma: '\'pista1\',\'pista2\',...'
No podemos hacer uso de parámetros ya que no podemos emplearlo en la cláusula IN de la forma: IN (?), sino que tendremos que concatener a la sentencia SQL el dato enviado.





Solución Ejercicios propuestos Sentencias preparadas
  • Ejercicio 1: Crea un procedimiento de nombre animales_getListFiltroNumeroColum que devuelva el número de animales indicado por el parámetro. También se le pasará en otro parámetro las columnas que debe mostrar de la forma 'col1,col3,col5'.
Nota: Este procedimiento sería un buen candidato para aplicar el control de excepciones de Mysql y controlar si enviamos una columna que no existe.
 1 USE CIRCO;
 2 DROP PROCEDURE IF EXISTS animales_getListFiltroNumeroColum;
 3 DELIMITER $$
 4 CREATE PROCEDURE animales_getListFiltroNumeroColum (p_columnas varchar(100),p_numero int)		
 5     COMMENT 'Devuelve el número de animales indicado por el parámetro. También se le pasará en otro parámetro las columnas que debe mostrar de la forma col1,col3,col5'
 6 BEGIN
 7     SET @v_consulta = CONCAT('SELECT ',p_columnas,' FROM ANIMALES LIMIT ?');	-- Añadir espacios en blanco despúes del SELECT y antes del FROM
 8     SET @v_columnas = p_columnas;
 9     SET @v_numero = p_numero;
10     PREPARE prepConsulta_animales_getListFiltroNumeroColum FROM @v_consulta;	
11     EXECUTE prepConsulta_animales_getListFiltroNumeroColum USING @v_numero;
12     DEALLOCATE PREPARE prepConsulta_animales_getListFiltroNumeroColum;
13 
14 
15 END $$
16 DELIMITER ;
Ejemplo de uso:
1 CALL animales_getListFiltroNumeroColum('nombre,anhos',2);
2 
3 CALL animales_getListFiltroNumeroColum('nombre,tipo,peso',4);



  • Ejercicio 2: Crea un procedimiento de nombre animales_getListFiltroPista que devuelva la lista de animales que trabajan en las pistas que se envían como parámetro en formato 'pista1,pista2,..'. En caso de que no se envíe dato (valor null) debe mostrar todos los animales.
 1 USE CIRCO;
 2 DROP PROCEDURE IF EXISTS animales_getListFiltroPista;
 3 DELIMITER $$
 4 CREATE PROCEDURE animales_getListFiltroPista (p_pistas varchar(100))		
 5     COMMENT 'la lista de animales que trabajan en las pistas que se envían como parámetro en formato pista1,pista2,... En caso de que no se envíe dato (valor null) debe mostrar todos los animales'
 6 BEGIN
 7 
 8 	IF (p_pistas IS NOT NULL) THEN
 9 		SET @v_consulta = CONCAT('SELECT * FROM ANIMALES WHERE nombre_pista IN (',p_pistas,')');
10         select @v_consulta;
11 		-- SET @v_consulta = 'SELECT * FROM ANIMALES WHERE nombre_pista IN (?)';
12 		PREPARE prepConsulta_animales_getListFiltroPista FROM @v_consulta;
13 		-- SET @v_pistas = p_pistas;
14 		EXECUTE prepConsulta_animales_getListFiltroPista;
15 		DEALLOCATE PREPARE prepConsulta_animales_getListFiltroPista;
16 	ELSE
17 		SELECT * FROM ANIMALES ORDER BY nombre;
18     END IF;
19     
20 END $$
21 DELIMITER ;
Ejemplo de uso:
1 CALL animales_getListFiltroPista('\'LATERAL1\'');
2 
3 CALL animales_getListFiltroPista('\'LATERAL2\',\'SUPER\'');



Enlace a la página principal de la UD7

Enlace a la página principal del curso




-- Ángel D. Fernández González -- (2020).