· 8 years ago · Jul 04, 2018, 06:06 PM
1/*Questao 01a
2Crie um procedimento que dado um mês de referencia retorne o nome e data de nascimento
3de todos os pacientes que fazem aniversário naquele mês. Verificar se o mês informado é
4válido.
5*/
6DELIMITER $
7CREATE PROCEDURE questao01a(mes int)
8BEGIN
9select p.nomep, p.dt_nasc from paciente p
10where MONTH(p.dt_nasc) = mes;
11END $
12DELIMITER ;
13
14CALL questao01a(1);
15DROP PROCEDURE questao01a;
16
17/*Questao 01b
18Crie um procedimento que dado o nome de um medico, liste o nome do paciente, data e
19hora da consulta, e dia da semana que ocorrerá a consulta (aplicar a função que converte o
20número do dia da semana em nome do dia da semana em Português).
21*/
22DELIMITER $
23CREATE PROCEDURE questao02a(nomeMed varchar(50))
24BEGIN
25SET lc_time_names = 'pt_PT';
26select p.nomep, DAYNAME(c.dt_consulta), c.hora from
27medico m, paciente p, consulta c
28where p.codp = c.codp AND
29 m.nomem = nomeMed;
30END $
31DELIMITER ;
32
33CALL questao02a('Claudia Avila');
34DROP PROCEDURE questao02a;
35
36/*Questao 02a
37Cria uma função que converta um campo char correspondente ao formato da data em
38português (ex.: ‘12/05/2017’) para o padrão americano (ex.: ‘2017-05-12’). Dê um exemplo
39de aplicação da função inserindo uma consulta na tabela Consulta.*/
40
41DELIMITER |
42|
43CREATE FUNCTION dataConverter(datap varchar(10))
44returns varchar(10)
45
46BEGIN
47
48 declare dataday varchar(2);
49 declare datamonth varchar(2);
50 declare datayear varchar(4);
51
52 if(datap is not null) then
53 set dataday = SUBSTRING(datap, 1, 2);
54 set datamonth = SUBSTRING(datap, 4, 2);
55 set datayear = SUBSTRING(datap, 7, 4);
56 return concat(datayear,'-',datamonth,'-',dataday);
57 else
58 SIGNAL SQLSTATE '45000'
59 SET MESSAGE_TEXT = 'Informe outra data!', MYSQL_ERRNO = 1000;
60 end if;
61
62END |
63delimiter ;
64
65
66SELECT dataConverter('22/03/1992');
67
68/*Exemplo */
69select * from consulta
70 where dt_consulta = dataConverter('10/06/2017');
71
72DROP FUNCTION dataConverter;
73
74/*Questao 02b
75Crie uma função que dado como entrada a especialidade retorne o total obtido com
76consultas para a especialidade informada. Mostrar um exemplo de execução da função onde
77apareça a especialidade e o valor obtido através de um select na tabela medico.
78*/
79DELIMITER |
80|
81CREATE FUNCTION totalEspecialidade(especialidade varchar(255))
82returns decimal(9,2)
83BEGIN
84 declare valorTotal decimal(9,2);
85 if(especialidade is not null) then
86 select SUM(valor_consulta) into valorTotal from
87 consulta c, medico m where c.codM = m.codM and m.especialidade = especialidade;
88 return valorTotal;
89 else
90 SIGNAL SQLSTATE '45000'
91 SET MESSAGE_TEXT = 'Informe outra especialidade!', MYSQL_ERRNO = 1000;
92 end if;
93
94END |
95delimiter ;
96
97select totalEspecialidade('Neurologista');
98
99DROP function totalEspecialidade;
100
101
102/*Questao 03a
103Crie uma trigger que faça o backup dos dados das consultas deletadas em uma tabela de
104backup juntamente com dados do usuário que efetuou a exclusão e data e hora da exclusão
105(campo datetime).
106*/
107
108/* Criação das tabelas */
109CREATE TABLE consultaBackups (
110 bkp_id int(11) NOT NULL AUTO_INCREMENT,
111 codc int(11) NOT NULL,
112 codm int(11) NOT NULL,
113 codp int(11) NOT NULL,
114 data_Consulta date NOT NULL,
115 Hora time DEFAULT NULL,
116 valor_Consulta decimal(9,2) NOT NULL,
117 usuario varchar(255) NOT NULL,
118 data_Deletado datetime NOT NULL,
119 PRIMARY KEY (bkp_id),
120 KEY codm (codm),
121 KEY codp (codp),
122 CONSTRAINT bkp_fk_1 FOREIGN KEY (codp) REFERENCES paciente (codp),
123 CONSTRAINT bkp_fk_2 FOREIGN KEY (codm) REFERENCES medico (codm)
124);
125
126
127/*Trigger da Questao*/
128drop trigger if exists trigger_consulta;
129DELIMITER |
130
131CREATE DEFINER = CURRENT_USER TRIGGER trigger_consulta AFTER DELETE ON consulta FOR EACH ROW
132BEGIN
133 INSERT INTO consultaBackups
134 values (null,old.codc,old.codm,old.codp,old.dt_consulta,
135 old.hora,old.valor_consulta,USER(),NOW());
136END
137|
138DELIMITER ;
139
140DROP TRIGGER trigger_consulta;
141DROP TABLE consultaBackups;
142
143
144/* Questao 03b
145b) Crie um trigger que antes de apagar os dados de uma consulta, verificar se ela já ocorreu,
146se sim, não permitir a exclusão.
147*/
148
149DELIMITER |
150
151CREATE DEFINER = CURRENT_USER TRIGGER trigger_checaConsulta BEFORE DELETE ON `consulta` FOR EACH ROW
152BEGIN
153 if(convert(concat(old.dt_consulta,' ',old.hora), DATETIME)< now()) then
154 SIGNAL SQLSTATE '45000'
155 SET MESSAGE_TEXT = 'Consulta ja ocorreu, logo nao pode ser apagada!', MYSQL_ERRNO = 1000;
156 end if;
157END
158|
159DELIMITER ;
160
161DROP TRIGGER trigger_checaConsulta;
162
163/* Questão 03c
164Crie uma trigger que ao inserir dados na tabela consulta faça as seguintes verificações:
1651)Verificar se a data informada não é inferior a data atual, caso seja, gerar um erro.
1662) Verificar se a data marcada ocorrerá no final de semana(sábado ou domingo), se
167sim, gerar um erro.
1683)Verificar se o horário da consulta esta entre 08:00 e 19:00hs. Caso contrário, gerar
169um erro.*/
170
171
172DELIMITER |
173
174CREATE DEFINER = CURRENT_USER TRIGGER trigger_Horarios BEFORE INSERT ON consulta FOR EACH ROW
175BEGIN
176 if(convert(concat(NEW.dt_consulta,' ',NEW.hora), DATETIME) < now()) then
177 SIGNAL SQLSTATE '45000'
178 SET MESSAGE_TEXT = 'Passado! ImpossÃvel agendar consultas!', MYSQL_ERRNO = 1000;
179 end if;
180
181 if(weekday(NEW.dt_consulta) = 5 OR weekday(NEW.dt_consulta) = 6) then
182 SIGNAL SQLSTATE '45000'
183 SET MESSAGE_TEXT = 'Final de Semana! ImpossÃvel agendar consultas', MYSQL_ERRNO = 1000;
184 end if;
185
186 if(NEW.hora < '08:00' OR NEW.hora > '19:00') then
187 SIGNAL SQLSTATE '45000'
188 SET MESSAGE_TEXT = 'Fora do expediente! ImpossÃvel agendar consultas!', MYSQL_ERRNO = 1000;
189 end if;
190END
191|
192DELIMITER ;
193
194DROP TRIGGER trigger_Horarios;