1. Bases de dades relacionals

Procediments

1. Creem un procediment a la base de dades cataleg per comptar el nombre de gens diferents de la taula refGene, que anomenem numGens.

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

Aquest procediment no té paràmetres () i mostra per pantalla el nombre de gens diferents que trobem al camp name2 de la taula refGene. En executar el codi, el procediment s’emmagatzema a la base de dades com un objecte més, com les taules, i no s’executa la consulta SELECT que conté fins que l’anomenem.

Per anomenar el procediment numGens escrivim:

CALL numGens();

Si volem veure els procediments emmagatzemats a la base de dades escrivim:

SHOW PROCEDURE STATUS WHERE db = 'cataleg';

Si volem eliminar un procediment creat escrivim:

DROP PROCEDURE IF EXISTS numGens;

2. Ara utilizarem variables, el resultat del SELECT el guardarem en la variable local gens, que hem de declarar i de la qual hem de definir el tipus de dades.

Com que en el procediment hi ha sentències que acaben en «;», primer assignarem un nou delimitador. Escrivim:

 DELIMITER //

Ara MySQL interpretarà que el final de les sentències SQL és el símbol //

Crearem un nou procediment anomenat numGens2.

Per declarar la variable gens necessitem escriure BEGIN abans de DECLARE i acabar amb END.

En declarar les variables locals cal especificar quin tipus de dada guardaran, INT, CHAR, VARCHAR, etc. En aquest cas és un INT.

DECLARE gens INT;

Per guardar el resultat del SELECT en la variable local gens fem:

INTO gens.

Per mostrar el contingut de la variable gens fem:

SELECT gens;

Veiem tot el codi per crear el nou procediment usant variables:

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

Tornem a canviar el delimitador per poder fer servir «;» en finalitzar la sentència DELIMITER ;

Invoquem el procediment:

 CALL numGens2();

3. Ara passarem un paràmetre al procediment i l’utilitzarem en la condició WHERE de la consulta SELECT.

Creem un nou procediment anomenat numTrans que ens servirà per comptar el nombre de transcrits segons el tipus de transcrit que li passem com a paràmetre.

Igual que les variables locals, cal especificar als paràmetres quin tipus de dades esperen, INT, CHAR, VARCHAR… En aquest cas, CHAR( 50).

El paràmetre el posem al costat del nom del procediment entre parèntesis numTrans(transcritType CHAR(50)).

Hi assignem una altra vegada un delimitador //:

DELIMITER //

Vegem el codi:

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

Canviem una altra vegada el delimitador:

DELIMITER ;

Invoquem el procediment passant-li el paràmetre NR:

CALL numTrans('%NR%');

Ara invoquem el procediment passant-li el paràmetre NM:

CALL numTrans('%NM%');

En usar el comodí % ens assegurem que no perdem cap registre amb el contingut del paràmetre.

4. En comptes de declarar una variable local dins d’un procediment, també podem utilitzar un paràmetre com a variable de sortida. Indiquem que és un paràmetre de sortida amb la clàusula OUT. Per defecte, els paràmetres són només d’entrada, però també els podem indicar amb la clàusula IN.

Hi assignem un delimitador //:

DELIMITER //

Creem un nou procediment anomenat numTrans2 que ens servirà també per comptar el nombre de transcrits segons el tipus de transcrit que li passem com a paràmetre, i amb un segon paràmetre que ens servirà per guardar el resultat de la consulta.

Vegem el codi:

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

Canviem el delimitador:

DELIMITER ;

Invoquem el procediment passant-li els dos paràmetres. Fem servir una variable definida per l’usuari amb @ trucada @transcritos per guardar el valor que retorna el SELECT del procediment:

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

Fem un SELECT de la variable @transcritos que conté el nombre de transcrits:

SELECT @transcrits;

Funcions

Les funcions retornen un valor, així que, per anomenar una funció emmagatzemada, en comptes de fer CALL fem directament SELECT nombre_de_la_función i ens mostra el valor que retorna la funció.

Hi assignem un delimitador //:

DELIMITER //

Creem una nova funció anomenada numTrans3 que ens servirà també per comptar el nombre de transcrits segons el tipus de transcrit que li passem com a paràmetre.

En les funcions hem d’indicar, després del nom i dels paràmetres, el tipus de dada que retorna la funció, en aquest cas un INT, i escrivim:

RETURNS INT

Al final de la funció li indiquem la variable que volem retornar, en aquest cas:

RETURN numT;

Vegem el codi íntegre de la funció:

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 //

Canviem el delimitador:

DELIMITER ;

Invoquem la funció passant-li el paràmetre, en aquest cas els passem el paràmetre NR:

SELECT numTrans3('%NR%');

Si passem a la funció el paràmetre NM:

SELECT numTrans3('%NM%');

Si volem veure les funcions emmagatzemades a la base de dades escrivim la sentència:

SHOW FUNCTION STATUS WHERE db = 'cataleg';

Si volem eliminar una funció emmagatzemada, escrivim:

DROP FUNCTION IF EXISTS numTrans3;

Triggers

Un trigger és un objecte emmagatzemat a la base de dades que està associat amb una taula i que s’activa quan ocorre un esdeveniment sobre la taula.

Els esdeveniments que poden ocórrer sobre la taula són:

• INSERT. El trigger s’activa quan s’insereix una nova fila sobre la taula associada.
• UPDATE. El trigger s’activa quan s’actualitza una fila sobre la taula associada.
• DELETE. El trigger s’activa quan s’elimina una fila sobre la taula associada.

El trigger es pot activar o disparar abans (BEFORE) de l’esdeveniment o després (AFTER) de l’esdeveniment.

Com a exemples vam crear dos triggers associats a la taula gens de la nostra base de dades:

Un trigger amb el nom de trig_check_gens_before_insert que s’associa a la taula gens. S’activa abans d’una operació d’inserció. Si el nou valor del camp inici que es vol inserir és negatiu, es guarda com a 0. Si el nou valor del camp final que es vol inserir és menor que el valor del camp inici, es guarda el valor del camp inici.

Un trigger amb el nom de trig_check_gens_before_update que s’associa a la taula gens. S’activa abans d’una operació de modificació. Si el nou valor del camp inici que es vol modificar és negatiu, es guarda com a 0. Si el valor del camp final del registre que es vol modificar és menor que el nou valor que volem actualitzar del camp inici es guarda com a 1.

Creem el trigger trig_check_gens_before_insert

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

En executar aquest codi de creació del trigger, trig_check_gens_before_insert queda emmagatzemat a la nostra base de dades, i només actuarà, s’activarà quan l’usuari executi una sentència d’inserció, per exemple:

Canviem el delimitador:

DELIMITER ;

Realitzem operacions d’inserció a la taula gens perquè es dispari el trigger trig_check_gens_before_insert:

INSERT INTO gens (nom, cromosoma, cadena, inici, final, proteina, especie) VALUES ('WASH7P', 'chrX', '+', -2527305, 2575270,'NR_033380', 'H. Sapiens');

INSERT INTO gens (nom, cromosoma, cadena, inici, final, proteina, especie) VALUES ('WASH7P2', 'chrX', '+', 252730599, 2575270,'NR_033381', 'H. Sapiens');

La variable composta NEW que utilitzem en el trigger emmagatzema tots els valors que inserim en cada operació INSERT. D’aquesta manera podem utilitzar-la en el trigger sense conèixer a priori quins valors s’hi inseriran.

En els nostres exemples la variable NEW.inici conté en el primer INSERT el valor -2527305 i en el segon INSERT la variable NEW.inici conté el valor 252730599 i la variable NEW.final conté el valor 2575270

En el primer INSERT es compleix NEW.inici < 0, aquesta condició dispara el trigger i en el camp inici es guarda el valor 0.

En el segon INSERT es compleix NEW.inici > NEW.final, aquesta condició dispara el trigger i en el camp final es guarda el valor 252730599.

Ara creem el trigger trig_check_gens_before_update:

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

En executar aquest codi de creació del trigger, trig_check_gens_before_update queda emmagatzemat a la nostra base de dades, i només actuarà, s’activarà quan l’usuari executi una sentència d’actualització, per exemple:

Canviem el delimitador:

DELIMITER ;

Realitzem operacions de modificació a la taula gens perquè es dispari el trigger trig_check_gens_before_update:

UPDATE gens SET inici = 228748314 WHERE nom = 'MYC';

UPDATE gens SET inici = -47643 WHERE nom = 'cbt';

En aquest cas, la variable composta NEW que utilitzem en el trigger emmagatzema el nou valor que volem actualitzar en la sentència UPDATE. En el primer UPDATE la variable NEW.inici conté el valor 228748314 i en el segon UPDATE la variable NEW.inici conté el valor -47643.

En canvi, la variable composta OLD emmagatzema tots els valors vells ja emmagatzemats a la taula del registre que es vol actualitzar. En el primer UPDATE, la variable OLD.final conté el valor emmagatzemat en el camp final del registre amb clau primària MYC en la taula gens, i en el segon UPDATE, la variable OLD.final conté el valor emmagatzemat en el camp final del registre amb clau primària cbt.

En el primer UPDATE es compleix NEW.inici > OLD.final, aquesta condició dispara el trigger i en el camp inici es guarda el valor 1.

En el segon UPDATE es compleix NEW.inici < 0 , aquesta condició dispara el trigger i en el camp inici es guarda el valor 0.

En les operacions DELETE només s’utilitza la variable composta OLD, capaç d’emmagatzemar tots els valors del registre que es vol eliminar i accedir-hi, amb el format OLD.nom_camp.