SQL avanzato

Finora SQL è servito solo a leggere. Questo modulo completa il quadro: prima i comandi che modificano i dati, poi il codice che vive dentro il database — procedure, trigger, event — e infine gli strumenti di analisi. Lo schema di riferimento resta quello della clinica: MEDICO, PAZIENTE, VISITA.

Modificare i dati

La parte di SQL che inserisce, aggiorna e cancella si chiama DML (Data Manipulation Language) e conta tre comandi. Le condizioni si scrivono esattamente come nelle interrogazioni: stessa WHERE, stesse subquery, stessi join.

INSERT

-- tutti i campi, nell'ordine dello schema: la lista di attributi si può omettere
INSERT INTO Paziente
VALUES ('PSSLVR65R67G702U', 'Passerotti', 'Elvira', 'F',
        'Pisa', '1965-10-27', 1500);

-- solo alcuni campi: gli altri prendono il valore di default (spesso NULL)
INSERT INTO Visita(Medico, Paziente, Data)
VALUES (16220, 'LPRNTA', CURRENT_DATE - INTERVAL 3 DAY);

I valori possono anche essere ricavati da una query, purché la proiezione sia coerente con lo schema della tabella di destinazione. È il modo standard per archiviare o travasare dati.

-- archivia le visite più vecchie di due anni in una tabella di storico
INSERT INTO VisitaVecchia
  SELECT P.Cognome, M.Cognome, V.Data, M.Specializzazione
  FROM Visita V
       INNER JOIN Medico M ON V.Medico = M.Matricola
       INNER JOIN Paziente P ON V.Paziente = P.CodFiscale
  WHERE YEAR(V.Data) < YEAR(CURRENT_DATE) - 2;

UPDATE

-- rendi mutuate le visite del mese corrente dei pazienti nati prima del 1955
UPDATE Visita
SET Mutuata = 1
WHERE MONTH(Data) = MONTH(CURRENT_DATE)
      AND YEAR(Data) = YEAR(CURRENT_DATE)
      AND Paziente IN (SELECT CodFiscale
                       FROM Paziente
                       WHERE YEAR(DataNascita) < 1955);

DELETE

-- il dottor Ettore Grigi ha lasciato la clinica: via le sue visite
DELETE FROM Visita
WHERE Medico = (SELECT Matricola
                 FROM Medico
                 WHERE Nome = 'Ettore' AND Cognome = 'Grigi');

Attenzione ai vincoli di integrità referenziale: cancellare un medico a cui puntano delle visite fallisce (o si propaga a cascata, a seconda di come è stata definita la chiave esterna). L'ordine delle cancellazioni non è mai casuale: prima le righe "figlie", poi quelle "padre".

Il divieto sulla target table

MySQL non permette di nominare la tabella che si sta modificando dentro il FROM di una subquery nella WHERE. Errore tipico: «You can't specify target table 'MEDICO' for update in FROM clause». Due modi per aggirarlo, entrambi legittimi.

DELETE FROM Medico
WHERE Matricola IN (
    SELECT M1.Matricola
    FROM Medico M1 -- stessa tabella!
    WHERE ...
);

DELETE FROM Medico
WHERE Matricola IN (
  SELECT * FROM (
      SELECT M1.Matricola
      FROM Medico M1
      WHERE ...
  ) AS D
);

La seconda soluzione è il join anticipato: si porta il join direttamente nel comando di modifica, indicando con M1.* quali righe eliminare.

DELETE M1.*
FROM Medico M1
     LEFT OUTER JOIN (
         SELECT DISTINCT V.Medico
         FROM Visita V
         WHERE YEAR(V.Data) = YEAR(CURRENT_DATE)
     ) AS D ON M1.Matricola = D.Medico
WHERE M1.Citta = 'Pisa' AND D.Medico IS NULL;

Stored procedure

Perché mettere codice nel database

Una stored procedure è un programma memorizzato dentro il DBMS e invocato con una chiamata. I motivi per usarle sono tre, e sono tutti pratici:

• prestazioni: l'applicazione invia una chiamata di poche decine di byte invece del testo di una query complessa; il traffico di rete crolla e il carico resta sul server;
• sicurezza: si possono concedere i permessi di eseguire una procedura negando l'accesso diretto alle tabelle; i dati grezzi restano invisibili;
• riuso: la logica sta in un posto solo, non replicata in ogni applicazione che usa il database.

Sintassi e parametri

DROP PROCEDURE IF EXISTS mostra_specializzazioni;
DELIMITER $$
CREATE PROCEDURE mostra_specializzazioni()
BEGIN
    SELECT DISTINCT Specializzazione
    FROM Medico;
END $$
DELIMITER ;

CALL mostra_specializzazioni();

Due dettagli che all'inizio confondono: DELIMITER $$ serve a dire al client che il punto e virgola dentro il corpo non termina il comando; una SELECT dentro una procedura equivale a "stampare a video" il suo risultato.

I parametri sono di tre tipi:

DROP PROCEDURE IF EXISTS pazienti_per_specializzazione;
DELIMITER $$
CREATE PROCEDURE pazienti_per_specializzazione(
        IN _specializzazione VARCHAR(100),
        OUT _totale INT)
BEGIN
    SELECT COUNT(DISTINCT V.Paziente) INTO _totale
    FROM Visita V
         INNER JOIN Medico M ON V.Medico = M.Matricola
    WHERE M.Specializzazione = _specializzazione;
END $$
DELIMITER ;

CALL pazienti_per_specializzazione('Cardiologia', @quanti);
SELECT @quanti;

Variabili

L'assegnamento si fa con SET oppure con SELECT … INTO:

DECLARE visite_mese INT DEFAULT 0;

SELECT COUNT(*) INTO visite_mese
FROM Visita V
WHERE MONTH(V.Data) = MONTH(CURRENT_DATE);

-- equivalente:
SET visite_mese = (SELECT COUNT(*) FROM Visita V
                   WHERE MONTH(V.Data) = MONTH(CURRENT_DATE));

Controllo di flusso

IF condizione THEN
    -- istruzioni
ELSEIF altra_condizione THEN
    -- istruzioni
ELSE
    -- istruzioni
END IF;

WHILE condizione DO
    -- controllata PRIMA di ogni iterazione
END WHILE;

REPEAT
    -- controllata DOPO ogni iterazione: il corpo gira almeno una volta
UNTIL condizione
END REPEAT;

etichetta: LOOP
    -- uscita a carico del programmatore: LEAVE (break) e ITERATE (continue)
END LOOP;

Esiste anche CASE … WHEN … THEN … END CASE;, l'equivalente dello switch. Il punto e virgola dopo END IF, END WHILE e compagnia è obbligatorio.

Cursori e handler

Un cursore scorre il result set di una query una riga alla volta, in avanti. Serve quando su ogni riga va fatta un'elaborazione che SQL da solo non esprime.

DROP PROCEDURE IF EXISTS elenco_pazienti;
DELIMITER $$
CREATE PROCEDURE elenco_pazienti(IN _spec VARCHAR(100), OUT _lista TEXT)
BEGIN
    -- 1. variabili, 2. cursori, 3. handler: in quest'ordine
    DECLARE finito INT DEFAULT 0;
    DECLARE cf VARCHAR(16);

    DECLARE cur CURSOR FOR
        SELECT DISTINCT V.Paziente
        FROM Visita V INNER JOIN Medico M ON V.Medico = M.Matricola
        WHERE M.Specializzazione = _spec;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finito = 1;

    SET _lista = '';
    OPEN cur;

    scan: LOOP
        FETCH cur INTO cf;
        IF finito = 1 THEN
            LEAVE scan;
        END IF;
        SET _lista = CONCAT(_lista, cf, ', ');
    END LOOP scan;

    CLOSE cur;
END $$
DELIMITER ;

L'handler NOT FOUND è la "campanella" che suona quando il FETCH non trova più righe: senza di esso il ciclo non saprebbe quando fermarsi. La variabile finito va controllata subito dopo il fetch, prima di usare i valori prelevati.

Trigger

Un database si dice attivo quando ha una parte reattiva: reagisce da solo ai cambiamenti. Lo strumento è il trigger, un blocco di istruzioni che scatta automaticamente al verificarsi di un evento DML.

Sintassi ed evento

NEW è la riga in arrivo (insert e update), OLD quella che sparisce (update e delete).

Business rule

Molti vincoli non sono esprimibili nello schema: «ogni mese un medico non può superare 10 visite non mutuate», «i pazienti devono essere maggiorenni». Si chiamano vincoli di integrità generici o business rule, e si implementano con un trigger BEFORE che solleva un errore quando la regola verrebbe violata.

DROP TRIGGER IF EXISTS blocca_non_mutuate;
DELIMITER $$
CREATE TRIGGER blocca_non_mutuate
BEFORE INSERT ON Visita FOR EACH ROW
BEGIN
    DECLARE non_mutuate_mese INT DEFAULT 0;

    SELECT COUNT(*) INTO non_mutuate_mese
    FROM Visita V
    WHERE V.Medico = NEW.Medico
          AND V.Mutuata = 0
          AND MONTH(V.Data) = MONTH(NEW.Data)
          AND YEAR(V.Data) = YEAR(NEW.Data);

    IF non_mutuate_mese >= 10 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Visita non inseribile: limite mensile raggiunto';
    END IF;
END $$
DELIMITER ;

SIGNAL solleva un errore e annulla l'operazione che ha fatto scattare il trigger: la INSERT viene abortita e la riga non entra.

Un trigger BEFORE può anche calcolare un valore della riga in arrivo, invece di rifiutarla:

-- il ticket dipende dal reddito annuo del paziente, ma solo se la visita è mutuata
CREATE TRIGGER calcola_ticket
BEFORE INSERT ON Visita FOR EACH ROW
BEGIN
    DECLARE reddito_annuo DECIMAL(10,2);

    IF NEW.Mutuata IS TRUE THEN
        SELECT Reddito * 12 INTO reddito_annuo
        FROM Paziente WHERE CodFiscale = NEW.Paziente;

        IF reddito_annuo BETWEEN 0 AND 14999 THEN
            SET NEW.Ticket = 36.15;
        ELSEIF reddito_annuo BETWEEN 15000 AND 25000 THEN
            SET NEW.Ticket = 45.25;
        ELSE
            SET NEW.Ticket = 50.00;
        END IF;
    ELSE
        SET NEW.Ticket = NULL;
    END IF;
END $$

Gestire le ridondanze

Nella progettazione logica si decide talvolta di tenere un dato derivabile (il totale delle visite di un medico, la data dell'ultima visita di un paziente) per risparmiare letture. Quel dato va mantenuto coerente: è il lavoro tipico di un trigger AFTER.

CREATE TRIGGER aggiorna_ultima_visita
AFTER INSERT ON Visita FOR EACH ROW
    UPDATE Paziente
    SET UltimaVisita = NEW.Data
    WHERE CodFiscale = NEW.Paziente;

Nota il legame fra i moduli: la ridondanza si decide nella progettazione logica facendo i conti sugli accessi, e si implementa qui. Se i conti dicevano che non conviene, questo trigger non va scritto.

Event

Gli event sono programmi eseguiti in base al tempo, non a un'operazione: l'equivalente di un cron job dentro il database. Servono per manutenzioni periodiche, aggiornamenti notturni, archiviazioni.

Schedulazione

-- lo scheduler va attivato una volta sola, a livello di server
SET GLOBAL event_scheduler = ON;

CREATE EVENT aggiorna_totali_visite
ON SCHEDULE EVERY 1 DAY
STARTS '2026-01-01 23:55:00'
DO
    UPDATE Medico
    SET TotaleVisite = TotaleVisite + (
        SELECT COUNT(*)
        FROM Visita V
        WHERE V.Medico = Matricola AND V.Data = CURRENT_DATE);

Le unità disponibili sono SECOND, MINUTE, HOUR, DAY, MONTH, YEAR. Scegliere l'orario non è un dettaglio: un event pesante va messo quando il carico è basso.

Viste e materialized view

Viste

Una vista è una query con un nome: ogni volta che la interroghi, il DBMS esegue la query sottostante. Non occupa spazio e non è mai disallineata, ma il costo si paga a ogni utilizzo.

CREATE VIEW VisiteComplete AS
    SELECT V.Data, M.Cognome AS Medico, M.Specializzazione,
           P.Cognome AS Paziente, V.Mutuata
    FROM Visita V
         INNER JOIN Medico M ON V.Medico = M.Matricola
         INNER JOIN Paziente P ON V.Paziente = P.CodFiscale;

SELECT * FROM VisiteComplete WHERE Specializzazione = 'Ortopedia';

Materialized view

Una materialized view (o snapshot) contiene il risultato già calcolato: è una tabella vera, popolata a partire da una query costosa. Si usa quando serve una risposta veloce a una domanda la cui query impiegherebbe minuti — tipicamente aggregazioni per analisi e reportistica.

Il punto chiave: appena qualcuno modifica i dati grezzi, la materialized view potenzialmente non è più aggiornata. Da qui il problema del refresh.

Politiche di refresh

E due modalità di ricalcolo:

• full refresh: si ricalcola tutto da zero. Semplice, costoso.
• incremental refresh: fra un refresh e l'altro le modifiche vengono accumulate in una log table, e il refresh usa solo quella più i dati già presenti nella vista. Molto più veloce, perché la log table ha pochissime righe rispetto alle tabelle grezze.

Window function

La clausola OVER

C'è una classe di domande che gli aggregati classici non sanno risolvere: «per ogni medico, mostrami la sua parcella e la media della sua specializzazione». GROUP BY qui non serve, anzi fa danni: collassa le righe e i singoli medici spariscono.

Le window function risolvono esattamente questo: applicano una funzione a un insieme di righe collegate alla riga corrente (la partition) senza fondere le righe.

SELECT Matricola, Cognome, Specializzazione, Parcella,
       AVG(Parcella) OVER() AS MediaClinica
FROM Medico;

Con OVER() vuoto la partition è l'intero result set: ogni riga porta con sé la media generale, e nessuna riga sparisce.

PARTITION BY

SELECT Matricola, Cognome, Specializzazione, Parcella,
       AVG(Parcella) OVER(PARTITION BY Specializzazione) AS MediaSpecializzazione
FROM Medico;

Ora la partition cambia riga per riga: per ogni medico è l'insieme dei colleghi della stessa specializzazione. Il risultato ha ancora una riga per medico, con accanto il valore di confronto.

RANK, DENSE_RANK e ROW_NUMBER

Oltre alle funzioni aggregate, con OVER si usano funzioni non aggregate pensate per le classifiche.

-- classifica dei medici per convenienza, dentro ogni specializzazione
SELECT Matricola, Cognome, Specializzazione, Parcella,
       RANK() OVER(PARTITION BY Specializzazione
                  ORDER BY Parcella ASC) AS PosizioneConvenienza
FROM Medico;

L'ORDER BY dentro OVER non ordina il risultato finale: definisce il criterio della classifica all'interno della partition. Per ordinare le righe in uscita serve comunque un ORDER BY esterno.

Esercizi svolti

DML

UPDATE Medico M
       INNER JOIN (
           SELECT V.Medico, COUNT(*) AS NumVisite
           FROM Visita V
           WHERE V.Data >= CURRENT_DATE - INTERVAL 1 YEAR
           GROUP BY V.Medico
       ) AS D ON M.Matricola = D.Medico
SET M.Parcella = M.Parcella * 1.05
WHERE D.NumVisite > 50;

DELETE FROM Paziente
WHERE Reddito IS NULL
      AND CodFiscale NOT IN (SELECT DISTINCT V.Paziente
                             FROM Visita V);

Trigger e procedure

DROP TRIGGER IF EXISTS no_visite_future;
DELIMITER $$
CREATE TRIGGER no_visite_future
BEFORE INSERT ON Visita FOR EACH ROW
BEGIN
    IF NEW.Data > CURRENT_DATE THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Non si possono registrare visite future';
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS riepilogo_specializzazione;
DELIMITER $$
CREATE PROCEDURE riepilogo_specializzazione(
        IN _spec VARCHAR(100),
        OUT _visite INT,
        OUT _parcella_media DECIMAL(10,2))
BEGIN
    SELECT COUNT(*) INTO _visite
    FROM Visita V INNER JOIN Medico M ON V.Medico = M.Matricola
    WHERE M.Specializzazione = _spec
          AND YEAR(V.Data) = YEAR(CURRENT_DATE);

    SELECT AVG(M.Parcella) INTO _parcella_media
    FROM Medico M
    WHERE M.Specializzazione = _spec;
END $$
DELIMITER ;

CALL riepilogo_specializzazione('Ortopedia', @v, @p);
SELECT @v AS Visite, @p AS ParcellaMedia;