Funcions d'usuari (User-Defined Functions)

Diferència entre funcions i procediments

Tant les funcions com els procediments encapsulen lògica al servidor, però hi ha diferències importants que determinen quan usar cadascun:

Característica	Procediment	Funció
Retorna un valor	No (només via OUT)	Sí, sempre
Usable en SELECT	No	Sí
Usable en WHERE	No	Sí (amb cautela)
Pot modificar dades	Sí (INSERT/UPDATE/DELETE)	Depèn del motor*
Gestió de transaccions	Sí (COMMIT/ROLLBACK)	Limitada
Cridat amb CALL/EXEC	Sí	No (és una expressió)
Retorna múltiples files	Via SELECT intern	Funcions de taula

*En PostgreSQL les funcions poden modificar dades; en MySQL, SQL Server i Oracle les funcions escalars no haurien de fer-ho per raons de predictibilitat i rendiment.

Quan usar cadascun

• Useu funcions quan necessiteu un valor que formi part d'una expressió SQL: calcular un preu amb IVA en un SELECT, filtrar per un valor derivat en un WHERE, etc.
• Useu procediments per a operacions complexes de negoci que impliquen múltiples passos, transaccions i efectes secundaris (escriure a taules, enviar notificacions, etc.).

---

Tipus de funcions

Funcions escalars

Reben zero o més paràmetres i retornen un valor únic: un número, una cadena de text, una data, un booleà. S'usen com qualsevol expressió SQL.

-- Exemple conceptual
SELECT nom, preu, calcular_preu_iva(preu, 21) AS preu_iva
FROM productes;

Funcions de taula (Table-Valued Functions)

Retornen un conjunt de files, com si fossin una taula virtual. Es poden usar en el FROM d'una consulta, igual que una vista, però accepten paràmetres.

-- Exemple conceptual
SELECT * FROM productes_per_categoria('electrònica')
WHERE preu < 500;

---

Creació de funcions escalars

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Funció escalar: retorna el preu amb IVA aplicat
CREATE OR REPLACE FUNCTION calcular_preu_iva(
    p_preu     NUMERIC,
    p_iva_pct  NUMERIC DEFAULT 21
)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE  -- El resultat depèn únicament dels paràmetres d'entrada
AS $$
BEGIN
    IF p_preu < 0 THEN
        RAISE EXCEPTION 'El preu no pot ser negatiu: %', p_preu;
    END IF;
    RETURN ROUND(p_preu * (1 + p_iva_pct / 100), 2);
END;
$$;

-- Ús en una consulta:
SELECT
    nom,
    preu                          AS preu_base,
    calcular_preu_iva(preu)       AS preu_iva_21,
    calcular_preu_iva(preu, 10)   AS preu_iva_10
FROM productes;

DELIMITER //

CREATE FUNCTION calcular_preu_iva(
    p_preu    DECIMAL(10,2),
    p_iva_pct DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC  -- Mateixos inputs → mateix output (sense efectes aleatoris)
NO SQL         -- O READS SQL DATA si fa SELECT
BEGIN
    IF p_preu < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'El preu no pot ser negatiu';
    END IF;
    RETURN ROUND(p_preu * (1 + p_iva_pct / 100), 2);
END //

DELIMITER ;

-- Ús:
SELECT
    nom,
    preu,
    calcular_preu_iva(preu, 21) AS preu_iva
FROM productes;

CREATE OR ALTER FUNCTION calcular_preu_iva(
    @p_preu    DECIMAL(10,2),
    @p_iva_pct DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    -- Les funcions escalars de T-SQL no poden llançar excepcions
    -- gestionem-ho retornant NULL en cas d'error
    IF @p_preu < 0
        RETURN NULL;
    RETURN ROUND(@p_preu * (1 + @p_iva_pct / 100), 2);
END;
GO

-- Ús (cal el prefix de l'esquema: dbo.nomFunció):
SELECT
    nom,
    preu,
    dbo.calcular_preu_iva(preu, 21) AS preu_iva
FROM productes;

CREATE OR REPLACE FUNCTION calcular_preu_iva(
    p_preu    IN NUMBER,
    p_iva_pct IN NUMBER DEFAULT 21
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
    IF p_preu < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'El preu no pot ser negatiu');
    END IF;
    RETURN ROUND(p_preu * (1 + p_iva_pct / 100), 2);
END calcular_preu_iva;
/

-- Ús:
SELECT
    nom,
    preu,
    calcular_preu_iva(preu)     AS preu_iva_21,
    calcular_preu_iva(preu, 10) AS preu_iva_10
FROM productes;

---

DETERMINISTIC / IMMUTABLE / VOLATILE

Informar el SGBD sobre el comportament de la funció li permet optimitzar quan i quantes vegades l'executa:

Qualificador	Motor	Significat
IMMUTABLE	PostgreSQL	Sempre retorna el mateix resultat per als mateixos inputs. No accedeix a la BD. Pot ser inlinada i avaluada en temps de planificació.
STABLE	PostgreSQL	Retorna el mateix resultat dins d'una mateixa consulta. Pot accedir a taules en mode lectura.
VOLATILE	PostgreSQL	Pot retornar valors diferents en crides successives (per defecte).
DETERMINISTIC	MySQL, Oracle	Equivalent a IMMUTABLE: mateixos inputs → mateix output.
NOT DETERMINISTIC	MySQL	Pot retornar resultats diferents (per defecte).

Impacte en l'índex

A PostgreSQL, podeu crear un índex sobre una funció IMMUTABLE:

CREATE INDEX idx_preu_iva ON productes (calcular_preu_iva(preu));

Això permet consultes del tipus WHERE calcular_preu_iva(preu) < 100 usant l'índex. Amb funcions VOLATILE no és possible.

---

Creació de funcions de taula

Les funcions de taula retornen un conjunt de files i es poden usar al FROM com si fossin una taula o vista.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- RETURNS TABLE(...) defineix l'estructura de retorn
CREATE OR REPLACE FUNCTION productes_per_categoria(
    p_categoria TEXT,
    p_preu_max  NUMERIC DEFAULT NULL
)
RETURNS TABLE(
    id        INT,
    nom       TEXT,
    preu      NUMERIC,
    preu_iva  NUMERIC,
    estoc     INT
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.id,
        p.nom,
        p.preu,
        calcular_preu_iva(p.preu),
        p.estoc
    FROM productes p
    JOIN categories c ON c.id = p.categoria_id
    WHERE c.nom = p_categoria
      AND (p_preu_max IS NULL OR p.preu <= p_preu_max)
    ORDER BY p.nom;
END;
$$;

-- Ús:
SELECT * FROM productes_per_categoria('electrònica', 500);
SELECT nom, preu_iva FROM productes_per_categoria('llibres');

-- MySQL no suporta funcions de taula de manera directa.
-- L'alternativa és usar una vista parametritzada amb variables de sessió
-- o un procediment que retorna un SELECT.

-- Alternativa 1: Procediment que retorna un result set
DELIMITER //

CREATE PROCEDURE productes_per_categoria(
    IN p_categoria VARCHAR(100),
    IN p_preu_max  DECIMAL(10,2)
)
BEGIN
    SELECT
        p.id,
        p.nom,
        p.preu,
        calcular_preu_iva(p.preu, 21) AS preu_iva,
        p.estoc
    FROM productes p
    JOIN categories c ON c.id = p.categoria_id
    WHERE c.nom = p_categoria
      AND (p_preu_max IS NULL OR p.preu <= p_preu_max)
    ORDER BY p.nom;
END //

DELIMITER ;

-- Ús:
CALL productes_per_categoria('electrònica', 500);

-- Inline Table-Valued Function (iTVF): la més eficient
-- El cos és un únic RETURN amb una SELECT (sense BEGIN/END)
CREATE OR ALTER FUNCTION productes_per_categoria(
    @p_categoria NVARCHAR(100),
    @p_preu_max  DECIMAL(10,2) = NULL
)
RETURNS TABLE
AS
RETURN (
    SELECT
        p.id,
        p.nom,
        p.preu,
        dbo.calcular_preu_iva(p.preu, 21) AS preu_iva,
        p.estoc
    FROM productes p
    JOIN categories c ON c.id = p.categoria_id
    WHERE c.nom = @p_categoria
      AND (@p_preu_max IS NULL OR p.preu <= @p_preu_max)
);
GO

-- Ús:
SELECT * FROM dbo.productes_per_categoria('electrònica', 500);

-- Es pot fer JOIN amb la funció de taula:
SELECT c.nom AS client, p.nom AS producte
FROM clients c
CROSS APPLY dbo.productes_per_categoria(c.categoria_preferida, NULL) p;

-- Oracle requereix un TYPE per definir l'estructura de retorn
-- i un TYPE TABLE d'aquell type.

-- Pas 1: Definir el tipus fila
CREATE OR REPLACE TYPE t_producte AS OBJECT (
    id       NUMBER,
    nom      VARCHAR2(200),
    preu     NUMBER,
    preu_iva NUMBER,
    estoc    NUMBER
);
/

-- Pas 2: Definir el tipus taula
CREATE OR REPLACE TYPE t_productes_tab AS TABLE OF t_producte;
/

-- Pas 3: Crear la funció PIPELINED
CREATE OR REPLACE FUNCTION productes_per_categoria(
    p_categoria IN VARCHAR2,
    p_preu_max  IN NUMBER DEFAULT NULL
)
RETURN t_productes_tab PIPELINED
AS
BEGIN
    FOR rec IN (
        SELECT p.id, p.nom, p.preu, p.estoc
        FROM productes p
        JOIN categories c ON c.id = p.categoria_id
        WHERE c.nom = p_categoria
          AND (p_preu_max IS NULL OR p.preu <= p_preu_max)
        ORDER BY p.nom
    ) LOOP
        PIPE ROW(t_producte(
            rec.id,
            rec.nom,
            rec.preu,
            calcular_preu_iva(rec.preu),
            rec.estoc
        ));
    END LOOP;
END productes_per_categoria;
/

-- Ús:
SELECT * FROM TABLE(productes_per_categoria('electrònica', 500));

---

Ús de funcions en consultes

Les funcions escalars s'integren de manera natural en qualsevol part d'una consulta SQL:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- En SELECT (columna calculada)
SELECT
    nom,
    preu,
    calcular_preu_iva(preu, 21)   AS preu_iva_21,
    calcular_preu_iva(preu, 10)   AS preu_iva_10
FROM productes;

-- En WHERE (filtre)
SELECT nom, preu
FROM productes
WHERE calcular_preu_iva(preu, 21) BETWEEN 100 AND 500;

-- En ORDER BY
SELECT nom, preu
FROM productes
ORDER BY calcular_preu_iva(preu, 21) DESC;

-- En una expressió de GROUP BY / HAVING
SELECT
    categoria_id,
    AVG(calcular_preu_iva(preu, 21)) AS preu_iva_mig
FROM productes
GROUP BY categoria_id
HAVING AVG(calcular_preu_iva(preu, 21)) > 200;

-- En SELECT
SELECT
    nom,
    preu,
    calcular_preu_iva(preu, 21) AS preu_iva
FROM productes;

-- En WHERE
SELECT nom, preu
FROM productes
WHERE calcular_preu_iva(preu, 21) < 100;

-- En una vista (útil per reutilitzar la lògica)
CREATE VIEW v_productes_iva AS
SELECT
    id,
    nom,
    preu,
    calcular_preu_iva(preu, 21) AS preu_iva
FROM productes;

-- En SQL Server cal el prefix de l'esquema (dbo.)
SELECT
    nom,
    preu,
    dbo.calcular_preu_iva(preu, 21) AS preu_iva
FROM productes;

-- En WHERE
SELECT nom, preu
FROM productes
WHERE dbo.calcular_preu_iva(preu, 21) < 100;

-- Columna computada persistent en una taula
ALTER TABLE productes
ADD preu_iva AS dbo.calcular_preu_iva(preu, 21) PERSISTED;

-- En SELECT
SELECT
    nom,
    preu,
    calcular_preu_iva(preu, 21) AS preu_iva
FROM productes;

-- En WHERE
SELECT nom, preu
FROM productes
WHERE calcular_preu_iva(preu, 21) < 100;

-- En una columna virtual de taula
ALTER TABLE productes
ADD preu_iva NUMBER GENERATED ALWAYS AS (ROUND(preu * 1.21, 2)) VIRTUAL;

---

Advertència sobre funcions en WHERE i rendiment

Funcions en WHERE i ús d'índexos

Quan apliqueu una funció sobre una columna indexada en un WHERE, el SGBD generalment no pot usar l'índex sobre aquella columna:

-- Aquesta consulta NO usarà l'índex sobre la columna 'preu'
WHERE calcular_preu_iva(preu, 21) < 100

-- Equivalent reformulat que SÍ pot usar l'índex:
WHERE preu < 100 / 1.21   -- (si la funció és prou simple)

Solucions:

1. Reformular la condició per operar sobre la columna pura (sense funció), quan sigui possible.
2. Crear un índex funcional (PostgreSQL, Oracle, MySQL 5.7+):

   -- PostgreSQL / MySQL (la funció ha de ser IMMUTABLE/DETERMINISTIC)
   CREATE INDEX idx_preu_iva ON productes ((calcular_preu_iva(preu, 21)));
   

3. Columnes computades persistents (SQL Server):

   -- L'índex es crea sobre la columna computada, no sobre la funció directament
   CREATE INDEX idx_preu_iva ON productes (preu_iva);
   

Sempre reviseu el pla d'execució (EXPLAIN) després d'afegir funcions als predicats de filtre.

---

Inspecció de funcions existents

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Llistar funcions de l'esquema públic
SELECT routine_name, routine_type, data_type
FROM information_schema.routines
WHERE routine_schema = 'public'
  AND routine_type = 'FUNCTION';

-- Veure el codi font d'una funció
SELECT pg_get_functiondef('calcular_preu_iva(numeric, numeric)'::regprocedure);

-- Llistar funcions
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- Veure el codi font
SHOW CREATE FUNCTION calcular_preu_iva;

-- Llistar funcions
SELECT name, type_desc
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF');  -- FN: scalar, IF: inline TVF, TF: multi-statement TVF

-- Veure el codi font
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.calcular_preu_iva'));

-- Llistar funcions de l'usuari actual
SELECT object_name, status, last_ddl_time
FROM user_objects
WHERE object_type = 'FUNCTION';

-- Veure el codi font
SELECT text
FROM user_source
WHERE name = 'CALCULAR_PREU_IVA'
ORDER BY line;

---

Miniactivitat — AC0404

Miniactivitat — AC0404 · Funcions de negoci

Teniu la BD d'una botiga amb les taules: productes(id, nom, preu, categoria_id, estoc), categories(id, nom, iva_pct), clients(id, nom, tipus).

Tasca 1 — Funció escalar: Creeu la funció preu_client(p_producte_id, p_client_id) que retorni el preu final per a un client concret, aplicant: - L'IVA de la categoria del producte. - Un descompte del 5% si el client és de tipus 'premium'. - Un descompte addicional del 2% si el client és 'premium' i compra un producte de la categoria 'electrònica'.

Tasca 2 — Funció de taula: Creeu la funció recomanacions_client(p_client_id) que retorni els 5 productes de la categoria preferida del client (la que ha comprat més vegades) que tinguin estoc > 0, ordenats per preu ascendent, amb el preu final per a aquell client calculat amb la funció de la Tasca 1.

Tasca 3 — Integració: Escriviu una consulta que mostri, per a cada client 'premium', les seves 5 recomanacions personalitzades en una sola consulta usant la funció de taula.

Entrega: Arxiu SQL amb les funcions, la consulta d'integració i un EXPLAIN de la consulta de la Tasca 3 comentant si s'usen índexos.