Salta el contingut

2. Tipus i paràmetres dels procediments emmagatzemats

2.1. Tipus de rutines

Hi ha dos tipus de rutines:

  • Procediments (procedures)
  • Funcions (functions)

Un procediment és un conjunt de tasques o accions que no retornen res a l'usuari; en canvi, les funcions retornen un valor. Igual que en qualsevol llenguatge de programació, la crida també serà diferent, ja que les funcions han d'arreplegar eixe valor.

2.1.1. Procediments

Veiem les parts més comunes amb un exemple. Suposem que tenim una base de dades amb una taula d'articles i volem un procediment per a que incremente el preu d'un article.

El delimitador

Com el punt i coma (;) executa ja la sentència, abans de crear els procediments (o funcions) hem de dir que el delimitador de sentències és un altre (//).

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
DELIMITER //

CREATE PROCEDURE incr_preu (art CHAR(10), incr FLOAT)
BEGIN
    DECLARE q INT;
    SET @trobat = FALSE;
    SELECT COUNT(*) INTO q
    FROM articles
    WHERE codi = art;
    IF (q = 1) THEN
        BEGIN
            SET @trobat = TRUE;
            UPDATE articles SET preu = preu + incr WHERE codi = art;
        END;
    ELSE
        SET @trobat = FALSE;
    END IF;
END //

DELIMITER ;

Parts del procediment

  • CREATE PROCEDURE per a crear el procediment i BEGIN i END per a delimitar el cos.
  • Nom del procediment: incr_preu.
  • Definició de cada paràmetre: nom i tipus → (art CHAR(10), incr FLOAT).
  • DECLARE per a declarar variables locals.
  • SET per a assignar valors a variables. Les variables amb @ són globals i no s'han de declarar.
  • SELECT ... INTO posa el resultat de la SELECT en una variable.
  • Acabem la sentència amb el nou delimitador: //.
  • Si volem, tornem a dir que el delimitador és ;.

Per a fer la crida a este procediment:

SQL
1
2
CALL incr_preu ('pomes', 0.25);
SELECT @trobat;
  • CALL és la sentència per a invocar l'execució del procediment.
  • SELECT @trobat és per si volem consultar el valor de la variable global modificada pel procediment.

És a dir: el client de la BD només ha de saber el nom del procediment i quins valors ha de passar com a paràmetre. No té per què saber ni el nom de la taula ni els noms dels camps.

Ara bé, com hem vist, si volem consultar si el procediment ha trobat o no l'article, també haurà de conéixer el nom de la variable global que modifica el procediment. Per això serà convenient que, en compte d'un procediment, usem una funció que retorne eixe valor.

Per a esborrar un procediment:

SQL
1
DROP PROCEDURE [IF EXISTS] nomProcediment;

2.1.2. Funcions

Veiem les parts principals amb un altre exemple. En una BD de futbol, on tenim en la taula de partits els gols que ha marcat cada equip en cada partit, volem una funció que ens retorne quants partits ha guanyat cada equip.

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
DELIMITER //

CREATE FUNCTION guanyats(equip VARCHAR(3)) RETURNS INT
BEGIN
    DECLARE q INT;
    SELECT COUNT(*) INTO q
    FROM partits
    WHERE (equipc = equip AND golsc > golsf)
       OR (equipf = equip AND golsf > golsc);
    RETURN q;
END;
//

DELIMITER ;

Parts de la funció

  • CREATE FUNCTION per a crear la funció.
  • El RETURNS és per a indicar el tipus del valor que retorna la funció.
  • El BEGIN-END no caldria si el cos de la funció només tinguera 1 instrucció: el RETURN. Igual amb els procediments.
  • Amb RETURN retornarem al client el valor calculat.

Error 1418

Potser done este error en funcions amb INSERT/UPDATE/DELETE:

Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Possibles solucions:

  • a) Executa en la consola de MySQL: SET GLOBAL log_bin_trust_function_creators = 1;
  • b) Afig açò al fitxer de configuració mysql.ini: log_bin_trust_function_creators = 1;

Per a fer la crida a una funció no es fa amb el CALL. En compte d'això, com la funció retorna un valor, la crida ha d'estar dins d'una expressió que arreplegue eixe valor.

Per tant, la crida es pot donar en diferents contextos (sempre formant part d'una expressió). Exemples de crides:

a) Mostrar els partits guanyats pel Barça:

SQL
1
SELECT guanyats('BAR');

b) Guardar en una variable el valor retornat per a després usar-la com siga:

SQL
1
2
SET @x = guanyats('BAR');
SELECT CONCAT('El Barça ha guanyat ', @x, ' partits');

c) Mostrar (entre altres coses) els partits guanyats per l'equip de cada jugador i/o posar-los en una condició:

SQL
1
2
3
SELECT *, guanyats(jugadors.equip)
FROM jugadors
WHERE guanyats(jugadors.equip) > 10;

d) Incrementar 1000 euros a cada jugador per cada partit que ha guanyat el seu equip:

SQL
1
2
UPDATE jugadors
SET sou = sou + 1000 * guanyats(jugadors.equip);

Per a esborrar una funció:

SQL
1
DROP FUNCTION [IF EXISTS] nomFunció;

2.2. Paràmetres i variables

2.2.1. Declaració de paràmetres

En les funcions tots els paràmetres són d'entrada. Però en la definició dels procediments podem indicar si són d'entrada, d'eixida, o d'entrada i eixida. Posarem davant de cada paràmetre:

  • IN: Paràmetre d'entrada. En el moment de cridar al procediment, s'ha de donar este valor. És l'opció per defecte.
  • OUT: Paràmetre de sortida. Quan es fa la crida al procediment, en eixe paràmetre es passa una variable, sense cap valor en concret (mai una constant). El procediment assignarà un valor a eixe paràmetre, de forma que podrà ser consultat després de fer la crida al procediment. Per exemple:
SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
DELIMITER //

CREATE PROCEDURE quantesRutines (IN bd CHAR(64), OUT qFun INT, OUT qPro INT)
BEGIN
    SELECT COUNT(*) INTO qFun
    FROM mysql.proc
    WHERE db = bd AND type = 'FUNCTION';
    SELECT COUNT(*) INTO qPro
    FROM mysql.proc
    WHERE db = bd AND type = 'PROCEDURE';
END //

CALL quantesRutines('lliga1213', @qf, @qp)//
SELECT @qf AS 'funcions', @qp AS 'procediments'//
  • INOUT: Paràmetre d'entrada i de sortida.

Recorda

Només podem usar paràmetres d'eixida en els procediments (no en les funcions).

Sobre els noms de les variables

  • Si en una SELECT/UPDATE hi ha també variables, en cas d'ambigüitat de noms, MySQL agafa la variable (no la columna). Si volem que agafe la columna cal usar el nom qualificat (taula.columna), o bé posar un nom diferent a la variable (v_art en lloc de art, per exemple).
  • El nom de les variables en MySQL no és case sensitive. És a dir, es considera la mateixa variable: preu, Preu, PREU...

2.2.2. Declaració de variables locals

L'àmbit de visibilitat estarà només entre el BEGIN i END on està declarada la variable.

Sintaxi:

SQL
1
DECLARE nom_var[, ...] tipus [DEFAULT valor];
  • Sempre es declaren just a continuació del BEGIN d'un PE.
  • En cada sentència DECLARE es poden declarar diverses variables, però sempre del mateix tipus i mateix valor inicial (DEFAULT). Si necessitàrem diferents tipus de variable o diferents valors inicials, haurem de posar diferents sentències DECLARE.

Exemples:

SQL
1
2
3
DECLARE edat INT;                  -- Si no posem valor per defecte serà nul.
DECLARE preu, import INT DEFAULT 0;
SET edat = 18;                     -- Assignem un valor a la variable;

2.2.3. Variables globals (variables de sessió)

  • L'àmbit de visibilitat és en qualsevol lloc de la connexió. El valor de les variables globals es guarda mentre està activa la connexió a la BD.
  • És l'única forma d'usar variables des de fora d'un PE.
  • No s'aconsella el seu ús dins dels PE.
  • No es declaren: simplement s'indica el nom de la variable que volem usar amb el signe @ davant: @nom_var.

Exemples:

SQL
1
2
SET @x = 10;
CALL quantUsuaris(@quantitat);

2.2.4. Assignar valor a les variables

  • Amb SET (ja ho hem vist abans):
SQL
1
2
3
4
5
SET nom = 'Pep';
SET naix = 1970;
SET cog = 'Garcia', edat = naix + 45;
SET @comptador = 0;
SET @comptador = @comptador + 1;
  • Amb SELECT ... INTO:
SQL
1
2
3
SELECT nom_alu, cog_alu INTO nom, cog
FROM alumnes
WHERE codi = 5;

Si la SELECT retornara més d'una fila de resultats, donaria error.

  • Invocant una funció o procediment amb paràmetres definits com a OUT o INOUT:
SQL
1
2
CALL quantUsuaris(@a);
-- Suposant que eixe paràmetre està definit com a OUT en el procediment

2.3. Exercicis de procediments i funcions

Exercicis sense sentències de control de flux (BD futbol)

1. Fes la funció maxGolejador que li passes com a paràmetre un equip i retorna el dorsal del golejador d'eixe equip que més gols ha marcat. A continuació fes les següents crides per a:

  • a) Mostrar per pantalla el dorsal que més gols ha marcat del Barça (codi 'bar').
  • b) Mostrar el dorsal que més gols ha marcat de cada equip.
  • c) Mostrar el nom del golejador que més gols ha marcat de cada equip.
  • d) Incrementar 10000 euros als jugadors que més gols han marcat en el seu equip.

2. Fes el procediment marcar, que li passes com a paràmetre un jugador (és a dir: codi d'equip i dorsal) i un partit (és a dir: equip de casa i de fora) i incremente en 1 els gols d'eixe golejador i també que incremente en 1 gol el resultat d'eixe partit. El procediment ha de retornar el nou resultat del partit (gols de casa i gols de fora). A continuació:

  • a) Fes la crida per a dir que ha marcat el dorsal 10 del Barça en el Barça-València (sabent que el codi del Barça és 'bar' i el del València és 'val').
  • b) Mostra per pantalla el resultat del partit (amb els valors retornats abans).