· 8 years ago · Nov 26, 2017, 02:24 PM
1Es desitja saber en tot moment quin usuari ha inserit cadascuna de les tuples de la taula Treballadors del fitxer adjunt. Es vol utilitzar per guardar aquesta informació l'atribut "usuari" de la taula Treballadors.
2
3Per mantenir aquesta informació disposem ja d'un disparador (que també podeu trobar al fitxer adjunt). Aquest disparador agafa com a nom de l'usuari que fa la inserció, el que es troba a l'única fila de la taula Usuari_actual.
4
5Implementar una solució alternativa que redueixi el nombre d'accessos a la base de dades. Aquesta solució alternativa s'ha de basar en aprofitar les possibilitats que dóna la variable NEW. A l'hora de fer aquest exercici és especialment important haver entès la transparència 220 i l'exemple de la 229.
6
7Pel joc de proves que trobareu al fitxer adjunt,i després de l'execució de lles sentències:
8insert into treballadors values(1,1000,NULL);
9insert into treballadors values(2,2000,NULL);
10l'extensió de la taula Treballadors ha de ser:
11
12num_treballador salari usuari
131 1000 bd0003
142 2000 bd0003
15-------------------------------------
16-- create table treballadors (num_treballador integer primary key, salari integer, usuari char(30));
17-- create table usuari_actual(usuari char(10));
18-- insert into usuari_actual values('bd0003');
19
20CREATE OR REPLACE FUNCTION auditoria() RETURNS TRIGGER AS $$
21 DECLARE
22 us char(30);
23 BEGIN
24 us := (SELECT usuari FROM usuari_actual);
25 NEW.usuari = us;
26 RETURN NEW;
27 END;
28 $$LANGUAGE plpgsql;
29
30CREATE TRIGGER ex172 BEFORE INSERT ON treballadors FOR EACH ROW EXECUTE PROCEDURE auditoria();
31
32-- insert into treballadors values(1,1000,NULL);
33-- insert into treballadors values(2,2000,NULL);
34---------------------------------------------------------------------------------------------------------------------------------------
35Implementar mitjançant disparadors la restricció d'integritat següent:
36No es pot esborrar l'empleat 123 ni modificar el seu número d'empleat.
37
38Cal informar dels errors a través d'excepcions tenint en compte les situacions tipificades a la taula missatgesExcepcions, que podeu trobar definida (amb els inserts corresponents) al fitxer adjunt. Concretament en el vostre procediment heu d'incloure, quan calgui, les sentències:
39SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=__; (el número que sigui, depenent de l'error)
40RAISE EXCEPTION '%',missatge;
41La variable missatge ha de ser una variable definida al vostre procediment, i del mateix tipus que l'atribut corresponent de l'esquema de la base de dades.
42
43Pel joc de proves que trobareu al fitxer adjunt i la instrucció:
44DELETE FROM empleats WHERE nempl=123;
45La sortida ha de ser:
46
47No es pot esborrar l'empleat 123 ni modificar el seu número d'empleat
48-------------------------------------
49CREATE OR REPLACE FUNCTION check_restriction() RETURNS TRIGGER AS $$
50DECLARE
51 missatge VARCHAR(100);
52BEGIN
53 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
54 IF (TG_OP = 'DELETE') THEN
55 IF (OLD.nempl = 123) THEN RAISE EXCEPTION '%', missatge;
56 END IF;
57 RETURN OLD;
58 END IF;
59 IF (TG_OP = 'UPDATE') THEN
60 IF (OLD.nempl = 123 AND NEW.nempl != 123) THEN RAISE EXCEPTION '%', missatge;
61 END IF;
62 RETURN NEW;
63 END IF;
64END $$ LANGUAGE plpgsql;
65
66CREATE TRIGGER trig1 BEFORE DELETE OR UPDATE OF nempl ON empleats
67FOR EACH ROW EXECUTE PROCEDURE check_restriction();
68---------------------------------------------------------------------------------------------------------------------------------------
69Implementar mitjançant disparadors la restricció d'integritat següent:
70No es poden esborrar empleats el dijous
71Tigueu en compte que:
72- Les restriccions d'integritat definides a la BD (primary key, foreign key,...) es violen amb menys freqüència que la restricció comprovada per aquests disparadors.
73- El dia de la setmana serà el que indiqui la única fila que hi ha d'haver sempre insertada a la taula "dia". Com podreu veure en el joc de proves que trobareu al fitxer adjunt, el dia de la setmana és el 'dijous'. Per fer altres proves podeu modificar la fila de la taula amb el nom d'un altre dia de la setmana. IMPORTANT: Tant en el programa com en la base de dades poseu el nom del dia de la setmana en MINÚSCULES.
74
75Cal informar dels errors a través d'excepcions tenint en compte les situacions tipificades a la taula missatgesExcepcions, que podeu trobar definida (amb els inserts corresponents) al fitxer adjunt. Concretament en el vostre procediment heu d'incloure, quan calgui, les sentències:
76SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=__;(el número que sigui, depenent de l'error)
77RAISE EXCEPTION '%',missatge;
78La variable missatge ha de ser una variable definida al vostre procediment, i del mateix tipus que l'atribut corresponent de l'esquema de la base de dades.
79
80Pel joc de proves que trobareu al fitxer adjunt i la instrucció:
81DELETE FROM empleats WHERE salari<=1000
82la sortida ha de ser:
83
84No es poden esborrar empleats el dijous
85-------------------------------------
86CREATE OR REPLACE FUNCTION check_condition2() RETURNS TRIGGER AS $$
87DECLARE
88 dia_actual char(10);
89 missatge varchar(50);
90BEGIN
91 SELECT dia INTO dia_actual FROM dia;
92 IF (dia_actual = 'dijous') THEN
93 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
94 RAISE EXCEPTION '%', missatge;
95 END IF;
96 RETURN NULL;
97END $$ LANGUAGE plpgsql;
98
99CREATE TRIGGER trigger2 BEFORE DELETE ON empleats
100FOR EACH STATEMENT EXECUTE PROCEDURE check_condition2();
101---------------------------------------------------------------------------------------------------------------------------------------
102Implementar mitjançant disparadors la restricció d'integritat següent:
103La suma dels sous dels empleats esborrats en una instrucció delete, no pot ser superior a la suma dels sous dels empleats que queden a la BD després de l'esborrat.
104Tigueu en compte que:
105- Per resoldre aquest exercici podeu utilitzar la taula temporal que trobareu al fitxer adjunt.
106
107Cal informar dels errors a través d'excepcions tenint en compte les situacions tipificades a la taula missatgesExcepcions, que podeu trobar definida (amb els inserts corresponents) al fitxer adjunt. Concretament en el vostre procediment heu d'incloure, quan calgui, les sentències:
108SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=__;(el número que sigui, depenent de l'error)
109RAISE EXCEPTION '%',missatge;
110La variable missatge ha de ser una variable definida al vostre procediment, i del mateix tipus que l'atribut corresponent de l'esquema de la base de dades.
111
112Pel joc de proves que trobareu al fitxer adjunt i la instrucció:
113DELETE FROM empleats WHERE salari<=2500
114la sortida ha de ser:
115
116Suma sous esborrats > Suma sous que queden
117
118-------------------------------------
119CREATE OR REPLACE FUNCTION calcul_sou_abans() RETURNS TRIGGER AS $$
120BEGIN
121 DELETE FROM TEMP;
122 INSERT INTO temp(x, y) SELECT SUM(salari), 0 FROM empleats;
123 RETURN NULL;
124END $$ LANGUAGE plpgsql;
125
126
127CREATE OR REPLACE FUNCTION check_condition3() RETURNS TRIGGER AS $$
128DECLARE
129 sou_abans1 INTEGER;
130 suma_esb INTEGER;
131 missatge VARCHAR(50);
132BEGIN
133 UPDATE TEMP
134 SET y = y + OLD.salari;
135 SELECT x, y INTO sou_abans1, suma_esb FROM TEMP;
136 IF (suma_esb >= (sou_abans1 - suma_esb)) THEN
137 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
138 RAISE EXCEPTION '%', missatge;
139 END IF;
140 RETURN OLD;
141END $$ LANGUAGE plpgsql;
142
143CREATE TRIGGER trigger4 BEFORE DELETE ON empleats
144FOR EACH STATEMENT EXECUTE PROCEDURE calcul_sou_abans();
145
146CREATE TRIGGER trigger3 BEFORE DELETE ON empleats
147FOR EACH ROW EXECUTE PROCEDURE check_condition3();
148---------------------------------------------------------------------------------------------------------------------------------------
149El procediment inclos al fitxer adjunt retorna el nom i sou dels empleats del departament amb el número de departament que es passa com a parà metre.
150
151Es vol que feu canvis en aquest procediment per tal de que:
152El procediment retorni únicament el nom i el sou dels empleats del departament que viuen a SITGES.
153El procediment capturi dels errors que es produeixin i retorni excepcions (Gestió d'errors: Opció 1 - Captura i retorn d'excepcions . Pà gina 208 transparències de procediments emmgatzemnats)
154Les situacions d'error que heu d'identificar són les tipificades a la taula missatgesExcepcions, que podeu trobar definida i amb els inserts corresponents al fitxer adjunt.
155El missatge Error Intern es refereix a qualsevol altre error que es pugui produir (WHEN OTHERS) no tipificat en la resta de les files de la taula missatgesExcepcions. Alguns exemples de situacions d'error no tifipicades poden ser: que no existeixi a la base de dades la taula empleats, que a la taula empleats no hi hagi la columna ciutat_empl,....
156Per informar dels error, en el vostre procediment, heu d'incloure, en la part del codi on s'identifiquin que ocorren les situacions d'error, les sentències:
157SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=___; (el número que sigui, depenent de l'error)
158RAISE EXCEPTION '%',missatge;
159On la variable missatge ha de ser una variable definida al vostre procediment.
160
161Pel joc de proves que trobareu al fitxer adjunt i la crida següent,
162SELECT * FROM empl_departament(1);
163el resultat ha de ser:
164
165NOM_EMPL SOU
166Josep 250000
167Miquel 200000
168
169-------------------------------------
170CREATE TYPE empl AS
171( nom char(30),
172 sou integer);
173
174CREATE OR REPLACE FUNCTION empl_departament(numdept integer) RETURNS setof empl AS $$
175DECLARE
176 e empl;
177 missatge varchar(50);
178 quants integer;
179
180BEGIN
181
182 quants = 0;
183 FOR e IN SELECT nom_empl, sou FROM empleats em WHERE em.num_dpt = numdept AND em.ciutat_empl = 'SITGES'
184 LOOP
185 quants=quants+1;
186 RETURN NEXT e;
187 END LOOP;
188 IF NOT FOUND THEN RAISE EXCEPTION '%',missatge;
189 END IF;
190
191 RETURN;
192
193 EXCEPTION
194 WHEN raise_exception THEN
195 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
196 RAISE EXCEPTION '%',missatge;
197 WHEN OTHERS THEN
198 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=2;
199 RAISE EXCEPTION '%',missatge;
200END;
201$$LANGUAGE plpgsql;
202---------------------------------------------------------------------------------------------------------------------------------------
203Donat un intèrval de DNIs, obtenir la informació de cadascun dels treballadors amb un DNI d'aquest interval.
204
205La informació que cal obtenir és la següent:
206- Per cada treballador destacat de l'interval (treballador que té un mÃnim de cinc lloguers actius), es vol obtenir les seves dades personals i la matrÃcula dels cotxes que té llogats;
207- Per la resta de treballadors, simplement es vol obtenir les seves dades personals.
208
209Tingueu en compte que:
210- En el cas de treballadors destacats, al llistat hi sortirà una fila per cadascun dels cotxes que té llogats.
211- En el cas de treballadors no destacats, al llistat hi sortirà una única fila, en què l'atribut matrÃcula tindrà valor nul.
212- El nom del procediment ha de ser llistat_treb, i ha de tenir dos parà metres corresponents als dos DNIs que defineixen l'interval.
213- El llistat ha d'estar ordenat per dni i matricula de forma ascendent.
214- Les dades de cada treballador, s'han de donar en l'ordre que apareixen al resultat del joc de proves públic.
215- El tipus de les dades que s'han de retornar han de ser els mateixos que hi ha a la taula on estan definits els atributs corresponents.
216
217El procediment ha d'informar dels errors a través d'excepcions. Les situacions d'error que heu d'identificar són les tipificades a la taula missatgesExcepcions, que podeu trobar definida i amb els inserts corresponents al fitxer adjunt. En el vostre procediment heu d'incloure, on s'identifiquin aquestes situacions, les sentències:
218SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=___; ( 1 o 2, depenent de l'error)
219RAISE EXCEPTION '%',missatge;
220On la variable missatge ha de ser una variable definida al vostre procediment.
221
222Pel joc de proves que trobareu al fitxer adjunt i la crida següent,
223SELECT * FROM llistat_treb('11111111','33333333');
224el resultat ha de ser:
225
226DNI Treballador Nom Treballador Sou base Plus sou Matricula
22722222222 Joan 1700 150 1111111111
22822222222 Joan 1700 150 2222222222
22922222222 Joan 1700 150 3333333333
23022222222 Joan 1700 150 4444444444
23122222222 Joan 1700 150 5555555555
232
233-------------------------------------
234CREATE TYPE treballador AS
235( dni char(8),
236nom char(30),
237sou_base real,
238plus real,
239matricula char(10)
240);
241CREATE TYPE lloguer AS (matricula char(10));
242
243CREATE OR REPLACE FUNCTION llistat_treb(dni_a treballadors.dni%type, dni_b treballadors.dni%type) RETURNS setof treballador AS $$
244DECLARE
245 num_lloguers integer;
246 t treballador;
247 lm lloguer;
248 missatge varchar(50);
249
250BEGIN
251 FOR t IN SELECT *
252 FROM treballadors
253 WHERE dni >= dni_a AND dni <= dni_b
254 ORDER BY dni ASC
255 LOOP
256 SELECT COUNT(*) INTO num_lloguers FROM lloguers_actius la
257 WHERE la.dni = t.dni;
258 IF (num_lloguers < 5) THEN
259 t.matricula = NULL;
260 return next t;
261 ELSE
262 FOR lm IN SELECT matricula FROM lloguers_actius la_1
263 WHERE la_1.dni = t.dni
264 ORDER BY la_1.matricula ASC
265 LOOP
266 t.matricula = lm.matricula;
267 return next t;
268 END LOOP;
269 END IF;
270 END LOOP;
271
272 IF not found THEN
273 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
274 RAISE EXCEPTION '%', missatge;
275 END IF;
276
277 RETURN;
278
279 EXCEPTION
280 WHEN raise_exception THEN
281 RAISE EXCEPTION '%', SQLERRM;
282 WHEN OTHERS THEN
283 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=2;
284 RAISE EXCEPTION '%', missatge;
285
286END;
287$$LANGUAGE plpgsql;
288---------------------------------------------------------------------------------------------------------------------------------------
289En aquest exercici es tracta de simular una asserció a base de definir disparadors. En concret, es demana definir els disparadors necessaris sobre empleats1 (veure definició de la base de dades al fitxer adjunt) per comprovar la restricció següent:
290Els valors de l'atribut ciutat1 de la taula empleats1 han d'estar inclosos en els valors de ciutat2 de la taula empleats2
291La idea és llançar una excepció en cas que s'intenti executar una sentència sobre EMPLEATS1 que pugui violar aquesta restricció.
292
293Cal informar dels errors a través d'excepcions tenint en compte les situacions tipificades a la taula missatgesExcepcions, que podeu trobar definida i amb els inserts corresponents al fitxer adjunt. Concretament en el vostre procediment heu d'incloure, quan calgui, les sentències:
294SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=__ (segons l'error 1,2,...);
295RAISE EXCEPTION '%',missatge;
296La variable missatge ha de ser una variable definida al vostre procediment.
297
298Pel joc de proves que trobareu al fitxer adjunt i la sentència:
299INSERT INTO empleats1 VALUES (1,'joan','mad');
300La sortida ha de ser:
301
302Els valors de l'atribut ciutat1 d'empleats1 han d''estar inclosos en els valors de ciutat2
303
304-------------------------------------
305CREATE OR REPLACE FUNCTION check_conditions() RETURNS TRIGGER AS $$
306DECLARE
307 missatge VARCHAR(100);
308BEGIN
309 IF NOT EXISTS (SELECT * FROM empleats2 e2 WHERE e2.ciutat2 = NEW.ciutat1) THEN
310 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
311 RAISE EXCEPTION '%', missatge;
312 END IF;
313
314 RETURN NEW;
315
316 EXCEPTION
317 WHEN raise_exception THEN
318 RAISE EXCEPTION '%', missatge;
319END;
320$$LANGUAGE plpgsql;
321
322
323CREATE TRIGGER TRIGGER BEFORE INSERT OR UPDATE OF ciutat1 ON empleats1
324FOR EACH ROW EXECUTE PROCEDURE check_conditions();
325---------------------------------------------------------------------------------------------------------------------------------------
326En aquest exercici es tracta definir els disparadors necessaris sobre empleats2 (veure definició de la base de dades al fitxer adjunt) per mantenir la restricció següent:
327Els valors de l'atribut ciutat1 de la taula empleats1 han d'estar inclosos en els valors de ciutat2 de la taula empleats2
328Per mantenir la restricció, la idea és que:
329
330En lloc de treure un missatge d'error en cas que s'intenti executar una sentència sobre empleats2 que pugui violar la restricció,
331cal executar operacions compensatories per assegurar el compliment de l'asserció. En concret aquestes operacions compensatories ÚNICAMENT podran ser operacions DELETE.
332
333Pel joc de proves que trobareu al fitxer adjunt, i la sentència:
334DELETE FROM empleats2 WHERE nemp2=1;
335La sentència s'executarà sense cap problema,i l'estat de la base de dades just després ha de ser:
336
337Taula empleats1
338nemp1 nom1 ciutat1
3391 joan bcn
3402 maria mad
341
342Taula empleats2
343nemp2 nom2 ciutat2
3442 pere mad
3453 enric bcn
346
347-------------------------------------
348CREATE or replace FUNCTION restriction() RETURNS trigger AS $$
349DECLARE
350BEGIN
351 IF TG_OP = 'UPDATE' THEN
352 IF (OLD.ciutat2 <> NEW.ciutat2) THEN --Si la ciudad se ha actualizado
353 IF NOT EXISTS(SELECT * FROM empleats2 WHERE ciutat2 = OLD.ciutat2) THEN --Si no queda cap ROW amb ciutat2 = ciutat modificada
354 --Borrar todo en empl1 donde ciutat1 = OLD.ciutat2;
355 DELETE FROM empleats1 WHERE ciutat1 = OLD.ciutat2;
356 END IF;
357 END IF;
358 RETURN NEW;
359 ELSE --TG_OP = 'DELETE'
360 IF NOT EXISTS(SELECT * FROM empleats2 WHERE ciutat2 = OLD.ciutat2) THEN
361 DELETE FROM empleats1 WHERE ciutat1 = OLD.ciutat2;
362 END IF;
363 RETURN OLD;
364 END IF;
365END;
366$$LANGUAGE plpgsql;
367
368CREATE TRIGGER trig AFTER
369DELETE OR UPDATE OF ciutat2 ON empleats2
370FOR EACH ROW EXECUTE PROCEDURE restriction();
371---------------------------------------------------------------------------------------------------------------------------------------
372Disposem de la base de dades del fitxer adjunt que gestiona clubs esportius i socis d'aquests clubs. Cal implementar un procediment emmagatzemat que enregistra l'assignació d'un soci a un club.
373
374El procediment ha de:
375- Enregistrar l'assignació d'un soci a un club (el soci i club es passen com a parà metre), inserint la fila corresponent a la taula Socisclubs.
376- En cas que el club amb la nova assignació passi a tenir més de 5 socis, inserir el club a la taula Clubs_amb_mes_de_5_socis.
377- Cal que el procediment informi d'errors mitjançant excepcions si la nova assignació fa que no es compleixi alguna de les restriccions d'usuari següents:
378RI1: Un club no pot tenir més de 10 socis
379RI2: Un club no pot tenir més homes que dones
380- El nom del procediment ha de ser assignar_individual.
381- El procediment no retorna cap resultat.
382- El tipus dels parà metres d'entrada han de ser els mateixos que hi ha a la taula on estan definits els atributs corresponents.
383
384El procediment ha d'informar dels errors a través d'excepcions. Les situacions d'error que cal que identifiqui són les tipificades a la taula missatgesExcepcions que podeu trobar definida (amb els inserts corresponents) al fitxer adjunt. En el vostre procediment heu d'incloure, on s'identifiquin aquestes situacions, les sentències:
385SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=___; ( 1 .. 5, depenent de l'error)
386RAISE EXCEPTION '%',missatge;
387On la variable missatge ha de ser una variable definida al vostre procediment.
388
389Suposem el joc de proves que trobareu al fitxer adjunt i la sentència
390select * from assignar_individual('anna','escacs');
391La sentència s'executarà sense cap problema, i l'estat de la base de dades just després ha de ser:
392
393Taula Socisclubs
394nsoci nclub
395anna escacs
396joanna petanca
397josefa petanca
398pere petanca
399Taula clubs_amb_mes_de_5_soci
400sense cap fila
401
402-------------------------------------
403CREATE OR REPLACE FUNCTION assignar_individual(nombreSocio char(10), nombreClub char(10)) RETURNS void AS $$
404DECLARE
405 numSocios integer;
406 numHombres integer;
407 numMujeres integer;
408 missatge varchar(50);
409BEGIN
410 INSERT INTO socisclubs VALUES (nombreSocio, nombreClub); --Insertar socio en el club
411
412 SELECT COUNT(*) INTO numSocios FROM socisclubs sc WHERE sc.nclub = nombreClub;
413
414
415 SELECT COUNT(*) INTO numHombres FROM socisclubs sc, socis s
416 WHERE sc.nclub = nombreClub AND sc.nsoci = s.nsoci AND s.sexe = 'M';
417
418 numMujeres := numSocios - numHombres;
419
420 IF numHombres > numMujeres THEN
421 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 2;
422 RAISE EXCEPTION '%', missatge;
423 END IF;
424
425
426 IF numSocios > 5 AND numSocios <= 10 THEN --El club tiene mas de 5 miembros, menos de 11.
427 IF NOT EXISTS(SELECT * FROM clubs_amb_mes_de_5_socis m WHERE m.nclub = nombreClub) THEN --El club no ha sido registrado aun en clubs_amb_mes_de_5_socis
428 INSERT INTO clubs_amb_mes_de_5_socis VALUES(nombreClub);
429 END IF;
430 ELSE
431 IF numSocios > 10 THEN --Excepcion: mas de 10 miembros en club
432 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
433 RAISE EXCEPTION '%', missatge;
434 END IF;
435 END IF;
436
437 EXCEPTION
438 WHEN raise_exception THEN
439 RAISE EXCEPTION '%', SQLERRM;
440 WHEN unique_violation THEN --Socio ya existe en club
441 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 3;
442 RAISE EXCEPTION '%', missatge;
443 WHEN foreign_key_violation THEN --Socio o club no existen
444 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 4;
445 RAISE EXCEPTION '%', missatge;
446 WHEN OTHERS THEN --Error interno
447 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 5;
448 RAISE EXCEPTION '%', missatge;
449END;
450$$LANGUAGE plpgsql;
451---------------------------------------------------------------------------------------------------------------------------------------
452El procediment inclòs al fitxer adjunt esborra el departament identificat pel número de departament que es passa com a parà metre. En cas que no es pugui esborrar el departament, el procediment retorna una excepció. Concretament retorna una excepció en cas que el departament no existeix (El departament no existeix), i en cas que en executar el procediment es produeixi qualsevol error de la base de dades (Error intern).
453
454Es vol que feu canvis en aquest procediment per tal de que:
455- Quan es produieix l'error número 1 de la taula missatgesExcepcions, salti una nova excepció amb el missatge corresponent.
456
457Pel joc de proves que trobareu al fitxer adjunt i després de les sentències següents:
458SELECT * FROM eliminar_dept(1);
459SELECT num_dpt FROM departaments; el resultat ha de l'execució de la segona sentència ha de ser:
460
461Num_dpt
4622
463-------------------------------------
464CREATE OR REPLACE FUNCTION eliminar_dept(numdept INTEGER) RETURNS void AS $$
465DECLARE
466 missatge VARCHAR(50);
467
468BEGIN
469
470 IF EXISTS (SELECT * FROM empleats em WHERE em.num_dpt = numdept) THEN
471 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
472 RAISE EXCEPTION '%',missatge;
473 END IF;
474
475 DELETE FROM departaments WHERE num_dpt = numdept;
476
477 IF NOT FOUND THEN
478 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=2;
479 RAISE EXCEPTION '%',missatge;
480 END IF;
481
482EXCEPTION
483 WHEN raise_exception THEN
484 RAISE EXCEPTION '%',SQLERRM;
485 WHEN OTHERS THEN
486 SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=3;
487 RAISE EXCEPTION '%',missatge;
488
489END;
490$$LANGUAGE plpgsql;
491---------------------------------------------------------------------------------------------------------------------------------------
492Una part dels codis no són meus, és una base per a estudiar els procedimients i triggers