iGHU6128

1/* Implementare una stored procedure ALL_DRUGS() che riceva in ingresso un principio attivo 
2p e un settore medico s, consideri i farmaci basati su p, e restituisca il numero totale di
3pazienti che, per curare patologie del settore medico s, nel corso della vita li hanno 
4assunti tutti o tutti tranne quello in generale meno usato nelle terapie per patologie del 
5settore medico s. Il parametro OUT della stored procedure deve essere unico, contenente il
6 valore cumulativo dei pazienti sopra descritti. */
7
8DROP PROCEDURE IF EXISTS farmacoMenoUsato;
9DELIMITER $$
10CREATE PROCEDURE farmacoMenoUsato ( IN princAtt VARCHAR(100),
11									IN settMedico VARCHAR(100),
12                                    OUT farmaco VARCHAR(100)
13								  )
14BEGIN
15	CREATE TEMPORARY TABLE QuantiFarmaci (
16		Farmaco VARCHAR(100) NOT NULL,
17        Usi INTEGER DEFAULT 0 NOT NULL
18	);
19    
20    INSERT INTO QuantiFarmaci
21		SELECT T.Farmaco, COUNT(*) AS Tot
22		FROM Terapia T
23			 INNER JOIN
24             Farmaco F ON T.Farmaco = F.NomeCommerciale
25		WHERE F.PrincipioAttivo = princAtt
26            AND T.Patologia IN ( SELECT P.Nome
27								 FROM Patologia
28                                 WHERE P.SettoreMedico = settMedico
29								)
30		GROUP BY T.Farmaco;
31	
32    SELECT QT.Farmaco INTO farmaco
33    FROM QuantiFarmaci QT1
34    WHERE Usi = ( SELECT MIN(QT2Usi)
35				  FROM QuantiFarmaci
36				)
37	LIMIT 1; 	-- Prendo solo il primo in caso di ex aequo
38					
39END $$
40
41
42DROP PROCEDURE IF EXISTS all_drugs;
43
44CREATE PROCEDURE all_drugs( IN pa VARCHAR(100),
45							IN sm VARCHAR(100),
46							OUT NumPaz INTEGER)
47BEGIN
48		DECLARE paziente VARCHAR(100) DEFAULT '';
49        DECLARE numFarmaci INTEGER DEFAULT 0;
50        DECLARE finito INTEGER DEFAULT 0;
51		DECLARE FarmacoMenoUsato VARCHAR(100);
52		DECLARE TotFarmaci INTEGER DEFAULT 0;	
53        DECLARE flag INTEGER DEFAULT -1;
54
55		DECLARE CursorePazienti CURSOR FOR
56			SELECT T.Paziente, COUNT(DISTINCT T.Farmaco) AS NumFarmaci
57            FROM Terapia T
58				 INNER JOIN
59                 Patologia P ON T.Patologia = P.Nome
60                 INNER JOIN
61                 Farmaco F ON F.NomeCommerciale = T.Farmaco
62			WHERE P.SettoreMedico = sm
63				AND F.PrincipioAttivo = pa				
64            GROUP BY T.Paziente;
65		
66        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finito = 1;
67        
68        SET TotFarmaci = 
69			( SELECT COUNT(DISTINCT T.Farmaco) AS TotaleFarmaci
70			  FROM  Terapia T
71					INNER JOIN
72					Farmaco F ON T.Farmaco = F.NomeCommerciale
73                    INNER JOIN
74                    Patologia P ON T.Patologia = P.Nome
75			  WHERE P.SettoreMedico = sm
76				AND F.PrincipioAttivo = pa
77			);
78           
79		OPEN CursorePazienti;
80        scan: LOOP
81			FETCH CursorePazienti INTO paziente, NumFarmaci;
82            
83            IF finito = 1 THEN
84				LEAVE scan;
85			END IF;
86            
87            IF NumFarmaci = TotFarmaci THEN		-- Ha assunto tutti i farmaci
88				SET NumPaz = NumPaz + 1;
89			END IF;
90            
91            IF NumFarmaci = TotFarmaci - 1 THEN		-- Controllo se non ha assunto quello meno usato
92					CALL farmacoMenoUsato (pa, sm, FarmacoMenoUsato);
93					
94                    /* flag =  0 non ha assunto solo il farmaco meno usato
95					   flag > 0 ha assunto il farmaco meno usato, ma non tutti i farmaci
96					*/
97                   SET flag = 		
98						( SELECT COUNT(*)		
99                          FROM Terapia T
100							   INNER JOIN
101                               Patologia P ON T.Patologia = P.SettoreMedico
102                               INNER JOIN 
103                               Farmaco F ON T.Farmaco = F.NomeCommerciale
104						  WHERE T.Paziente = paziente
105                            AND P.SettoreMedico = sm
106                            AND T.Farmaco = FarmacoMenoUsato   
107                            
108                        );
109				
110                    IF flag = 0 THEN
111						SET numPaz = numPaz + 1;
112					END IF;      
113            END IF; 
114		
115        END LOOP scan ;    
116        CLOSE CursorePazienti;
117END $$
118DELIMITER ;
119
120CALL all_drugs ("Acido Acetilsalicilico", "Medicina Generale", @cont);
121SELECT @cont;