-- FUNÇÕES
6 DROP FUNCTION IF EXISTS fun_calcular_idade;
7 DELIMITER %%
8 CREATE FUNCTION fun_calcular_idade(data_nasc date)
9 RETURNS INT
10 NOT DETERMINISTIC -- o resultado pode ser diff para o mesmo input -> depende da data atual do
sistema
11 BEGIN
12 declare var_age int;
13 select TIMESTAMPDIFF(YEAR, data_nasc, NOW()) into var_age;
14
15 return var_age;
16
17 END %%
18 DELIMITER ;
19
20 select fun_calcular_idade('1981-08-14');
21 select alu_nome, alu_dnsc, fun_calcular_idade(alu_dnsc) as Idade from alunos
22 where fun_calcular_idade(alu_dnsc) >= 23;
23
24
25 -- 2 ----------------------------------------------------------26
27 -- alterar ficha aluno para lançar excecao caso ID nao exista.
28 drop procedure if exists sp_ficha_aluno;
29 delimiter $$
30 create procedure sp_ficha_aluno(IN arg_id_aluno int)
31 begin
32
33 declare msg varchar(100);
34
35 IF EXISTS(select 1 from alunos where alu_id = arg_id_aluno)
36 THEN
37
38 select pla_semestre as 'Semestre', dis_id as 'ID Disciplina', dis_nome as 'Disciplina',
dis_creditos as 'Créditos',
39 ins_dt_inscricao as 'Data Inscrição',
40 ins_dt_avaliacao as 'Data Lançamento',
41 ins_nota as 'Nota'
42 from inscricoes
43 join disciplinas on dis_id = ins_pla_dis_id
44 join planoestudos on pla_dis_id = dis_id
45 where ins_alu_id = arg_id_aluno and pla_cur_id = (select alu_cur_id from alunos where
alu_id = arg_id_aluno)
46 order by pla_semestre, dis_nome ;
47
48 ELSE
49
50 select concat('Aluno com ID=',arg_id_aluno,' inválido') into msg;
51 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
52 END IF;
53
54 end $$
55 delimiter ;
56
57 call sp_ficha_aluno(19);
58 SHOW ERRORS;
59
60
61 -- 3 ----------------------------------------------------------62
63 -- validação idade between [17, 65]; sexo = {F,M} e curso existente
64 drop procedure if exists sp_matricular_aluno;
65 delimiter $$
66 create procedure sp_matricular_aluno(
67 IN arg_alu_nome varchar(60),
68 IN arg_alu_local varchar(30),
69 IN arg_alu_dnsc date,
70 IN arg_alu_sexo char(1) ,
71 IN arg_alu_email varchar(30),
72 IN arg_alu_cur_id int,
73 OUT alu_id_inserted int)
74 begin
75
76 declare msg varchar(100);
77 declare idade int;
78
79 select fun_calcular_idade(arg_alu_dnsc) into idade;
80
81 if (idade < 17 or idade > 65) then
82 select concat('O aluno possui ', idade,' anos. Não são permitidas idades fora de [17, 65]')
into msg;
83 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
84
85 elseif (select arg_alu_sexo in ('F','M') ) = 0 then
86 select concat('O valor relativo ao sexo do aluno é inválido: ', arg_alu_sexo) into msg;
87 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
88
89 elseif not exists(select 1 from cursos where cur_id = arg_alu_cur_id) then
90 select concat('O curso com ID=', arg_alu_cur_id,' é inválido.') into msg;
91 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
92
93 else
94
95 insert into alunos (alu_nome, alu_local, alu_dnsc, alu_sexo, alu_email, alu_cur_id)
96 values (arg_alu_nome, arg_alu_local, arg_alu_dnsc, arg_alu_sexo, arg_alu_email,
arg_alu_cur_id);
97
98 set alu_id_inserted = last_insert_id();
99
100 end if;
101
102 end $$
103 delimiter ;
104
105 show errors;
106 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '1920-04-08', 'F', 'fabreu@mail.pt', 2, @id);
107 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '2019-04-08', 'F', 'fabreu@mail.pt', 2, @id);
108 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '1990-04-08', 'B', 'fabreu@mail.pt', 2, @id);
109 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '1990-04-08', 'F', 'fabreu@mail.pt', 12, @id);
110
111 select @id;
112
113 -- 4 ----------------------------------------------------------------------114 -- CURSOR
115
116 -- alterar sp_matricular_aluno por forma a utilizar um cursor; nao permitir duas inscricoes
117 -- na mesma disciplina no mesmo ano civil.
118
119 drop procedure if exists sp_inscrever_semestre;
120 delimiter $$
121 create procedure sp_inscrever_semestre(
122 IN arg_alu_id int,
123 IN arg_pla_semestre int)
124 begin
125 declare msg varchar(128); -- para guardar o conjunto de dis_id "repetidos" nas incricoes
126 declare var_cur_id int; -- variavel que guarda o cur_id do aluno
127 declare done int default false;
128 declare current_dis_id int; -- variavel que guarda a dis_id atual obtida do cursor para o plano
curricular
129
130 -- declarar cursor. sub-query necessaria pois nao podemos fazer antes de declaracoes
131 declare cursor_plano_semestre cursor for
132 (select pla_dis_id from planoestudos
133 where pla_cur_id = (select alu_cur_id from alunos where alu_id = arg_alu_id) and
pla_semestre = arg_pla_semestre);
134
135 -- declaracao de handler (tem de ser apos declaracao do cursor)
136 declare continue handler for not found set done = true; -- para sair do fetch-loop
137
138 -- obter curso do aluno
139 select alu_cur_id into var_cur_id from alunos where alu_id = arg_alu_id;
140
141
142 -- definir inicio de (possível) aviso
143 set msg = 'Aluno já inscrito a: ';
144
145 open cursor_plano_semestre;
146 -- fetch loop
147 fetch_loop: LOOP
148
149 fetch cursor_plano_semestre into current_dis_id;
150
151 IF done THEN
152 LEAVE fetch_loop;
153 END IF;
154
155 -- para cada current_dis_id verificar se já existe alguma inscricao cuja data_inscricao
156 -- coincida com o ano civil da data atual
157 if exists( select 1 from inscricoes
158 where ins_alu_id = arg_alu_id
159 and ins_pla_cur_id = var_cur_id
160 and ins_pla_dis_id = current_dis_id
161 and year(ins_dt_inscricao) = year(curdate()) )
162 then
163 -- concatenar id ao texto da mensagem
164 set msg = concat_ws(';', msg, current_dis_id);
165 -- emitir aviso; começa por '01' e não termina procedimento
166 SIGNAL SQLSTATE '01000'
167 SET MESSAGE_TEXT = msg;
168 else
169 -- efetuar inscricao
170 insert into inscricoes (ins_alu_id, ins_pla_cur_id, ins_pla_dis_id, ins_dt_inscricao)
171 values(arg_alu_id, var_cur_id, current_dis_id, curdate());
172 end if;
173
174 end LOOP fetch_loop;
175 -- fechar cursor / libertar recursos
176 close cursor_plano_semestre;
177
178 end $$
179 delimiter ;
180
181
182 call sp_matricular_aluno ('Marta Lopes', 'Setúbal', '1997-04-08', 'F', 'mlopes@mail.pt', 2, @id);
183 call sp_ficha_aluno(@id);
184 call sp_inscrever_semestre(@id, 1);
185
186 show warnings;
187
188
189 -- 5 ----------------------------------------------------------------------190 -- TRIGGERS
191
192 -- 5.1
193 -- garantir que siglas de departamentos estão sempre em maíusculas
194 DROP TRIGGER IF EXISTS before_insert_departamentos;
195 delimiter $$
196 CREATE TRIGGER before_insert_departamentos
197 BEFORE INSERT
198 ON departamentos FOR EACH ROW
199 BEGIN
200 SET NEW.dep_sigla = upper(NEW.dep_sigla);
201 END $$
202 delimiter ;
203
204 insert into departamentos(dep_nome, dep_sigla) values ('Departamento de Eng.ª Mecânica', 'dem');
205 select * from departamentos;
206 delete from departamentos where dep_id = 5;
207
208 -- 5.2
209 -- Total Créditos
210 -- Acrescentar coluna à tabela alunos
211 ALTER TABLE alunos ADD alu_total_creditos int default 0 AFTER alu_cur_id;
212
213 -- garantir que no campo total_creditos esta sempre um valor correto
214 DROP TRIGGER IF EXISTS after_update_inscricoes;
215 DELIMITER $$
216 CREATE TRIGGER after_update_inscricoes
217 AFTER UPDATE
218 ON inscricoes FOR EACH ROW
219 BEGIN
220
221 declare var_total_creditos int;
222
223 -- também é passível de ser feito com um cursor
224
225 select sum(dis_creditos) into var_total_creditos
226 from inscricoes
227 join disciplinas on dis_id = ins_pla_dis_id
228 join planoestudos on pla_dis_id = dis_id
229 where ins_alu_id = NEW.ins_alu_id
230 and pla_cur_id = (select alu_cur_id from alunos where alu_id = NEW.ins_alu_id)
231 and ins_nota >= 9.5;
232
233 update alunos set alu_total_creditos = var_total_creditos where alu_id = NEW.ins_alu_id;
234
235 END $$
236 DELIMITER ;
237
238 -- invocar sp_lancar_nota e verificar creditos
239 call sp_lancar_nota(1, 8, 7);
240 call sp_lancar_nota(1, 8, 15);
241
242
243 /* ATENÇÃO QUE PARA O TRIGGER FUNCIONAR CORRETAMENTE A TABELA ALUNOS
244 NAO PODE SER INVOCADA NA QUERY QUE DISPARA O TRIGGER. NA RESOLUCAO
245 DO LAB 6 O USO DA TABELA ALUNOS ESTA PRESENTE NUMA SUB-QUERY. PARA
246 FUNCIONAR DEVE SER ALTERADO PARA A FORMA:
247
248
249 drop procedure if exists sp_lancar_nota;
250 delimiter $$
251 create procedure sp_lancar_nota(
252 IN arg_alu_id int,
253 IN arg_ins_pla_dis_id int,
254 IN arg_ins_nota decimal(4,2))
255 begin
256 -- alterar para uso de variável em vez de subquery.
257 declare var_cur_id int;
258 select alu_cur_id into var_cur_id from alunos where alu_id = arg_alu_id;
259
260 update inscricoes set ins_nota = arg_ins_nota, ins_dt_avaliacao = curdate()
261 where ins_alu_id = arg_alu_id
262 and ins_pla_dis_id = arg_ins_pla_dis_id
263 and ins_pla_cur_id = var_cur_id;
264
265 end $$
266 delimiter ;
267
268 */
269
270
271 -- NAO UTILIZADOS:
272 -- criar um email institucional dado o seu nome e id
273
274 DROP FUNCTION IF EXISTS fun_criar_endereco_email;
275 DELIMITER %%
276 CREATE FUNCTION fun_criar_endereco_email(nome varchar(200), id int, dominio varchar(200))
277 RETURNS varchar(200)
278 DETERMINISTIC -- o resultado pode ser diff para o mesmo input -> depende da data atual do sistema
279 BEGIN
280 declare var_endereco varchar(200);
281
282 select concat_ws('@',
283 concat_ws('.', substring_index(nome, ' ', 1), id),
284 'estsetubal.ips.pt') into var_endereco;
285
286 return var_endereco;
287
288 END %%
289 DELIMITER ;