1. Bases de datos relacionales

Procedimientos

1. Creamos un procedimiento en la base de datos catalogo para contar el número de genes diferentes de la tabla refGene, que denominamos numGenes.

CREATE PROCEDURE numGenes()                 
SELECT COUNT(distinct name2) FROM refGene;

Este procedimiento no tiene parámetros () y muestra por pantalla el número de genes diferentes que encontramos en el campo name2 de la tabla refGene. Al ejecutar el código, el procedimiento se almacena en la base de datos como un objeto más, como las tablas, y no se ejecuta la consulta SELECT que contiene hasta que lo llamemos.

Para llamar el procedimiento numGenes escribimos:

CALL numGenes();

Si queremos ver los procedimientos almacenados a la base de datos catalogo escribimos:

SHOW PROCEDURE STATUS WHERE db = 'catalogo';

Si queremos eliminar un procedimiento creado escribimos:

DROP PROCEDURE IF EXISTS numGenes;

2. Ahora vamos a utilizar variables, el resultado del SELECT lo guardaremos en la variable local gens que tenemos que declarar y de la cual debemos definir el tipo de datos.

Como en el procedimiento hay sentencias que acaban en «;» primero asignaremos un nuevo delimitador. Escribimos:

 DELIMITER //

Ahora MySQL interpretará que el final de las sentencias SQL es el símbolo //

Crearemos un nuevo procedimiento llamado numGenes2.

Para declarar la variable gens necesitamos poner BEGIN antes de DECLARE y acabar con END.

Al declarar las variables locales es necesario especificar qué tipo de dato van a guardar, INT, CHAR, VARCHAR, etc. En este caso es un INT.

DECLARE gens INT;

Para guardar el resultado del SELECT en la variable local gens hacemos:

INTO gens.

Para mostrar el contenido de la variable gens hacemos:

SELECT gens;

Vemos todo el código para crear el nuevo procedimiento usando variables:

CREATE PROCEDURE numGenes2()                    
BEGIN
DECLARE gens INT;                              
SELECT COUNT(distinct name2)
INTO gens
FROM refGene;  
SELECT gens;                               
END //

Volvemos a cambiar el delimitador para poder usar «;» al finalizar la sentencia DELIMITER ;

Invocamos el procedimiento:

 CALL numGenes2();

3. Ahora pasaremos un parámetro al procedimiento y lo utilizaremos en la condición WHERE de la consulta SELECT.

Creamos un nuevo procedimiento llamado numTrans que nos servirá para contar el número de transcritos según el tipo de transcrito que le pasamos como parámetro.

Al igual que las variables locales, es necesario especificar a los parámetros qué tipo de datos esperan, INT, CHAR, VARCHAR… En este caso, CHAR(50).

El parámetro lo ponemos junto al nombre del procedimiento entre paréntesis numTrans(transcritType CHAR(50)).

Asignamos otra vez un delimitador //:

DELIMITER //

Veamos el código:

CREATE PROCEDURE numTrans ( transcritType CHAR(50))           
BEGIN
DECLARE numT INT;                                   
SELECT COUNT(*) INTO numT FROM refGene 
WHERE name LIKE transcritType;  
SELECT numT;
END //

Cambiamos otra vez el delimitador:

DELIMITER ;

Invocamos el procedimiento pasándole el parámetro NR:

CALL numTrans('%NR%');

Ahora invocamos el procedimiento pasándole el parámetro NM:

CALL numTrans('%NM%');

Al usar el comodín % nos aseguramos que no perdemos ningún registro con el contenido del parámetro.

4. En vez de declarar una variable local dentro de un procedimiento, también podemos utilizar un parámetro como variable de salida. Indicamos que es un parámetro de salida con la cláusula OUT. Por defecto, los parámetros son solo de entrada, pero también los podemos indicar con la cláusula IN.

Asignamos un delimitador //:

DELIMITER //

Creamos un nuevo procedimiento llamado numTrans2 que nos servirá también para contar el número de transcritos según el tipo de transcrito que le pasamos como parámetro, y con un segundo parámetro que nos servirá para guardar el resultado de la consulta.

Veamos el código:

CREATE PROCEDURE numTrans2(IN transcritType CHAR(50), OUT numT INT)         
BEGIN
SELECT COUNT(*) INTO numT FROM refGene 
WHERE name LIKE transcritType;   
END //

Cambiamos el delimitador:

DELIMITER ;

Invocamos el procedimiento pasándole los dos parámetros. Usamos una variable definida por el usuario con @ llamada @transcritos para guardar el valor que devuelve el SELECT del procedimiento:

CALL numTrans2('%NR%', @transcrits);

Hacemos un SELECT de la variable @transcritos que contiene el número de transcritos:

SELECT @transcrits;

Funciones

Las funciones devuelven un valor, así que, para llamar una función almacenada, en vez de hacer CALL hacemos directamente SELECT nombre_de_la_función y nos muestra el valor que devuelve la función.

Asignamos un delimitador //:

DELIMITER //

Creamos una nueva función llamada numTrans3 que nos servirá también para contar el número de transcritos según el tipo de transcrito que le pasamos como parámetro.

En las funciones tenemos que indicar, después del nombre y de los parámetros, el tipo de dato que devuelve la función, en este caso un INT, y escribimos:

RETURNS INT

Al final de la función le indicamos la variable que queremos devolver, en este caso:

RETURN numT;

Veamos el código íntegro de la función:

CREATE FUNCTION numTrans3 (transcritType CHAR(50)) RETURNS INT   
BEGIN
DECLARE numT INT;                                 
SELECT COUNT(*) INTO numT FROM refGene 
WHERE name LIKE transcritType;          
RETURN numT;
END //

Cambiamos el delimitador:

DELIMITER ;

Invocamos la función pasándole el parámetro, en este caso les pasamos el parámetro NR:

SELECT numTrans3('%NR%');

Si pasamos a la función el parámetro NM:

SELECT numTrans3('%NM%');

Si queremos ver las funciones almacenadas a la base de datos catalogo escribimos la sentencia:

SHOW FUNCTION STATUS WHERE db = 'catalogo';

Si queremos eliminar una función almacenada, escribimos:

DROP FUNCTION IF EXISTS numTrans3;

Triggers

Un trigger es un objeto almacenado en la base de datos que está asociado con una tabla y que se activa cuando ocurre un evento sobre la tabla.

Los eventos que pueden ocurrir sobre la tabla son:

• INSERT. El trigger se activa cuando se inserta una nueva fila sobre la tabla asociada.
• UPDATE. El trigger se activa cuando se actualiza una fila sobre la tabla asociada.
• DELETE. El trigger se activa cuando se elimina una fila sobre la tabla asociada.

El trigger se puede activar o disparar antes (BEFORE) del evento o después (AFTER) del evento.

Como ejemplos vamos a crear dos triggers asociados a la tabla genes de nuestra base de datos catalogo:

Un trigger con el nombre de trig_check_genes_before_insert que se asocia a la tabla genes. Se activa antes de una operación de inserción. Si el nuevo valor del campo inicio que se quiere insertar es negativo, se guarda como 0. Si el nuevo valor del campo final que se quiere insertar es menor que el valor del campo inicio, se guarda el valor del campo inicio.

Un trigger con el nombre de trig_check_genes_before_update que se asocia a la tabla genes. Se activa antes de una operación de modificación. Si el nuevo valor del campo inicio que se quiere modificar es negativo, se guarda como 0. Si el valor del campo final del registro que se quiere modificar es menor que el nuevo valor que queremos actualizar del campo inicio se guarda como 1.

Creamos el trigger trig_check_genes_before_insert

DELIMITER //
CREATE TRIGGER trig_check_genes_before_insert      
BEFORE INSERT
ON genes FOR EACH ROW           
BEGIN IF NEW.inicio < 0 THEN SET NEW.inicio = 0;           
ELSEIF NEW.inicio > NEW.final THEN SET NEW.final = NEW.inicio;      
END IF;                         
END//

Al ejecutar este código de creación del trigger, trig_check_genes_before_insert queda almacenado en nuestra base de datos, y solo actuará, se activará cuando el usuario ejecute una sentencia de inserción, por ejemplo:

Cambiamos el delimitador:

DELIMITER ;

Realizamos operaciones de inserción en la tabla genes para que se dispare el trigger trig_check_genes_before_insert:

INSERT INTO genes (nombre, cromosoma, hebra, inicio, final, proteina, especie) VALUES ('WASH7P', 'chrX', '+', -2527305, 2575270,'NR_033380', 'H. Sapiens');

INSERT INTO genes (nombre, cromosoma, hebra, inicio, final, proteina, especie) VALUES ('WASH7P2', 'chrX', '+', 252730599, 2575270,'NR_033381', 'H. Sapiens');

La variable compuesta NEW que utilizamos en el trigger almacena todos los valores que insertamos en cada operación INSERT. De esta forma podemos usarla en el trigger sin conocer a priori qué valores se van a insertar.

En nuestros ejemplos la variable NEW.inicio contiene en el primer INSERT el valor -2527305 y en el segundo INSERT la variable NEW.inicio contiene el valor 252730599 y la variable NEW.final contiene el valor 2575270

En el primer INSERT se cumple NEW.inicio < 0, esta condición dispara el trigger y en el campo inicio se guarda el valor 0.

En el segundo INSERT se cumple NEW.inicio > NEW.final, esta condición dispara el trigger y en el campo final se guarda el valor 252730599.

Ahora creamos el trigger trig_check_genes_before_update:

DELIMITER //
CREATE TRIGGER trig_check_genes_before_update 
BEFORE UPDATE ON genes 
FOR EACH ROW 
BEGIN 
IF NEW.inicio < 0 THEN SET NEW.inicio = 0;
ELSEIF NEW.inicio > OLD.final THEN SET NEW.inicio = 1; 
END IF; 
END //

Al ejecutar este código de creación del trigger, trig_check_genes_before_update queda almacenado en nuestra base de datos, y solo actuará, se activará cuando el usuario ejecute una sentencia de actualización, por ejemplo:

Cambiamos el delimitador:

DELIMITER ;

Realizamos operaciones de modificación en la tabla genes para que se dispare el trigger trig_check_genes_before_update:

UPDATE genes SET inicio = 228748314 WHERE nombre = 'MYC';

UPDATE genes SET inicio = -47643 WHERE nombre = 'cbt';

En este caso, la variable compuesta NEW que utilizamos en el trigger almacena el nuevo valor que queremos actualizar en la sentencia UPDATE. En el primer UPDATE la variable NEW.inicio contiene el valor 228748314 y en el segundo UPDATE la variable NEW.inicio contiene el valor –47643.

En cambio, la variable compuesta OLD almacena todos los valores viejos ya almacenados en la tabla del registro que se quiere actualizar. En el primer UPDATE, la variable OLD.final contiene el valor almacenado en el campo final del registro con clave primaria ‘MYC’ en la tabla genes, y en el segundo UPDATE, la variable OLD.final contiene el valor almacenado en el campo final del registro con clave primaria ‘cbt’.

En el primer UPDATE se cumple NEW.inicio > OLD.final, esta condición dispara el trigger y en el campo inicio se guarda el valor 1.

En el segundo UPDATE se cumple NEW.inicio < 0 , esta condición dispara el trigger y en el campo inicio se guarda el valor 0.

En las operaciones DELETE solo se utiliza la variable compuesta OLD capaz de almacenar todos los valores del registro que se quiere eliminar y acceder a ellos con el formato OLD.nombre_campo.