daily pastebin goal
15%
SHARE
TWEET

Untitled

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