· 9 years ago · Nov 15, 2016, 12:30 AM
1
2/* 1. U bazi autoradionica: Modificirati tablicu odjel tako da dodate novi atribut najmanjaPlaca
3tipa DOUBLE.
4Napisati proceduru koja će:
5• primiti šifru odjela
6• za zadani odjel izraÄunati plaću radnika koji radi u zadanom odjelu a ima
7najmanju plaću u tom odjelu
8• u novostvoreni atribut najmanjaPlaca unijeti prethodno izraÄunat iznos
9• plaću je potrebno izraÄunati kao umnožak iznosa osnovice i koeficijenta plaće
10Napisati primjer poziva procedure */
11
12ALTER TABLE odjel ADD COLUMN najmanjaPlaca DOUBLE;
13
14DROP PROCEDURE IF EXISTS minimalnaplaca;
15
16DELIMITER //
17CREATE PROCEDURE minimalnaplaca(sifra INT)
18BEGIN
19
20 DECLARE placa DOUBLE;
21
22 SELECT MIN(KoefPlaca*IznosOsnovice) INTO placa
23
24 FROM radnik
25 WHERE sifOdjel = sifra;
26
27 UPDATE odjel
28 SET najmanjaPlaca = placa
29 WHERE sifOdjel = sifra;
30END; //
31DELIMITER ;
32
33
34CALL minimalnaplaca(1);
35
36
37/* 2. U bazi studenti: Napisati funkciju koja će unijeti novog studenta te provjeriti postoji li već
38student sa istim jmbag-om, da li su poštanski brojevi valjani, da datum upisa nije danas ili u
39budućnosti, te da li smjer postoji. U sluÄaju pogreÅ¡ke, vratiti poruku o tome Å¡to se dogodilo.
40Napisati primjer poziva funkcije. */
41DROP FUNCTION IF EXISTS novistudent;
42DELIMITER //
43CREATE FUNCTION novistudent(sjmbag CHAR(10), sime VARCHAR(50), sprezime VARCHAR(50), datum DATE, postprebivanje INT, poststan INT, smjer INT)
44RETURNS VARCHAR(255)
45DETERMINISTIC
46BEGIN
47
48DECLARE error VARCHAR(255) DEFAULT "upisan";
49
50IF sjmbag IN( SELECT studenti.jmbag FROM studenti)
51THEN SET error= "Već postoji student s ovim jmbagom";
52RETURN error;
53END IF;
54
55IF postprebivanje NOT IN( SELECT mjesta.postbr FROM mjesta)
56THEN SET error= "Nije valjan broj mjesta";
57RETURN error;
58END IF;
59
60IF poststan NOT IN( SELECT mjesta.postbr FROM mjesta)
61THEN SET error= "Nije valjan broj mjesta";
62RETURN error;
63END IF;
64
65IF smjer NOT IN( SELECT smjerovi.id FROM smjerovi)
66THEN SET error= "Ne postoji taj smjer";
67RETURN error;
68END IF;
69
70
71IF datum > CURDATE()-1
72THEN SET error= "Nije dobar datum";
73RETURN error;
74END IF;
75
76INSERT INTO studenti SET jmbag = sjmbag,
77ime = sime,
78prezime = sprezime,
79datumUpisa = datum,
80postBrPrebivanje = postprebivanje,
81postBrStanovanja = poststan,
82idSmjer = smjer;
83
84RETURN error;
85
86
87END; //
88DELIMITER ;
89
90
91SELECT novistudent(11111111, 'Testic', 'Testovski', '2007-07-24', 10000, 10000, 1);
92
93
94/* davorovo */
95DROP FUNCTION IF EXISTS fooStudent;
96DELIMITER //
97CREATE FUNCTION fooStudent(jmbagS INT, imeS VARCHAR(50), prezimeS VARCHAR(50), datumS DATE, prebS INT, stanS INT, smjerS INT)
98RETURNS VARCHAR(100)
99DETERMINISTIC
100BEGIN
101 DECLARE broj INT;
102 DECLARE pbr1 INT;
103 DECLARE pbr2 INT;
104 DECLARE ids INT;
105
106 SELECT COUNT(*) INTO ids FROM smjerovi
107 WHERE id = smjerS;
108 SELECT COUNT(*) INTO pbr1 FROM mjesta
109 WHERE postbr = prebS;
110 SELECT COUNT(*) INTO pbr2 FROM mjesta
111 WHERE postbr = stanS;
112 SELECT COUNT(*) INTO broj FROM studenti
113 WHERE jmbag = jmbagS;
114
115 IF datumS >= CURDATE() THEN
116 RETURN "Neispravan datum!";
117 END IF;
118 IF broj != 0 THEN
119 RETURN "Student s istim JMBAG-om vec postoji!";
120 END IF;
121 IF pbr1 = 0 || pbr2 = 0 THEN
122 RETURN "Neispravan postanski broj stanovanja/prebivanja!";
123 END IF;
124 IF ids = 0 THEN
125 RETURN "Neispravan ID smjera!";
126 END IF;
127
128 INSERT INTO studenti (jmbag, ime, prezime, datumUpisa, postBrPrebivanje, postBrStanovanja, idSmjer) VALUES (jmbagS, imeS, prezimeS, datumS, prebS, stanS, smjerS);
129 RETURN "Student upisan!";
130END; //
131DELIMITER ;
132
133
134SELECT fooStudent(4564451, "Marko", "Markovic", "2010-09-28", 10050, 10000, 3);
135
136
137
138/*3. U bazi studenti: Napisati funkciju koja prima naziv smjera. Funkcija mora za sve kolegije sa
139zadanog smjera u atribut opis upisati tekst:
140a. „Lagani kolegij“ – ako je prosjek ocjena na tom kolegiju veći od 3.5
141b. „Težak kolegij“ – ako je prosjek ocjena na tom kolegiju manji ili jednak 3.5
142c. Funkcija vraća broj kolegija kojima je upisala u opis „Težak kolegij“.
143Zadatak je obavezno riješiti koristeći kursore.
144Napisati primjer poziva funkcije.*/
145
146
147DROP FUNCTION IF EXISTS smjerovi;
148DELIMITER //
149CREATE FUNCTION smjerovi(snaziv VARCHAR(100))
150RETURNS VARCHAR(100)
151DETERMINISTIC
152BEGIN
153
154DECLARE dohvaceno INT DEFAULT NULL;
155DECLARE prosjecic DOUBLE(3,2);
156DECLARE brojac, i INT DEFAULT 0;
157DECLARE smrdi INT;
158
159
160
161DECLARE kur CURSOR FOR SELECT kolegiji.id, AVG(ocjene.ocjena) FROM ocjene
162JOIN kolegiji ON ocjene.idKolegij=kolegiji.id
163JOIN smjerovi ON kolegiji.idSmjer=smjerovi.id
164WHERE smjerovi.naziv=snaziv
165GROUP BY kolegiji.id;
166
167OPEN kur;
168
169SELECT FOUND_ROWS() INTO dohvaceno;
170
171WHILE i<dohvaceno DO
172
173FETCH kur INTO smrdi,prosjecic;
174
175IF prosjecic > 3.5 THEN
176
177UPDATE kolegiji
178SET kolegiji.opis="Lagani kolegij"
179WHERE kolegiji.id=smrdi;
180
181ELSE
182
183UPDATE kolegiji
184SET kolegiji.opis="Težak kolegij"
185WHERE kolegiji.id=smrdi;
186
187SET brojac=brojac+1;
188
189END IF;
190SET i=i+1;
191
192END WHILE;
193CLOSE kur;
194
195RETURN brojac;
196
197END; //
198DELIMITER ;
199
200SELECT smjerovi("smjer informatika");
201
202
203
204/* 4. U bazi autoradionica: Napisati proceduru koja će koristeći proceduru iz prvog zadatka
205popuniti u tablici odjel atribut najmanjaPlaca. Procedura prima podatak o županiji te
206popunjava atribut najmanjaPlaca iskljuÄivo za odjele koji se odnose na radnika iz zadane
207županije.
208Zadatak je obavezno riješiti koristeći kursore.
209
210Ako za određeni odjel procedura ne uspije pronaći zapis (pogreška NOT FOUND), potrebno je
211problem riješiti koristeći odgovarajući handler. Procedura vraća broj dohvaćenih i broj
212obrađenih zapisa.
213Napisati primjer poziva procedure*/
214
215
216DROP PROCEDURE IF EXISTS minimalnaplacazupanija;
217
218DELIMITER //
219CREATE PROCEDURE minimalnaplacazupanija(sifrazupanije INT,OUT obradenizahtjevi INT, OUT brojdohvacenih INT)
220BEGIN
221
222 DECLARE placa DOUBLE;
223DECLARE flag BOOL;
224DECLARE idodjelica INT;
225
226 DECLARE kursoric CURSOR FOR SELECT odjel.sifOdjel
227 FROM odjel
228 JOIN radnik ON odjel.sifOdjel = radnik.sifOdjel
229 JOIN mjesto ON radnik.pbrStan = mjesto.pbrMjesto
230 JOIN zupanija ON mjesto.sifZupanija = zupanija.sifZupanija
231 WHERE zupanija.sifZupanija = sifrazupanije;
232
233 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
234
235 SET flag = FALSE;
236 SET brojdohvacenih=0;
237 SET obradenizahtjevi=0;
238
239 OPEN kursoric;
240 SELECT FOUND_ROWS() INTO brojdohvacenih;
241
242 petlja:LOOP
243
244 FETCH kursoric INTO idodjelica;
245
246 IF flag=TRUE THEN
247 LEAVE petlja;
248 END IF;
249
250 CALL minimalnaplaca(idodjelica);
251 SET obradenizahtjevi = obradenizahtjevi + 1;
252
253 END LOOP;
254 CLOSE kursoric;
255
256
257END; //
258DELIMITER ;
259
260
261CALL minimalnaplacazupanija(21, @a, @b);
262SELECT @a AS obradeno, @b AS dohvaceni;
263
264
265/* 5. U bazi studenti: Napisati funkciju koja će primiti naziv smjera. Funkcija mora svim studentima
266sa zadanog smjera postaviti datum upisa na 1.9.2016. Funkcija vraća broj obrađenih zapisa..
267Zadatak je potrebno riješiti koristeći kursore i odgovarajuće handlere.
268Napisati primjer poziva funkcije*/
269
270
271
272DROP FUNCTION IF EXISTS smjeroviupis;
273DELIMITER //
274CREATE FUNCTION smjeroviupis(snaziv VARCHAR(100))
275RETURNS VARCHAR(100)
276DETERMINISTIC
277BEGIN
278
279DECLARE sjmbag CHAR(10);
280DECLARE obradenih INT DEFAULT 0;
281DECLARE flag BOOL;
282
283DECLARE c CURSOR FOR SELECT studenti.jmbag
284 FROM studenti
285 JOIN smjerovi ON studenti.idSmjer = smjerovi.id
286 WHERE smjerovi.naziv = snaziv;
287
288DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
289
290SET flag = FALSE;
291
292OPEN c;
293petlja:LOOP
294
295 IF flag=TRUE THEN
296 LEAVE petlja;
297 END IF;
298
299 FETCH c INTO sjmbag;
300
301 UPDATE studenti
302 SET datumUpisa = '2016-09-01'
303 WHERE jmbag = sjmbag;
304
305 SET obradenih=obradenih+1;
306
307 END LOOP;
308
309 CLOSE c;
310
311
312RETURN obradenih;
313
314END; //
315DELIMITER ;
316
317
318SELECT smjeroviupis("smjer informatika");
319
320
321
322/* 6. U bazi studenti: Napisati proceduru koja će primiti naziv županije i broj N. Procedura mora za
323sva mjesta u zadanoj županiji ispisati:
324a. Naziv mjesta
325b. „Velik broj nastavnika“ – ako u tom mjestu stanuje više od N nastavnika
326c. „Mali broj nastavnika“ – ako u tom mjestu stanuje N ili manje nastavnika
327Napisati primjer poziva procedure.
328*/
329
330DROP PROCEDURE IF EXISTS proceduralnazupanija;
331
332DELIMITER //
333CREATE PROCEDURE proceduralnazupanija(IN nazivzupanije VARCHAR(100) , IN N INT)
334BEGIN
335
336 DECLARE flag BOOL;
337 DECLARE mjestonaziv VARCHAR(50);
338 DECLARE broj INT DEFAULT 0;
339
340 DECLARE c CURSOR FOR SELECT mjesta.nazivMjesto, COUNT(nastavnici.jmbg)
341 FROM nastavnici
342 JOIN mjesta ON nastavnici.postBr = mjesta.postbr
343 JOIN zupanije ON mjesta.idZupanija = zupanije.id
344 WHERE zupanije.nazivZupanija = nazivzupanije
345 GROUP BY mjesta.nazivMjesto;
346
347 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
348
349 SET flag = FALSE;
350
351 DROP TEMPORARY TABLE IF EXISTS tmp;
352 CREATE TEMPORARY TABLE tmp (naziv VARCHAR(50), stanje VARCHAR(50));
353
354 OPEN c;
355
356 petlja:LOOP
357
358 FETCH c INTO mjestonaziv, broj;
359
360 IF flag=TRUE THEN
361 LEAVE petlja;
362 END IF;
363
364
365 IF broj > N THEN
366 INSERT INTO tmp(naziv, stanje) VALUES (mjestonaziv, "Velik broj nastavnika");
367 ELSE
368 INSERT INTO tmp(naziv, stanje) VALUES (mjestonaziv, "Mali broj nastavnika");
369 END IF;
370
371
372 END LOOP;
373 CLOSE c;
374SELECT * FROM tmp;
375
376END; //
377DELIMITER ;
378
379
380CALL proceduralnazupanija("Grad Zagreb", 17);
381
382
383
384
385/* 7. U bazi studenti: Napisati proceduru koja će studenta sa ocjenom 1 iz nekog kolegija
386kopirati u privremenu tablicu s atributima (opisUsmenog, ime, prezime, jmbag).
387U atribut opisUsmenog napisati "Usmeni iz: X, Y, Z..." XYZ su kolegiji iz kojih student ima
388barem jednu jedinicu.
389Napisati primjer poziva procedure. */
390
391
392DROP PROCEDURE IF EXISTS proceduralnistudenti;
393
394DELIMITER //
395CREATE PROCEDURE proceduralnistudenti()
396BEGIN
397
398 DECLARE flag BOOL;
399 DECLARE ime, prezime,knaziv VARCHAR(50);
400 DECLARE jj CHAR(10);
401
402 DECLARE broj INT DEFAULT 0;
403
404 DECLARE c CURSOR FOR SELECT DISTINCT studenti.ime, studenti.prezime, studenti.jmbag, kolegiji.naziv
405 FROM studenti
406 JOIN ocjene ON studenti.jmbag=ocjene.jmbagStudent
407 JOIN kolegiji ON ocjene.idKolegij=kolegiji.id
408 WHERE ocjene.ocjena=1;
409
410 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
411
412 SET flag = FALSE;
413
414 DROP TEMPORARY TABLE IF EXISTS tmp;
415 CREATE TEMPORARY TABLE tmp (opisUsmenog VARCHAR(50), ime VARCHAR(50), prezime VARCHAR(50), jmbag CHAR(10));
416
417 OPEN c;
418
419 petlja:LOOP
420
421 FETCH c INTO ime, prezime, jj, knaziv;
422
423 IF flag=TRUE THEN
424 LEAVE petlja;
425 END IF;
426
427 IF jj NOT IN(SELECT tmp.jmbag FROM tmp) THEN
428 INSERT INTO tmp(opisUsmenog, ime,prezime,jmbag) VALUES (CONCAT("usmeni iz:", knaziv), ime, prezime, jj) ;
429 ELSE
430 UPDATE tmp
431 SET opisUsmenog = CONCAT(opisUsmenog, ", ", knaziv)
432 WHERE jmbag = jj;
433 END IF;
434
435 END LOOP;
436 CLOSE c;
437SELECT * FROM tmp;
438
439END; //
440DELIMITER ;
441
442CALL proceduralnistudenti();
443
444
445
446/* 8. U bazi autoradionica: Napisati proceduru koja će ispisati podatke klijenta (ime, prezime i
447Å¡ifru), te uz njega ispisati iznos popusta koji je taj klijent ostvario. Popust se dodjeljuje po
448slijedećem principu:
449- 5% popusta za do 10 sati kvara,
450- 10% popusta za 11 do 20 sati kvara,
451- 15% popusta za 21 do 30 sati kvara,
452- 20% popusta za 31 do 40 sati kvara.
453Potrebno je koristiti kursor, handler i privremenu tablicu. Ispisati samo one klijente koji imaju
454pravo na popust. Osigurat da po završetku procedure privremena tablica više nije dostupna.
455Napisati primjer poziva procedure. */
456
457
458DROP PROCEDURE IF EXISTS radionicapopusti;
459
460DELIMITER //
461CREATE PROCEDURE radionicapopusti()
462BEGIN
463
464 DECLARE flag BOOL;
465 DECLARE ime, prezime VARCHAR(50);
466 DECLARE ss INT;
467 DECLARE sumica DOUBLE;
468
469 DECLARE broj INT DEFAULT 0;
470
471 DECLARE c CURSOR FOR SELECT klijent.imeKlijent, klijent.prezimeKlijent, klijent.sifKlijent, SUM(satiKvar)
472 FROM klijent
473 NATURAL JOIN nalog
474 NATURAL JOIN kvar
475 GROUP BY klijent.imeKlijent, klijent.prezimeKlijent, klijent.sifKlijent;
476
477
478 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
479
480 SET flag = FALSE;
481
482 DROP TEMPORARY TABLE IF EXISTS tmp;
483 CREATE TEMPORARY TABLE tmp (ime VARCHAR(50), prezime VARCHAR(50), sifra CHAR(10), iznospopusta VARCHAR(10) );
484
485 OPEN c;
486
487 petlja:LOOP
488
489 FETCH c INTO ime, prezime, ss, sumica;
490
491 IF flag=TRUE THEN
492 LEAVE petlja;
493 END IF;
494
495 IF sumica <=10 THEN
496 INSERT INTO tmp(ime,prezime,sifra,iznospopusta) VALUES (ime,prezime,ss,"5%");
497 END IF;
498
499 IF sumica <=20 AND sumica >=11 THEN
500 INSERT INTO tmp(ime,prezime,sifra,iznospopusta) VALUES (ime,prezime,ss,"10%");
501 END IF;
502 IF sumica <=30 AND sumica >=21 THEN
503 INSERT INTO tmp(ime,prezime,sifra,iznospopusta) VALUES (ime,prezime,ss,"15%");
504 END IF;
505 IF sumica <=40 AND sumica >=31 THEN
506 INSERT INTO tmp(ime,prezime,sifra,iznospopusta) VALUES (ime,prezime,ss,"20%");
507 END IF;
508
509
510 END LOOP;
511 CLOSE c;
512SELECT * FROM tmp;
513 DROP TEMPORARY TABLE IF EXISTS tmp;
514
515END; //
516DELIMITER ;
517
518CALL radionicapopusti();
519
520
521
522/* 9. U bazi studenti: Potrebno je tablici studenti dodati novi atribut prosjecnaOcjena.
523Napisati proceduru koja će pomoću kursora i handlera svim studentima izraÄunati
524njihovu prosjeÄnu ocjenu te ju upisat u novokreirani atribut. Ukoliko student nema nijednu ocjenu
525potrebno je upisati vrijednost 'NEOCJENJEN' (obratiti pažnju na
526tip podatka atributa prosjecnaOcjena). Procedura treba ispisat broj dohvaćenih
527podataka, te broj neocijenjenih studenata.
528Napisati primjer poziva procedure.*/
529
530ALTER TABLE studenti DROP COLUMN prosjecnaOcjena;
531ALTER TABLE studenti ADD COLUMN prosjecnaOcjena VARCHAR(50);
532
533DROP PROCEDURE IF EXISTS STUDAVG;
534
535DELIMITER //
536CREATE PROCEDURE STUDAVG()
537BEGIN
538
539
540DECLARE flag BOOL;
541
542 DECLARE prosjecic DOUBLE;
543 DECLARE sjmbag CHAR(10);
544 DECLARE neoc,DOH INT DEFAULT 0;
545
546 DECLARE c CURSOR FOR SELECT studenti.jmbag, AVG(ocjene.ocjena)
547 FROM studenti
548 LEFT JOIN ocjene ON studenti.jmbag=ocjene.jmbagStudent
549 GROUP BY studenti.jmbag;
550
551
552
553 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
554
555 SET flag = FALSE;
556
557 OPEN c;
558 SELECT FOUND_ROWS() INTO doh;
559 petlja:LOOP
560
561 FETCH c INTO sjmbag, prosjecic;
562
563 IF flag=TRUE THEN
564 LEAVE petlja;
565 END IF;
566
567
568 IF prosjecic IS NOT NULL THEN
569 UPDATE studenti
570 SET prosjecnaOcjena = prosjecic
571 WHERE studenti.jmbag=sjmbag;
572
573 ELSE
574 UPDATE studenti
575 SET prosjecnaOcjena = "NEOCIJENJEN"
576 WHERE jmbag = sjmbag;
577
578
579
580 SET neoc = neoc + 1;
581 END IF;
582 END LOOP;
583 CLOSE c;
584
585 SELECT doh AS Dohvaceni, neoc AS Neocijenjeni;
586END; //
587DELIMITER ;
588
589
590
591CALL STUDAVG();
592
593
594
595
596/* u bazi radionica: Napisati funkciju koja prima dvije šifre radnika. Ti radnici trebaju zamijeniti odjele i svoje već
597preuzete naloge.
598
599Potrebno je ažurirati naloge sukladno planu kao i šifre odjela kojima radnici priapdaju
600
601Funkcija vraća sljedeću poruku: Radnik IME_Prezime je zamijenio dojel s radnikom ime_prezime
602te njihovih br_naloga naloga
603
604Zadatak je potrebno riješiti s kursorom
605
606Napisati primjer poziva funkcije.*/
607
608DROP FUNCTION IF EXISTS wtfRadnici;
609DELIMITER //
610CREATE FUNCTION wtfRadnici(sif1 INT, sif2 INT)
611RETURNS VARCHAR(50)
612DETERMINISTIC
613BEGIN
614 DECLARE trenutni_sifra, trenutni_odj, drugi, temp, temp2, drugi2 INT;
615 DECLARE ime1, prez1, ime2, prez2 VARCHAR(50);
616 DECLARE flag BOOL;
617 DECLARE kur CURSOR FOR SELECT radnik.sifRadnik, radnik.imeRadnik, radnik.prezimeRadnik, radnik.sifOdjel FROM radnik
618 JOIN nalog ON radnik.sifRadnik = nalog.sifRadnik
619 JOIN odjel ON radnik.sifOdjel = odjel.sifOdjel
620 WHERE nalog.sifRadnik = sif1
621 OR nalog.sifRadnik = sif2;
622
623
624 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
625
626 SELECT imeRadnik INTO ime1 FROM radnik
627 WHERE sifRadnik= sif1;
628 SELECT imeRadnik INTO ime2 FROM radnik
629 WHERE sifRadnik= sif2;
630
631 OPEN kur;
632
633 petlja:LOOP
634 FETCH kur INTO trenutni_sifra, ime1, prez1, trenutni_odj;
635
636 IF flag = TRUE THEN
637 LEAVE petlja;
638 END IF;
639
640 IF trenutni_sifra = sif1 THEN
641
642
643 SELECT sifOdjel INTO temp FROM radnik
644 WHERE sifRadnik = sif1;
645
646 SELECT sifOdjel INTO drugi FROM radnik
647 WHERE sifRadnik = sif2;
648
649 UPDATE radnik
650 SET sifOdjel = drugi
651 WHERE sifRadnik = trenutni_sifra;
652 UPDATE radnik
653 SET sifOdjel = temp
654 WHERE sifRadnik = sif2;
655
656 /* *** */
657
658 SELECT sifRadnik INTO temp2 FROM radnik
659 WHERE sifRadnik = sif1;
660
661 SELECT sifRadnik INTO drugi2 FROM radnik
662 WHERE sifRadnik = sif2;
663
664
665 UPDATE nalog
666 SET sifRadnik = 999
667 WHERE sifRadnik = trenutni_sifra;
668
669
670 UPDATE nalog
671 SET sifRadnik = temp2
672 WHERE sifRadnik = sif2;
673
674 UPDATE nalog
675 SET sifRadnik = drugi2
676 WHERE sifRadnik = 999;
677
678
679 ELSE
680
681 SELECT sifOdjel INTO temp FROM radnik
682 WHERE sifRadnik = sif2;
683
684 SELECT sifOdjel INTO drugi FROM radnik
685 WHERE sifRadnik = sif1;
686
687 UPDATE radnik
688 SET sifOdjel = drugi
689 WHERE sifRadnik = trenutni_sifra;
690 UPDATE radnik
691 SET sifOdjel = temp
692 WHERE sifRadnik = sif1;
693
694 /* ******** */
695
696 SELECT sifRadnik INTO temp2 FROM radnik
697 WHERE sifRadnik = sif2;
698
699 SELECT sifRadnik INTO drugi2 FROM radnik
700 WHERE sifRadnik = sif1;
701
702
703
704 UPDATE nalog
705 SET sifRadnik = 999
706 WHERE sifRadnik = trenutni_sifra;
707
708
709 UPDATE nalog
710 SET sifRadnik = temp2
711 WHERE sifRadnik = sif1;
712
713 UPDATE nalog
714 SET sifRadnik = drugi2
715 WHERE sifRadnik = 999;
716
717
718
719 END IF;
720 END LOOP;
721 CLOSE kur;
722 RETURN CONCAT("Radnik ", ime1, " je zamijenjen s ", ime2);
723END; //
724DELIMITER ;
725
726
727SELECT wtfRadnici(379,326);
728
729/* krace proba */
730
731/* u bazi radionica: Napisati funkciju koja prima dvije šifre radnika. Ti radnici trebaju zamijeniti odjele i svoje već
732preuzete naloge.
733
734Potrebno je ažurirati naloge sukladno planu kao i šifre odjela kojima radnici priapdaju
735
736Funkcija vraća sljedeću poruku: Radnik IME_Prezime je zamijenio dojel s radnikom ime_prezime
737te njihovih br_naloga naloga
738
739Zadatak je potrebno riješiti s kursorom
740
741Napisati primjer poziva funkcije.*/
742
743DROP FUNCTION IF EXISTS wtfRadnici;
744DELIMITER //
745CREATE FUNCTION wtfRadnici(sif1 INT, sif2 INT)
746RETURNS VARCHAR(50)
747DETERMINISTIC
748BEGIN
749DECLARE odjel_jedan, odjel_dva, br_nalog1, br_nalog2 INT DEFAULT 0;
750DECLARE ime1, ime2, sifrakv, sifrarad VARCHAR(50);
751DECLARE flag BOOL DEFAULT FALSE;
752
753DECLARE cur CURSOR FOR SELECT sifKvar, sifRadnik FROM nalog
754WHERE sifRadnik = sif1 OR sifRadnik = sif2;
755
756DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
757
758SELECT sifOdjel, imeRadnik INTO odjel_jedan, ime1 FROM radnik
759WHERE sifRadnik = sif1;
760SELECT sifOdjel, imeRadnik INTO odjel_dva, ime2 FROM radnik
761WHERE sifRadnik = sif2;
762
763
764UPDATE radnik
765SET sifOdjel = odjel_dva
766WHERE sifRadnik = sif1;
767
768UPDATE radnik
769SET sifOdjel = odjel_jedan
770WHERE sifRadnik = sif2;
771
772OPEN cur;
773
774petlja:LOOP
775FETCH cur INTO sifrakv, sifrarad;
776
777
778 IF flag = TRUE THEN
779 LEAVE petlja;
780 END IF;
781
782IF sifrarad= sif1 THEN
783SET br_nalog1 = br_nalog1 + 1;
784ELSEIF sifrarad = sif2 THEN
785SET br_nalog2 = br_nalog2 + 1;
786END IF;
787
788UPDATE nalog
789SET sifRadnik = sif1+sif2-sifrarad
790WHERE sifKvar = sifrakv AND sifRadnik = sifrarad;
791
792
793
794END LOOP;
795CLOSE cur;
796RETURN CONCAT(ime1, " s ", ime2, " :" , br_nalog1, " drugog je: ", br_nalog2);
797END; //
798DELIMITER ;
799
800SELECT wtfRadnici(379, 326)
801
802
803
804
805
806/* Ispisati radnike kojima je datum izvršenom naloga unutar zadnjih n godina,
807n je ono što se unosi u proceduru, ako ima 2 ili više sati kvara veći od pros sati kvara
808onda povećaju njegov koeficijent za 0,7 i ispisati u tmp table te promjenjene*/
809
810DELIMITER //
811CREATE PROCEDURE blic(IN godina INT)
812BEGIN
813 DECLARE avgKvar,koef FLOAT DEFAULT 0;
814 DECLARE flag BOOL DEFAULT FALSE;
815 DECLARE sif,sati,broj INT;
816
817 DECLARE curl1 CURSOR FOR SELECT radnik.sifRadnik,COUNT(kvar.satiKvar),radnik.KoefPlaca FROM kvar
818 JOIN nalog ON kvar.sifKvar=nalog.sifKvar
819 JOIN radnik ON nalog.sifRadnik=radnik.sifRadnik
820 WHERE YEAR(nalog.datPrimitkaNalog)>=YEAR(CURDATE())-godina
821 AND satiKvar>avgKvar
822 GROUP BY radnik.sifRadnik;
823
824 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
825
826 SELECT AVG(satiKvar) INTO avgKvar FROM kvar;
827
828 OPEN curl1;
829 DROP TEMPORARY TABLE IF EXISTS tmp;
830 CREATE TEMPORARY TABLE tmp(sifra INT,koefPlacePrije FLOAT,koefPlacePoslije FLOAT);
831 petlja:LOOP
832 IF flag=TRUE THEN LEAVE petlja;
833 END IF;
834 FETCH curl1 INTO sif,sati,koef;
835 IF sati>=2 THEN
836 INSERT INTO tmp VALUES(sif,koef,koef*1.3);
837 END IF;
838 END LOOP;
839 SELECT * FROM tmp;
840END;//
841DELIMITER ;
842
843
844
845
846
847CALL blic(10);
848
849
850
851/* martina **********************/
852
853
854DROP FUNCTION IF EXISTS zamjenaRadnika;
855DELIMITER //
856CREATE FUNCTION zamjenaRadnika(sifR1 INT, sifR2 INT) RETURNS VARCHAR(50)
857DETERMINISTIC
858BEGIN
859
860DECLARE t_ime, t_prezime, ime1, prezime1, ime2, prezime2 VARCHAR(50);
861DECLARE t_sifRadnika, t_sifOdjel, sifOdjel1, sifOdjel2, pomocna_sifOdjel INT DEFAULT 0;
862DECLARE flag BOOL;
863DECLARE kursor CURSOR FOR
864 SELECT radnik.sifRadnik, imeRadnik, prezimeRadnik, odjel.sifOdjel FROM odjel JOIN
865 radnik ON odjel.sifOdjel = radnik.sifOdjel JOIN
866 nalog ON radnik.sifRadnik = nalog.sifRadnik
867 WHERE radnik.sifRadnik IN (sifR1, sifR2);
868DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
869/*open kursor;*/
870
871SELECT sifOdjel, imeRadnik, prezimeRadnik INTO sifOdjel1, ime1, prezime1 FROM radnik WHERE sifRadnik = sifR1;
872SELECT sifOdjel, imeRadnik, prezimeRadnik INTO sifOdjel2, ime2, prezime2 FROM radnik WHERE sifRadnik = sifR2;
873
874
875 SET t_sifOdjel = sifOdjel1;
876 UPDATE radnik SET sifOdjel=sifOdjel2 WHERE sifRadnik = sifR1;
877 UPDATE radnik SET sifOdjel=t_sifOdjel WHERE sifRadnik = sifR2;
878
879/*close kursor;*/
880RETURN CONCAT("Radnik ", ime1, " je zamijenjen s ", ime2);
881END; //
882DELIMITER ;
883
884
885SELECT zamjenaRadnika( 122, 126);