t11 - lenguaje procedimental mysql

1

Lenguaje procedimental - MySQLDaniel Santiago

Lenguaje procedimental

Introducción 2

Declaración de variables de usuario 2

Asignación de valores a variables 2

Estructuras condicionales 3

Bucles 4

Handlers 5

Cursores 6

Funciones 8

Procedimientos 9

Triggers 10

2


1. Introducción

A partir de la versión 5.0, MySQL permite la extensión procedimental. Mediante esta característica, podemos almacenar procedimientos y funciones dentro del servidor. Estos procedimientos son un conjunto de comandos SQL que los clientes pueden ejecutar conjuntamente refiriéndose al procedimiento que los contiene.

El hecho de poder crear procedimientos almacenados dentro del servidor MySQL ofrece algunas ventajas. Por ejemplo, estas funciones son independientes del lenguaje de las aplicaciones clientes que usen las bases de datos del servidor. Además, con procedimientos conseguimos entornos de ejecución más seguros.

Pero una de las ventajas del uso del lenguaje procedimental es la ganancia en el rendimiento que representa ejecutar funciones dentro del servidor, allá donde tenemos los datos. De esta forma el envío de información entre cliente y servidor es menor.

En este documento se explica la sintaxis básica que ofrece MySQL para poder crear funciones y procedimientos almacenados.

2. Declaración de variables de usuario

MySQL permite el uso de variables de usuario para que éste pueda almacenar valores. De esta forma se puede, por ejemplo, pasar valores entre diferentes sentencias.

Las variables que declara un usuario están presentes únicamente en la conexión desde la que se crean, por lo tanto estas variables no pueden ser vistas ni utilizadas por otros clientes.

Las variables se declaran de la siguiente forma:

SET @nombre_variable = valor;

Es obligatorio anteponer el carácter @ al nombre de la variable de usuario, ya que sino MySQL entiende que queremos hacer uso de una variable de sistema, y muy probablemente nos dará un error (si ponemos un nombre de variable que no conoce el sistema).

Hay un tipo de variables que son las que van dentro de un procedimiento o función. Estas variables se llaman locales, y se declaran de la siguiente forma:

DECLARE nombre_variable TIPO_DATO [DEFAULT valor];

Entre corchetes se ha puesto el valor por defecto que se le puede dar a una variable al declararla. Esta parte es opcional.

3. Asignación de valores a variables

Para asignar un valor a una variable local, utilizaremos el operador SET:

SET variable = valor;

El operador SET nos permite también asignar a una variable el resultado de una consulta SELECT, siempre y cuando esta consulta devuelva un único resultado.

3


Si queremos almacenar los valores devueltos por una consulta SELECT en diferentes variables, podemos usar la sentencia SELECT … INTO:

SELECT column1, column2 INTO var1, var2 … FROM tabla [WHERE condiciones]

En el ejemplo anterior, los valores de column1 y column2 se guardarán en variable1 y variable2 respectivamente.

En el apartado de declaración de variables hemos visto el operador SET y DECLARE. Pero hay otra forma de crear una variable. El código siguiente ejemplifica la tercera forma de declarar una variable, asignándole un valor en la misma sentencia:

SELECT @var := valor;

Se aprecia que el operador de asignación es dos puntos igual (:=). Éste es el que debemos usar cuando no usemos los operadores SET o SELECT … INTO.

Veamos otro ejemplo de asignación de valores a variables, en este caso dentro de una consulta SELECT, sin usar el operador INTO:

SELECT @var1:=column1, @var2:=column2… FROM tabla [WHERE condiciones]

4. Estructuras condicionales

MySQL nos ofrece las estructuras IF…ELSE y CASE…WHEN para poder crear estructuras condicionales.

La sintaxis para crear una estructura IF…ELSE es la siguiente:

IF condición THENSentencias;

ELSEIF condición THENSentencias;

ELSESentencias;

END IF;

La condición puede ser una comparación entre variables y/o valores, usando los operadores de comparación (=, <, <=, =>, <>, !=, [NOT] BETWEEN). También podemos verificar en la condición si una variable es nula, con el operador IS NULL. Para verificar que no es nula, usaremos IS NOT NULL.

La sintaxis para crear una estructura CASE…WHEN es la siguiente:

CASE variableWHEN valor1 THEN sentencias;WHEN valor2 THEN sentencias;ELSE sentencias;

END CASE;

4


Si no se quisiera añadir ninguna sentencia en el bloque ELSE, añadiremos BEGIN END; tal y como sigue:

CASE variableWHEN valor1 THEN sentencias;WHEN valor2 THEN sentencias;ELSE BEGIN END;

END CASE;

5. Bucles

Para repetir varias veces un fragmento de sentencias, MySQL nos ofrece los bucles LOOP, REPEAT y WHILE. Además, disponemos de las sentencias LEAVE e ITERATE.

La sintaxis para el bucle LOOP es la siguiente:

[etiqueta:] LOOPSentencias;

END LOOP [etiqueta];

Veamos un ejemplo de uso del bucle LOOP, en el que haremos 10 iteraciones:

CREATE PROCEDURE looping (p1 INT)BEGIN

label1: LOOPSET p1 = p1 + 1;IF p1<10 THEN

ITERATE label1;END IF;LEAVE label1;

END LOOP label1;END

En el código anterior vemos que aparecen las sentencias ITERATE y LEAVE. El comando ITERATE sirve para volver a ejecutar el bucle con la etiqueta que acompaña a ITERATE. El comando LEAVE se utiliza para salir del bucle con la etiqueta que acompaña a LEAVE.

El bucle REPEAT tiene la siguiente sintaxis:

[etiqueta:] REPEATsentencias;UNTIL condición

END REPEAT [etiqueta];

Las sentencias que hay dentro del bucle REPEAT se repetirán hasta que la condición sea cierta. El etiquetado del bucle REPEAT es opcional.

Veamos un ejemplo de uso del bucle REPEAT:

CREATE PROCEDURE repite (p1 INT)BEGIN

SET @x = 0;REPEAT SET @x = @x+1; UNTIL @x>p1 END REPEAT;

5


END

El bucle más popular es el WHILE. Su sintaxis es la siguiente:

[etiqueta:] WHILE condición DOsentencias;

END WHILE [etiqueta];

Las sentencias dentro de un bucle WHILE se repiten mientras la condición sea cierta. El etiquetado del bucle WHILE es opcional.

A continuación vemos un ejemplo del bucle WHILE:

CREATE PROCEDURE bucle()BEGIN

DECLARE var INT DEFAULT 10WHILE var > 0 DO

SET var = var-1;END WHILE;

END

6. Handlers

Un handler en MySQL se invoca cuando se produce una condición previamente definida. Esta condición o evento está asociado con una condición de error. El handler lleva un comando específico asociado, que se ejecutará inmediatamente después de que se cumpla la condición.

Un handler se declara de la siguiente forma:

DECLARE tipo_handler HANDLER FOR condición(es) sentencia;

Los handlers en MySQL soportan los siguientes tipos:

CONTINUE: continúa la rutina actual tras la ejecución del comando handler. EXIT: termina la ejecución del comando BEGIN … END actual.

La condición de un handler hemos comentado que está asociada a una condición de error, que podemos definir de tres formas:

Como código de error de MySQL. Como código SQLSTATE. Con los nombres SQLEXCEPTION, SQLWARNING y NOT FOUND.

Los códigos SQLSTATE son independientes de la base de datos con la que trabajemos. Por tanto, en Oracle, SQL Server, MySQL, etc. tendremos el mismo código de error SQLSTATE, lo que hará que nuestro código sea más portable. Sin embargo, los códigos de error de MySQL son propios, y nada tendrán que ver con los códigos de error de otras bases de datos.

Dicho lo anterior, podemos pensar que es mejor usar en nuestros handlers los códigos de SQLSTATE en lugar de los códigos de error de MySQL. Esto no es correcto, ya que no todos los códigos de error de MySQL tienen su equivalencia en código SQLSTATE. Además, si pensamos

6


en términos de portabilidad, el lenguaje para implementar funciones y procedimientos almacenados es diferente en MySQL, Oracle y SQL Server.

Veamos un ejemplo de creación y uso de handlers:

CREATE PROCEDURE miHandler()BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2 = 1;SET @x = 1;INSERT INTO test.t VALUES (1);SET @x = 2;INSERT INTO test.t VALUES (1);SET @x = 3;

END

En el código anterior vemos que hacemos dos operaciones INSERT con la misma PRIMARY KEY. Esto produciría un error en MySQL con el código SQLSTATE 23000. Lo que hacemos para que nuestro programa siga ejecutándose es declarar un handler de tipo CONTINUE. Así pues, el procedimiento seguirá ejecutándose después de producirse el error. Si luego ejecutamos la sentencia SELECT @x, nos devolverá un 3. Esto es porque se ha ejecutado la última línea de código del procedimiento. Si hubiéramos declarado el handler de tipo EXIT, la ejecución hubiera terminado al producirse el error y la variable @x tendría el valor 2.

Se puede encontrar un listado de códigos de error MySQL y su correspondiente código SQLSTATE aquí.

7. Cursores

Los cursores nos sirven para ir procesando fila a fila el resultado de una consulta SELECT dentro de procedimientos y funciones almacenadas. Los cursores deben declararse antes de declarar los handlers, y las variables y condiciones deben declararse antes de declarar cursores o handlers.

La sintaxis de declaración de cursores es la siguiente:

DECLARE nombre_cursor CURSOR FOR consulta SELECT;

La consultado SELECT no puede tener la cláusula INTO.

Después de declarar un cursor, para poder utilizarlo tenemos que abrirlo. Lo hacemos con la sentencia:

OPEN nombre_cursor;

Para recorrer las filas de una consulta SELECT mediante un cursor declarado y abierto, usaremos la sentencia:

FETCH nombre_cursor INTO var1 [, var2…]

https://dev.mysql.com/doc/refman/5.0/es/error-handling.html

7


De esta forma avanzaremos el cursor abierto hacia el siguiente registro. En caso de no haber más registros, se producirá un error con código SQLSTATE 02000 (que podremos capturar con un handler previamente declarado).

Al finalizar el uso de un cursor, se recomienda cerrarlo con la sentencia:

CLOSE nombre_cursor;

Si no usamos este comando para cerrar explícitamente un cursor, éste se cerrará al final del procedimiento en que se declaró.

Veamos un ejemplo de uso de cursores:

CREATE PROCEDURE cursores()BEGIN

DECLARE done INT DEFAULT 0;DECLARE a CHAR(16);DECLARE b, c INT;DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;OPEN cur1;OPEN cur2;REPEAT

FETCH cur1 INTO a, b;FETCH cur2 INTO c;IF NOT done THEN

IF b<c THENINSERT INTO test.t3 VALUES (a,b);

ELSEINSERT INTO test.t3 VALUES (a,c);

END IF;END IF;

UNTIL done END REPEAT;CLOSE cur1;CLOSE cur2;

END

Se han creado dos cursores para recorrer las filas devueltas por dos consultas SELECT. Además, se ha declarado un handler con el código SQLSTATE 02000. Éste se ejecutará cuando uno de los dos cursor se quede sin filas que procesar (falle la operación FETCH). Hacemos un bucle en el que iremos tratando cada uno de los registro de los cursores (con la operación FETCH), e insertaremos en una tercera tabla los valores de los cursores en función de una condición (IF … ELSE). Este bucle se repetirá mientras ninguna de las dos operaciones FETCH de error (nos quedemos sin registros). En tal caso, capturaremos el error mediante el handler y cambiaremos el valor de la variable done, con lo que se terminará la ejecución del bucle y del procedimiento.

8


8. Funciones

Una función es un conjunto de sentencias SQL que se almacenan en el servidor bajo un nombre. Los clientes pueden ejecutar este conjunto de sentencias haciendo una llamada a la función, sin necesidad de tener que ejecutar las sentencias individualmente una a una.

Las funciones y los procedimientos se guardan en la tabla proc de MySQL, y están asociados a una base de datos concreta. Para poder crear y modificar funciones y procedimientos necesitamos los permisos CREATE ROUTINE y ALTER ROUTINE. Para poder ejecutar las funciones y procedimientos necesitamos el permiso EXECUTE. El creador de una rutina tendrá este permiso automáticamente.

Para crear funciones tenemos el comando:

CREATE FUNCTION

Las funciones pueden llamarse desde dentro de otras funciones, y pueden retornar un valor escalar.

Para ejecutar una función usaremos la sintaxis:

SELECT nombre_funcion();

Para borrar una función haremos lo siguiente:

DROP FUNCTION [IF EXISTS] nombre_funcion

Para borrar una función sólo debemos especificar su nombre, sin paréntesis ni parámetros.

Veamos un ejemplo de creación de una función:

delimiter //CREATE FUNCTION hola (s CHAR(20)) RETURNS CHAR(50)

RETURN CONCAT(‘Hola ‘, s, ‘!’);//delimiter ;

La función anterior se llama hola, tiene un parámetro de entrada de tipo CHAR(20) que se llama s, y devuelve una cadena de caracteres. La sentencia RETURN acaba la ejecución de la función, devolviendo un valor. Ejecutamos la función de la siguiente forma:

SELECT hola(‘Dani’);

Obtendremos como resultado la frase Hola Dani!

En el código anterior aparece una cláusula importante, delimiter. En MySQL, cada instrucción que ejecutamos la terminamos con punto y coma(;). MySQL entiende que hemos terminado una transacción y la ejecuta. En funciones y procedimientos, queremos que se ejecuten de golpe varias instrucciones, sin tener en cuenta el punto y coma con el que finalizamos cada una. Por eso, definimos un delimitador que nos indica cuándo termina la función y que, todas las instrucciones antes del delimitador, deben ejecutarse juntas. En el ejemplo, se ha usado como delimitador dos barras (//). Al terminar el código de la función, se escribe el delimitador,

9


y después volvemos a definir el punto y coma (;) como delimitador de las siguientes instrucciones.

Otro ejemplo de función:delimiter //create function sueldoMedio (nombreDept text) returns decimal(6,2)begin

declare id_dept int;declare media double;set id_dept = (select id from departamento where nombre = nombreDept);set media = (select avg(sueldo) from empleado where dept=id_dept);return media;

end//delimiter ;

¿Qué hace?

9. Procedimientos

Los procedimientos son parecidos a las funciones, sólo que en los procedimientos no se devuelven valores (mediante la sentencia RETURN, veremos que tienen otros métodos).

La forma de crear y borrar un procedimiento es análoga a la de las funciones sustituyendo la palabra FUNCTION por PROCEDURE:

CREATE PROCEDURE nombre_procedimiento(lista_parámetros)DROP PROCEDURE [IF EXISTS] nombre_procedimiento;

No obstante, la ejecución de un procedimiento es diferente. Se hace con la orden:

CALL nombre_procedimiento(lista_parámetros);

Veamos un ejemplo de creación de procedimiento:

delimiter //CREATE PROCEDURE miProc (OUT param1 INT)BEGIN

SELECT COUNT(*) INTO param1 FROM t;END//delimiter ;

Del código anterior cabe destacar cómo se ha declarado el único parámetro que tiene el procedimiento: OUT param1 INT. Esto indica que el procedimiento devolverá un valor entero al ejecutarse. Tenemos diversos tipos de parámetros, según sean de entrada o salida, o ambos: IN, OUT, INOUT.

Veamos otro ejemplo en el que se da de alta un usuario y automáticamente se registra una nueva entrada en la tabla Registro:

10


delimiter //CREATE PROCEDURE insertaUsuario (in usu_nombre varchar(25))BEGIN

INSERT INTO usuario (nombre) VALUES (usu_nombre);INSERT INTO registro(codigo_usu, fecha, tipo) values(LAST_INSERT_ID(), NOW(), 'Alta');

END//delimiter ;

El procedimiento anterior da de alta un usuario dado su nombre (parámetro de entrada) e inserta una fila nueva en la tabla registro, con el último id insertado (el id del usuario creado), la fecha y hora actuales, y el evento que se ha producido (Alta).

Otro ejemplo más elaborado:

delimiter //CREATE PROCEDURE listaEmails (INOUT listaEmails TEXT)BEGIN

DECLARE final INTEGER DEFAULT 0;DECLARE v_email varchar(100) DEFAULT "";-- declare cursor for employee emailDECLARE cursorEmpl CURSOR FOR SELECT email FROM Empleado;-- declare NOT FOUND handlerDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET final=1;OPEN cursorEmpl;WHILE final = 0 DO

FETCH cursorEmpl INTO v_email;IF final = 0 THEN

SET listaEmails = CONCAT (v_email, ";", listaEmails);

END IF;END WHILE;CLOSE cursorEmpl;

END//delimiter ;

Y su ejecución sería la siguiente:

SET @listaEmails = "";CALL listaEmails(@listaEmails);SELECT @listaEmails;

Como el parámetro que usa el procedimiento es de entrada y salida (INOUT), debemos declarar una variable de usuario antes de llamar al procedimiento. Esta variable será la que pasaremos como parámetro y, por tanto, contendrá el valor generador por el procedimiento.

10. Triggers

11


Los triggers o disparadores son procedimientos que se ejecutan como respuesta a un evento producido sobre una tabla concreta de la base de datos. Los eventos causantes del trigger son las operaciones INSERT, UPDATE o DELETE.

Así pues, a diferencia de las funciones y los procedimientos, los triggers no los ejecuta explícitamente el usuario de la base de datos. El administrador los programa para que se ejecuten cuando el usuario realice alguna operación de INSERT, UPDATE o DELETE sobre la tabla a la que se asocia el trigger.

Cuando creamos un trigger, debemos especificar su nombre, la tabla a la que se asocia, y la operación que hará que se ejecute el trigger. Además, hay que decir en qué momento queremos que se ejecute el código del trigger: antes de realizar la operación INSERT, UPDATE o DELETE que lo desencadena, o después.

La sintaxis para crear un trigger es:

delimiter //CREATE TRIGGER nombre_trigger MOMENTO EVENTO ON nombre_tabla FOR EACH ROWBEGIN

Sentencias;END//

A continuación se explican los siguientes parámetros:

MOMENTO: especifica cuándo debe ejecutarse el código asociado al trigger. Admite los valores BEFORE y AFTER.

EVENTO: operación que desencadena el trigger, como ya se ha dicho, INSERT, UPDATE o DELETE.

Es importante tener en cuenta que en MySQL sólo podemos crear un trigger sobre la misma tabla y el mismo momento y evento.

Vamos a crear un trigger de ejemplo: supongamos que tenemos una tabla Venta, donde se almacenan las ventas que se realizan en una tienda. Además tenemos otra tabla llamada Ingreso, en la que se registran las entradas de dinero a la tienda. Queremos que cada vez que se registre una venta, automáticamente se inserte en la tabla Ingreso una nueva fila que contenga el importe de la venta realizada. Vamos a automatizar esta operación con el siguiente trigger:

Debe asociarse a la tabla Venta. Debe ejecutarse al detectar una operación de INSERT sobre la tabla Venta. Puede ejecutarse después de registrar la venta.

El código sería el siguiente:

delimiter //CREATE TRIGGER registra_ingreso AFTER INSERT ON Venta FOR EACH ROWBEGIN

12


INSERT INTO Ingreso(valor) VALUES(NEW.precio);END//delimiter ;

El trigger hace un INSERT sobre la tabla Ingreso cuyo valor es el que contiene la variable NEW.precio. “precio” es el nombre del campo de la tabla Venta, contiene el valor de la venta. El alias NEW se utiliza en triggers para hacer referencia a la fila que se acaba de añadir (para triggers de tipo AFTER), o se va a añadir (para triggers de tipo BEFORE).

Así pues, el alias NEW contiene todos los valores de la fila que se va a añadir, identificados por los nombres de los campos de la tabla sobre la que se actúa. También tenemos el alias OLD, que hace referencia a una fila existente, antes de ser actualizada o borrada.

Veamos otro ejemplo de trigger: queremos promocionar jugadores de un equipo B a un equipo A. Por tanto, crearemos un trigger que se ejecute cada vez que borremos datos de jugadores del equipo B. Los datos del jugador que se va a borrar los copiaremos en el equipo A.

delimiter //CREATE TRIGGER promocionar BEFORE DELETE ON EquipoB FOR EACH ROWBEGIN

INSERT INTO EquipoA VALUES (OLD.nombre, OLD.dorsal);END//delimiter ;

Hay casos en los que crearemos un trigger con el objetivo de comprobar que los datos modificados en una tabla con las operaciones INSERT, UPDATE o DELETE cumplen algún requisito. En caso de no hacerlo, debemos cancelar la operación, es decir, evitar que se produzca la operación que desencadenó al trigger. Para esto MySQL nos ofrece la sentencia SIGNAL.

Para ejemplificar el uso de la sentencia SIGNAL, vamos a construir el siguiente escenario: queremos controlar que cada vez que se actualice el sueldo de un empleado, sea para aumentarlo. Haremos el siguiente trigger:

delimiter //CREATE TRIGGER comprueba_sueldo BEFORE UPDATE ON Empleado FOR EACH ROWBEGIN

if NEW.sueldo <= OLD.sueldo thensignal sqlstate ‘12345’ set message_text = ‘El sueldo debe incrementarse’;

end if;END//delimiter ;

En el ejemplo anterior se comprueba que el sueldo nuevo de un empleado (guardado en la variable NEW.sueldo) es siempre mayor que el sueldo que tenía (OLD.sueldo). Si no se cumple esta condición, creamos un signal especificando el SQLSTATE junto con el texto (opcional) que queremos que se muestre como error. Al ejecutarse, el signal aborta la actualización que había desencadenado el trigger.

13


Por último, para borrar un trigger usaremos la siguiente sentencia:

DROP TRIGGER nombre_trigger;

t11 - lenguaje procedimental mysql

Education