Diferencia entre revisiones de «BD UD7 Procedimientos»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 739: Línea 739:
 
==== Ejercicio 8 ====  
 
==== Ejercicio 8 ====  
 
Crea un procedimiento de nombre pistas_delete que borre una pista por su nombre. Haz que borre en base al patrón nombre% (empleando el Like).
 
Crea un procedimiento de nombre pistas_delete que borre una pista por su nombre. Haz que borre en base al patrón nombre% (empleando el Like).
: Borra la atracción que hayas añadido en el ejercicio 6 mandando las primeras letras (ten cuidado de que no haya otra atracción con esas letras al comienzo).
+
: Borra la pista que hayas añadido en el ejercicio 6 mandando las primeras letras (ten cuidado de que no haya otra atracción con esas letras al comienzo).
 
: <u>Pista:</u> Emplea la función CONCAT para el LIKE
 
: <u>Pista:</u> Emplea la función CONCAT para el LIKE
 
::<syntaxhighlight lang="sql" enclose="div" highlight="" >
 
::<syntaxhighlight lang="sql" enclose="div" highlight="" >
Línea 763: Línea 763:
 
CALL pistas_delete('El gran m'); -- Yo había añadido una de nombre El gran misil
 
CALL pistas_delete('El gran m'); -- Yo había añadido una de nombre El gran misil
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=== Salida ===
 
=== Salida ===

Revisión del 09:59 1 abr 2022

Introducción


  • Un procedimiento almacenado es un recurso que incorporan todos los gestores relacionales y que permite asociar un nombre (el nombre del procedimiento) a un conjunto de instrucciones SQL.
Cada vez que se llama al procedimiento, se ejecutan todas las instrucciones.
  • A diferencia de un método en programación orientada a objetos, podremos emplear instrucciones SQL que serán ejecutadas directamente en el gestor.
  • Normalmente al desarrollar una aplicación, tendremos:
  • Un frontal cliente (es decir, la pantalla/s que usa el cliente para obtener y realizar operaciones contra una base de datos). Este programa cliente estará instalado en cada uno los ordenadores de los usuarios (si es una aplicación de escritorio, como una realizada con NetBeans, VC#, VB,...)
  • Un servidor donde está instalado el gestor y donde se encuentra la base de datos a la que los usuarios acceden (normalmente en un único ordenador).
Dentro del código fuente que conforma el programa del cliente es donde vamos a ejecutar las órdenes SQL de consulta o modificación de los datos (SELECT, UPDATE, DELETE o INSERT) así como transacciones....
El uso de procedimientos almacenados para dar solución a este tipo de aplicaciones nos va a proporcionar:
  • Más rapidez, ya que las órdenes SQL que conforman el procedimientos están pre-compiladas y el gestor no tiene que verificar que estén bien escritas (como tiene que hacer si empleamos las órdenes SQL directamente).
  • Más simplicidad en la seguridad, ya que podemos especificar quien tiene permiso para ejecutar el procedimiento. Si usamos las órdenes SQL directamente tenemos que establecer la seguridad a nivel de tabla-columna (quien tiene permiso para realizar qué operación y con qué columnas) por lo que es más complejo.
  • Más seguridad. El usar procedimientos con parámetros, nos va a permitir evitar acciones que comprometan la seguridad de la base de datos, como la inyección de código en formularios. Más información en este enlace aplicado a páginas Web, pero cuyo contenido se podrían aplicar a aplicaciones de escritorio.
  • Estándar: En el sentido que existe un único lugar centralizado (el gestor de base de datos) donde se encuentran todos los procedimientos almacenados (asociados a una base de datos) y por lo tanto todos los programadores deberán de hacer uso de los procedimientos creados evitando duplicidades de código.
  • Velocidad: Como el código ya se encuentra todo en el lado del servidor, no es necesario que los clientes envíen todas las órdenes, solamente los datos que van a necesitar los procedimientos para su funcionamiento.


Operaciones


Crear procedimientos

Un procedimiento almacenado puede estar formado por órdenes de todo tipo. Las que no permite las podéis consultar en este enlace.
Mod BD Prog Proc 15.jpg
Imagen obtenida de https://dev.mysql.com
El conjunto de instrucciones van en el 'routine_body'. Si lo forman varias (lo normal) deben ir entre las etiquetas BEGIN y END.
Fijarse que el END debe llevar punto y coma si a continuación le siguen nuevas instrucciones.


Veremos poco a poco las opciones que nos permite utilizar la orden SQL anterior.
Como mínimo debemos de indicar un nombre para el procedimiento y un conjunto de órdenes SQL entre las palabras clave BEGIN y END.
USE BD;

DELIMITER $$

CREATE PROCEDURE nombreProc()
BEGIN
        -- Instrucciones SQL a ejecutar dentro del procedimiento

END$$

DELIMITER ;
  • Un procedimiento se crea dentro de una base de datos concreta.
Si no indicamos la base de datos, lo hará en la base de datos activa que recordar que en MysqlWorkBench es la que se visualiza en negrilla.
Cuando ejecutemos la orden CREATE PROCEDURE, podemos cambiar antes la base de datos activa con la orden SQL: USE nombre_bd;
Otra forma de indicar en qué base de datos debe crearse el procedimiento es anteponiendo al nombre del mismo, el nombre de la base de datos seguido por un punto, de la forma: CREATE PROCEDURE nombreBD.nombreProc()
  • Entre las cláusulas que podemos emplear dentro del CREATE PROCEDURE estaría COMMENT que nos sirve para documentar cual es el objeto del procedimiento:
USE BD;

DELIMITER $$

CREATE PROCEDURE nombreProc()
COMMENT 'Objetivo del procedimiento'
BEGIN
        -- Instrucciones SQL a ejecutar dentro del procedimiento

END$$

DELIMITER ;


  • Lo primero que llama la atención cuando se ve la definición de un procedimiento almacenado en Mysql es la orden SQL DELIMITER:
USE CIRCO;

DELIMITER $$

CREATE PROCEDURE animales_getList()
BEGIN
    SELECT nombre
    FROM ANIMALES
    ORDER BY nombre;

END$$

DELIMITER ;
Mysql toma como delimitador de sentencias SQL el punto y coma por defecto. Quiere decir que cada vez que encuentra un punto y coma intenta ejecutar la orden SQL.
Debemos de ver al procedimiento almacenado como un todo, como si fuera una única instrucción que queremos que Mysql ejecute entera.
Si mantenemos el punto y coma tendríamos algo así:
use employees;

CREATE PROCEDURE animales_getList()
BEGIN

END;
¿ Pero qué pasa ahora si queremos poner una orden SQL dentro del procedimiento almacenado ?
Que intentará ejecutarla y no la interpretará como parte del procedimiento almacenado.
Para evitarlo, indicamos con la orden SQL DELIMITER cual es el símbolo que debe encontrar Mysql para ejecutar la orden SQL (en nuestro caso el CREATE PROCEDURE) y por lo tanto dentro del mismo ya podemos poner sentencias SQL acabadas en punto y coma para separar unas de otras.

Cuando acabamos de definir el procedimiento almacenado volvemos a indicar que el delimitador sea ';' por si queremos ejecutar a continuación otras instrucciones SQL.

Indicar que el delimitador no tiene por qué ser $$, podría ser cualquiera que sepamos que no se va a emplear dentro del procedimiento almacenado, como por ejemplo %%.


  • Una vez creado, aparecerá en la sección Stores Procedures en MysqlWorkBench.
Mod BD Prog Proc 1.jpg


  • En Mysql disponemos de una sección específica para crear, borrar, modificar, ejecutar o exportar un procedimiento almacenado:
Mod BD Prog Proc 7.jpg
  • En MysqlWorkBench también podemos crearlo de la siguiente forma:
En este caso no hace falta poner DELIMITER, ya que se encargar el propio MysqlWorkBench de hacerlo...


Listar procedimientos

La orden SQL que permite ver la información de los procedimientos almacenados es: SHOW PROCEDURE STATUS.
Por ejemplo, para ver la información de los procedimientos almacenados de la base de datos 'CIRCO':
SHOW PROCEDURE STATUS
WHERE Db = 'CIRCO';
Mod BD Prog Proc 2.jpg


Llamar a un procedimiento

  • Para llamar a un procedimiento almacenado debemos hacer uso de la orden SQL CALL.
Al llamar a un procedimiento almacenado, las órdenes de su interior será ejecutadas.
Por ejemplo:
CALL CIRCO.animales_getList();
  • Fijarse que delante del nombre del procedimiento almacenado lleva el nombre de la base de datos. Esto no sería necesario si estamos ejecutando el procedimiento con la base de datos seleccionada o teniendo la orden SQL: USE db_name; antes de la llamada al procedimiento.
Mod BD Prog Proc 3.jpg


Visualizar el código de un procedimiento

SHOW CREATE PROCEDURE animales_getList;
Mod BD Prog Proc 4.jpg


Modificar un procedimiento

  • En MYSQL no se permite modificar los parámetros y el cuerpo de un procedimiento.
Para ello tendremos que borrarlo y volverlo a crear.
  • Lo que sí permite es modificar ciertas características del mismo.
  • Por ejemplo, podemos modificar la cláusula COMMNENT que sirve para tener una descripción del procedimiento.
ALTER PROCEDURE animales_getList
COMMENT 'Obtiene un listado de todos los animales';

SHOW PROCEDURE STATUS 
WHERE Db = 'CIRCO';
Mod BD Prog Proc 5.jpg
  • Desde el MysqlWorkBench podemos 'modificar' un procedimiento (realmente lo va a borrar y volverá a crearlo) de la forma:
Mod BD Prog Proc 6.jpg


Borrar un procedimiento

  • Indicar que podemos incluir una cláusula 'IF EXISTS' para indicar que solamente en caso de que exista, borre el procedimiento. Esto es muy útil cuando tenemos un bloque de sentencias, ya que si no ponemos el IF, si el procedimiento no existe daría un error y se dejarían de ejecutar las sentencias.
DROP PROCEDURE IF EXISTS nombre_proc;
  • Indicar que podemos hacer referencia al procedimiento anteponiendo a su nombre el nombre de la base de datos donde está creado seguido del un punto: DROP PROCEDURE IF EXISTS CIRCO.nombre_proc;
  • Nota: Al igual que en la operación anterior, un procedimiento se puede borrar gráficamente desde el MysqlWorkBench (pulsando el botón derecho sobre el mismo) o desde el PhpMyadmin.


Ejercicios propuestos

Notas:

  • Probar a crear los procedimientos de diferentes formas: PhpMyAdmin, directamente en una pestaña SQL o a través del asistente de MysqlWorkBench.
  • Después de crear el procedimiento llámalo.
  • Crea cada procedimiento con un pequeño comentario de su objetivo.
  • En caso de que no se indique ordenación, establecer vosotros mismos un criterio de ordenación.


Ejercicio 1

Crea un procedimiento de nombre artistas_getList() que devuelva el nombre y apellidos de los artistas separados por coma con el formato: apellidos,nombre ordenados de forma descendente.

Ejercicio 2

Crea un procedimiento de nombre artistas_getListAnimales() que devuelva los nombres de los artistas junto con su nif así como el nombre y peso de los animales que están atendidos por los artistas, ordenados por nif del artista y nombre del animal.

Ejercicio 3

Crea un procedimiento de nombre atracciones_getListConAntiguedad5() que devuelva los datos de las atracciones que hayan comenzado hace 5 años con respecto a la fecha actual. Tendrás que hacer uso de alguna de las funciones Date Time. Intenta averiguar cual.

Fijarse que este procedimiento es un buen candidato para emplear un parámetro en donde indiquemos el número de años. Lo veremos después cuando expliquemos el paso de datos por parámetros.

Ejercicio 4

Crea un procedimiento de nombre animales_Leo_getPista() que muestre los datos de la pista donde trabaja el animal de nombre 'Leo'. Hacerlo empleando una variable local que guarde el nombre de la pista. Después consultar los datos de la pista empleando dicha variable local.

Fijarse que este procedimiento es un buen candidato para emplear un parámetro en donde indiquemos el nombre del animal. Lo veremos después cuando expliquemos el paso de datos por parámetros.

Ejercicio 5

Crea un procedimiento de nombre atracciones_getUltima() que obtenga los datos de la última atracción celebrada (tabla ATRACCION_DIA), empleando variables locales. Para ello guarda en una variable el nombre de la última atracción celebrada y busca los datos de dicha atracción. Ten en cuenta limitar con LIMIT el número de filas que devuelva una consulta si no sabes con certeza que vaya a devolver una única fila y vas a guardar el datos en una variable.

Ejercicio 6

Crea un procedimiento de nombre atracciones_getArtistaUltima() que obtenga los datos de la atracción y del artista que trabaja en dicha atracción, cuya fecha de inicio ha empezado más tarde. Emplea dos variables. Una para guardar el nif del artista y otra para guardar el nombre de la atracción.


Solución Ejercicios propuestos

Ejercicio 1

Crea un procedimiento de nombre artistas_getList() que devuelva el nombre y apellidos de los artistas separados por coma con el formato: apellidos,nombre ordenados de forma descendente.

USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getList;

DELIMITER $$

CREATE PROCEDURE artistas_getList()
COMMENT 'Devuelve el nombre completo de todos los artistas ordenado descendentemente'
BEGIN
    SELECT CONCAT(apellidos,',',nombre) as nombreCompleto
    FROM ARTISTAS
    ORDER BY nombreCompleto DESC;

END$$

DELIMITER ;

Ejercicio 2

Crea un procedimiento de nombre artistas_getListAnimales() que devuelva los nombres de los artistas junto con su nif así como el nombre y peso de los animales que están atendidos por los artistas, ordenados por nif del artista y nombre del animal.

USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getListAnimales;

DELIMITER $$

CREATE PROCEDURE artistas_getListAnimales()
COMMENT 'Devuelve el nombre, apellidos y nif de los artistas junto con el nombre y peso de los animales que atienden'
BEGIN
    SELECT nif, apellidos, ARTISTAS.nombre as nombreArtista, ANIMALES.nombre as nombreAnimal, peso
    FROM ARTISTAS INNER JOIN ANIMALES_ARTISTAS ON (ANIMALES_ARTISTAS.nif_artista=ARTISTAS.nif)
				  INNER JOIN ANIMALES ON (ANIMALES_ARTISTAS.nombre_animal=ANIMALES.nombre)
    ORDER BY nif, nombreAnimal;

END$$

DELIMITER ;

Ejercicio 3

Crea un procedimiento de nombre atracciones_getListConAntiguedad5() que devuelva los datos de las atracciones que hayan comenzado hace 5 años con respecto a la fecha actual. Tendrás que hacer uso de alguna de las funciones Date Time. Intenta averiguar cual.

La función a emplear es DATE_SUB que tiene la misma sintaxis que DATE_ADD.
USE CIRCO;
DROP PROCEDURE IF EXISTS atracciones_getListConAntiguedad5;
DELIMITER $$

CREATE PROCEDURE atracciones_getListConAntiguedad5()
COMMENT 'Devuelve todos los datos de las atracciones que comenzaran hace 5 años con respecto a la fecha actual'
BEGIN
    SELECT *
    FROM ATRACCIONES
    WHERE fecha_inicio BETWEEN DATE_SUB(curdate(), INTERVAL 5 YEAR) AND  curdate()
    ORDER BY nombre;

END$$

DELIMITER ;

Ejercicio 4

Crea un procedimiento de nombre animales_Leo_getPista() que muestre los datos de la pista donde trabaja el animal de nombre 'Leo'. Hacerlo empleando una variable local que guarde el nombre de la pista. Después consultar los datos de la pista empleando dicha variable local.

Fijarse que este procedimiento es un buen candidato para emplear un parámetro en donde indiquemos el nombre del animal. Lo veremos después cuando expliquemos el paso de datos por parámetros.
USE CIRCO;
DROP PROCEDURE IF EXISTS animales_Leo_getPista;
DELIMITER $$

CREATE PROCEDURE animales_Leo_getPista()
COMMENT 'Devuelve los datos de la pista donde trabaja el animal de nombre Leo'
BEGIN
	DECLARE v_nombrePista varchar(50) default '';	-- El tipo y tamaño debe de coincidir con el declarado en la tabla. Si no podemos nada, el valor por defecto es NULL

    SELECT nombre_pista
    INTO v_nombrePista
    FROM ANIMALES
    WHERE nombre = 'Leo';
    
    
    SELECT *                                   -- Devuelve una única fila. No hace falta order by
    FROM PISTAS
    WHERE nombre=v_nombrePista;
    

END$$

DELIMITER ;
Línea 8: Fijarse que está declarada como varchar(50) que se corresponde al tipo de dato y tamaño de la columna nombre de la tabla PISTAS.
Se le da un valor por defecto. Esto es opcional, pero tenéis que tener en cuenta que si la orden SELECT no encuentra dato, no se realiza la parte INTO y por tanto a la salida de la orden SELECT la variable tendrá el valor por defecto. Cuando veremos la sentencia IF veremos que usaremos el valor por defecto para comprobar si queremos que siga ejecutando el procedimiento o salga del mismo.

Ejercicio 5

Crea un procedimiento de nombre atracciones_getUltima() que obtenga los datos de la última atracción celebrada (tabla ATRACCION_DIA), empleando variables locales. Para ello guarda en una variable el nombre de la última atracción celebrada y busca los datos de dicha atracción.

USE CIRCO;
DROP PROCEDURE IF EXISTS atracciones_getUltima;
DELIMITER $$

CREATE PROCEDURE atracciones_getUltima()
COMMENT 'Devuelve los datos de la última atracción celebrada'
BEGIN
	DECLARE v_nombreAtraccion varchar(50) default '';	-- El tipo y tamaño debe de coincidir con el declarado en la tabla. Si no podemos nada, el valor por defecto es NULL

    SELECT nombre_atraccion
    INTO v_nombreAtraccion
    FROM ATRACCION_DIA
    ORDER BY fecha DESC
    LIMIT 1;
    
    /*
    Si empleáramos subconsultas, podríamos hacer algo como lo siguiente
    SELECT nombre_atraccion
    INTO v_nombreAtraccion
	FROM ATRACCION_DIA
	WHERE fecha = (SELECT MAX(fecha)
                   FROM ATRACCION_DIA)
	LIMIT 1; 
    */
    
    SELECT *                     -- Devuelve una única fila. No hace falta order by
    FROM ATRACCIONES
    WHERE nombre=v_nombreAtraccion;
    

END$$

DELIMITER ;

Ejercicio 6

Crea un procedimiento de nombre atracciones_getArtistaUltima() que obtenga los datos de la atracción y del artista que trabaja en dicha atracción, cuya fecha de inicio ha empezado más tarde. Emplea dos variables. Una para guardar el nif del artista y otra para guardar el nombre de la atracción.

USE CIRCO;
DROP PROCEDURE IF EXISTS atracciones_getArtistaUltima;
DELIMITER $$

CREATE PROCEDURE atracciones_getArtistaUltima()
COMMENT 'Devuelve los datos de la atracción y del artista que trabaja en dicha atracción, cuya fecha de inicio ha empezado más tarde'
BEGIN
	DECLARE v_nombreAtraccion varchar(50) default '';	-- El tipo y tamaño debe de coincidir con el declarado en la tabla. Si no podemos nada, el valor por defecto es NULL
	DECLARE v_nifArtista char(9);		-- Al no llevar default el valor por defecto es null y sería lo que tendríamos que comparar en un if por ejemplo.

    SELECT nombre_atraccion,nif_artista
    INTO v_nombreAtraccion,v_nifArtista
    FROM ATRACCIONES_ARTISTAS
    ORDER BY fecha_inicio DESC
    LIMIT 1;
    
    SELECT *						-- Devuelve una única fila. No hace falta order by
    FROM ATRACCIONES,ARTISTAS
    WHERE ATRACCIONES.nombre=v_nombreAtraccion AND
		  ARTISTAS.nif = v_nifArtista;
          

END$$

DELIMITER ;


Parámetros

  • Cuando llamamos a un procedimiento puede ser necesario 'enviarle' información.
Por ejemplo:
  • Podemos querer saber las conexiones activas de un determinado usuario.
  • Podemos querer saber qué usuarios tienen permiso de selección sobre determinada base de datos.
  • Podemos querer saber quien cobra más que un determinado sueldo.

....

En cada una de las consultas anteriores vamos a querer hacer uso de un dato que debe ser suministrador por el usuario en el momento de llamar al procedimiento.
  • Cada uno de los datos que enviamos a un procedimiento almacenado se denomina parámetro.
Los parámetros son variables cuyo valor se utiliza dentro del procedimiento almacenado.
Como norma general, cualquier modificación que hagamos sobre el valor del parámetro dentro del procedimiento almacenado no tiene efecto fuera de él.
Este tipo de parámetros se denominan de entrada.
  • Los parámetros de salida son parámetros que son utilizados por el procedimiento almacenado para 'informar' a quien llama y asignarle un valor a dicho parámetro dentro del procedimiento. Este valor será utilizado posteriormente por quien realizó la llamada, leyendo el valor del parámetro modificado. Este tipo de parámetro se denomina de salida.
  • Los parámetros que pueden llevar información al procedimiento, que pueden ser modificados dentro del procedimiento y que pueden ser utilizados por quien realizó la llamada al procedimiento se denominan de entrada/salida.


  • Debemos de tener cuidado con el nombre de las parámetros y que estos no coincidan con el nombre de las columnas de una tabla, ya que Mysql interpretará siempre que se trata del nombre del parámetro y nunca el de la columna, como podéis ver en esta explicación.
Podríamos solucionarlo referenciando el nombre de la columna de la forma: nombre_tabla.nombre_columna
Aunque no existe una norma que indique como llamar a los parámetro, os recomiendo seguir la siguiente:
  • A los parámetros, definirlos con el prefijo: p_nombreParámetro tipo


Entrada

  • Es el tipo por defecto si no indicamos nada en la definición del procedimiento.
DELIMITER \\
CREATE PROCEDURE nombre_proc (IN nombre_parametro tipo_dato)
BEGIN

END//
DELIMITER ;
Sería lo mismo que no poner IN (tipo por defecto)
DELIMITER \\
CREATE PROCEDURE nombre_proc (nombre_parametro tipo_dato)
BEGIN

END//
DELIMITER ;


  • Una vez definido podremos disponer de él dentro del procedimiento como si tuviéramos una variable local.
La diferencia es que desde quien llama al procedimiento, va a poder enviar un valor de la forma:
CALL nombre_proc(100);
En este caso el parámetro debe estar definido como de tipo numérico (por ejemplo int)
O de la forma:
SET @dato = 100;
CALL nombre_proc(@dato);
Nota: Recordar que este tipo de variables son de sesión y que son globales en la conexión actual a Mysql. Por lo tanto podríamos acceder directamente a su valor y modificarlo dentro del procedimiento almacenado sin hacer uso de parámetros, pero a nivel de programación es más correcto emplear parámetros para aquellos procedimientos que lo necesitan. Además vamos a poder llamar a procedimientos desde otros procedimientos, donde las variables son locales.
  • Podemos definir tantos parámetros como queramos, siempre que el número de datos a enviar se corresponda con el número de parámetros del procedimiento.
DELIMITER \\
CREATE PROCEDURE nombre_proc_varios_param (nombre_parametro1 tipo_dato,nombre_parametro2 tipo_dato,....)
BEGIN

END//
DELIMITER ;
CALL nombre_proc_varios_param(dato1,datos2,...);


Ejercicios propuestos parámetros de Entrada

Crea los procedimientos indicados utilizando las formas vistas (SQL, PhpMyAdmin o MysqlWorkBench) y añadiendo en todos ellos un comentario de cual es su objetivo. Si no está claro, documenta también cada uno de los parámetros.

  • Ejercicio 1: Crea un procedimiento de nombre artistas_getAnimalesPorNif que devuelva los animales que cuida un artista. Llevará como parámetro el nif de artista.
  • Ejercicio 2: Crea un procedimiento de nombre artistas_getAnimalesPorNombreApel que devuelva los animales que cuida un artista. Llevará como parámetro el nombre y apellidos del artista. Suponemos que el nombre y apellidos conforman una clave alternativa.
  • Ejercicio 3: Crea un procedimiento de nombre atracciones_getListConAntiguedad que devuelva los datos de las atracciones que hayan comenzado hace un número de años con respecto a la fecha actual. Tendrás que hacer uso de alguna de las funciones Date Time. Intenta averiguar cual.
  • Ejercicio 4: Crea un procedimiento de nombre artistas_getListMasAnimalesCuida que devuelva los datos del/os artista/s que cuidan a más animales de los indicados (parámetro que se le envía).
Pista: Como la consulta puede devolver más de un artista no podremos hacer uso de INTO....
  • Ejercicio 5: Crea un procedimiento de nombre atracciones_getListPorFecha que devuelva los datos de las atracciones que han comenzado a partir de la fecha indicada.
Pista: Recordar que las fechas son tratadas como cadenas...y tener en cuenta el formato.
Añade una nueva atracción con la fecha de inicio actual.
Llama al procedimiento empleando la fecha actual menos 3 días (haz uso de la función DATE_SUB y curdate)
  • Ejercicio 6: Crea un procedimiento de nombre pistas_add y que añada una nueva pista.
Nota: Aún no vimos la validación de datos que tendría que darse en el paso de parámetros. En este caso podríamos tener condiciones if en el que se comprueba sin el aforo es mayor que cero....
Se puede hacer uso de la función ROW_COUNT() para saber cuantas filas fueron añadidas, borradas o modificadas.
Importante: Los parámetros deben de tener el mismo tipo de dato y tamaño que el que está definido a nivel de columnas en la tabla PISTAS.
  • Ejercicio 7: Crea un procedimiento de nombre atracciones_update que permita modificar los datos de una atracción (no se permite actualizar su clave primaria).
Modifica la fecha de inicio de la atracción 'El gran felino' y ponla un día después de la que tiene ahora mismo.
Pista: Tendrás que guardar las ganancias y la fecha de inicio que tiene para poder enviar ese dato al procedimiento.
Comprueba como al llamar al método con una atracción que no existe, row_count va a devolver 0.
  • Ejercicio 8: Crea un procedimiento de nombre pistas_delete que borre una pista por su nombre. Haz que borre en base al patrón nombre% (empleando el Like).
Borra la atracción que hayas añadido en el ejercicio 6 mandando las primeras letras (ten cuidado de que no haya otra atracción con esas letras al comienzo).
Pista: Emplea la función CONCAT para el LIKE


Solución Ejercicios propuestos parámetros de Entrada

Ejercicio 1

Crea un procedimiento de nombre artistas_getAnimalesPorNif que devuelva los animales que cuida un artista. Llevará como parámetro el nif de artista.

USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getAnimalesPorNif;
DELIMITER $$;

CREATE PROCEDURE artistas_getAnimalesPorNif(p_nif char(9))
COMMENT 'Devuelva los animales que cuida un artista. Llevará como parámetro el nif de artista.'
BEGIN
	SELECT *
    FROM ANIMALES INNER JOIN ANIMALES_ARTISTAS
					ON (ANIMALES.nombre = ANIMALES_ARTISTAS.nombre_animal)
	WHERE ANIMALES_ARTISTAS.nif_artista = p_nif
    ORDER BY nombre;            
END$$
DELIMITER ;
Ejemplos de llamadas:
CALL artistas_getAnimalesPorNif('11111111A');
CALL artistas_getAnimalesPorNif('22222222B');

SET @nifBuscar='44444444D';
CALL artistas_getAnimalesPorNif(@nifBuscar);

Ejercicio 2

Crea un procedimiento de nombre artistas_getAnimalesPorNombreApel que devuelva los animales que cuida un artista. Llevará como parámetro el nombre y apellidos del artista. Suponemos que el nombre y apellidos conforman una clave alternativa.

USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getAnimalesPorNombreApel;
DELIMITER $$

CREATE PROCEDURE artistas_getAnimalesPorNombreApel(p_nombre varchar(45),p_apellidos varchar(100))	-- Fijarse en el tipo de dato y tamaño que coincide con el definido en la tabla
COMMENT 'Devuelva los animales que cuida un artista. Llevará como parámetro el nombre y apellidos del artista'
BEGIN
	DECLARE v_nifArtista char(9);		-- Si no ponemos valor por defecto vale null
    
    SELECT nif 
    INTO v_nifArtista
    FROM ARTISTAS 
    WHERE apellidos = p_apellidos AND
          nombre = p_nombre;
	-- LIMIT 1;		Tendríamos que poner LIMIT 1 para poder emplear INTO en el caso de que hubiera varias personas con el mismo nombre y apellidos
          
	SELECT *
    FROM ANIMALES INNER JOIN ANIMALES_ARTISTAS
					ON (ANIMALES.nombre = ANIMALES_ARTISTAS.nombre_animal)
	WHERE ANIMALES_ARTISTAS.nif_artista = v_nifArtista
    ORDER BY nombre;            
END$$
DELIMITER ;
Ejemplos de llamadas:
CALL artistas_getAnimalesPorNombreApel('Luis','Sanchez');

Ejercicio 3

Crea un procedimiento de nombre atracciones_getListConAntiguedad que devuelva los datos de las atracciones que hayan comenzado hace un número de años con respecto a la fecha actual. Tendrás que hacer uso de alguna de las funciones Date Time. Intenta averiguar cual.

USE CIRCO;
DROP PROCEDURE IF EXISTS atracciones_getListConAntiguedad;
DELIMITER $$

CREATE PROCEDURE atracciones_getListConAntiguedad(p_antiguedad tinyint)	-- Fijarse en el tipo. No hace falta poner un int.
    COMMENT 'Dvuelva los datos de las atracciones que hayan comenzado hace un número de años con respecto a la fecha actual.'
BEGIN
    SELECT *
    FROM ATRACCIONES
    WHERE fecha_inicio BETWEEN DATE_SUB(curdate(), INTERVAL p_antiguedad YEAR) AND  curdate()
    ORDER BY nombre;

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL atracciones_getListConAntiguedad(4);
CALL atracciones_getListConAntiguedad(20);


Ejercicio 4

Crea un procedimiento de nombre artistas_getListMasAnimalesCuida que devuelva los datos del/os artista/s que cuidan a más animales de los indicados (parámetro que se le envía).

Pista: Como la consulta puede devolver más de un artista no podremos hacer uso de INTO....
USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getListMasAnimalesCuida;
DELIMITER $$

CREATE PROCEDURE artistas_getListMasAnimalesCuida(p_numAnimales tinyint)	-- Fijarse en el tipo. No hace falta poner un int.
    COMMENT 'Devuelve los datos del/os artista/s que cuida a más animales de los indicados. p_numAnimales: indica a cuantos animales tiene que cuidad el artista para salir en el listado'
BEGIN
    SELECT *
    FROM ARTISTAS
    WHERE nif IN (SELECT nif_artista
		  FROM ANIMALES_ARTISTAS
		  GROUP BY nif_artista
		  HAVING COUNT(*) > p_numAnimales)
    ORDER BY nif;

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL artistas_getListMasAnimalesCuida(2);

Ejercicio 5

Crea un procedimiento de nombre atracciones_getListPorFecha que devuelva los datos de las atracciones que han comenzado a partir de la fecha indicada.

Pista: Recordar que las fechas son tratadas como cadenas...y tener en cuenta el formato.
Añade una nueva atracción con la fecha de inicio actual.
Llama al procedimiento empleando la fecha actual menos 3 días (haz uso de la función DATE_SUB y curdate)
USE CIRCO;
DROP PROCEDURE IF EXISTS atracciones_getListPorFecha;
DELIMITER $$

CREATE PROCEDURE atracciones_getListPorFecha(p_fecha char(10))	-- Fijarse en el tipo. aaaa-mm-dd
    COMMENT 'Devuelve los datos de las atracciones que han comenzado a partir de la fecha indicada. p_fecha: Formato aaaa-mm-dd'
BEGIN
	SELECT *
    FROM ATRACCIONES
    WHERE fecha_inicio > p_fecha
	ORDER BY nombre;

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL atracciones_getListPorFecha('2001-01-01');
CALL atracciones_getListPorFecha(DATE_SUB(curdate(), INTERVAL 3 DAY));

Ejercicio 6

Crea un procedimiento de nombre pistas_add y que añada una nueva pista.

USE CIRCO;
DROP PROCEDURE IF EXISTS pistas_add;
DELIMITER $$

CREATE PROCEDURE pistas_add(p_nombre varchar(50),p_aforo smallint)	-- Fijarse en el tipo de dato. Se corresponde con las columnas de la tabla PISTAS
    COMMENT 'Añade una nueva pista'
BEGIN
    INSERT INTO PISTAS (nombre, aforo)		-- Mejor poner las columnas, ya que si modificamos la estructura de la tabla, la orden sigue funcionando
    VALUES (p_nombre,p_aforo);
    
    SELECT ROW_COUNT();

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL pistas_add('El gran misil',134);

Ejercicio 7

Crea un procedimiento de nombre atracciones_update que permita modificar los datos de una atracción (no se permite actualizar su clave primaria).

Modifica la fecha de inicio de la atracción 'El gran felino' y ponla un día después de la que tiene ahora mismo.
Pista: Tendrás que guardar las ganancias y la fecha de inicio que tiene para poder enviar ese dato al procedimiento.
Comprueba como al llamar al método con una atracción que no existe, row_count va a devolver 0.
USE CIRCO;
DROP PROCEDURE IF EXISTS atracciones_update;
DELIMITER $$

CREATE PROCEDURE atracciones_update(p_nombre varchar(50),p_fecha date, p_ganancias decimal(8,2))	-- Fijarse en el tipo de dato. Se corresponde con las columnas de la tabla ATRACCIONES
    COMMENT 'Modifica una atracción'
BEGIN

	UPDATE ATRACCIONES
    SET fecha_inicio = p_fecha,
        ganancias = p_ganancias
	WHERE nombre = p_nombre;
    
    SELECT ROW_COUNT();

END$$
DELIMITER ;
Ejemplos de llamadas:
SELECT ganancias,fecha_inicio
INTO @ganancias,@fecha_inicio
FROM ATRACCIONES
WHERE nombre = 'El gran felino';
CALL atracciones_update('El gran felino',DATE_ADD(@fecha_inicio,INTERVAL 1 DAY),@ganancias);


CALL atracciones_update('El gran felino_no_existe','1890-01-03',1000.34);

Ejercicio 8

Crea un procedimiento de nombre pistas_delete que borre una pista por su nombre. Haz que borre en base al patrón nombre% (empleando el Like).

Borra la pista que hayas añadido en el ejercicio 6 mandando las primeras letras (ten cuidado de que no haya otra atracción con esas letras al comienzo).
Pista: Emplea la función CONCAT para el LIKE
USE CIRCO;
DROP PROCEDURE IF EXISTS pistas_delete;
DELIMITER $$

CREATE PROCEDURE pistas_delete(p_nombre varchar(50))	-- Fijarse en el tipo de dato. Se corresponde con las columnas de la tabla PISTAS
    COMMENT 'Borra una pista'
BEGIN

    DELETE FROM PISTAS
    WHERE nombre LIKE CONCAT(p_nombre,'%');
    
    SELECT ROW_COUNT();

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL pistas_delete('El gran m');		-- Yo había añadido una de nombre El gran misil

Salida

  • En este tipo de parámetros, el procedimiento almacenado, al modificar su valor, este queda reflejado en el código desde donde se realizó la llamada.
En la definición del parámetro debemos añadir la palabra OUT.
  • Por ejemplo:
DELIMITER \\
CREATE PROCEDURE nombre_proc (OUT nombre_parametro1 tipo_dato)
BEGIN
     .......
     SET nombre_parametro1 = 1;  // Cualquier instrucción que modifique el valor, como SELECT INTO, SET,....
END//
DELIMITER ;
Al salir del procedimiento, el valor del parámetro será de 1.
Quien lo llama:
CALL nombre_proc(@dato);

SELECT @dato;    // El valor de la variable @dato es uno, ya que fue modificado por el procedimiento y es de salida.
  • Podemos utilizar cualquier combinación de parámetros de salida y de entrada en el mismo procedimiento.
  • Nota: Aunque veremos algún ejercicio con parámetros de salida aplicado a una variable de sesión, recordar que la variable de sesión tiene un alcance global y por tanto podría ser modificada directamente dentro del procedimiento.
Veremos otros ejercicios en los que un método llama a otro empleando una variable local.


Ejercicios propuestos de parámetros de Salida

Crea los procedimientos indicados utilizando las formas vistas (SQL, PhpMyAdmin o MysqlWorkBench) y añadiendo en todos ellos un comentario de cual es su objetivo. Si no está claro, documenta también cada uno de los parámetros. Escribe un ejemplo de llamada a cada uno de los procedimientos creados.

Ejercicio 1

Crea un procedimiento de nombre pistas_getAforo al que se le pase el nombre de una pista y devuelve en forma de parámetro de salida su aforo.

Ejercicio 2

Crea un procedimiento de nombre artistas_getNumAnimalesCuida al que se le pase el nif de un artista y que devuelva en forma de parámetro de salida a cuantos animales cuida.

Ejercicio 3

Crea un procedimiento de nombre animales_getNombreAforo al que se le pase el nombre de un animal y devuelva, empleando un parámetro de salida y haciendo uso del procedimiento creado en el ejercicio 1, de una cadena con el formato: NombreAnimal:peso:pista:aforo

Pista: Emplea la función CONCAT

Ejercicio 4

Crea un procedimiento de nombre artistas_getNumAtracAnimal al que se le pase los apellidos y nombre de un artista y devuelva, empleando un parámetro de salida, el número de atracciones en las que trabaja y el número de animales que cuida (empleando el procedimiento del ejercicio 2) con el formato: nif:NumAtracciones:NumAnimales.

Nota: Suponemos que no hay artistas con el mismo nombre y apellidos.


Solución ejercicios propuestos de parámetros de Salida

Ejercicio 1

Crea un procedimiento de nombre pistas_getAforo al que se le pase el nombre de una pista y devuelve en forma de parámetro de salida su aforo.

USE CIRCO;
DROP PROCEDURE IF EXISTS pistas_getAforo;
DELIMITER $$
CREATE PROCEDURE pistas_getAforo(p_nombre varchar(50), OUT p_aforo smallint)
    COMMENT 'Devuelve en p_aforo el aforo de la pista indicada por p_nombre'
BEGIN

    SELECT aforo
    INTO p_aforo
    FROM PISTAS
    WHERE nombre = p_nombre;

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL pistas_getAforo('LATERAL1',@aforo);	
SELECT @aforo;

Ejercicio 2

Crea un procedimiento de nombre artistas_getNumAnimalesCuida al que se le pase el nif de un artista y que devuelva en forma de parámetro de salida a cuantos animales cuida.

USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getNumAnimalesCuida;
DELIMITER $$
CREATE PROCEDURE artistas_getNumAnimalesCuida(p_nif char(9), OUT p_numAnimales tinyint)		
    COMMENT 'Devuelve en p_numAnimales el número de animales que cuida el artista indicado por p_nif'
BEGIN

    SELECT COUNT(*)
    INTO p_numAnimales
    FROM ANIMALES_ARTISTAS
    WHERE nif_artista = p_nif;

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL artistas_getNumAnimalesCuida('11111111A',@numAnimales);	
SELECT @numAnimales;

Ejercicio 3

Crea un procedimiento de nombre animales_getNombreAforo al que se le pase el nombre de un animal y devuelva, empleando un parámetro de salida y haciendo uso del procedimiento creado en el ejercicio 1, de una cadena con el formato: NombreAnimal:peso:pista:aforo

Pista: Emplea la función CONCAT
USE CIRCO;
DROP PROCEDURE IF EXISTS animales_getNombreAforo;
DELIMITER $$
CREATE PROCEDURE animales_getNombreAforo(p_nombre varchar(50), OUT p_cadena varchar(150))		
    COMMENT 'Devuelve en p_cadena una cadena con el formato: NombreAnimal:peso:pista:aforo en base al nombre del animal p_nombre'
BEGIN
    DECLARE v_peso float;		-- Fijarse en el tipo. Se corresponde con el tipo de la columna en ANIMALES
    DECLARE v_aforo smallint;
    DECLARE v_nombrePista varchar(50);
    
    SELECT nombre_pista,peso
    INTO v_nombrePista,v_peso
    FROM ANIMALES
    WHERE nombre = p_nombre;
    
    CALL pistas_getAforo(v_nombrePista,v_aforo);		-- El método devuelve en v_aforo el aforo de la pista
    
    SET p_cadena = CONCAT(p_nombre,':',v_peso,':',v_nombrePista,':',v_aforo);

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL animales_getNombreAforo('Leo',@datos);	
SELECT @datos;

Ejercicio 4

Crea un procedimiento de nombre artistas_getNumAtracAnimal al que se le pase los apellidos y nombre de un artista y devuelva, empleando un parámetro de salida, el número de atracciones en las que trabaja y el número de animales que cuida (empleando el procedimiento del ejercicio 2) con el formato: nif:NumAtracciones:NumAnimales.

USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getNumAtracAnimal;
DELIMITER $$
CREATE PROCEDURE artistas_getNumAtracAnimal(p_nombre varchar(45),p_apellidos varchar(100), OUT p_cadena varchar(20))		
    COMMENT 'Devuelve en p_cadena una cadena con el formato: nif:NumAtracciones:NumAnimales en base al nombre y apellidos del artista enviado en p_nombre y p_apellidos'
BEGIN
    DECLARE v_nif char(9);		
    DECLARE v_numAnimales tinyint default 0;		-- Ponemos default para en el caso de que el SELECT no encuentre el artista no apareza null
    DECLARE v_numAtracciones tinyint default 0;		-- Ponemos default para en el caso de que el SELECT no encuentre el artista no apareza null
    
    SELECT nif
    INTO v_nif
    FROM ARTISTAS
    WHERE nombre = p_nombre AND
          apellidos = p_apellidos;
          
    
    CALL artistas_getNumAnimalesCuida(v_nif,v_numAnimales);		-- El método devuelve en v_numAnimales el número de animales que cuida el artista
    SELECT COUNT(*)
    INTO v_numAtracciones
    FROM ATRACCIONES_ARTISTAS
    WHERE fecha_fin IS NULL AND
		  nif_artista = v_nif;
    
    SET p_cadena = CONCAT(v_nif,':',v_numAtracciones,':',v_numAnimales);

END$$
DELIMITER ;
Ejemplos de llamadas:
CALL artistas_getNumAtracAnimal('Carlos','Perez',@datos);	
SELECT @datos;


Entrada/Salida

  • Los parámetros de Entrada/Salida son el resultado de aplicar los conceptos aprendidos para los de entrada y para los de salida.
Son parámetros a los que vamos a poder dar un valor inicial, llamar al procedimiento y que pueda hacer uso del valor enviado y dentro del procedimiento va a poder cambiar su valor y este será reflejado desde donde se llamó.
  • Por ejemplo:
DELIMITER \\
CREATE PROCEDURE nombre_proc (INOUT nombre_parametro1 tipo_dato)
BEGIN
     SELECT 
     FROM TABLA
     WHERE columna = nombre_parametro1;
     .......
     SET nombre_parametro1 = 1;  // Cualquier instrucción que modifique el valor, como SELECT INTO, SET,....
END//
DELIMITER ;
Al salir del procedimiento, el valor del parámetro será de 1.
Quien lo llama:
SET @dato = 100;  // El procedimiento podrá hacer uso del valor enviado en la variable @dato
CALL nombre_proc(@dato); 

SELECT @dato;    // El valor de la variable @dato es uno, ya que fue modificado por el procedimiento y es de salida.


Ejercicios propuestos de parámetros de Entrada/Salida

Crea los procedimientos indicados utilizando las formas vistas (SQL, PhpMyAdmin o MysqlWorkBench) y añadiendo en todos ellos un comentario de cual es su objetivo. Si no está claro, documenta también cada uno de los parámetros. Escribe un ejemplo de llamada a cada uno de los procedimientos creados.

Ejercicio 1

Crea un procedimiento de nombre pistas_addAforo al que se le envíe como parámetros el nombre de la pista y una cantidad que representa el incremento del aforo.

El procedimiento debe devolver en el mismo parámetro el nuevo aforo de la pista.
Nota: Aún no vimos el uso de IF pero en este método habría que tener en cuenta si el aforo es superior al rango de un smallint...

Ejercicio 2

Crea un procedimiento de nombre artistas_getNombreCompleto al que se le pase como parámetro el nif y devuelva en el mismo parámetro el nombre completo con el formato: apellidos, nombre

Ejercicio 3

Crea un procedimiento de nombre animales_addAforo al que se le envíe como parámetros el nombre del animal y el incremento del aforo en la pista en la que trabaja el animal. Debe de hacer uso del procedimiento creado en el ejercicio 1 y debe de devolver empleando los dos parámetros anteriores, el nombre de la pista y su nuevo aforo.


Soluciones ejercicios propuestos de parámetros de Entrada/Salida

Ejercicio 1

Crea un procedimiento de nombre pistas_addAforo al que se le envíe como parámetros el nombre de la pista y una cantidad que representa el incremento del aforo.

El procedimiento debe devolver en el mismo parámetro el nuevo aforo de la pista.
USE CIRCO;
DROP PROCEDURE IF EXISTS pistas_addAforo;
DELIMITER $$
CREATE PROCEDURE pistas_addAforo(p_nombre varchar(50),INOUT p_incAforo smallint)		-- El parámetro tiene que ser smallint ya que lo usamos para guardar el aforo
    COMMENT 'Devuelve en p_incAforo el nuevo aforo incrementado en la pista p_nombre'
BEGIN
	UPDATE PISTAS
    SET aforo = aforo + p_incAforo
    WHERE nombre = p_nombre;
    
    SELECT aforo
    INTO p_incAforo
    FROM PISTAS
    WHERE nombre = p_nombre;

END$$
DELIMITER ;
Ejemplo de uso:
SET @dato = 50;	-- Incremento de aforo
CALL pistas_addAforo('LATERAL1',@dato);	
SELECT @dato;

Ejercicio 2

Crea un procedimiento de nombre artistas_getNombreCompleto al que se le pase como parámetro el nif y devuelva en el mismo parámetro el nombre completo con el formato: apellidos, nombre

USE CIRCO;
DROP PROCEDURE IF EXISTS artistas_getNombreCompleto;
DELIMITER $$
CREATE PROCEDURE artistas_getNombreCompleto(INOUT p_dato varchar(145))		-- varchar(145) porque es el tamaño de nombre+apellidos
    COMMENT 'Devuelve el nombre y apellidos con el formato apellidos, nombre. p_dato tiene el nif del artista y se modifica con el nombre y apellidos'
BEGIN
    
    SELECT CONCAT(apellidos,', ',nombre)
    INTO p_dato
    FROM ARTISTAS
    WHERE nif = p_dato;

END$$
DELIMITER ;
Ejemplo de uso:
SET @dato = '11111111A';	
CALL artistas_getNombreCompleto(@dato);	
SELECT @dato;

Ejercicio 3

Crea un procedimiento de nombre animales_addAforo al que se le envíe como parámetros el nombre del animal y el incremento del aforo en la pista en la que trabaja el animal. Debe de hacer uso del procedimiento creado en el ejercicio 1 y debe de devolver empleando los dos parámetros anteriores, el nombre de la pista y su nuevo aforo.

USE CIRCO;
DROP PROCEDURE IF EXISTS animales_addAforo;
DELIMITER $$
CREATE PROCEDURE animales_addAforo(INOUT p_nombre varchar(50),INOUT p_aforo SMALLINT)		
    COMMENT 'Devuelve el nombre de la pista y el nuevo aforo en el que trabaja el animal. p_nombre indica el nombre del animal p_aforo indica el incremento del aforo'
BEGIN
    DECLARE p_nombrePista varchar(50);
    
    SELECT nombre_pista
    INTO p_nombre
    FROM ANIMALES
    WHERE nombre = p_nombre;
    
    CALL pistas_addAforo(p_nombre, p_aforo);
END$$
DELIMITER ;
Ejemplo de uso:
SET @nombre = 'Princesa1';
SET @aforo = 10;

CALL animales_addAforo(@nombre,@aforo);	
SELECT @nombre,@aforo;


Enlace a la página principal de la UD7

Enlace a la página principal del curso


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