DAM - DAW - MP 0484 Bases de Dades - NF5

Funcions

Les funcions es creen amb la comanda CREATE FUNCTION. A diferència dels procediments, retornen un valor d'un tipus especificat.

CREATE FUNCTION permet donar un nom a la funció i definir una sèrie de paràmetres que podrem utilitzar dins el codi com si fossin variables locals.

Aquests paràmetres, en el cas de les funcions, només poden ser d’entrada (IN).

Una funció sempre ha de retornar un valor de sortida.

CREATE FUNCTION

Sintaxis de CREATE FUNCTION.

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [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

Characteristic:
    LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'

CREATE FUNCTION crea una funció emmagatzemada.

Quan s’invoca des d’una altra base, diferent a la que pertany, s’executa un USE baseDeDades implícit.

Les funcions les podem utilitzar en sentències SQL de qualsevol tipus: DML, assignacions, com part d’una expressió, etc.

Les funcions només admeten paràmetres d’entrada.

En cas de tenir paràmetres de sortida o de entrada i sortida només es podrà utilitzar la funció en sentències SET, a partir de la versió 10.8.

DETERMINISTIC

Una funció és DETERMINISTIC quan, donats uns paràmetres d'entrada determinats, sempre produeix el mateix resultat.

Si el resultat es pot veure afectat per dades emmagatzemades, variables del servidor, nombres aleatoris o qualsevol valor que no s'ha passat explícitament, aleshores la funció és NOT DETERMINISTIC.

A més, una funció és NOT DETERMINISTIC si utilitza funcions no deterministes com NOW() o CURRENT_TIMESTAMP().

Exemple DETERMINISTIC

DELIMITER //
CREATE OR REPLACE
    FUNCTION Maxim(pUn INT, pDos INT)
    RETURNS INT DETERMINISTIC
BEGIN
    DECLARE v_major INT;
    IF pUn > pDos THEN
       SET v_major = pUn;
    ELSE
        SET v_major = pDos;
    END IF;
    RETURN v_major;
END//
DELIMITER ;
SELECT Maxim(23,45);
SELECT Maxim(45,23);

Exemple NOT DETERMINISTIC

DELIMITER //
CREATE OR REPLACE
    FUNCTION QuantsEmpleatsDepartament
    (p_dept_id TYPE OF departments.department_id)
    RETURNS INT
    NOT DETERMINISTIC
BEGIN
    DECLARE v_quants INT;
    SELECT COUNT(*) INTO v_quants
        FROM employees
        WHERE DEPARTMENT_ID = p_dept_id;
    RETURN v_quants;
END//
DELIMITER ;
SELECT QuantsEmpleatsDepartament(50);
SELECT QuantsEmpleatsDepartament(190);
SELECT QuantsEmpleatsDepartament(100);
-- la darrera SELECT dona 0 tot i no existir el departament

Exemple amb SIGNAL

DELIMITER //
CREATE OR REPLACE
    FUNCTION QuantsEmpleatsDepartament
    (p_dept_id TYPE OF departments.department_id)
    RETURNS INT READS SQL DATA NOT DETERMINISTIC
BEGIN
    DECLARE v_quants INT;
    DECLARE v_dept_id TYPE OF departments.department_id;
    DECLARE v_missatge VARCHAR(50);
    SELECT department_id, COUNT(employee_id) INTO v_dept_id, v_quants
        FROM employees RIGHT JOIN departments USING (department_id)
        WHERE department_id = p_dept_id
        GROUP BY department_id;
    IF v_dept_id IS NULL THEN
        SET v_missatge = CONCAT_WS(' ','Departament',p_dept_id,'no trobat');
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT= v_missatge;
    END IF;
    RETURN v_quants;
END//
DELIMITER ;
SELECT QuantsEmpleatsDepartament(50);
SELECT QuantsEmpleatsDepartament(190);
SELECT QuantsEmpleatsDepartament(100); -- dona l'error del SIGNAL

ALTER FUNCTION

ALTER FUNCTION s'utilitza per a modificar alguna de les característiques d’un procediment. No permet modificar ni els paràmetres ni el codi. Per a fer aquests canvis tenim la comanda CREATE OR REPLACE FUNCTION.

ALTER FUNCTION Maxim
    CONTAINS SQL
    SQL SECURITY INVOKER 
    COMMENT 'Retorna el valor més gran de dos passats';

Diccionari de dades

select * from information_schema.routines 
where routine_name = 'QuantsEmpleatsDepartament';

Name	Value
SPECIFIC_NAME	QuantsEmpleatsDepartament
ROUTINE_CATALOG	def
ROUTINE_SCHEMA	empresa
ROUTINE_NAME	QuantsEmpleatsDepartament
ROUTINE_TYPE	FUNCTION
DATA_TYPE	int
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DTD_IDENTIFIER	int(11)
***	***
ROUTINE_BODY	SQL
***	***
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	YES
SQL_DATA_ACCESS	READS SQL DATA
***	***
SECURITY_TYPE	DEFINER
CREATED	AAAA-MM-DD HH:mm :SS
LAST_ALTERED	AAAA-MM-DD HH:mm :SS
***	***
DEFINER	usuari@localhost
***	***