Funcions
Llic猫ncia: CC BY-NC-SA 4.0
(Reconeixement - NoComercial - CompartirIgual) 馃叚
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.
Documentaci贸
Documentaci贸 oficial sobre funcions:
Creaci贸 de funcions
Les funcions es creen amb la sent猫ncia CREATE FUNCTION
i s贸n definides amb la seg眉ent sintaxi:
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)
Dades d'exemple
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);
Exemple
Funci贸 que calcula l'inter猫s generat per un compte bancari
donat un saldo inicial i una taxa d'inter猫s.
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.
Exemple
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.
DETERMINISTIC
La funci贸 interes 茅s DETERMINISTIC ja que sempre retorna el mateix valor
per al mateixos valors d'entrada.
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.
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.
Advert猫ncia
A MySQL 8.0 no permet la creaci贸 de funcions d'agregaci贸 amb la sent猫ncia CREATE AGGREGATE FUNCTION.
Documentaci贸
Documentaci贸 oficial sobre funcions d'agregaci贸:
La sintaxi per a declarar una funci贸 d'agregaci贸 茅s la seg眉ent:
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;
- 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.
Exemple
Informaci贸
Aquest exemple s'ha realitzat amb MariaDB.
- Funci贸 d'agregaci贸 que calcula el
N valor m茅s gran d'un conjunt de valors.
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:
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 |
+------------+
馃搶 Aquest document pot quedar desactualitzat despr茅s d鈥檌mprimir-lo. Pots consultar la versi贸 m茅s recent
a la p脿gina web.
馃尶 Abans d鈥檌mprimir aquest document, considera si 茅s realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
