NWZeTVry

1/*
2Implementare una stored procedure all_drugs()che riceva in ingresso un principio attivo p e un settore
3medico s, consideri i farmaci basati su p, e restituisca il numero totale di pazienti che, per curare patologie
4del settore medico s, nel corso della vita li hanno assunti tutti o tutti tranne quello in generale meno usato
5nelle terapie per patologie del settore medico s. Il parametro OUT della stored procedure deve essere unico,
6contenente il valore cumulativo dei pazienti sopra descritti.
7*/
8
9DROP PROCEDURE IF EXISTS all_drugs;
10DELIMITER $$
11CREATE PROCEDURE all_drugs(IN _p CHAR(50), IN _s CHAR(50), OUT _res INT)
12BEGIN
13	-- temp table con i farmaci a base del principio attivo _p
14	DROP TEMPORARY TABLE IF EXISTS farmaci_target;
15	CREATE TEMPORARY TABLE farmaci_target
16    SELECT f.NomeCommerciale FROM Farmaco f WHERE f.PrincipioAttivo=_p;
17    
18    -- num pazienti che hanno assunto tutti i farmaci a base di p per patologie del settore medico s
19	SET @num_paz_tutti_farmaci = (
20		SELECT COUNT(*)
21		FROM terapia t INNER JOIN
22			 patologia p ON t.Patologia=p.Nome 
23		WHERE t.Farmaco IN (SELECT * FROM farmaci_target) AND p.SettoreMedico=_s
24		GROUP BY t.Paziente
25		HAVING COUNT(DISTINCT t.Farmaco)=(SELECT COUNT(*) FROM farmaci_target)
26    );
27    
28    -- userei una temp table se funzionassero bene
29	DROP TABLE IF EXISTS temp;
30	CREATE TABLE temp
31    SELECT t.Farmaco, COUNT(*) AS numT
32    FROM terapia t INNER JOIN
33		 patologia p
34	WHERE p.SettoreMedico=_s
35    GROUP BY t.Farmaco;
36    
37    -- temp table con i farmaci meno usati per curare terapie della specializzazione _s
38    DROP TEMPORARY TABLE IF EXISTS farmaci_meno_usati;
39	CREATE TEMPORARY TABLE farmaci_meno_usati
40    SELECT t.Farmaco
41    FROM temp t
42    WHERE t.numT <= ALL (SELECT s.numT FROM temp s);
43    
44    -- la droppo tanto mi serviva solo per calcolare i farmaci meno usati
45    DROP TABLE temp;
46    
47    -- temp table con i pazienti che non hanno usato tutti i farmaci a base di _p per patologie, e quali farmaci hanno usato
48	DROP TEMPORARY TABLE IF EXISTS paz_non_tutti_farmaci;
49	CREATE TEMPORARY TABLE paz_non_tutti_farmaci
50    SELECT DISTINCT t.Paziente, t.Farmaco
51    FROM terapia t INNER JOIN
52		( -- pazienti che non hanno usato tutti i farmaci ecc ecc
53		SELECT t1.Paziente
54		FROM terapia t1 INNER JOIN
55			 patologia p ON t1.Patologia=p.Nome 
56		WHERE t1.Farmaco IN (SELECT * FROM farmaci_target) AND p.SettoreMedico=_s
57		GROUP BY t1.Paziente
58		HAVING COUNT(DISTINCT t1.Farmaco)<(SELECT COUNT(*) FROM farmaci_target)
59        ) AS d ON t.Paziente=d.Paziente;
60	
61    
62    SET @num_paz_non_tutti_i_farmaci = (
63		SELECT COUNT(*)
64		-- se faccio join esterno con farmaci target quelli che avranno a sinistra null sono quelli che non hanno usato
65		FROM paz_non_tutti_farmaci p RIGHT OUTER JOIN
66			 farmaci_target f ON p.Farmaco=f.Farmaco
67		WHERE p.Farmaco IS NULL AND-- il paziente non ha usato quel farmaco
68			  f.Farmaco IN (SELECT * FROM farmaci_meno_usati) -- e il farmaco che non ha usato è uno di quelli meno usati
69	);
70    
71    -- return
72    SET _res = @num_paz_tutti_farmaci + @num_paz_non_tutti_i_farmaci;
73    
74    -- not needed but why not
75    DROP TEMPORARY TABLE IF EXISTS farmaci_target;
76    DROP TEMPORARY TABLE IF EXISTS farmaci_meno_usati;
77	DROP TEMPORARY TABLE IF EXISTS paz_non_tutti_farmaci;
78END $$
79DELIMITER ;