forked from Fecsio/Orto-Botanico-database
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Procedure.sql
141 lines (123 loc) · 4.71 KB
/
Procedure.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
USE OrtoBotanico;
DROP PROCEDURE IF EXISTS AddPref;
DROP PROCEDURE IF EXISTS DelPref;
DROP PROCEDURE IF EXISTS AddList;
DROP PROCEDURE IF EXISTS DelList;
DROP PROCEDURE IF EXISTS Registrazione;
DROP PROCEDURE IF EXISTS DelUser;
DROP PROCEDURE IF EXISTS CountPlants;
DELIMITER ||
CREATE PROCEDURE AddPref(In Ut VARCHAR(20),In psw VARCHAR(20), IN listaI VARCHAR(20), IN piantaS VARCHAR(50))
BEGIN
IF(Control(Ut,psw)) THEN
IF (piantaS IN (SELECT DISTINCT LP.Pianta
FROM ListePiante LP
WHERE LP.lista=listaI AND Ut=LP.utente)) THEN
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'Pianta gia'' presente nella lista indicata';
ELSE
INSERT INTO ListePiante values (piantaS, Ut, listaI);
END IF;
ELSE
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'Username o password errati';
END IF;
END ||
CREATE PROCEDURE DelPref(In Ut VARCHAR(20), In psw VARCHAR(20),IN listaI VARCHAR(20), IN piantaS VARCHAR(50))
BEGIN
IF(Control(Ut,psw)) THEN
IF(listaI NOT IN (SELECT DISTINCT LP.Lista
FROM ListePiante LP
WHERE Ut=LP.Utente)) THEN
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'La lista preferiti indicata non esiste';
ELSEIF(piantaS NOT IN (SELECT DISTINCT LP.Pianta
FROM ListePiante LP
WHERE LP.lista=listaI AND Ut=LP.Utente)) THEN
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'Pianta non presente nella lista indicata';
ELSE
DELETE LP.* FROM ListePiante LP
WHERE listaI=LP.lista and LP.Utente=Ut and LP.pianta=piantaS;
END IF;
ELSE
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'Username o password errati';
END IF;
END ||
CREATE PROCEDURE DelList(In Ut VARCHAR(20), In psw VARCHAR(20),IN listaI VARCHAR(20))
BEGIN
IF(Control(Ut,psw)) THEN
IF(listaI NOT IN (SELECT DISTINCT LP.Nome
FROM ListePreferiti LP
WHERE Ut=LP.Utente)) THEN
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'La lista preferiti indicata non esiste';
ELSE
DELETE LP.*
FROM ListePreferiti LP
WHERE listaI=LP.Nome and LP.Utente=Ut;
END IF;
ELSE
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'Username o password errati';
END IF;
END||
CREATE PROCEDURE AddList(In Ut VARCHAR(20), In psw VARCHAR(20), IN listaI VARCHAR(20))
BEGIN
IF(Control(Ut,psw)) THEN
INSERT INTO ListePreferiti VALUES(Ut,listaI,current_timestamp());
ELSE
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'Username o password errati';
END IF;
END ||
CREATE PROCEDURE Registrazione(IN Ut VARCHAR(20), IN mail VARCHAR(30), IN psw VARCHAR(20), IN nome VARCHAR(20), IN cognome VARCHAR(20))
BEGIN
INSERT INTO Utenti VALUES(Ut, mail, password(psw), nome, cognome);
END ||
CREATE PROCEDURE DelUser(IN Ut VARCHAR(20), IN psw VARCHAR(41))
BEGIN
IF(Control(Ut,psw)) THEN
DELETE Utenti.* FROM Utenti WHERE Ut=Username;
ELSE
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'Username o password errati';
END IF;
END ||
/*Tramite questa procedura è possibile, dato in input il nome di una sezione dell'orto botanico, sapere quante specie di piante contenute in esso hanno il fusto eretto e quante invece diverso da eretto (dunque si considerano solo le piante tracheofite). Oltre a questi dati, la procedura mostra il nome della sezione ricercata e il bioma in essa rappresentato.*/
CREATE PROCEDURE CountPlants (IN sezorto varchar(5))
BEGIN
IF(sezorto NOT IN (SELECT SezioneOrto FROM BiomiTerrestri) AND sezorto NOT IN (SELECT SezioneOrto FROM BiomiAcquatici)) THEN
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = 'La sezione indicata non esiste.';
ELSE
(SELECT IFNULL(PianteFustoEretto, 0) AS PianteFustoEretto, IFNULL(PianteFustoNonEretto,0) AS PianteFustoNonEretto, Bioma, SezioneOrto
FROM (
SELECT COUNT(DISTINCT PT.Pianta) AS PianteFustoEretto, PT.BiomaT as Bioma, BT.SezioneOrto AS SezioneOrto
FROM BiomiTerrestri BT, PresenzeT PT, Piante P
WHERE BT.Nome=PT.BiomaT AND P.Fusto='Eretto' AND P.NomeScientifico=PT.Pianta AND BT.SezioneOrto=sezorto
GROUP BY Bioma
) AS TAB
LEFT JOIN (
SELECT COUNT(DISTINCT PT1.Pianta) AS PianteFustoNonEretto, PT1.BiomaT AS Bioma1
FROM PresenzeT PT1, Piante P1
WHERE P1.Fusto<>'Eretto' AND P1.NomeScientifico=PT1.Pianta
GROUP BY Bioma1) AS TAB1
ON Bioma=Bioma1)
UNION (SELECT IFNULL(PianteFustoEretto, 0) AS PianteFustoEretto, IFNULL(PianteFustoNonEretto,0) AS PianteFustoNonEretto, Bioma, SezioneOrto
FROM (
SELECT COUNT(DISTINCT PA.Pianta) AS PianteFustoEretto, PA.BiomaA as Bioma, BA.SezioneOrto AS SezioneOrto
FROM BiomiAcquatici BA, PresenzeA PA, Piante P
WHERE BA.Nome=PA.BiomaA AND P.Fusto='Eretto' AND P.NomeScientifico=PA.Pianta AND BA.SezioneOrto=sezorto
GROUP BY Bioma
) AS TABA
LEFT JOIN (
SELECT COUNT(DISTINCT PA1.Pianta) AS PianteFustoNonEretto, PA1.BiomaA AS Bioma1
FROM PresenzeA PA1, Piante P1
WHERE P1.Fusto<>'Eretto' AND P1.NomeScientifico=PA1.Pianta
GROUP BY Bioma1) AS TABA1
ON Bioma=Bioma1);
END IF;
END ||
DELIMITER ;