JPy1fvFa

1/*
2Implementare una stored function therapy_failures()che riceva in ingresso il codice fiscale di un
3paziente e il nome commerciale di un farmaco e restituisca, solo se esiste, il settore medico con il più alto
4numero di terapie iniziate dal paziente nel mese scorso, terminate senza guarigione nello stesso mese.
5*/
6
7DROP FUNCTION IF EXISTS therapy_failures;
8DELIMITER $$
9CREATE FUNCTION therapy_failures(codFiscale CHAR(50), nomeCommerciale CHAR(50))
10RETURNS CHAR(50) NOT DETERMINISTIC READS SQL DATA
11BEGIN
12	-- per allegerire la query soto mi calcolo il mese e anno target
13	SET @mese_scorso = CURRENT_DATE - INTERVAL 1 MONTH;
14    SET @mese_target = MONTH(@mese_scorso);
15    SET @anno_target = YEAR(@mese_scorso);
16    
17	DROP TEMPORARY TABLE IF EXISTS terapie_target;
18	CREATE TEMPORARY TABLE terapie_target
19    (
20    SELECT p.SettoreMedico, COUNT(*) AS NumTerapie
21    FROM terapia t INNER JOIN
22		 patologia p ON t.Patologia=p.Nome NATURAL JOIN
23         esordio e
24	WHERE t.Paziente=CodFiscale AND 
25		  t.Farmaco=nomeCommerciale AND
26          MONTH(t.DataInizioTerapia)=@mese_target AND -- inizia e finisce nello stesso mese, cioe il mese scorso
27          YEAR(t.DataInizioTerapia)=@anno_target AND
28          MONTH(t.DataFineTerapia)=@mese_target AND
29          YEAR(t.DataFineTerapia)=@anno_target  AND
30          (e.DataGuarigione IS NULL OR e.DataGuarigione > t.DataFineTerapia) -- la terapia non ha curato l'esordio
31	GROUP BY p.SettoreMedico
32    );
33    
34    -- controllo che ci sia qualcosa nella table
35    IF NOT EXISTS (SELECT * FROM terapie_target) THEN
36		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Non ci sono settori medici che rispettano le condizioni';
37	END IF;
38    
39    -- prendo il massimo numero di terapie
40    SET @max = (SELECT MAX(NumTerapie) FROM terapie_target);
41    
42    -- se piu di una settore medico ha il max mi fermo
43    IF (SELECT COUNT(*) FROM terapie_target WHERE NumTerapie=@max) > 1 THEN
44		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Piu di un settore medico hanno il massimo numero';
45	END IF;
46    
47    -- estraggo il settore medico che ha il massimo di numero terapie
48    SET @rv = (SELECT SettoreMedico FROM terapie_target WHERE NumTerapie=@max);    
49
50	RETURN @rv;
51END $$
52DELIMITER ;