Con el trabajo desarrollado por el equipo de Vindio, Noiba y Naroba, en BK Sistemas Informáticos ya disponen de una base de datos que integra toda la información que necesitaba el cliente para llevar la gestión del taller mecánico. Atrás han quedado los archivos de facturas en papel, las fichas manuales con los datos de nuestros clientes, los listados para consultar los recambios y sus precios, etc. El trabajo se ha simplificado enormemente, el acceso a la información es rápido y los socios están muy satisfechos con su trabajo. Pero Juan les recuerda que aún le pueden sacar más partido a la base de datos si incorporan al lenguaje SQL algunas características que, en general, están disponibles en cualquier lenguaje de programación y que permiten a los usuarios autorizados escribir bloques de sentencias SQL, y guardarlos en el servidor como cualquier otro objeto de la base de datos para utilizarlos cuando lo necesiten.
¿Es eso posible con SQL? Sí, la mayoría de los SGBD permiten que los usuarios creen sus propios procedimientos y funciones mediante una extensión del lenguaje SQL.
¿Cómo podrían aplicar esto en el taller? La creación de un procedimiento guardado podría servir, por ejemplo, para recoger todas las sentencias que tenemos que realizar cada vez que un nuevo cliente nos trae su vehículo para que solucionemos una avería. Si podemos hacer esto de forma automática sin más que activar el procedimiento nos ahorraremos trabajo y posibles errores, ¿no crees? Vamos a ello.
Ahora que ya dominas el uso de SQL para la manipulación y consulta de datos, es el momento de dar una vuelta de tuerca adicional para mejorar las aplicaciones que utilicen nuestra base de datos. Para ello nos vamos a centrar en la programación de bases de datos o construcción de guiones, utilizando el lenguaje SQL/PSM (Persistent Stored Modules). En esta unidad conoceremos qué es SQL/PSM, cuál es su sintaxis y veremos cómo podemos sacarle el máximo partido a una base de datos mediante su uso.
Aunque en un principio SQL fue un lenguaje dirigido exclusivamente a la manipulación interactiva de conjuntos de datos, ejecutando sentencias individuales más o menos complejas como las que hemos visto en unidades anteriores, con el tiempo quedaron patentes los beneficios que aportaría la inclusión en los SGBDR de toda la lógica relacionada con el tratamiento de datos, incluyendo procesos más complejos que una simple inserción, actualización o consulta.
Por ello, casi todos los grandes Sistemas Gestores de Base de Datos incorporan utilidades que permiten ampliar el lenguaje SQL para producir pequeñas utilidades que añaden al SQL mejoras de la programación estructurada (bucles, condiciones, funciones, etc.)
En informática, se denomina guión a una secuencia de comandos o un programa relativamente simple, conjunto de sentencias y de instrucciones, que realiza una tarea específica y que se guarda con un nombre en la base de datos.
Noiba, Vindio y Naroba se plantean la necesidad de añadir funcionalidades nuevas a su aplicación. Para eso van a tener que pasar de un lenguaje de consultas a un lenguaje de procedimientos. Creen que diseñar sus propios guiones de sentencias y hacer que se ejecuten cuando lo necesiten les va a permitir mejorar en el control y mantenimiento de los datos. Para dar este paso, tienen que empezar por revisar cómo se pueden crear esas rutinas o subprogramas con MySQL.
Hasta ahora hemos visto que el SQL es un lenguaje que permite consultar, añadir, modificar y eliminar datos en las tablas de la base de datos, pero existen otras funciones propias de un lenguaje de programación que no hemos visto hasta ahora.
Algunos de los procesos que llevamos a cabo habitualmente, como es el caso del registro de una reparación en el taller, implican que se ejecuten varias sentencias, unas detrás de otras. Sería muy interesante que este conjunto de sentencias estuvieran recogidas en una rutina, y que se pudieran guardar y ejecutar cuando lo necesitemos, sin necesidad de enviar las consultas de una en una.
Tampoco podíamos, hasta ahora, establecer condiciones para que se lleven a cabo o no unas determinadas acciones o hacer que estas se repitan. Si queremos que se muestre un listado para hacer una compra a los proveedores, cada vez que el stock de los recambios baje por debajo de una cantidad que consideramos como el stock mínimo, y que este proceso se ejecute automáticamente, necesitaríamos establecer condiciones, algo que no hemos visto con SQL.
Cuando tengamos que realizar operaciones que impliquen la ejecución condicional, así como operaciones repetitivas, necesitaremos un lenguaje de programación de procedimientos como Java, Perl, Php, Visual Basic, etc. Podemos insertar sentencias SQL dentro de estos lenguajes que actuarían como Lenguaje anfitrión. Este método es muy común pero no el único.
Con la aparición de las bases de datos distribuidas y las bases de datos orientadas a objetos se necesita que ese código se pueda guardar en la base de datos y sea compartido por las aplicaciones que accedan a los datos, porque así se mejora el mantenimiento y el control lógico. Por este motivo actualmente la mayoría de los SGBD han incluido extensiones de programación que incluyen:
Estructuras de control como: IF-THEN-ELSE y DO-WHILE.
Declaración de variables y utilización dentro de las rutinas.
Manejo de errores.
Parte de un programa principal que realiza una tarea determinada.
Es un lenguaje que proporciona determinadas funcionalidades como estructuras de control, tratamiento de variables, operaciones de cálculo a otro lenguaje que está incrustado y que se denomina Lenguaje huésped.
Varias bases de datos conectadas por una red a las que el usuario accede como si fuera una base de datos local.
El SQL de procedimientos permite utilizar código de procedimientos y sentencias SQL que se guardan dentro de la base de datos y se ejecutan cuando el usuario los invoca.
En el caso de MySQL la extensión de SQL que permite utilizar código y crear rutinas se denomina SQL /PSM, y simplemente es un lenguaje procedimental estructurado en bloques que amplía la funcionalidad de SQL . Con SQL /PSM podemos usar sentencias SQL para manipular datos y sentencias de control de flujo para procesar los datos. Por tanto, SQL /PSM combina la potencia de SQL para la manipulación de datos, con la potencia de los lenguajes procedimentales para procesar los datos.
En esta unidad vamos a crear procedimientos, funciones y disparadores, utilizando estructuras alternativas y estructuras repetitivas dentro de guiones escritos en SQL. La posibilidad de incrustar lenguaje SQL dentro de un lenguaje de programación como es el caso MySQL con PHP, es muy utilizada en la creación de páginas Web dinámicas. En el módulo de Aplicaciones Web se estudia esta alternativa.
Permite realizar una serie de acciones según se cumpla o no una condición.
Ejecuta un conjunto de acciones de forma reiterada, terminando cuando la condición de salida alcance un valor determinado.
La base de datos en la que puedes probar todos los ejemplos de la unidad es : campeonato6
Se trata de la base de datos campeonato, con la que has trabajado en las unidades anteriores, pero que ahora se le ha añadido una vista y una nueva tabla, y se ha renombrado la base de datos como campeonato6. Las otras tablas se mantienen igual.
Los nuevos elementos son:
Una vista (<span lang="en">VIEW</span>) incluye una consulta que lista para cada juego: su código, su nombre, el código y nombre de su equipo, el total de puntos del juego y total de concursantes que participan en el juego. El listado se muestra ordenado de más a menos puntos. Si no se participa en el juego, éste aparece también en el listado con 0 puntos y 0 concursantes.
Una tabla para guardar resúmenes de puntos y concursantes por juego.
Se han modificado algunos datos de la tabla participa para recoger un rango más amplio de fechas de participación en juegos.
El modelo o esquema de la base de datos campeonato6 es el siguiente:
Workbench(Elaboración propia)
Para ir probando todos los ejemplos expuestos y propuestos en la Unidad descarga la base de datos (zip - 2,33 KB)y la despliegas o instalas en tu servidor MySQL.
Naroba se dispone a estudiar junto a Noiba las nuevas posibilidades que se abren al analizar cómo puede crear sus propios procedimientos y funciones, además de almacenarlos en TalleresFaber y asociar disparadores o triggersa las tablas.
Con el resto de los empleados del taller que acceden a la base de datos estudia qué procesos se componen de varias sentencias y se utilizan frecuentemente, para poder crear estas estructuras como procedimientos almacenados; por ejemplo registrar una nueva reparación, o algunas operaciones habituales con las que sería interesante crear funciones y simplemente utilizarlas en nuestras consultas, como podría ocurrir con el cálculo del coste de la mano de obra empleada en una reparación. También que algunas operaciones se realicen automáticamente creando disparadores o triggers, caso de comprobar la existencia de recambios en el almacén. Para ver si esto es posible, estudiaremos las estructuras funcionales que maneja nuestro SGBD.
Módulos: Se denominan así a las partes en que se puede dividir un programa para reducir su complejidad, que son independientes de la aplicación en sí y de las restantes partes del programa, aunque tienen conexiones con otros módulos. También se les denomina rutinas o subrutinas. En bases de datos las rutinas pueden ser procedimientos, funciones o disparadores (triggers).
Procedimientos: Se trata de un conjunto de instrucciones SQL que se guardan en el servidor y que se ejecutan cuando ese procedimiento es llamado o invocado explícitamente.
Funciones: Son rutinas que devuelven un valor en su nombre.
Triggers: Son rutinas que van asociadas a una tabla y se ejecutan automáticamente cuando sobre esa tabla se realiza alguna de estas operaciones: un INSERT, un UPDATE o un DELETE. Se suelen utilizar para prevenir errores y mejorar la administración de la base de datos sin necesidad de que el usuario ejecute la sentencia SQL, ya que se ejecuta automáticamente en función de que se cumpla una determinada condición.
En general las funciones y los procedimientos no son muy diferentes. Ambos están constituidos por un conjunto de sentencias lógicamente agrupadas para realizar una tarea específica. Se trata de un bloque de código que se almacena en tablas del sistema de la base de datos.
Ventajas del uso de rutinas.
Las ventajas que presenta el uso de rutinas almacenadas son:
Seguridad. Se pueden establecer autorizaciones variadas de ejecución para los usuarios sobre los procedimientos y funciones que se deseen, con lo que se puede gestionar la seguridad. Proporcionan un único punto de acceso a la base de datos lo que permite controlar más fácilmente qué operaciones se realizan, quienes pueden utilizarlas y en qué momento.
Reutilización. Es posible disponer de librerías de procedimientos y funciones almacenadas en el servidor, que pueden ser invocadas desde otros lenguajes de programación o ser reutilizados para construir otras rutinas.
Mejoran el rendimiento, ya que están almacenados en la base de datos, y ello supone que se necesita enviar menos información entre el servidor y el cliente.
Se simplifican las tareas cotidianas.
Son útiles cuando queremos realizar una misma operación que afecte a varias bases de datos y tablas de un mismo servidor.
Un procedimiento almacenado en el servidor ayuda a mantener la consistencia y la integridad de los datos, ya que evita que éstos puedan ser corrompidos por el acceso de programas defectuosos.
Permiten la validación de datos, y se integran en la estructura de la base de datos. Cuando funcionan con este propósito se denominan triggers.
Son dinámicos ya que admiten parámetros que se pasan antes de su ejecución y puedan realizar diferentes tareas dependiendo de esos parámetros que se hayan pasado.
Parte de un programa que realiza una acción determinada.
Son los valores que se pasan a un procedimiento o función cuando se llaman o invocan.
La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor; sin embargo, en los procedimientos esto no es posible, aunque sí que podemos definir múltiples parámetros de salida. Esto último también es posible en las funciones.
En todas las herramientas gráficas (MySQL Workbench, PhpMyAdmin, Navicat, SQLMaestro, etc.) que te hemos presentado hasta ahora podemos encontrar recursos para crear, modificar y/o eliminar procedimientos y funciones en modo gráfico.
Veamos cómo crear en modo gráfico estas rutinas con MySQL Workbench. Observa lo siguiente:
El modo gráfico permite que te familiarices con la sintaxis que hay que utilizar en la creación de estas rutinas, pues crea el esqueleto de cada una de ellas.
A pesar de crear esas rutinas en modo gráfico, es necesario escribir el código SQL necesario para realizar la tarea específica que se quiere automatizar.
Observa la composición de código que finalmente envía el cliente al servidor (le añade unos delimitadores con la palabra reservada DELIMITER
1.- Crear procedimiento .
Realiza con tu usuario una conexión con Workbench a MySQL y pon en uso o activa la base de datos campeonato para trabajar con ella.
En la sección izquierda aparecen contenedores para organizar de forma gráfica diferentes objetos de la base de datos: tablas, procedimientos almacenados, funciones, vistas, ...
Haz clic derecho sobre el contenedor de procedimientos almacenados y después pulsas en la opción "Crear procedimiento almacenado".
En esa estructura básica que te aparece en la ventana derecha debes incluir el código del procedimiento.
Workbench(Elaboración propia)
2.- Crear procedimiento.
Un vez que hayas escrito el código apropiado para el procedimiento debes pulsar en aplicar.
Workbench(Elaboración propia)
3.- Crear procedimiento.
Aparece en una nueva ventana el código generado por MySQL. Ese será el formato que seguiremos nosotros para escribir mediante SQL/PSM los procedimientos almacenados.
Pulsamos aplicar y, si todo es correcto y se ejecuta sin errores, nos permite finalizar. El procedimiento ya se ha creado.
Si al pulsar el botón de aplicar se producen errores, habrá que corregirlos y volver a intentarlo.
Workbench(Elaboración propia)
4.- Ejecutar procedimiento.
Una vez creado el procedimiento, este se habrá almacenado en la base de datos. Realmente se almacena en una de las tablas del sistema, pero la herramienta Workbench organiza los objetos de la base de datos en esos contenedores para facilitar su tratamiendo visual o gráfico.
Para ejecutarlo:
Pulsa sobre el rayo que aparece sobre el nombre del procedimiento y mostrará el formulario de la derecha, pues ese procedimiento requiere un parámetro de entrada.
Se introduce el valor de un código de equipo, el '01'.
Y pulsas Ejecutar.
Workbench(Elaboración propia)
5.- Ejecutar procedimiento.
El resultado de la ejecución del procedimiento en modo gráfico muestra dos cosas:
En la sección superior se ven las sentencias SQL que ha generado Workbench y ha enviado al servidor MySQL para ejecutarlo.
En la sección inferior se ve el resultado de la ejecución.
Workbench(Elaboración propia)
6.- Crear función.
En la sección izquierda aparecen contenedores para organizar de forma gráfica diferentes objetos de la base de datos: tablas, procedimientos almacenados, funciones, vistas, ...
Haz clic derecho sobre el contenedor de funciones almacenadas y después pulsas en la opción "Crear función".
En esa estructura básica que te aparece en la ventana derecha debes incluir el código de la función.
Workbench(Elaboración propia)
7.- Crear función.
Un vez que hayas escrito el código apropiado para la función debes pulsar en aplicar.
Workbench(Elaboración propia)
8.- Crear función.
Aparece en una nueva ventana el código generado por MySQL. Ese será el formato que seguiremos nosotros para escribir mediante SQL/PSM las funciones almacenadas.
Pulsamos aplicar y, si todo es correcto y se ejecuta sin errores, nos permite finalizar. La función ya se ha creado.
Si al pulsar el botón de aplicar se producen errores, habrá que corregirlos y volver a intentarlo.
Workbench(Elaboración propia)
9.- Ejecutar función.
Una vez creada la función, éste se habrá almacenado en la base de datos. Realmente se almacena en una de las tablas del sistema, pero la herramienta Workbench organiza los objetos de la base de datos en esos contenedores para facilitar su tratamiento visual o gráfico.
Para ejecutarla:
Pulsa sobre el rayo que aparece sobre el nombre de la función y mostrará el resultado directamente, pues en este caso no hay que suministrar datos para que se ejecute.
Para diseñar procedimientos, funciones o triggers, Noiba tiene que empezar por revisar los elementos que estas rutinas pueden incluir. En unidades anteriores ha tratado los tipos de datos que emplea MySQL y algunos aspectos relativos a la estructura del lenguaje, como: tratamiento de palabras reservadas, valores literales, escritura de comentarios, nombres de objetos, etc. Es necesario tener presente todos estos temas, pero ahora tiene que detenerse en algunos elementos que forman parte de la estructura del lenguaje, que no ha tratado hasta ahora: las variables y las estructuras de control. En este apartado, vamos a seguirla en el estudio de las reglas que hay que cumplir para definir y utilizar variables en un guión.
Una variable de usuario, también denominada variable de sesión, permite almacenar un valor y referirnos a él más tarde, durante la misma sesión.
Pueden pasarse de una sentencia a otra
No pueden ser vistas por otros usuarios.
Desaparecen cuando la conexión se cierra.
Son visibles o accesibles dentro y fuera de las rutinas almacenadas. (Son como variables globales).
No es necesario declararlas con un tipo de dato y van precedidas por el símblo @.
Si no se les da valor concreto, su valor es NULL
Para crear una variable de usuario es suficiente con nombrarla. Se le pueden asignar valores con SET :
SET @NombreVariable1 = Expresión [, @NombreVariable2 = Expresión] …
Por ejemplo, en la siguiente línea de código se están creando tres variables y se les asigna un valor concreto.
-- define tres variables de sesión y le asigna un valor a cada una de ellas.
SET @elementos = 50, @fecha:='2023-03-25', @usuario='Azucena';
Podemos utilizar como operador de asignación tanto el signo = como el signo := cuando las definimos con <b>SET</b>. Si la variable recibe su valor de otras sentencias el operador debe ser := para diferenciarlo del operador de comparación.
En este otro ejemplo, es obligatorio usar el operador := para asignar un valor a la variable de sesión puesto que no la estamos definiendo con SET.
use campeonato6;
/*consulta la cuota media de los concursantes y lo dejas en una variable de sesión */
SELECT @cuota_media:=AVG(cuota_inscri)
from concursante;
/*Se puede usar esa variable de sesión en otra sentencia SQL durante
esa misma sesión: Por ejemplo, para mostrar los concursantes con cuota inferior a la cuota media*/
select *
from concursante
where cuota_inscri <@cuota_media;
Para utilizar una variable de usuario:
Podemos utilizar las variables de usuario en cualquier sitio donde se puedan usar expresiones, siempre y cuando no sea necesario que sea un valor literal. Por ejemplo, la cláusula LIMIT en una SELECT tiene que contener un valor literal que refleje el número de filas devueltas por la consulta.
No se debe asignar un valor a una variable de usuario en una parte de una sentencia y usarla en otra parte de la misma sentencia. Por ejemplo: no se debe asignar un valor a una variable en SELECT y hacer referencia a ella en HAVING, GROUP BY u ORDER BY. Puede dar resultados inesperados.
Si hacemos referencia a una variable sin inicializar con ningún valor, su valor es NULL y de tipo cadena.
En esta unidad las usaremos fundamentalmente en las llamadas a los procedimientos almacenados, en el lugar del parámetro de salida (OUT) y los de entrada/salida (INOUT)
Una variable global es, en informática, una variable accesible en todos los ámbitos de un programa informático.
Aunque ya hemos tratado en unidades anteriores el resto de elementos que forman parte de la estructura del lenguaje SQL en MySQL, es aconsejable que los recuerdes como paso previo a la escritura de procedimientos y funciones.
Para recordar las reglas sobre cómo escribir: literales, nombres de objetos de la base de datos, comentarios, variables de usuario y tratamiento de palabras reservadas, puedes acceder al manual de MySQL en el siguiente enlace:
Vindio le comenta a Noiba y Naroba que considera muy interesante poder almacenar en el servidor de la base de datos TalleresFaber algunos bloques de instrucciones que se repiten frecuentemente. El cree que esto va a resultar, en primer lugar, más rápido porque no habrá que enviar las consultas de una en una al servidor, y en segundo lugar más seguro. Si el código es correcto, no tendrán que estar pendientes de posibles errores.
Por otra parte, todas las personas que acceden a la base de datos con autorización podrán ejecutar estos procedimientos, aunque se trate de tareas complejas.
Los procedimientos son rutinas o subprogramas compuestos por un conjunto nombrado de sentencias SQL agrupados lógicamente para realizar una tarea específica, que se guardan en la base de datos y se ejecutan como una unidad cuando son invocados por su nombre.
Analizaremos en primer lugar, las ventajas de utilizar procedimientos:
Mejoran el rendimiento en la comunicación entre el cliente y el servidor. Dado que un procedimiento consta de varias instrucciones y está almacenado en el servidor, desde el cliente MySQL (Workbench o cliente en modo texto) no hay que enviar todas esas instrucciones una a una, sino simplemente se envía la llamada al procedimiento. Por tanto, con los procedimientos se agiliza el envío de instrucciones desde los clientes al servidor, no recibiendo este tantas peticiones de tareas. Como contrapartida, el servidor tiene una mayor carga de trabajo al tener que buscar y decodificar los procedimientos almacenados cuando son invocados desde los clientes.
Cuando las aplicaciones cliente trabajan con diferentes lenguajes y plataformas, los procedimientos son muy útiles.
Proporcionan mayor seguridad en dos sentidos: por una parte, las aplicaciones y los usuarios no acceden directamente a las tablas, sino que sólo pueden ejecutar algunos procedimientos y no tendrán que construir esos procesos sobre la base de las sentencias que los forman, con el posible riesgo de alteraciones no deseadas de los datos por operaciones indebidamente realizadas; y por otra parte, se pueden establecer autorizaciones de ejecución diferentes, para los usuarios sobre los procedimientos y funciones que se deseen.
Resumir un proceso con varias instrucciones SQL complejas. Se ejecuta más rápido como un procedimiento almacenado que si se trata de un programa instalado en el cliente que envía y recibe consultas SQL al servidor.
Los permisos asociados a las distintas operaciones que se pueden realizar con procedimientos almacenados son: CREATE ROUTINE (creación), EXECUTE (ejecución), ALTER ROUTINE (borrado). Por ejemplo un usuario puede tener permiso para ejecutar un procedimiento (EXECUTE), pero en cambio puede no tener permiso para crear nuevos procedimientos (CREATE ROUTINE).
Hemos dicho que los procedimientos se almacenan en el SGBD, pero ¿dónde se almacenan?
En MySQL los procedimientos se almacenan en:
La tabla proc de la base de datos del sistema mysql. (hasta la versión MySQL 5.7). Esta tabla se crea durante la instalación de MySQL.
La tabla routines de la base de datos de los metadatos information_schema (versiones MySQL 8.x ). Esta tabla se crea durante la instalación de MySQL.
Puedes ejecutar las siguientes sentencias para ver los procedimientos en tu servidor MySQL:
/* Versiones hasta MySQL 5.7 -- en base de datos del sistema mysql */
use mysql;
show tables;
select * from proc;
/* Versiones MySQL 8.0 -- en base de datos de los metadatos information_schema */
use information_schema;
show tables;
select * from routines;
El <strong>NombreProcedimiento </strong>debe ser un nombre descriptivo de la tarea que realiza y debe seguir las reglas de sintaxis de los nombres creados.
Los parámetros son variables del procedimiento que le permiten recibir y/o devolver información. Y pueden ser de tres tipos:
De entrada: IN
De salida: OUT
De entrada/salida: INOUT
<b>CuerpoDelProcedimiento</b>: Casi todas las sentencias SQL válidas. El cuerpo del procedimiento empieza con la sentencia <b>BEGIN</b> y termina con la sentencia <b>END</b> y consta de varias instrucciones. Cada una termina con punto y coma (;). En el caso de que el cuerpo conste de una única sentencia se puede omitir BEGIN y END, pero esto no será lo habitual.
Para definir cada parámetro se sigue el siguiente formato:
<span class=" destacado_inline">[IN | OUT | INOUT] NombreParámetro Tipo </span>
IN: el procedimiento recibe el parámetro y no lo modifica. Lo usa para consultar y utilizar su valor.
OUT: el procedimiento únicamente puede escribir en el parámetro, no puede consultarlo.
INOUT: el procedimiento recibe el parámetro y puede consultarlo y modificarlo.
Los parámetros OUT o INOUT se usan cuando se desea que un procedimiento nos devuelva valores en determinadas variables, esto es, los parámetros de salida permiten simular que el procedimiento devuelve valores.
Es obligatorio escribir una lista de parámetros, aunque sea una lista vacía, reflejada con ( ).
El NombreParámetro sigue las reglas de sintaxis de los nombres creados.
Por defecto cada parámetro es de tipo IN (entrada), por lo que si se omite IN al indicar el parámetro, MySQL asume que es de entrada. Si queremos especificar otro tipo se escribe delante del nombre del parámetro.
Un procedimiento puede tener cualquier número de parámetros y pueden ser de cualquiera de los tipos indicados.
<b>Tipo</b>: Es el Tipo de datos de ese parámetro o variable del procedimiento, y puede ser cualquier tipo de datos válido de MySQL(INT, DATE, CHAR, VARCHAR, etc).
Ejemplo1.- Procedimiento sin parámetros.
Un procedimiento en la base de datos campeonato6 que se encargue de listar los datos de todos los concursantes, sería el siguiente.
USE campeonato6;
CREATE PROCEDURE listado_concursantes()
SELECT * FROM concursante;
Observa que para realizar esa tarea, el procedimiento no necesita de ningún parámetro. Por ello los paréntesis del nombre del procedimiento se dejan en blanco.
Ejemplo 2.- Procedimiento con parámetro de entrada.
Un procedimiento en la base de datos campeonato6 que se encargue de listar los datos del concursante cuyo código le pasemos como parámetro, sería el siguiente.
USE campeonato6;
CREATE PROCEDURE listado_un_concursante (IN pconcur CHAR(3))
SELECT *
FROM concursante
WHERE pconcur = cdconcur;
En este caso es necesario un parámetro de entrada pconcur en el que se reciba o se copie el código del concursante que nos interesa. Su tipo de dato será el mismo que tenga la columna que guarda el código de un concursante, en este caso CHAR(3).
Ejemplo 3.- Procedimiento con un parámetro de salida.
Un procedimiento que obtiene el total de concursantes de la base de datos y devuelve ese valor en un parámetro de salida, sería el siguiente:
USE campeonato6;
CREATE PROCEDURE total_concursantes (OUT ptotal INT)
SELECT COUNT( * ) INTO ptotal
FROM concursante;
En este caso es necesario un parámetro de salida en el cual el procedimiento devuelve el valor calculado. Su tipo de dato será un entero.
Observa que para almacenar los resultados de una consulta directamente en variables usamos la sentencia:
De esta manera dejamos los valores devueltos en las variables indicadas en lugar de mostrarlos en pantalla. Para hacer esto, la consulta debe devolver una sola fila porque no se puede asignar a una variable una lista de contenidos.
En la sección de características se puede especificar la siguiente información:
LANGUAGE SQL significa que el cuerpo del procedimiento está escrito en SQL. Por defecto se tiene esa característica para prever la posible construcción de procedimientos almacenados con otros lenguajes como Java.
DETERMINISTIC / NOT DETERMINISTIC. El procedimiento se considera “determinista” si siempre produce el mismo resultado para los mismos valores de los parámetros de entrada, y “no determinista” si no es así. Por defecto es <b>NOT DETERMINISTIC</b>.<b></b>
<b>SQL SEQURITY</b> sirve especificar si el procedimiento es llamado, usando los permisos del usuario que lo creó (<b>DEFINER</b>, que es el valor por defecto), o usando los permisos del usuario que está haciendo la llamada (<b>INVOKER</b>).<b></b>
<b>COMMENT</b> se usa para escribir el comentario descriptivo de lo que hace el procedimiento y que aparecerá cuando se ejecute una sentencia del tipo SHOW para ver el código o ciertas características del procedimiento con:
SHOW CREATE PROCEDURE o <span class=" destacado_inline">SHOW PROCEDURE status LIKE <nombre_procedimiento> </span>
Las cláusulas de la sección de características tienen como valores predeterminados los siguientes:
LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''
<b></b> En MySQL las sentencias se ejecutan después de escribir el punto y coma (;). El ; es el carácter delimitador por defecto.
Para poder escribir el procedimiento completo, formado por más de una sentencia SQL evitando que finalice su ejecución al encontrar el símbolo (;) tenemos que asignar la función de delimitador a otro carácter diferente, como por ejemplo la barra (|), o bien los símbolos ($$) o (//), que son los habitualmente utilizados y que veremos en los siguientes ejemplos.
Al finalizar el procedimiento le asignaremos al punto y coma su función habitual utilizando de nuevo DELIMITER.
Ejemplo 4. Procedimiento con un parámetro de entrada, uno de salida y con varias sentencias en el cuerpo del procedimiento.
En la base de datos campeonato6 creamos un procedimiento que obtenga un listado con todos los concursantes que participan en juegos a partir de una determinada fecha. Además, debe dejar en un parámetro de salida el total de participantes a partir de esa esa fecha, incluida es fecha.
Observa el uso de DELIMITER.
DELIMITER //
CREATE PROCEDURE total_participantes_fecha(IN pfecha DATE, OUT ptotal INT)
COMMENT 'listado y total de participantes a partir de una fecha'
BEGIN
SELECT *
FROM participa
WHERE fecha_inicio >= pfecha;
SELECT count(*) INTO total
FROM participa
WHERE fecha_inicio >= pfecha;
END//
DELIMITER ;
Este procedimiento necesita dos parámetros para resolver lo que se pide:
Un parámetro de entrada (IN) denominado pfecha en el que recibirá una fecha y un parámetro de salida (OUT) denominado ptotal en el que el procedimiento deja el valor calculado.
El código del procedimiento incluye dos sentencias SELECT para obtener lo que se pide:
La primera select obtiene el listado de los participantes a partir de la fecha que pasamos en pfecha.
La segunda select obtiene el total de participantes a partir de la fecha que pasamos en pfecha.
Por defecto, al crear un procedimiento queda almacenado en el servidor y se asocia a la base de datos que está en uso o activada. Si se quiere asociar a una base de datos específica se escribe el nombre de la base de datos antes del procedimiento:
NombreBaseDatos.NombreProcedimiento
Cuando se crea un procedimiento, el servidor MySQL nos devolverá indicaciones sobre los errores que pueda tener el procedimiento. Cuando la sintaxis es correcta, el servidor almacena el procedimiento.
Crea un procedimiento que obtenga un listado de todos los concursantes y otro listado de todos los juegos de campeonato6. (La base de datos deberá estar en uso).
Crea un procedimiento en campeonato6 que liste todos los juegos y en un parámetro de salida deje el total de juegos que hay.
En la base de datos campeonato6 crea un procedimiento que obtenga un listado de los juegos cuyo nombre contiene la palabra que le pasemos al procedimiento. Y en un parámetro de salida debe dejar el total de juegos encontrados.
¿Cómo y cuando se ejecuta el código asociado a un procedimiento?
Para que se ejecute el código asociado a un procedimiento es necesario hacer una llamada al procedimiento o invocarlo de forma explícita.
Los procedimientos pueden ser llamados por cualquier usuario con autorización para ello y por el usuario que los creó. Al ejecutar un procedimiento, el servidor ejecuta automáticamente una sentencia USE BaseDatos.
Para ejecutar un procedimiento, una vez almacenado, usaremos la sentencia CALL con el siguiente formato:
Observa que es obligatorio en la invocación poner los paréntesis, aunque no haya que pasar parámetros al procedimiento.
Si el procedimiento requiere parámetros, se deben indicar en la llamada en el mismo orden que en la definición del mismo.
Cuando el procedimiento utiliza parámetros de entrada (IN), en la llamada al procedimiento se debe poner en ese lugar un valor concreto que es el que recibirá o se copiará en el parámetro de entrada.
Cuando un procedimiento utiliza un parámetro de salida (OUT) o un parámetro de entrada/salida (INOUT), para llamar al procedimiento, es necesario pasar una variable que cargue el dato devuelto por el procedimiento. Para este fin utilizaremos las variables de usuario o sesión vistas anteriormente y cuyo nombre comienza por @.
Uso de variables de sesión y llamadas a procedimientos.
Para definir o crear una variable de sesión, que será una variable temporal de sistema, la variable debe tener el nombre precedido del carácter @ y se usa la sentencia: SET @NombreVariable=Valor;
Se puede usar una variable de sesión indicándola directamente como parámetro en la llamada a un procedimiento, tanto si ya se había creado anteriormente la variable, por ejemplo @Num, como si se crea en la propia llamada al procedimiento: CALL NombreProcedimiento(@Num);
Una vez ejecutado el procedimiento, éste habrá dejado el valor calculado en la variable @Num, de manera que se puede consultar su valor con la sentencia: SELECT @Num; o bien utilizar su valor en otras sentencias SQL.
Veamos cómo invocar a los procedimientos creados en el apartado anterior.
En el Ejemplo 1 se creó un procedimiento sin parámetros. El procedimiento denominado listado_concursantes().
Para invocarlo y que se ejecute su código, sería:
CALL listado_concursantes();
El resultado de ejecutarlo sería un listado con todos los concursantes, tal y como puedes observar en la siguiente imagen. Tras hacer la invocación al procedimiento, se muestra el listado de todos los concursantes, indicándonos el sistema que se han listado 19 filas.
En el Ejemplo 2 se creó un procedimiento con un parámetro de entrada, listado_un_concursante(IN pconcur CHAR(3)).
Para invocarlo y que se ejecute su código, sería:
CALL listado_un_concursante ('A10');
En la llamada, al ser un parámetro de entrada podemos poner directamente el valor que va recibir, en este caso 'A10'.
El procedimiento copia el valor 'A10' en el parámetro pconcur, por lo que si existe ese concursante mostrará una fila con todos sus datos.
En el Ejemplo 3 se creó un procedimiento con un parámetro de salida, total_concursantes (OUT ptotal INT).
Para invocarlo y que se ejecute su código, sería:
CALL total_concursantes(@total);
SELECT @total;
En la llamada con CALL debemos poner una variable de sesión, con el nombre que queramos empezando por @, En esa variable dejará el procedimiento el valor calculado. En nuestro caso @total.
Hay que consultar con una select el valor de la variable @total, para ver lo que ha dejado el procedimiento tras su ejecución.
En el Ejemplo 4 se creó un procedimiento con dos parámetros, el primero de entrada y el segundo de salida: : total_participantes_fecha(IN pfecha DATE, OUT ptotal INT).
Para invocarlo y que se ejecute su código, en la llamada debemos poner en la posición que corresponda, el valor que recibirá el parámetro de entrada y la variable en la que dejará el procedimiento el valor calculado. La llamada sería:
-- Se invoca al procedimiento con:
-- primer parámetro de entrada y el segundo es de salida (una variable de sesión)
CALL total_participantes_fecha('2019-05-01', @num);
-- Hay que consultar el valor que ha dejado el procedimiento en @num
SELECT @num 'total participantes a partir de esa fecha';
En la llamada con CALL debemos poner en primera posición el valor que recibe el parámetro de entrada, en este caso la fecha '2019-05-01' y en segundo lugar una variable de sesión, en este caso @num, en la que dejará el valor calculado.
Hay que consultar con una select el valor de la variable @num, para ver lo que ha dejado el procedimiento tras su ejecución.
Ejercicio resuelto. Juego, sus concursantes y puntos
En la base de datos campeonato6 crea un procedimiento que reciba como parámetro de entrada el código de un juego y nos muestre todos los datos del juego, que deje en un parámetro de salida el total de concursantes que participan en ese juego, y en otro parámetro de salida el total de puntos acumulados entre esos concursantes.
a)Haz la llamada al procedimiento y muestra el valor de los dos parámetros de salida.
b)Obtén un listado de los juegos que igualen o superen el número de participantes anterior.
Utilizando la base de datos campeonato6 realiza las siguientes tareas:
Crea un procedimiento que recibe el código de un concursante y muestra un listado con los juegos en los que participa. En un parámetro de salida deja ese total.
Crea un procedimiento que muestra un listado de todos los juegos con dificultad la que se pasa como parámetro y deja en un parámetro de salida la media de los megusta de esos juegos.
Genera un listado con los juegos que tienen un megusta superior a la media obtenida en apartado anterior.
Cuando creamos una rutina, (procedimiento, función o trigger), puede ser necesario que se tenga que almacenar temporalmente algún valor en variables. Para eso se usarán las variables locales.
Variables locales a un procedimiento o función almacenada son:
Los parámetros que declaramos en el encabezado al definir el procedimiento o la función.
Las variables que declaramos dentro del cuerpo de la rutina con la sentencia DECLARE .
Estas variables solo son visibles dentro de la correspondiente rutina, por lo que no existen fuera de ella.
Veamos como se declaran y se usan.
Declaración de variables locales con DECLARE:
Dentro de cada procedimiento se pueden definir variables locales, es decir, que sólo existen mientras se ejecuta el procedimiento y después se destruyen. Las variables locales únicamente son visibles dentro del bloque BEGIN … END donde estén declaradas, y deben estar al comienzo de este bloque, antes de cualquier sentencia.
Para declarar variables locales se utiliza la sintaxis:
Es una variable que sólo puede ser manipulada en la función o procedimiento donde se ha declarado.
Una variable local es aquella cuyo ámbito se restringe a la rutina o sección en la que se ha declarado. Esto implica que esa variable sólo va a ser visible dentro de esa rutina o sección, y no se podrá hacer referencia a ella fuera de dicha rutina o sección.
Es una variable que sólo puede ser manipulada en la función o procedimiento donde se ha declarado. Fuera de la rutina en la que se ha declarado esa variable no existe.
Para proporcionar un valor inicial a la variable declarada se utiliza DEFAULT.
El valor inicial puede ser una constante o una expresión.
En caso de no emplear DEFAULT el valor por defecto es NULL.
En cuanto al tipo, puede utilizarse cualquier tipo válido en MySQL.
Para cada variable que se declara es necesario utilizar una sentencia <b>DECLARE</b> distinta.
Sentencia SET
Para asignar un valor a cualquier variable (local, global o pasada como parámetro) se utiliza la sentencia <b>SET</b>. Las variables que se asignan con SET pueden declararse dentro de una rutina o como variables globales de servidor.
Para modificar el valor de una variable o de un parámetro utilizando una asignación debe utilizarse la sentencia:
En MySQL son variables de sistema accesibles para todos los usuarios.
En <b>Expresión</b> puede haber una constante, una función, una variable, una operación entre ellas o incluso una sentencia SELECT que devuelva un solo resultado.
Ejemplo. Observa en el siguiente segmento de código cómo se declaran variables, se les asignan valores en el momento de declararlas y después de su declaración.
-- Declaramos variables locales dentro del cuerpo de la rutina
BEGIN
-- declaro la variable vtotal de tipo INT y la inicializo con el valor 0
DECLARE vtotal INT DEFAULT 0;
-- declaro otras dos variables locales.
DECLARE vmedia FLOAT;
DECLARE vsuma INT;
-- inicializo con SET a 0 la variable vsuma.
SET vsuma=0;
-- asigno valor con SET a la variable vmedia
SET vsuma=SELECT AVG(cuota_inscri) FROM concursante;
.........
END
Aunque el uso de procedimientos almacenados ha supuesto para Noiba y Naroba la posibilidad de ejecutar con una sola llamada una serie de sentencias, con las ventajas que hemos visto, los procedimientos no permiten algo que ellas consideran muy útil: poder ser llamados dentro de una sentencia SQL. Para poder llamar a un conjunto de instrucciones dentro de una sentencia SQL necesitará utilizar funciones, que además devuelven siempre un valor.
En unidades anteriores Noiba ha incorporado funciones propias de SQL para obtener los resultados requeridos en las consultas que ha realizado en TalleresFaber. ¿Podrá crear ella sus propias funciones?
Otra de las rutinas que podemos crear y almacenar en el servidor de base de datos son las funciones, que serán similares a las funciones SQL que proporciona el SGBD.
Un función es un conjunto de instrucciones SQL que después de ejecutarse devuelven un valor.
La forma de crear una función es similar a la de creación de un procedimiento. Las funciones, una vez creadas, quedan almacenadas en el servidor y pueden ser invocadas en cualquier momento por cualquier cliente MySQL.
Aunque las funciones comparten muchas características con respecto a los procedimientos, presentan también algunas diferencias:
Las funciones devuelven siempre un dato a través de una sentencia <b>RETURN</b>. El dato se corresponde con un tipo de dato declarado para la función.
Las funciones no pueden trabajar con parámetros <b>OUT</b> o <b>INOUT</b>, sino únicamente con parámetros de entrada IN, por eso no se especifica el tipo de parámetro al definir la función.
En las funciones no se pueden usar sentencias que devuelvan filas de resultados (<b>SELECT</b>, <b>SHOW</b>, <b>DESC</b>). Se pueden usar sentencias <b>SELECT</b> que devuelvan una fila siempre que los datos devueltos se carguen en variables, para sentencias que devuelvan varias filas se utilizan los cursores.
Las funciones son llamadas a ejecución, al igual que las funciones internas de MySQL, escribiendo su nombre y la lista de parámetros pasados a la función encerrados entre paréntesis. Por tanto no usa una sentencia de llamada o invocación como la sentencia <b>CALL</b> en el caso de los procedimientos.
Las funciones podrán ser llamadas desde cualquier sentencia SQL como <b>SELECT</b>, <b>UPDATE</b>, <b>INSERT</b>, <b>DELETE</b>. Los procedimientos nunca pueden ser llamados a ejecución dentro de otra sentencia.
Las funciones se llaman simplemente poniendo su nombre y parámetros, si los necesita: <b>NombreFunción(parámetros)</b>.
Las funciones definidas por el usuario están disponibles en versiones anteriores de MySQL. Actualmente se soportan junto con los procedimientos almacenados. En el futuro será posible un marco para procedimientos almacenados externos en lenguajes distintos a SQL, por ejemplo PHP.
Los permisos asociados a las distintas operaciones que se pueden realizar con funciones almacenadas son: CREATE ROUTINE (creación), EXECUTE (ejecución), ALTER ROUTINE (borrado).
Hemos dicho que las funciones se almacenan en el SGBD, pero ¿dónde se almacenan?
En MySQL las funciones se almacenan en:
La tabla func de la base de datos del sistema mysql. (hasta la versión MySQL 5.7). Esta tabla se crea durante la instalación de MySQL.
La tabla routines de la base de datos de los metadatos information_schema (versiones MySQL 8.x ). Esta tabla se crea durante la instalación de MySQL.
Workbench(Elaboración propia)
A continuación puedes ver un ejmplo muy sencillo de creación de una función y su uso dentro de sentencias SQL
La forma de crear una función es similar a la de creación de un procedimiento. Las funciones, una vez creadas, quedan almacenadas en el servidor y pueden ser invocadas en cualquier momento por cualquier usuario con permisos para ello.
Vamos a describir detalladamente la sintaxis para crear funciones:
Aunque las funciones comparten muchas características con respecto a los procedimientos, presentan también algunas diferencias:
Como hemos dicho en el punto anterior con relación a los parámetros, no es necesario especificar el tipo porque no trabajan con parámetros <b>OUT</b> o <b>INOUT</b>.
La cláusula <b>RETURNS</b> es obligatoria e indica el tipo de retorno que nos va a devolver la función.
En el cuerpo de la función debe incluirse un comando RETURNvalor o expresión, debiendo ser expresión del mismo tipo que la función. Generalmente la sentencia <b>RETURN</b> para devolver un resultado es la última del cuerpo de la función.
En las funciones, en las sentencias de su cuerpo, no se pueden usar sentencias que devuelvan filas de resultados (<strong>SELECT</strong>, <strong>SHOW</strong>, <strong>DESC</strong>). Se pueden usar sentencias <strong>SELECT</strong> que devuelvan una fila siempre que los datos devueltos se carguen en variables; para sentencias que devuelvan varias filas trataremos los cursores.
<b>Tipo</b>: El Tipo de datos devuelto puede ser cualquier tipo de datos válido de MySQL.
Las funciones son llamadas a ejecución, al igual que las funciones internas de MySQL, escribiendo su nombre y la lista de parámetros pasados a la función encerrados entre paréntesis. Por tanto no usa una sentencia de llamada como en el caso de los procedimientos.
Las funciones podrán ser llamadas desde cualquier sentencia SQL como <strong>SELECT</strong>, <strong>UPDATE</strong>, <strong>INSERT</strong>, <strong>DELETE</strong>. Los procedimientos nunca pueden ser llamados a ejecución dentro de otra sentencia.
Las funciones se invocan usando: <strong>NombreFunción(parámetros)</strong>.
La descripción de las características que puede incorporar una función es similar a la que hemos visto para los procedimientos.
Como por ejemplo:
Function DETERMINISTIC es la que no realiza cambios en los datos, o para los mismos parametros de entrada produce los mismos resultados, en otro caso es NOT DETERMINISTIC. Por defecto MySQL supone que son NO DETERMINISTIC. En nuestro caso, la mayoría de las funciones que diseñemos serán DETERMINISTIC.
CuerpoDeLaRutina: Contiene el código ejecutable de la función entre las sentencias <b>BEGIN</b> y <b>END</b>.
Ejemplo.
En la base de datos campeonato6 crea una función que se encargue de devolver la cuota media de los concursantes inscritos en un determinado año.
USE campeonato6;
DELIMITER //
CREATE FUNCTION cuota_media_concursante( anio YEAR)
RETURNS FLOAT
DETERMINISTIC
BEGIN
DECLARE media FLOAT DEFAULT 0;
SELECT AVG(cuota_inscri) INTO media
FROM concursante
WHERE YEAR(fecha_inscri)=anio;
RETURN media;
END//
DELIMITER ;
Para probar la función y ver el valor devuelto la incluimos en una SELECT:
SELECT cuota_media_concursante(2020) 'es la cuota media';
Todo SGBD permite que los usuarios creen sus propias funciones y procedimientos. Además, cualquier SGBD incluye un conjunto de funciones en la distribución. Sean funciones de la distribución o funciones desarrolladas por el usuario, pueden usarse en sentencias SQL para que estas sentencias traten los resultados que devuelven las funciones.
MySQL incluye un numeroso conjunto de funciones. En esta unidad se expone la sintaxis de utilización de cada función y se agrupan las funciones por el tipo de datos que manipulan o por el tipo de proceso que llevan a cabo.
Normalmente, las funciones operan con las columnas de una tabla, se utilizan en las expresiones que indican los datos que se muestran en una consulta, en las condiciones <b>WHERE</b>, en las expresiones <b>SET</b> para obtener los datos con los que se modifica una columna, en los valores que se insertan mediante <b>INSERT</b>, etc.
Las funciones se clasifican en:
Funciones matemáticas.
Funciones de cadenas de caracteres.
Funciones de fecha y hora.
Funciones de control de flujo.
Funciones de búsqueda sobre índices <b>FULLTEXT</b>.
Puedes consultar un resumen de las funciones disponibles en MySQL en en el siguiente enlace. Obviamente no se trata de aprenderlas de memoria, sino de saber que existen para utilizarlas cuando se necesiten.
Noiba y Vindio están revisando los procedimientos y funciones que han creado hasta ahora para la base de datos TalleresFaber, han visto que hay algún procedimiento que deben modificar para incluir nuevas funcionalidades y hay otros que deben eliminar. ¿Cómo hacerlo?
Precisamente eso es lo que vamos a tratar en este apartado.
Para modificar el código de un procedimiento o función almacenada tendrás que volver a escribir su códígo y crear de nuevo esa rutina, pues lo único que se puede cambiar o modificar son sus características.
Para cambiar las características de un procedimiento o de una función se utiliza la sentencia <b>ALTER</b>, cuya sintaxis es:
Utilizamos la cláusula <b>IF EXISTS</b> para evitar que si el procedimiento o la función ya existen, la sentencia devuelva un error.
Ejemplo.
Para eliminar la función cuota_media_concursante() creada anteriormente en la base de datos campeonato6, sería:
USE campeonato6;
DROP FUNCTION IF EXISTS cuota_media_concursante;
Observa que no hay que poner los paréntesis, solo el nombre de la rutina.
Otras sentencias útiles:
Esta sentencia muestra algunas características del procedimiento o de la función como el nombre de la base de datos, el tipo de rutina: procedimiento o función, el creador, fecha de creación y modificación, etc.
A medida que van avanzando en la utilización de los procedimientos y de las funciones, a Naroba y Noiba se les van abriendo nuevas perspectivas y están muy contentas de sus prácticas de FCT en BK Sistemas Informáticos. Muchas de las sentencias que antes tenían que repetir periódicamente, las tienen ahora almacenadas como procedimientos que pueden ejecutar como una única sentencia; algunas operaciones complejas que antes tenían que ejecutar en una consulta ahora están recogidas como una función propia. Como suele ocurrir, una vez que han empezado a dominar estos nuevos recursos se les van ocurriendo otras aplicaciones para su base de datos. ¿Y si pudieran elegir la sentencia que se ejecuta en función de una condición o del valor de un dato? ¿No se podría repetir un proceso un número determinado de veces hasta que se alcance una condición? Naroba y Noiba están echando en falta dos tipos de estructuras que no hemos visto hasta ahora y que van a abrir nuevas posibilidades a sus procedimientos y funciones: las estructuras de control.
En el SQL de MySQL disponemos de varias estructuras o sentencias de control de flujo. Estas sentencias sirven para codificar estructuras de decisión y repetitivas en una función o en un procedimiento.
Las sentencias de control de flujo disponibles en MySQL son:
<b>IF</b>
<b>CASE</b><b></b>
<b>WHILE</b>
<b>REPEAT</b>
<b>LOOP</b><b></b>
<b>ITERATE</b>
<b>LEAVE</b>
En otros SGBD se utiliza también la sentencia <b>FOR</b> que en caso de MySQL no se admite.
Estas estructuras pueden contener una sola sentencia o un bloque de ellas usando los comandos <b>BEGIN … END</b>. Unas estructuras pueden estar anidadas dentro de otras.
A veces, puede interesar realizar acciones diferentes en base a que se cumpla o no cierta condición. Esto lo permiten las sentencias alternativas, condicionales o de decisión.
Estas alternativas pueden ser simples, múltiples y anidadas.
La sentencia <b>IF</b> es un tipo de alternativa simple, que permite elegir qué sentencias se ejecutarán y cuáles no, dependiendo de que una condición sea verdadera o falsa.
Si la Condición1 del <strong>IF</strong> se cumple (es verdadera), se ejecutan las sentencias correspondientes (Sentencias1) y la ejecución del programa coninúa por el END IF. Si no es verdadera, se evalúa la Condición2 y si es verdadera se ejecutan las sentencias asociadas (Sentencias2), saltando después al END IF, y así sucesivamente con todos los <strong>ELSEIF</strong>.
Si ninguna de las condiciones es verdadera, en caso de que haya <strong>ELSE</strong>, se ejecutan las sentencias asociadas a <strong>ELSE</strong> (Sentencias3), si no hay <strong>ELSE</strong> no se ejecuta ninguna sentencia.
Ejemplo 1.
Ejemplo de IF- THEN para contar aprobados, a partir de la variable nota cuyo valor ya está validado entre 0 y 10.
-- Contar aprobados
-- si el valor de la variable nota es mayor o igual a 5 suma 1 a la variable aprobados.
IF nota >= 5 THEN
SET aprobados=aprobados+1;
END IF;
Otra sentencia;
Ejemplo 2.
Ejemplo de IF-THEN-ELSE para contar aprobados y suspensos. A partir de la variable nota cuyo valor ya está validado entre 0 y 10.
-- Contar aprobados y suspensos
-- si el valor de la variable nota es mayor o igual a 5 suma 1 a la variable aprobados,
-- en caso contrario suma 1 a la variable suspensos.
IF nota >= 5 THEN
SET aprobados=aprobados+1;
ELSE
SET suspensos=suspensos+1;
END IF;
Otra sentencia;
Ejemplo 3.
Ejemplo de IF-THEN-ELSEIF-ELSE para contar matrículas de honor, aprobados y suspensos. A partir de la variable nota cuyo valor ya está validado entre 0 y 10.
-- Contar notas igual a 10, notas menores de 10 y mayor o igual que 5, y notas -- menores de 5.
IF nota =10 THEN
SET mhonor=mhonor+1;
ELSEIF nota >= 5 THEN
SET aprobados=aprobados+1;
ELSE
SET suspensos=suspensos+1;
END IF;
Otra sentencia;
Ejemplo 4.
Procedimiento que recibe 2 números enteros y muestra un mensaje indicando el de mayor valor o si son iguales.
CREATE DATABASE rutinas; -- base de datos para los ejemplos ilustrativos
USE rutinas;
DELIMITER //
CREATE PROCEDURE numero_mayor(IN num1 INTEGER, in num2 INTEGER)
BEGIN
IF num1 >num2 THEN
SELECT 'El mayor es: ', num1;
ELSEIF num2>num1 THEN
SELECT 'El mayor es: ', num2;
ELSE
SELECT 'Los dos números son iguales ';
END IF;
END//
DELIMITER ;
-- comprobación
CALL numero_mayor(4,5);
CALL numero_mayor(5,5);
Realizar una función que reciba una fecha y devuelva el nombre del día de la semana que le corresponde (Lunes, Martes, etc).
Nivel concursante.
En la base de datos campeonato6 crea una función que devuelva el nivel de un concursante. El nivel se obtiene según el total de puntos conseguidos participando en juegos. Para ello a la función le debes pasar el código del concursante. Si el concursante no existe o bien no participa en juegas, la función devuelve un 0. Los niveles son los siguientes:
'Bronce' si el total de puntos es menor o igual de 50.
'Plata' si el total de puntos es mayor de 50 y menor o igual de 100.
'Oro' si el total de puntos es mayor de 100 y menor o igual de 150.
'Platino' si el total de puntos es mayor de 150.
Comprueba su funcionamiento para concursantes en diferentes niveles.
Se evalúa la expresión y se ejecutan las sentencias correspondientes al primer valor igual al valor de la expresión. Ejecutadas esas sentencias salta al END CASE.
Si ningún valor es igual, se ejecutan las sentencias que hay dentro de <b>ELSE</b>, caso de que hubiera <b>ELSE</b>.
Ejemplo.
Procedimiento que recibe un número entero y comprueba si el número es el 1, 2, 3, 4 o 5, y muestra un mensaje literal indicando el número recibido. Si el número no es ninguno de esos valores, se muestra un mensaje indicando esa situación.
DELIMITER //
CREATE PROCEDURE numero_pulsado(IN num INTEGER)
BEGIN
CASE num
WHEN 1 THEN SELECT 'Has pulsado 1';
WHEN 2 THEN SELECT 'Has pulsado 2';
WHEN 3 THEN SELECT 'Has pulsado 3';
WHEN 4 THEN SELECT 'Has pulsado 4';
WHEN 5 THEN SELECT 'Has pulsado 5';
ELSE
SELECT 'Número no valido, debe ser un entero del 1 al 5';
END CASE;
END//
DELIMITER ;
-- comprobación
CALL numero_pulsado(3);
CALL numero_pulsado(8);
Si una condición de las indicadas tras WHEN se evalúa como verdadera, se ejecutan las sentencias correspondientes. Ejecutadas esas sentencias salta al END CASE.
Si no se cumple ninguna condición, se ejecutan las sentencias de la cláusula <b>ELSE</b> en caso de que la haya.
Ejemplo.
Realizamos el Ejemplo anterior (comprobar si el número es el 1, 2, 3, 4 o 5,) pero utilizando ahora este segundo formato de CASE.
DELIMITER //
CREATE PROCEDURE numero_pulsado2(IN num INTEGER)
BEGIN
CASE
WHEN num=1 THEN SELECT 'Has pulsado 1';
WHEN num=2 THEN SELECT 'Has pulsado 2';
WHEN num=3 THEN SELECT 'Has pulsado 3';
WHEN num=4 THEN SELECT 'Has pulsado 4';
WHEN num=5 THEN SELECT 'Has pulsado 5';
ELSE
SELECT 'Número no valido, debe ser un entero del 1 al 5';
END CASE;
END//
DELIMITER ;
-- comprobación
CALL numero_pulsado2(3);
CALL numero_pulsado2(8);
Realiza una función que devuelve la calificación en texto correspondiente a una calificación numérica que puede tener decimales. Debe tratarse el caso de una nota que no esté en el rango entre 0 y 10. Debes resolverla usando CASE.
Nivel concursante.
En la base de datos campeonato6 creamos la versión CASE de la función que devuelve el nivel de un concursante según el total de puntos conseguidos participando en juegos. Para ello a la función le debes pasar el código del concursante. Si el concursante no existe o bien no participa en juegos, la función devuelve un 0.Los niveles son los siguientes:
'Bronce' si el total de puntos es menor o igual de 50.
'Plata' si el total de puntos es mayor de 50 y menor o igual de 100.
'Oro' si el total de puntos es mayor de 100 y menor o igual de 150.
'Platino' si el total de puntos es mayor de 150.
Comprueba su funcionamiento para concursantes en diferentes niveles.
No hay que confundir las estructuras <b>IF</b> o <b>CASE</b> que acabamos de ver para procedimientos almacenados, con las funciones <b>IF()</b> o <b>CASE()</b> del SGBD, ya que IF() y CASE() son funciones de control de flujo y en el caso de <b>CASE()</b> su sintaxis es también diferente.
La sentencia <strong>REPEAT</strong> permite representar una estructura repetitiva del tipo repetir … hasta, muy común en la mayoría de los lenguajes de programación.
En esta estructura se empieza ejecutando las sentencias que están dentro de <strong>REPEAT</strong>. Al final se evalúa si se cumple o no una condición. Si la condición se cumple, se sale del bucle en caso contrario se vuelve al comienzo y se repite una nueva iteración. Las sentencias continúan repitiéndose hasta que la condición es cierta.
La condición de parada del bucle se especifica detrás de la cláusula UNTIL.
El formato o sintaxis de esta sentencia es el siguiente:
Conjunto de sentencias que se ejecutan de forma repetitiva hasta o mientras que se cumple una condición. Si nunca se cumple esa condición se tendrá un bucle infinito
Para ilustrar el funcionamiento de REPEAT vamos a ver un ejemplo sencillo.
En la base de datos rutinas creamos un procedimiento que recibe un número entero y muestra los números anteriores hasta llegar al uno. (El número debe ser mayor que cero).
USE rutinas;
DELIMITER //
CREATE PROCEDURE pa_numeros_menores_repeat(IN num INT)
BEGIN
IF num <= 0 THEN
SELECT 'El valor introducido debe ser positivo';
ELSE
REPEAT -- REPITE las siguientes sentencias
SELECT num, ' ';
SET num = num-1;
UNTIL num<1 -- hasta que la condición es cierta
END REPEAT;
END IF;
END //
DELIMITER ;
-- llamada
CALL pa_numeros_menores_repeat(0);
CALL pa_numeros_menores_repeat(10);
Realiza una función que recibe una cadena de texto y una letra. La función devuelve una cadena en la que ha sustituido con caracteres subrayados todos los de la cadena inicial excepto los que son iguales a la letra pasada.
Realiza un procedimiento en campeonato6 que permita rebajar las cuotas de los concursantes de la siguiente forma.
Mientras que la cuota media de los concursantes sea superior a 50€, debes disminuir la cuota de cada concursante en un 5%. Debes contar las veces que se repite este proceso y dejar ese valor en un parámetro de salida del procedimiento. Utiliza un bucle REPEAT para resolverlo.
La sentencia <b>WHILE </b>permite representar una estructura repetitiva del tipo conocido como <strong>Mientras</strong>…
En este bucle la condición se evalúa al iniciar el bucle, si la condición se cumple, se ejecutan las sentencias que están dentro del bucle, y cuando se llega a la última sentencia se vuelve a evaluar la condición de <strong>WHILE</strong>, repitiéndose este proceso de nuevo si la condición se cumple. La salida del bucle se produce cuando la condición es falsa.<b></b>
Para ilustrar el funcionamiento de WHILE vamos a ver un ejemplo sencillo.
En la base de datos rutinas creamos un procedimiento que recibe un número entero y muestra los números anteriores hasta llegar al uno. (El número debe ser mayor que cero).
USE rutinas;
DELIMITER //
CREATE PROCEDURE pa_numeros_menores_while(IN num INT)
BEGIN
IF num <= 0 THEN
SELECT 'El valor introducido debe ser positivo';
ELSE -- n > 0
WHILE num >= 1 DO -- Mientras la condición es cierta HAZ las siguientes sentencias
SELECT num, ' ';
SET num = num-1;
END WHILE;
END IF;
END //
DELIMITER ;
-- llamada
CALL pa_numeros_menores_while(0);
CALL pa_numeros_menores_while(10);
Realiza un procedimiento, en la base de datos campeonato6, que reciba una fecha y que cargue en una tabla, denominada fechas_sinjuegos, las fechas en las que no se ha iniciado ningún juego desde la fecha pasada al procedimiento hasta la fecha actual (incluida la fecha actual).
La tabla fechas_sinjuegos consta de una sola columna de tipo DATE para almacenar las fechas correspondientes
Rebajar cuotas.
Realiza un procedimiento en campeonato6 que permita rebajar las cuotas de los concursantes de la siguiente forma.
Mientras que la cuota media de los concursantes sea superior a 50€, debes disminuir la cuota de cada concursante en un 5%. Debes contar las veces que se repite este proceso y dejar ese valor en un parámetro de salida del procedimiento. Utiliza un bucle WHILE para resolverlo.
La sentencia LOOP se utiliza para implementar un bucle sin condición de parada, por lo que será un bucle infinito.<strong> <br /></strong>
Al inicio y al final de un bucle de este tipo suele aparecer una <strong>etiqueta</strong> (label), aunque no es obligatorio. Si tras END LOOP se coloca una etiqueta, entonces la misma etiqueta debe aparecer al principio del bucle.
En combinación con la sentencia LOOP se puede utilizar la sentencia LEAVE para abandonar el bucle (exit loop), mientras que ITERATE se utiliza para volver a comenzar una iteración (start the loop again).
<b></b>
El formato o sintaxis de LOOP es el siguiente:
Conjunto de sentencias que se ejecutan de forma repetitiva hasta o mientras que se cumple una condición. Si nunca se cumple esa condición se tendrá un bucle infinito.
Como ves, es muy sencillo. Este comando se utiliza para abandonar cualquier control de flujo etiquetado. Puede usarse con <b>BEGIN … END</b> o con bucles.
La sintaxis o formato de la sentencia <b>ITERATE</b> es también muy sencillo:
La sentencia ITERATE sólo puede usarse dentro de <b>LOOP</b>, <b>REPEAT</b> y <b>WHILE</b>. Esta sentencia provoca un salto para reiniciarse de nuevo el bucle desde la primera sentencia. Para ello, es necesario que el bucle correspondiente esté marcado con una etiqueta.
Ejemplo.
Como ejemplo ilustrativo del funcionamiento de este bucle vamos a realizar un procedimiento en la base de datos rutinas que recibe un número entero y muestra los números anteriores hasta llegar al 1. (Controla que el número debe ser mayor que cero).
USE rutinas;
DELIMITER //
CREATE PROCEDURE pa_numeros_menores_loop(IN num INT)
BEGIN
IF num <= 0 THEN
SELECT 'El valor introducido debe ser positivo';
ELSE
b1: LOOP -- REPITE las siguientes sentencias
SELECT num, ' ';
SET num = num-1;
IF num < 1 THEN -- si la condición es cierta
LEAVE b1; -- salir del bucle etiquetado b1
ELSE
ITERATE b1; -- comenzar el bucle
END IF;
END LOOP b1; -- punto final del bucle
END IF;
END //
DELIMITER ;
-- llamada y comprobación
CALL pa_numeros_menores_loop(0);
CALL pa_numeros_menores_loop(10);
Ejercicio resuelto. Ilustra el funcionamiento de LOOP
Ejemplo de utilización de las sentencias LEAVE e ITERATE dentro de un bucle LOOP.
Este ejemplo realiza la siguiente tarea: Recibe un valor en un parámetro de entrada en p1, y deja dos valores calculados en los parámetros de salida p2 y p3. Obtiene el total de iteraciones que hay que realizar hasta conseguir que el valor introducido, increméntandolo de 1 en 1 llegue hasta el valor 10.
Realiza un procedimiento en campeonato6 que permita rebajar las cuotas de los concursantes de la siguiente forma. Si la cuota media de los concursantes es superior a 50€, debes disminuir la cuota de cada concursante en un 5%. Este proceso se debe repetir hasta que la cuota media sea menor o igual de 50€. Utiliza un bucle LOOP para resolverlo.
Noiba y sus compañeros han observado que, al ejecutar procedimientos o funciones éstos se interrumpen porque ha ocurrido algún error, del cual MySQL sólo devuelve un código. En muchos casos a Noiba le interesa que el procedimiento continúe su ejecución o que se lleve a cabo una acción determinada, por eso se dispone ahora a repasar cómo manipular los errores en MySQL, y las posibilidades que ofrece para cambiar la respuesta del servidor ante un error en una rutina almacenada.
Cuando se ejecuta una sentencia SQL, el servidor devuelve un código de error (numérico) relativo a esa sentencia. Por ejemplo, se devuelve un código de error, cuando se ejecuta una sentencia que trata de insertar una fila con un valor que ya existe en la columna que es clave primaria.
Si la sentencia no ha generado ningún error, entonces devuelve 0 como código de error.
Si una sentencia forma parte de un procedimiento o función y genera un error, entonces se termina automáticamente la ejecución del procedimiento o función.
Esto, generalmente, es un grave problema ya que es normal que, aunque una sentencia de una rutina produzca error, se desee procesar las siguientes sentencias de la rutina. Para solucionar este problema, MySQL permite usar manipuladores de errores o handler que sirven para indicar como debe responder el servidor MySQL, en procedimientos y funciones, a situaciones de error.
Además del código de error, MySQL también devuelve un estado de error, que es algo más genérico que el propio código de error. Un estado de error engloba varios códigos de errores. El estado de error suele aparecer encerrado entre paréntesis.
Por ejemplo, si intentamos insertar una fila en una tabla y esa fila no se puede insertar porque el valor de la clave primaria ya existe, MySQL devuelve una indicación de error cuyo estado de error o <b>SQLSTATE </b>es '23000' y cuyo código de error MySQL es 1062 (los valores o códigos de estado devueltos para las sentencias ejecutadas son cadenas de caracteres mientras que los códigos de error son números enteros).
Ejemplo.
En la base de datos campeonato ya existe un equipo en la tabla equipo con el código '01',(la columna cdequipo es la clave primaria de la tabla equipo), luego si se intenta insertar otro equipo con ese mismo código, MySQL devuelve el error 1062 y el estado de error '23000':
MySQL Command Line(Elaboración propia)
Es posible, aunque no obligatorio, asignar un nombre a una condición de error usando la sintaxis:
Es un manejador de errores que sirev para indicar cómo debe responder el servidor MySQL en procediminetos y funciones, ante situaciones de error.
Es una cadena de carcateres que se corresponden o engloba a varios códigos de error.
Es un valor númerico específico de MYSQL para indicar el error concreto que se ha producido. Cara error diferente tendrá un código de error (un número) diferente.
Así por ejemplo, para asignar un nombre a una condición de error, lo podemos hacer de dos formas:
En función del código de error de 1062
DECLARE ClaveRepe CONDITION FOR 1062’;
En función del código de estado SQLSTATE '23000'<b></b>
DECLARE ClaveRepe CONDITION FOR SQLSTATE ‘23000’;
Declaración de un manipulador de errores.
Para declarar un manipulador de errores o de excepción hay que usar la sintaxis:
Un código de estado es una cadena que se corresponde con varias situaciones de error y se le denomina SQLSTATE.
Es un valor númerico específico de MYSQL para indicar el error concreto que se ha producido. Cara error diferente tendrá un código de error (un número) diferente.
Si se produce cualquiera de las condiciones de error declaradas en el manipulador, se ejecutará la sentencia especificada para el manipulador. Si es más de una sentencia las que se debe ejecutar, éstas deben ir entre BEGIN ... END.
TipoManipulador: puede ser EXIT o CONTINUE.
Un manipulador de tipo <b>CONTINUE</b> hace que prosiga la ejecución de la siguiente sentencia a aquella donde se hay producido un error controlado por el manipulador.
Un manipulador de tipo <b>EXIT</b> hace que se termine el bloque en el que se encuentra la sentencia que ha producido el error controlado por el manipulador y, por tanto, termine la rutina en la que se encuentra.
ValorCondicion:
<b>SQLWARNING</b> se usa para referenciar a todos los valores de estado que comienzan por 01.
<b>NOT FOUND</b> se usa para referenciar a todos valores de estado que comienzan por 02.
<b>SQLEXCEPTION</b> se usa para referenciar a todos los valores de estado que no son controlados por <b>SQLWARNING</b> y por <b>NOT FOUND</b>.
Veamos algunos ejemplos, simplemente ilustrativos, para entender mejor como funcionan los manipuladores de error.
Ejemplo.
En la base de datos campeonato6, supongamos que tenemos el siguiente procedimiento que se encarga de insertar un equipo concreto, listar los equipos existentes, el total que hay y finalmente listar los juegos existentes:
USE campeonato6;
DELIMITER |
CREATE PROCEDURE proceso_sinhandler()
BEGIN
BEGIN
INSERT INTO equipo (cdequipo, nombre, comunidad, anio_funda) VALUES
('08','Monster','Andalucía',year(curdate()));
SELECT * FROM equipo;
SELECT COUNT(*) FROM equipo;
END;
SELECT * FROM juego;
END |
DELIMITER ;
-- comprobación
CALL proceso_sinhandler();
Si invocamos a este procedimiento y no existe el equipo que inserta directamente, el equipo de código '08', no se producirá ningún error.
Pero suponiendo que hacemos la llamada al procedimiento y el código del equipo a insertar ya existe en la tabla, no se ejecutará ninguna de las sentencias que siguen a la sentencia <b>INSERT</b> que da error.
CALL proceso_sinhandler();
<b>ERROR 1062 (23000): Duplicate entry '08' for key 'equipo.PRIMARY'</b>
Sabiendo que un error de inserción por clave duplicada da el valor de estado '23000' y el código de error 1062, modificaríamos el procedimiento anterior para controlar y poder capturar ese error con un handler de forma similar a la siguiente (le cambiamos también el nombre al procedimiento):
DELIMITER |
CREATE PROCEDURE proceso_conhandler()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
SELECT 'Se ha producido un error';
BEGIN
INSERT INTO equipo (cdequipo, nombre, comunidad, anio_funda) VALUES
('08','Monster','Andalucía',year(curdate()));
SELECT * FROM equipo;
SELECT COUNT(*) FROM equipo;
END;
SELECT * FROM juego;
END |
DELIMITER ;
Ahora, al ejecutar el procedimiento, se ejecuta la sentencia asociada al manipulador o handler cuando se produce el error '23000', es decir, se escribe el mensaje se ha producido un error y se continua (tipo <b>CONTINUE</b>) con la sentencia que hay a continuación de la sentencia <b>INSERT</b> que produjo error. Si el manipulador fuese tipo <b>EXIT</b> se ejecutaría la sentencia asociada al manipulador, pero no las siguientes y se iría al final del procedimiento.
La declaración del manipulador podríamos haberla hecho usando el código de error y no el valor de estado de la sentencia. En su lugar podríamos haber puesto:
DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Se ha producido un error';
O incluso podíamos haber definido previamente una condición de error llamada clave_duplicada, para después declarar un manipulador o handler para esa condición de error:
DECLARE clave_duplicada CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR clave_duplicada SELECT 'Se ha producido un error';
Si las sentencias que asociamos al handler son más de una, deben escribirse entre un BEGIN y un END .
Por ejemplo, si además de mostrar ese mensaje de error, interesa que deje en una variable denominada perror el valor 1 cuando se produce el error de clave duplicada, la definición del handler sería la siguiente:
DECLARE CONTINUE HANDLER FOR clave_duplicada
BEGIN
SELECT 'Se ha producido un error';
SET perror=1;
END;
En la base de datos campeonato6 crea un procedimiento que permita insertar filas en la tabla equipo de la siguiente forma:
En parámetros de entrada recibe los valores a insertar, excepto el año, que se debe poner el año actual del sistema.
En un parámetro de salida deja el total de equipos existentes.
Debe controlar la duplicidad de clave primaria, de manera que al capturar ese error debe mostrar un mensaje indicando ese error, debe dejar en un parámetro de salida el valor 1, e insertar en un tabla errores_equipo una fila con el código, nombre y comunidad del equipo que ha dado error, la fecha y hora del sistema, y el usuario que realiza la inserción. Si no hay error en la inserción, el parámetro de salida debe valer 0. El procedimiento debe finalizar cuando se produce el error.
Ejercicio resuelto. Juego, sus concursantes y puntos
Partiendo del ejercicio resuelto del apartado 4.2, ' Juego, sus concursantes y puntos', añade un Handler que evite que las consultas se ejecuten cuando el código que se reciba no exista en campeonato6:
Vindio, Noiba y Naroba están consiguiendo sacar mucho partido a las funciones y procedimientos que tiene almacenados en la base de datos; no sólo para sus propias consultas sino que le permiten ocultar al resto de usuarios de TalleresFaber operaciones complejas y manejar las acciones que deben llevarse a cabo cuando se produce algún error.
Pero aún encuentran algunas limitaciones importantes, como por ejemplo que el resultado de las consultas tratadas en sus funciones y procedimientos tenga que devolver una única fila. ¿No podría utilizarse algún elemento que le permita tratar varias filas? Efectivamente, se trata de los cursores y a continuación aprenderás a usarlos en tus procedimientos y funciones.
Un cursor es una consulta declarada que provoca que el servidor, cuando se realiza la operación de abrir cursor, cargue en memoria los resultados de la consulta en una tabla interna. Teniendo abierto el cursor, es posible, mediante una sentencia <b>FETCH</b>, leer una a una las filas correspondientes al cursor y, por tanto, correspondientes a la consulta definida. Los cursores deben declararse después de las variables locales.
Para hacer uso de un cursor, tendremos que:
Declarar el cursor (después de las variables locales).
Abrir el cursor.
Asignar las filas al cursor según tarea a realizar.
En la sentencia <b>SELECT</b> de declaración del cursor puede haber cualquier cláusula utilizada dentro de una <b>SELECT</b>, excepto la cláusula <b>INTO</b>. En un procedimiento o en una función podemos definir tantos cursores como necesitemos.
Abrir un cursor.
Para abrir el cursor o, lo que es lo mismo, hacer que los resultados de la consulta asociada al cursor queden cargados en memoria, se usa la sentencia:
La primera vez que se lea sobre el cursor se leerá la primera fila de la consulta, la segunda vez se leerá sólo la segunda fila y así sucesivamente. Si quisiéramos volver a leer desde la primera, tendríamos que cerrar el cursor y abrirlo nuevamente.
Asignar las filas de un cursor. Para leer la fila actualmente disponible en el cursor, se debe usar la sintaxis:
Esta sentencia asigna los valores devueltos de la fila que se está leyendo sobre las variables indicadas tras <b>INTO</b>. Debe haber una variable por cada valor que devuelve el cursor (por cada valor seleccionado en la <b>SELECT</b>). Un cursor se comporta como un puntero que inicialmente apunta a los datos de la primera fila y, cuando se lee, el puntero se incrementa para apuntar a la siguiente fila y así sucesivamente hasta que el puntero llega al final tomando el valor nulo.
Cerrar un cursor.
Todo cursor abierto debe ser cerrado. No es necesario haber consultado todas las filas controladas por el cursor para cerrar el cursor, se puede cerrar en cualquier momento. Si no se cierra, se cerrará al final del comando en el que se ha declarado.
En el siguiente ejemplo se crea un procedimiento que, utilizando un cursor, obtiene en un parámetro de salida la suma de los megusta correspondientes a tres de los juegos que más megusta tengan.
USE campeonato6;
DROP PROCEDURE IF EXISTS suma_megusta;
DELIMITER $$
CREATE PROCEDURE suma_megusta (OUT psum INT)
BEGIN
DECLARE vmegusta INT;
DECLARE cursor_megusta CURSOR FOR
SELECT megusta
FROM juego
ORDER BY megusta DESC;
OPEN cursor_megusta;
SET psum=0;
FETCH cursor_megusta INTO vmegusta;
SET psum=psum+vmegusta;
FETCH cursor_megusta INTO vmegusta;
SET psum=psum+vmegusta;
FETCH cursor_megusta INTO vmegusta;
SET psum=psum+vmegusta;
CLOSE cursor_megusta;
END $$
DELIMITER ;
<b></b>
<b></b>
La llamada al procedimiento sería:
CALL suma_megusta(@suma);
-- comprobamos el valor dejado en @suma
SELECT @suma;
A continuación se muestra un ejemplo de una función que obtiene usando cursores la suma de los puntos de los juegos del equipo cuyo código se pase a esa función.
Si el equipo no existe se controla el error devuelto por una <b>SELECT</b> nula ('02000') asignando un valor 0 a la variable vexiste. Esto hace que cuando se llegue a no poder leer una fila con la condición dada también se pueda salir del bucle.
<b></b>
USE campeonato6;
DELIMITER //
CREATE FUNCTION suma_puntos_equipo(pcdequipo char(2))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE vexiste INT DEFAULT 1;
DECLARE vtotal, vpuntos INT;
DECLARE cursor_puntos CURSOR FOR
SELECT participa.puntos
FROM participa
INNER JOIN juego ON juego.cdjuego=participa.cdjuego
WHERE juego.cdequipo=pcdequipo;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET vexiste = 0;
SET vtotal=0;
OPEN cursor_puntos;
FETCH cursor_puntos INTO vpuntos ;
WHILE vexiste=1 DO
SET vtotal=vtotal+vpuntos;
FETCH cursor_puntos INTO vpuntos;
END WHILE;
CLOSE cursor_puntos;
RETURN vtotal;
END//
DELIMITER ;
-- Comprobación
-- Equipo que existe
SELECT suma_puntos_equipo('06');
-- Equipo que no existe
SELECT suma_puntos_equipo('XX');
Crear una función que reciba una comunidad y nos devuelva en una fila los nombres de los juegos cuyo equipo es de esa comunidad separados por comas. Debes utilizar cursores.
Indica si la siguiente afirmación es verdadera o falsa
Retroalimentación
Falso
Un cursor se puede usar tanto el código de procedimiento como en el de una función, pues el cursor nos muestra el resultado de la consulta SELECT, sino que la deja en una tabla temporar para operar con ella.
María ha se ha reunido hoy con el equipo de Vindio y han estado hablando sobre el proyecto de que están desarrollando.
Aunque muchos procesos rutinarios que se llevan a cabo en la base de datos de su proyecto se han simplificado, todavía en muchos casos es necesario que las personas recuerden que deben realizar determinadas operaciones. Es el caso, por ejemplo, del registro de la disminución de las unidades en stock de un recambio cuando se incorpora a una reparación.
Para evitar despistes durante el registro de operaciones en su base de datos, Nooiba, Vindio y Naroba consideran que la solución sería que algunas rutinas se ejecutaran automáticamente, es decir que cada vez que se incorpora un recambio a la reparación de un vehículo la cantidad en stock de ese recambio disminuyera automáticamente sin necesidad de que la encargada de recambios tenga que hacer esa modificación o lanzar el procedimiento que la realiza. Es hora de que conozcamos con ellos la utilidad de los disparadores o triggers.
En este apartado vamos a tratar una herramienta muy potente para programar nuestra base de datos que son los disparadores, desencadenadores o triggersen inglés.
Un trigger o disparador es una rutina asociada con una tabla, que se activa o ejecuta automáticamente cuando se produce algún evento sobre la tabla.
Es necesario tener en cuenta:
Un trigger siempre está asociado con una tabla de la base de datos.
Un trigger siempre se invoca antes o después de que una fila se inserta, modifica o elimina.
Cada base de datos puede o no, tener uno o más triggers.
Un trigger se ejecuta como parte de la transacción que lo activó.
Los triggers pueden utilizarse para:
Implementar restricciones definidas en el diseño de la base de datos.
Automatizar acciones críticas suministrando avisos y sugerencias cuando haya que reparar alguna acción.
Actualizar los valores de una tabla, insertar registros en una tabla o llamar a otros procedimientos almacenados.
Gestionar auditorías y tablas de históricos
La sintaxis de creación de un trigger es la siguiente:
<strong>trigger_nombre</strong>: nombre que recibe el desencadenador, disparador o trigger.
<strong>BEFORE|AFTER</strong>: determina si las instrucciones asociadas al trigger se ejecutarán antes (BEFORE) o después (AFTER) de la operación o evento que lo activó.
<strong>INSERT|UPDATE|DELETE</strong>: es el evento u operación sobre la tabla que activará al desencadenador.
<strong>tabla</strong>: nombre de la tabla con la que está asociado el desencadenador o trigger.
<strong>FOLLOWS/PRECEDES</strong>: determina el orden en el que se ejecutará ese trigger en relación al triggerotro_trigger (trigger asociado a la misma tabla, el mismo evento y mismo moento de disparo)
Entre BEGIN y END van las sentencias asociadas al desencadenador.
Para acceder a los valores de las columnas de la tabla asociada al trigger, se utilizan OLD y NEW:
En un trigger INSERT sólo se pueden usar los valores NEW.columna.
En un trigger DELETE sólo se pueden usar los valores OLD.columna.
En un trigger UPDATE se puede acceder a los valores NEW.columna que son los nuevos valores que se dan a las columnas y OLD.columna que son los antiguos valores de las columnas antes de la actualización.
Por ejemplo el valor de la columna nombre que se ha insertado con un <b>INSERT</b> que ha disparado un trigger, se representa como <b>NEW.Nombre</b>.
Debes tener en cuenta que:
No se puede asociar un trigger a una tabla temporal o a una Vista (VIEW)
No puede haber dos triggerso más para una misma tabla, que respondan al mismo evento y en el mismo momento de disparo, en versiones anteriores a MySQL 8.0.x.
Si puede haber dos o más triggersasociados a una tabla, respondiendo al mismo evento y que se diparen en el mimso momento a partir de la versión MySQL 8.0.x, y en este caso hay que gestionar el orden o prioridad de ejecución de cada uno de ellos mediante las opciones FOLLOW/PRECED. En otro caso, se activan según el orden de creación..
No pueden incluirse sentencias de control de transacción, tales como <strong>COMMIT</strong> y <strong>ROLLBACK</strong> en el código de un trigger.
Dado que los triggers son rutinas, podemos usar para ellos las mismas sentencias que en los procedimientos y en las funciones.
Para crear y ejecutar TRIGGERS se necesita el privilegio TRIGGER.
Ejemplo.
Creamos un trigger sobre la base de datos campeonato6 y asociado a la tabla juego controlando que si se intenta poner como nuevo valor de la columna megusta un NULL, se deja con el antiguo valor que tuviese.
USE campeonato6;
DELIMITER //
CREATE TRIGGER comprueba_juego
BEFORE UPDATE ON juego
FOR EACH ROW
BEGIN
IF NEW.megusta IS NULL THEN
SET NEW.megusta=OLD.megusta;
END IF;
END//
DELIMITER ;
-- comprobación
SELECT * FROM juego; -- vemos los juegos
UPDATE juego
SET megusta=NULL
WHERE cdjuego='ELV'; -- modificamos y después volvemos a consultar para ver que no se ha cambiado
La sintaxis para eliminar un trigger es la siguiente:
En MySQL, versión 8.0.x, los disparadores o triggers que creamos en cada base de datos se almacenan dentro la tabla triggers de la base de datos INFORMATION_SCHEMA.
En este apartado veremos algunos ejemplos más de triggers y cómo utilizar la sentencia SIGNAL para lanzar condiciones de error dentro de rutinas almacenadas.
Veamos previamente otro ejemplo de trigger.
Realizar un trigger que al modificar en la tabla juego las estrellas de un juego, compruebe que el nuevo valor de las estrellas sea como máximo 6. Si se supera el valor 6, se asigna directamente el valor 6.
USE campeonato6;
DROP TRIGGER IF EXISTS estrellas_juego_update;
DELIMITER //
CREATE TRIGGER estrellas_juego_update
BEFORE UPDATE ON juego
FOR EACH ROW
BEGIN
IF NEW.estrellas > 6 THEN
SET NEW.estrellas=6;
END IF;
END//
DELIMITER ;
-- Comprobación
-- Caso de incrementar valor. Hasta que no supere el valor 6 lo incrementa en 1
UPDATE juego
SET estrellas = estrellas +1
WHERE cdjuego= 'FOE';
-- Caso de asignar valor. Como asignamos 7, el trigger le asigna 6
UPDATE juego
SET estrellas = 7
WHERE cdjuego= 'FOE';
Puedes utilizar la sentencia SIGNAL para devolver de manera personalizada el error que se produce en un procedimiento almacenado, función almacenada o disparador. La sentencia SIGNAL te proporciona el control sobre qué información devolver al generarse el error, tales como el valor devuelto y el mensaje SQLSTATE.
El formato de la sentencia SIGNAL es el siguiente:
Un valor genércio de SQLSTATE para gestionar errores es el valor '45000'.
Si se utiliza nombre_condicon, debe estar previamente declarado con un valor de SQLSTATE.
El item_informacion puede ser entre otros un MESSAGE_TEXT.
Ejemplo con SIGNAL.
En la base de datos campeonato6 vamos a crear un trigger que al intentar eliminar un equipo no deje eliminarlo si su año de fundación es posterior a 2018.
USE campeonato6;
DELIMITER //
CREATE TRIGGER control_elimina_equipo
BEFORE DELETE ON equipo
FOR EACH ROW
BEGIN
IF OLD.anio_funda > 2018 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'No puede ser eliminar un equipo de año > 2018';
END IF;
END//
DELIMITER ;
-- comprobación
DELETE FROM equipo
WHERE cdequipo='07'; -- se mostrará el mensaje personalizado
Realiza los triggers necesarios en la base de datos campeonato6 para que al eliminar un juego:
a) Se guarde el total de puntos y el total de concursantes que participan en ese juego, en la tabla ranking_juegos.
Puedes utilizar la vista (VIEW) existente en la base de datos en el código de este trigger.
b) Se inserten todos los datos del juego en una tabla historia_juegos, así como la fecha y hora el sistema en que se ha eliminado y el usuario que lo eliminó. Debes crear previamente esa tabla.
Han encargado a BK Sistemas Informáticos la programación de una base de datos que permita llevar las gestiones más comunes de una Agencia de viajes y que por lo tanto serán tareas comunes a todas las aplicaciones que accedan a esa base de datos. Para ello, el equipo de Vindio, Noiba y Naroba serán los encargados de crear en la base de datos de nombre viajes los procedimientos, funciones y disparadores o triggers apropiados que permitan una gestión eficiente de la misma.
En este apartado vas a ver otro ejemplo de cómo dotar a una base de datos de toda una serie de rutinas que automatizan ciertos procesos de su lógica de negocio o funcionamiento. El ejemplo propuesto es el de una base datos para llevar la gestión de las reservas de viajes a sus clientes.
Ejemplo. Agencia de viajes.
Una Agencia de Viajes dispone una base de datos para gestionar las reservas de viajes a clientes. La base de datos VIAJES está formada por las tablas VIAJE, CATEGORIA, MAYORISTA, RESERVA y CLIENTE, relacionadas tal y como puedes ver en su esquema relacional de la siguiente imagen.
Workbench(Elaboración propia)
MAYORISTA: almacena el código y nombre de los mayoristas de viajes con las que trabaja la Agencia.
CATEGORIA: almacena el código y nombre de una determinada categoría de viaje.
VIAJE: almacena los datos de los viajes existentes para cada categoría y mayorista. Para cada viaje se guarda su en 'plazas' el total de plazas de ese viaje y en 'ocupadas' el total de plazas reservadas hasta el momento. De manera que las plazas disponibles en un momento dado serán: plazas - ocupadas.
CLIENTE: almacena los datos de los clientes de la Agencia. Para cada cliente se almacenan unos 'puntos' que otorga la Agencia en función de los viajes que ya ha realizado previamente.
RESERVA: almacena datos del cliente, viaje y fecha en la que ha se hace la reserva, así como el precio final del viaje con posibles descuentos aplicados.
LISTADO_VIAJES: es una vista (VIEW) que genera un listado de todos los viajes, mostrando su código, nombre, importe, plazas disponibles, fecha de salida, código de la mayorista y categoría del viaje.
¿como funciona la reserva de viajes?
Cuando se va a reservar un viaje por parte de un cliente de la Agencia:
Se comprueba si el número de plazas ocupadas (columna 'ocupadas') es inferior a la cantidad de plazas existentes (columna 'plazas') de ese viaje.
Si es inferior, se reserva el viaje insertando una fila en la tabla de RESERVA e incrementando en 1 la columna 'ocupadas' de la tabla VIAJE.
En caso contrario, el viaje no se puede reservar, pues ya está completo.
La Agencia de viajes nos solicita automatizar las siguientes tareas o procesos:
Saber el total de viajes disponibles de una determinada categoría en un año concreto, y a partir de un determinado mes.
Listar ciertos datos de los viajes disponibles de una determinada categoría a partir de un mes y año concretos.
Controlar de forma adecuada la inserción de viajes verificando la fecha de salida y las plazas ocupadas.
Automatizar el proceso de reservas de viajes por los clientes.
Incrementa en 40 los puntos del cliente si la reserva la hace al menos 30 días antes de la fecha de salida y en 10 puntos en otro caso.
Obtener el nivel de un cliente según sus puntos.
Obtener la letra que le corresponde a un DNI, y actualizar la columna dni de la tabla cliente sustituyendo el DNI por el NIFque corresponda.
Llevar una auditoría de la modificación de clientes.
Para automatizar esas tareas vamos a desarrollar las siguientes funciones, procedimientos y triggers, teniendo en cuenta las especificaciones concretas que nos indican en cada caso desde la Agencia de Viajes.
Para desarrollar y probar esas rutinas puedes descargar la base de datos viajes desde aquí (zip - 1,94 KB).
Materiales desarrollados inicialmente por el Ministerio de Educación, Cultura y Deporte y actualizados por el profesorado de la Junta de Andalucía bajo licencia Creative Commons BY-NC-SA.
Antes de cualquier uso leer detenidamente el siguenteAviso legal
Actualización de materiales y correcciones menores.
Versión: 02.00.00
Fecha de actualización: 23/05/23
Autoría: Isabel Cruz Granados
Ubicación: En la Unidad Mejora (tipo 3): * Sustituir los ejemplos que aluden a una base de datos inexistente y sustituirlos por ejemplos sobre la
base de datos que se ha proporcionado al principio de la unidad para que el alumnado pueda probar los ejemplos expuestos sobre: procedimientos y funciones almacenados, y triggers.
* Apartados 7, 8 , 9 y 10. Incluir más ejemplos que clarifiquen el uso de las sentencias de control: IF THEN ELSE, WHILE, REPEAT, Manejadores de errores
y cursores en funciones, procedimientos y triggers.
* Incluir al final un caso práctico, mediante un tutorial o videotutorial que muestre un caso realista completo de cómo dotar a una base de datos
de diversas rutinas para automatizar tareas.
* Modificar el mapa conceptual según los nuevos contenidos.
* Modificar las orientaciones al Alumnado según los nuevos contenidos.
Ubicación: En toda la unidad Mejora (tipo 1): Lista de pequeñas erratas:
Construcción de guiones.
trabajo.Pero - trabajo. Pero
¿Es eso posible con SQL? Si - ¿Es eso posible con SQL? Sí
SQL/PSM (Persistent Stores Modules) - SQL/PSM (Persistent Stored Modules)
fué - fue
quedó patente los beneficios - quedaron patentes los beneficios
Sistemas Gestores de Datos - Sistemas Gestores de Base de Datos
1.- Introducción. Lenguaje de programación.
Tampoco podemos, hasta ahora - Tampoco podíamos, hasta ahora
2.1.- Herramientas gráficas para procedimientos y funciones.
procedimeintos lamacenados - procedimientos almacenados
4.- Procedimientos.
consta de varias instrucciones y está almacenado en el servidor
Por ejemplo un usuario puede tener permiso para ejecutar un procedimiento EXECUTE, pero en cambio puede no tener permiso para crear nuevos procedimientos CREATE ROUTINE.
4.2.- Creación de procedimientos II.
entarda/salida - entrada/salida
4.3.- Declaración de variables locales.
Veamos como se delcaran - Veamos como se declaran
6.- Modificar y borrar procedimientos y funciones.
procedimeinto - procedimiento
carcaterísticas - características
7.1.- La sentencia IF.
correponde - corresponde
10.- Disparadores o triggers
sobre en la base de datos - sobre la base de datos
Ubicación: Mapa Mejora (Mapa conceptual): Actualizado según los nuevos contenidos.
Ubicación: Tabla de contenidos Mejora (Orientaciones del alumnado): Actualizada según los nuevos contenidos.
