<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # Funcions <div class="text-end fit-content ms-auto my-3 mt-auto pt-3"> <p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p> <p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p> <p><strong>Curs:</strong> 2023/2024</p> </div> <div> <p class="fw-bold mb-0">Llicència: BY-NC-SA</p> <p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p> <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank"> <img class="mx-auto" src="/itb/images/license.png" alt="Licence"/> </a> </div><!--license--> </div><!--cover--> </div><!--page--> {:toc} ## Introducció Les __funcions__ són un conjunt de sentències SQL que es compilen i s'emmagatzemen en el SGBD per a ser executades posteriorment. Són molt similars als procediments emmagatzemats, però tenen algunes diferències: - Les funcions sempre retornen un valor. - Les funcions s'utilitzen en sentències SQL en compte d'utilitzar la sentència `CALL`. ::: docs Documentació oficial sobre funcions: - MariaDB: https://mariadb.com/kb/en/create-function/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/create-function.html ::: ## Creació de funcions Les funcions es creen amb la sentència `CREATE FUNCTION` i són definides amb la següent sintaxi: ```sql CREATE [OR REPLACE] [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]]) RETURNS type [characteristic ...] RETURN func_body func_parameter: [ IN | OUT | INOUT | IN OUT ] param_name type type: Any valid MySQL data type func_body: Valid SQL procedure statement ``` - `AGGREGATE`: Indica que la funció és una funció d'agregació. - `func_name`: Nom de la funció. - `func_parameter`: Paràmetres de la funció. (Idèntics als procediments emmagatzemats) - `RETURNS type`: Tipus de dades que retorna la funció. - `characteristic`: Característiques de la funció. Consultar la documentació oficial per a més informació. - `DETERMINISTIC` o `NOT DETERMINISTIC`: Indica si la funció retorna el mateix valor per a les mateixes entrades. - `RETURN func_body`: Cos de la funció. (Idèntic als procediments emmagatzemats) ::: info "Dades d'exemple" ```sql CREATE DATABASE IF NOT EXISTS banc; USE banc; CREATE TABLE IF NOT EXISTS compte_bancari ( id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(100) NOT NULL, saldo DECIMAL(10, 2) NOT NULL ); INSERT INTO compte_bancari (nom, saldo) VALUES ('Pep', 1000), ('Maria', 2000), ('Mar', 3000); ``` ::: ::: example Funció que calcula l'interès generat per un compte bancari donat un saldo inicial i una taxa d'interès. ```sql DELIMITER // CREATE FUNCTION interes( saldo DECIMAL(10, 2), taxa DECIMAL(5, 2) ) RETURNS DECIMAL(10, 2) DETERMINISTIC BEGIN RETURN saldo * taxa / 100; END // DELIMITER ; ``` ::: ## Ús de funcions Les funcions es poden utilitzar en qualsevol sentència SQL de la mateixa manera que es fa amb les funcions integrades de MySQL. ::: example ```sql SELECT nom, saldo, interes(saldo, 5) AS interes FROM compte_bancari; +-------+-------+---------+ | nom | saldo | interes | +-------+-------+---------+ | Pep | 1000 | 50.00 | | Maria | 2000 | 100.00 | | Mar | 3000 | 150.00 | +-------+-------+---------+ ``` ::: ## `DETERMINISTIC` i `NOT DETERMINISTIC` Les funcions poden ser declarades com a `DETERMINISTIC` o `NOT DETERMINISTIC`. Per defecte, les funcions són `NOT DETERMINISTIC`. Una funció és `DETERMINISTIC` si sempre retorna el mateix valor per a els mateixoa valors d'entrada. Si el resultat pot ser afectat per valors de la base de dades, variables, nombres aleatoris o funcions no determinístiques (com `NOW()` o `CURDATE()`), la funció és `NOT DETERMINISTIC`. A més, existeixen altres característiques que es poden afegir a la funció: - `NO SQL`: Indica que la funció no accedeix a la base de dades. - `CONTAINS SQL`: Indica que la funció conté sentències SQL que no llegeixen ni modifiquen dades. (Valor per defecte) - `READS SQL DATA`: Indica que la funció llegeix dades de la base de dades. - `MODIFIES SQL DATA`: Indica que la funció modifica dades de la base de dades. ::: note A MySQL 8.0 és necessari afegir una de les característiques `{DETERMINISTIC | NO SQL | READS SQL DATA}` a la funció. Si no s'afegeix cap característica, MySQL 8.0 mostrarà l'error: ``` 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) ``` Més informació: https://stackoverflow.com/questions/26015160/deterministic-no-sql-or-reads-sql-data-in-its-declaration-and-binary-logging-i ::: ::: example "DETERMINISTIC" La funció `interes` és `DETERMINISTIC` ja que sempre retorna el mateix valor per al mateixos valors d'entrada. ::: ::: example "NOT DETERMINISTIC" Una funció que retorna l'id del compte bancari amb més saldo. És `NOT DETERMINISTIC` ja que el resultat pot variar si el saldo dels comptes bancaris canvia. ```sql DELIMITER // CREATE FUNCTION compte_mes_saldo() RETURNS INT NOT DETERMINISTIC READS SQL DATA BEGIN RETURN (SELECT id FROM compte_bancari ORDER BY saldo DESC LIMIT 1); END // DELIMITER ; ``` ::: ## `AGGREGATE` Les funcions d'agregació són funcions que operen sobre un conjunt de valors i retornen un únic valor. Per a declarar una funció com a funció d'agregació, s'ha d'afegir la paraula clau `AGGREGATE` a la sentència `CREATE FUNCTION`. ::: warning A MySQL 8.0 no permet la creació de funcions d'agregació amb la sentència `CREATE AGGREGATE FUNCTION`. ::: ::: docs Documentació oficial sobre funcions d'agregació: - MariaDB: https://mariadb.com/kb/en/stored-aggregate-functions/ ::: La sintaxi per a declarar una funció d'agregació és la següent: ```sql CREATE AGGREGATE FUNCTION function_name (parameters) RETURNS return_type BEGIN -- All types of declarations DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN -- All instructions after the end of the loop RETURN return val; END; LOOP FETCH GROUP NEXT ROW; -- fetches next row from table other instructions END LOOP; END; ``` L'estrucura que segueix és: - Primer, es defineixen les variables locals que s'utilitzaran. - Després, es declara un `HANDLER` per a capturar l'error `NOT FOUND`, que es produeix quan no hi ha més files per a processar. En aquest handler, es realitzen les operacions finals i es retorna el valor de la funció. - A continuació, es declara un bucle `LOOP` que recorrerà totes les files del conjunt de valors. :::: example ::: info Aquest exemple s'ha realitzat amb MariaDB. ::: - Funció d'agregació que calcula el `N` valor més gran d'un conjunt de valors. ```sql DELIMITER // CREATE AGGREGATE FUNCTION nth_max ( x INT, N INT ) RETURNS INT DETERMINISTIC BEGIN DECLARE max_val DOUBLE; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET max_val = NULL; -- Resta 1 a N per a obtenir l'índex correcte en la clàusula LIMIT SET N = N - 1; -- Obté el nth valor més gran SELECT DISTINCT(a) INTO max_val FROM tt ORDER BY a DESC LIMIT N, 1; -- Elimina la taula temporal DROP TEMPORARY TABLE tt; -- Retorna el valor RETURN max_val; END; -- Crea una taua temporal per a emmagatzemar els valors CREATE TEMPORARY TABLE tt (a INT); LOOP -- Obté els valors i els insereix a la taula temporal FETCH GROUP NEXT ROW; INSERT INTO tt VALUES (x); END LOOP; END // DELIMITER ; ``` Utilització de la funció d'agregació `nth_max`: ```sql select distinct(saldo) saldo from compte_bancari order by saldo desc; +-------+ | saldo | +-------+ | 3000 | | 2000 | | 1000 | +-------+ select nth_max(saldo, 1) as primer_max from compte_bancari; +------------+ | primer_max | +------------+ | 3000 | +------------+ select nth_max(saldo, 2) as segon_max from compte_bancari; +-----------+ | segon_max | +-----------+ | 2000 | +-----------+ select nth_max(saldo, 3) as tercer_max from compte_bancari; +------------+ | tercer_max | +------------+ | 1000 | +------------+ ``` ::::