XdB2KhxS

1-- FUNÇÕES
26 DROP FUNCTION IF EXISTS fun_calcular_idade;
37 DELIMITER %%
48 CREATE FUNCTION fun_calcular_idade(data_nasc date)
59 RETURNS INT
610 NOT DETERMINISTIC -- o resultado pode ser diff para o mesmo input -> depende da data atual do 
7sistema
811 BEGIN
912 declare var_age int;
1013 select TIMESTAMPDIFF(YEAR, data_nasc, NOW()) into var_age;
1114
1215 return var_age;
1316
1417 END %%
1518 DELIMITER ;
1619
1720 select fun_calcular_idade('1981-08-14');
1821 select alu_nome, alu_dnsc, fun_calcular_idade(alu_dnsc) as Idade from alunos 
1922 where fun_calcular_idade(alu_dnsc) >= 23;
2023
2124
2225 -- 2 ----------------------------------------------------------26
2327 -- alterar ficha aluno para lançar excecao caso ID nao exista.
2428 drop procedure if exists sp_ficha_aluno; 
2529 delimiter $$
2630 create procedure sp_ficha_aluno(IN arg_id_aluno int)
2731 begin
2832
2933 declare msg varchar(100);
3034
3135 IF EXISTS(select 1 from alunos where alu_id = arg_id_aluno) 
3236 THEN
3337
3438 select pla_semestre as 'Semestre', dis_id as 'ID Disciplina', dis_nome as 'Disciplina', 
35dis_creditos as 'Créditos', 
3639 ins_dt_inscricao as 'Data Inscrição', 
3740 ins_dt_avaliacao as 'Data Lançamento', 
3841 ins_nota as 'Nota' 
3942 from inscricoes 
4043 join disciplinas on dis_id = ins_pla_dis_id 
4144 join planoestudos on pla_dis_id = dis_id 
4245 where ins_alu_id = arg_id_aluno and pla_cur_id = (select alu_cur_id from alunos where 
43alu_id = arg_id_aluno)
4446 order by pla_semestre, dis_nome ;
4547
4648 ELSE
4749
4850 select concat('Aluno com ID=',arg_id_aluno,' inválido') into msg;
4951 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
5052 END IF;
5153
5254 end $$
5355 delimiter ;
5456
5557 call sp_ficha_aluno(19);
5658 SHOW ERRORS;
5759
5860
5961 -- 3 ----------------------------------------------------------62
6063 -- validação idade between [17, 65]; sexo = {F,M} e curso existente
6164 drop procedure if exists sp_matricular_aluno; 
6265 delimiter $$
6366 create procedure sp_matricular_aluno(
6467 IN arg_alu_nome varchar(60),
6568 IN arg_alu_local varchar(30),
6669 IN arg_alu_dnsc date,
6770 IN arg_alu_sexo char(1) ,
6871 IN arg_alu_email varchar(30),
6972 IN arg_alu_cur_id int,
7073 OUT alu_id_inserted int)
7174 begin
7275
7376 declare msg varchar(100);
7477 declare idade int;
7578
7679 select fun_calcular_idade(arg_alu_dnsc) into idade;
7780
7881 if (idade < 17 or idade > 65) then
7982 select concat('O aluno possui ', idade,' anos. Não são permitidas idades fora de [17, 65]') 
80into msg;
8183 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
8284
8385 elseif (select arg_alu_sexo in ('F','M') ) = 0 then
8486 select concat('O valor relativo ao sexo do aluno é inválido: ', arg_alu_sexo) into msg;
8587 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
8688
8789 elseif not exists(select 1 from cursos where cur_id = arg_alu_cur_id) then
8890 select concat('O curso com ID=', arg_alu_cur_id,' é inválido.') into msg;
8991 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
9092
9193 else
9294
9395 insert into alunos (alu_nome, alu_local, alu_dnsc, alu_sexo, alu_email, alu_cur_id) 
9496 values (arg_alu_nome, arg_alu_local, arg_alu_dnsc, arg_alu_sexo, arg_alu_email, 
95arg_alu_cur_id); 
9697
9798 set alu_id_inserted = last_insert_id();
9899
99100 end if;
100101
101102 end $$
102103 delimiter ;
103104
104105 show errors;
105106 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '1920-04-08', 'F', 'fabreu@mail.pt', 2, @id);
106107 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '2019-04-08', 'F', 'fabreu@mail.pt', 2, @id);
107108 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '1990-04-08', 'B', 'fabreu@mail.pt', 2, @id);
108109 call sp_matricular_aluno ('Filipa Abreu', 'Setúbal', '1990-04-08', 'F', 'fabreu@mail.pt', 12, @id);
109110
110111 select @id;
111112
112113 -- 4 ----------------------------------------------------------------------114 -- CURSOR
113115
114116 -- alterar sp_matricular_aluno por forma a utilizar um cursor; nao permitir duas inscricoes 
115117 -- na mesma disciplina no mesmo ano civil.
116118
117119 drop procedure if exists sp_inscrever_semestre;
118120 delimiter $$
119121 create procedure sp_inscrever_semestre(
120122 IN arg_alu_id int,
121123 IN arg_pla_semestre int)
122124 begin
123125 declare msg varchar(128); -- para guardar o conjunto de dis_id "repetidos" nas incricoes
124126 declare var_cur_id int; -- variavel que guarda o cur_id do aluno
125127 declare done int default false;
126128 declare current_dis_id int; -- variavel que guarda a dis_id atual obtida do cursor para o plano 
127curricular
128129
129130 -- declarar cursor. sub-query necessaria pois nao podemos fazer antes de declaracoes
130131 declare cursor_plano_semestre cursor for 
131132 (select pla_dis_id from planoestudos 
132133 where pla_cur_id = (select alu_cur_id from alunos where alu_id = arg_alu_id) and 
133pla_semestre = arg_pla_semestre);
134134
135135 -- declaracao de handler (tem de ser apos declaracao do cursor)
136136 declare continue handler for not found set done = true; -- para sair do fetch-loop
137137
138138 -- obter curso do aluno
139139 select alu_cur_id into var_cur_id from alunos where alu_id = arg_alu_id;
140140
141141
142142 -- definir inicio de (possível) aviso
143143 set msg = 'Aluno já inscrito a: ';
144144
145145 open cursor_plano_semestre;
146146 -- fetch loop
147147 fetch_loop: LOOP
148148
149149 fetch cursor_plano_semestre into current_dis_id;
150150
151151 IF done THEN
152152 LEAVE fetch_loop;
153153 END IF;
154154
155155 -- para cada current_dis_id verificar se já existe alguma inscricao cuja data_inscricao
156156 -- coincida com o ano civil da data atual
157157 if exists( select 1 from inscricoes 
158158 where ins_alu_id = arg_alu_id 
159159 and ins_pla_cur_id = var_cur_id
160160 and ins_pla_dis_id = current_dis_id
161161 and year(ins_dt_inscricao) = year(curdate()) ) 
162162 then 
163163 -- concatenar id ao texto da mensagem
164164 set msg = concat_ws(';', msg, current_dis_id);
165165 -- emitir aviso; começa por '01' e não termina procedimento
166166 SIGNAL SQLSTATE '01000'
167167 SET MESSAGE_TEXT = msg;
168168 else
169169 -- efetuar inscricao
170170 insert into inscricoes (ins_alu_id, ins_pla_cur_id, ins_pla_dis_id, ins_dt_inscricao)
171171 values(arg_alu_id, var_cur_id, current_dis_id, curdate());
172172 end if;
173173
174174 end LOOP fetch_loop;
175175 -- fechar cursor / libertar recursos
176176 close cursor_plano_semestre;
177177
178178 end $$
179179 delimiter ;
180180
181181
182182 call sp_matricular_aluno ('Marta Lopes', 'Setúbal', '1997-04-08', 'F', 'mlopes@mail.pt', 2, @id);
183183 call sp_ficha_aluno(@id);
184184 call sp_inscrever_semestre(@id, 1);
185185
186186 show warnings;
187187
188188
189189 -- 5 ----------------------------------------------------------------------190 -- TRIGGERS
190191
191192 -- 5.1
192193 -- garantir que siglas de departamentos estão sempre em maíusculas
193194 DROP TRIGGER IF EXISTS before_insert_departamentos;
194195 delimiter $$
195196 CREATE TRIGGER before_insert_departamentos 
196197 BEFORE INSERT 
197198 ON departamentos FOR EACH ROW
198199 BEGIN
199200 SET NEW.dep_sigla = upper(NEW.dep_sigla);
200201 END $$
201202 delimiter ;
202203  
203204 insert into departamentos(dep_nome, dep_sigla) values ('Departamento de Eng.ª Mecânica', 'dem');
204205 select * from departamentos;
205206 delete from departamentos where dep_id = 5;
206207
207208 -- 5.2 
208209 -- Total Créditos
209210 -- Acrescentar coluna à tabela alunos
210211 ALTER TABLE alunos ADD alu_total_creditos int default 0 AFTER alu_cur_id;
211212
212213 -- garantir que no campo total_creditos esta sempre um valor correto
213214 DROP TRIGGER IF EXISTS after_update_inscricoes;
214215 DELIMITER $$
215216 CREATE TRIGGER after_update_inscricoes
216217 AFTER UPDATE 
217218 ON inscricoes FOR EACH ROW
218219 BEGIN
219220
220221 declare var_total_creditos int;
221222
222223 -- também é passível de ser feito com um cursor
223224
224225 select sum(dis_creditos) into var_total_creditos 
225226 from inscricoes 
226227 join disciplinas on dis_id = ins_pla_dis_id 
227228 join planoestudos on pla_dis_id = dis_id 
228229 where ins_alu_id = NEW.ins_alu_id 
229230 and pla_cur_id = (select alu_cur_id from alunos where alu_id = NEW.ins_alu_id)
230231 and ins_nota >= 9.5;
231232
232233 update alunos set alu_total_creditos = var_total_creditos where alu_id = NEW.ins_alu_id;
233234
234235 END $$
235236 DELIMITER ;
236237
237238 -- invocar sp_lancar_nota e verificar creditos
238239 call sp_lancar_nota(1, 8, 7);
239240 call sp_lancar_nota(1, 8, 15);
240241
241242
242243 /* ATENÇÃO QUE PARA O TRIGGER FUNCIONAR CORRETAMENTE A TABELA ALUNOS
243244 NAO PODE SER INVOCADA NA QUERY QUE DISPARA O TRIGGER. NA RESOLUCAO
244245 DO LAB 6 O USO DA TABELA ALUNOS ESTA PRESENTE NUMA SUB-QUERY. PARA
245246 FUNCIONAR DEVE SER ALTERADO PARA A FORMA:
246247
247248
248249 drop procedure if exists sp_lancar_nota;
249250 delimiter $$
250251 create procedure sp_lancar_nota(
251252 IN arg_alu_id int,
252253 IN arg_ins_pla_dis_id int,
253254 IN arg_ins_nota decimal(4,2))
254255 begin
255256 -- alterar para uso de variável em vez de subquery.
256257 declare var_cur_id int;
257258 select alu_cur_id into var_cur_id from alunos where alu_id = arg_alu_id;
258259
259260 update inscricoes set ins_nota = arg_ins_nota, ins_dt_avaliacao = curdate()
260261 where ins_alu_id = arg_alu_id
261262 and ins_pla_dis_id = arg_ins_pla_dis_id
262263 and ins_pla_cur_id = var_cur_id;
263264
264265 end $$
265266 delimiter ;
266267
267268 */
268269
269270
270271 -- NAO UTILIZADOS:
271272 -- criar um email institucional dado o seu nome e id
272273  
273274 DROP FUNCTION IF EXISTS fun_criar_endereco_email;
274275 DELIMITER %%
275276 CREATE FUNCTION fun_criar_endereco_email(nome varchar(200), id int, dominio varchar(200))
276277 RETURNS varchar(200)
277278 DETERMINISTIC -- o resultado pode ser diff para o mesmo input -> depende da data atual do sistema
278279 BEGIN
279280 declare var_endereco varchar(200);
280281
281282 select concat_ws('@', 
282283 concat_ws('.', substring_index(nome, ' ', 1), id),
283284 'estsetubal.ips.pt') into var_endereco;
284285
285286 return var_endereco;
286287
287288 END %%
288289 DELIMITER ;