CaYPXPpm

1/*
2U bazi autoradionica: Napisati proceduru koja će preko parametra primiti oznaku radionice.
3Procedura mora ispisati ime i prezime te iznos plaće za onog radnika(radnike) koji radi u
4zadanoj radionici i ima najveću plaću. Iznos plaće potrebno je računati kao umnožak
5vrijednosti atributa koefPlace i iznosOsnovice.
6*/
7USE `radionica`;
8DROP PROCEDURE IF EXISTS proc1;
9DELIMITER //
10CREATE PROCEDURE proc1(IN oznRadionice VARCHAR(50))
11    BEGIN
12        DECLARE najveca DOUBLE;
13        SELECT MAX(radnik.KoefPlaca*radnik.IznosOsnovice) INTO najveca FROM radnik
14        JOIN kvar ON radnik.sifOdjel=kvar.sifOdjel
15        JOIN rezervacija ON kvar.sifKvar=rezervacija.sifKvar
16        JOIN radionica ON radionica.oznRadionica=rezervacija.oznRadionica
17        WHERE radionica.oznRadionica = oznRadionice;
18        
19        SELECT najveca;
20    END //
21DELIMITER ;
22CALL proc1("R10");
23
24/*
25U bazi studenti: Napisati proceduru "dohvatiJMBAG" koja će za ulazne parametre imati:
26- ime studenta - VARCHAR(50)
27- prezime studenta - VARCHAR(50)
28Procedura ispisuje ime, prezime, te jmbag pronađenih studenata
29*/
30USE `studenti`;
31DROP PROCEDURE IF EXISTS dohvatiJMBAG;
32DELIMITER //
33CREATE PROCEDURE dohvatiJMBAG(IN im VARCHAR(50), IN prez VARCHAR(50))
34    BEGIN
35        SELECT studenti.ime, studenti.prezime, jmbag FROM studenti
36        WHERE studenti.ime = im
37        AND studenti.prezime = prez;
38    END //
39DELIMITER ;
40CALL dohvatiJMBAG("Ana", "Radak");
41
42/*
43U bazi autoradionica: Napisati proceduru koja preko iste varijable prima podatak o radniku
44(sifRadnik), te vraća ukupan broj naloga na kojima je zadani radnik radio. 
45*/
46USE `radionica`;
47ALTER DATABASE `radionica` CHARACTER SET utf8 COLLATE utf8_general_ci;
48DROP PROCEDURE IF EXISTS proc2;
49DELIMITER //
50CREATE PROCEDURE proc2(INOUT sifra INT)
51    BEGIN
52        SELECT COUNT(nalog.sifRadnik) INTO sifra FROM nalog
53        WHERE nalog.sifRadnik = sifra;
54    END //
55DELIMITER ;
56
57SET @k=146;
58CALL proc2(@k);
59SELECT @k;
60
61/*
62 U bazi studenti: Napisati proceduru "izbrojiNastavnikeNaSmjeru" koja će za ulazno/izlazni
63parametar imati ID Smjera. U isti parametar potrebno je vratiti broj nastavnika, dok je u
64samoj proceduri potrebno ispisati sve nastavnike tog smjera.
65*/
66USE `studenti`;
67ALTER DATABASE `studenti` CHARACTER SET utf8 COLLATE utf8_general_ci;
68DROP PROCEDURE IF EXISTS izbrojiNastavnikeNaSmjeru;
69DELIMITER //
70CREATE PROCEDURE izbrojiNastavnikeNaSmjeru(INOUT idsm INT)
71    BEGIN
72        SELECT COUNT(*) INTO idsm FROM nastavnici
73        JOIN izvrsitelji ON nastavnici.jmbg = izvrsitelji.jmbgNastavnik
74        JOIN kolegiji ON izvrsitelji.idKolegij = kolegiji.id
75        JOIN smjerovi ON kolegiji.idSmjer = smjerovi.id
76        WHERE smjerovi.id = idsm;
77    END //
78DELIMITER ;
79
80SET @z=1;
81CALL izbrojiNastavnikeNaSmjeru(@z);
82SELECT @z;
83
84
85
86/* 1. U bazi autoradionica: 
87
88Modificirati tablicu odjel tako da dodate novi atribut 
89
90najmanjaPlaca tipa DOUBLE 
91
92
93
94Napisati proceduru koja će:
95
96• primiti šifru odjela
97
98• za zadani odjel izračunati plaću radnika koji radi u zadanom
99
100odjelu a ima najmanju plaću u tom odjelu
101
102• u novostvoreni atribut najmanjaPlaca unijeti prethodno 
103
104izračunat iznos
105
106• plaću je potrebno izračunati kao umnožak iznosa osnovice i 
107
108koeficijenta plaće
109
110Napisati primjer poziva procedure*/
111
112
113
114ALTER TABLE odjel ADD najmanjaPlaca DOUBLE;
115
116
117
118DROP PROCEDURE IF EXISTS procNajmanja;
119
120DELIMITER //
121
122CREATE PROCEDURE procNajmanja(IN ulazSifOdjel INT)
123
124BEGIN
125
126DECLARE najmanja DOUBLE;
127
128SELECT MIN(KoefPlaca*IznosOsnovice) INTO najmanja
129
130FROM radnik WHERE sifOdjel = ulazSifOdjel;
131
132UPDATE odjel
133
134SET najmanjaPlaca = najmanja
135
136WHERE sifOdjel = ulazSifOdjel;
137
138END //
139
140DELIMITER ;
141
142
143
144CALL procNajmanja(2);
145
146
147
148
149
150
151
152/* 2. U bazi studenti:
153
154Napisati funkciju koja prima naziv smjera. 
155
156Funkcija mora za sve kolegije sa zadanog smjera u 
157
158atribut opis upisati tekst:
159
160a. „Lagani kolegij“ – ako je prosjek ocjena na tom kolegiju 
161
162veći od 3.5
163
164b. „Težak kolegij“ – ako je prosjek ocjena na tom kolegiju 
165
166manji ili jednak 3.5
167
168c. Funkcija vraća broj kolegija kojima je upisala u opis 
169
170„Težak kolegij“.
171
172Zadatak je obavezno riješiti koristeći kursore.
173
174Napisati primjer poziva funkcije */
175
176
177
178DROP FUNCTION IF EXISTS func1;
179
180DELIMITER //
181
182CREATE FUNCTION func1(ulazNaziv VARCHAR(50)) RETURNS INT
183
184DETERMINISTIC
185
186BEGIN
187
188DECLARE broj INT DEFAULT 0;
189
190DECLARE flag BOOL DEFAULT FALSE;
191
192DECLARE t_id INT DEFAULT 0;
193
194DECLARE prosjek DOUBLE;
195
196DECLARE kur CURSOR FOR
197
198SELECT kolegiji.id FROM kolegiji
199
200JOIN smjerovi ON kolegiji.idSmjer = smjerovi.id
201
202WHERE smjerovi.naziv = ulazNaziv;
203
204DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
205
206SET flag = FALSE;
207
208SET broj = 0;
209
210OPEN kur;
211
212petlja:LOOP
213
214FETCH kur INTO t_id;
215
216IF flag=TRUE THEN
217
218LEAVE petlja;
219
220END IF;
221
222SELECT AVG(ocjena) INTO prosjek FROM ocjene
223
224JOIN kolegiji ON ocjene.idKolegij = kolegiji.id
225
226WHERE kolegiji.id = t_id;
227
228IF prosjek>3.5 THEN
229
230UPDATE kolegiji
231
232SET opis = "Lagani kolegij"
233
234WHERE id = t_id;
235
236ELSE
237
238UPDATE kolegiji
239
240SET opis = "Težak kolegij"
241
242WHERE id = t_id;
243
244SET broj = broj + 1;
245
246END IF;
247
248END LOOP;
249
250#close kur;
251
252RETURN broj;
253
254END //
255
256DELIMITER ;
257
258
259
260SELECT func1("smjer računarstvo");
261
262
263
264
265
266/* 3. U bazi autoradionica: 
267
268Napisati proceduru koja će koristeći proceduru iz prvog zadatka
269
270popuniti u tablici odjel atribut najmanjaPlaca. 
271
272Procedura prima podatak o županiji te popunjava atribut najmanjaPlaca
273
274isključivo za odjele koji se odnose na radnika iz zadane županije.
275
276Zadatak je obavezno riješiti koristeći kursore.
277
278Ako za određeni odjel procedura ne uspije pronaći zapis (pogreška NOT FOUND),
279
280potrebno je problem riješiti koristeći odgovarajući handler.
281
282Procedura vraća broj dohvaćenih i broj obrađenih zapisa.
283
284Napisati primjer poziva procedure */
285
286
287
288DROP PROCEDURE IF EXISTS proc2;
289
290DELIMITER //
291
292CREATE PROCEDURE proc2(IN ulazZup INT, OUT dohv INT, OUT obra INT)
293
294BEGIN
295
296DECLARE flag BOOL DEFAULT FALSE;
297
298DECLARE t_sifOdjel INT;
299
300DECLARE kur CURSOR FOR
301
302SELECT radnik.sifOdjel FROM radnik
303
304JOIN mjesto ON radnik.pbrStan = mjesto.pbrMjesto
305
306WHERE mjesto.sifZupanija = ulazZup;
307
308DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
309
310SET dohv = 0;
311
312SET obra = 0;
313
314SET flag = FALSE;
315
316OPEN kur;
317
318SELECT FOUND_ROWS() INTO dohv;
319
320petlja: LOOP
321
322FETCH kur INTO t_sifOdjel;
323
324IF flag=TRUE THEN
325
326LEAVE petlja;
327
328END IF;
329
330CALL procNajmanja(t_sifOdjel);
331
332SET obra = obra+1;
333
334END LOOP;
335
336CLOSE kur;
337
338END //
339
340DELIMITER ;
341
342
343
344CALL proc2(10,@a,@b);
345
346SELECT @a,@b;
347
348
349
350/* 4. U bazi studenti: 
351
352Napisati funkciju koja će primiti naziv smjera. Funkcija mora svim studentima
353
354sa zadanog smjera postaviti datum upisa na 1.9.2014. 
355
356Funkcija vraća broj obrađenih zapisa..
357
358Zadatak je potrebno riješiti koristeći kursore i odgovarajuće handlere.
359
360Napisati primjer poziva funkcije. */
361
362
363
364DROP FUNCTION IF EXISTS func2;
365
366DELIMITER //
367
368CREATE FUNCTION func2(ulazNaziv VARCHAR(100)) RETURNS INT
369
370DETERMINISTIC
371
372BEGIN
373
374DECLARE flag BOOL DEFAULT FALSE;
375
376DECLARE obra INT DEFAULT 0;
377
378DECLARE t_jmbag VARCHAR(20);
379
380DECLARE kur CURSOR FOR
381
382SELECT studenti.jmbag FROM studenti
383
384JOIN smjerovi ON studenti.idSmjer = smjerovi.id
385
386WHERE smjerovi.naziv = ulazNaziv;
387
388DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
389
390OPEN kur;
391
392petlja:LOOP
393
394FETCH kur INTO t_jmbag;
395
396IF flag=TRUE THEN
397
398LEAVE petlja;
399
400END IF;
401
402UPDATE studenti
403
404SET datumUpisa = '2014-09-01'
405
406WHERE jmbag = t_jmbag;
407
408SET obra = obra + 1;
409
410END LOOP;
411
412CLOSE kur;
413
414RETURN obra;
415
416END //
417
418DELIMITER ;
419
420
421
422SELECT func2("smjer računarstvo");
423
424
425
426/* 5. U bazi studenti: 
427
428Napisati proceduru koja će primiti naziv županije i broj N. 
429
430Procedura mora za sva mjesta u zadanoj županiji ispisati:
431
432a. Naziv mjesta
433
434b. „Velik broj nastavnika“ – ako u tom mjestu stanuje više od N nastavnika
435
436c. „Mali broj nastavnika“ – ako u tom mjestu stanuje N ili manje nastavnika
437
438Napisati primjer poziva procedure. */
439
440
441
442DROP PROCEDURE IF EXISTS proc3;
443
444DELIMITER //
445
446CREATE PROCEDURE proc3(IN ulazNazivZup VARCHAR(100), IN ulazN INT)
447
448BEGIN
449
450DECLARE t_mjesto VARCHAR(50); 
451
452DECLARE t_brojac INT;
453
454DECLARE flag BOOL DEFAULT FALSE;
455
456DECLARE kur CURSOR FOR
457
458SELECT mjesta.nazivMjesto, COUNT(nastavnici.jmbg) FROM nastavnici
459
460JOIN mjesta ON nastavnici.postBr = mjesta.postbr
461
462JOIN zupanije ON mjesta.idZupanija = zupanije.id
463
464WHERE zupanije.nazivZupanija = ulazNazivZup
465
466GROUP BY mjesta.nazivMjesto;
467
468DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
469
470DROP TEMPORARY TABLE IF EXISTS temp;
471
472CREATE TEMPORARY TABLE temp(
473
474tempNaziv VARCHAR(50),
475
476tempVelicina VARCHAR(50)
477
478);
479
480OPEN kur;
481
482petlja:LOOP
483
484FETCH kur INTO t_mjesto, t_brojac;
485
486IF flag=TRUE THEN
487
488LEAVE petlja;
489
490END IF;
491
492IF t_brojac > ulazN THEN
493
494INSERT INTO temp (tempNaziv, tempVelicina) VALUES(t_mjesto, "Velik br nast");
495
496ELSE
497
498INSERT INTO temp (tempNaziv, tempVelicina) VALUES(t_mjesto, "Mali br nast");
499
500END IF;
501
502END LOOP;
503
504CLOSE kur;
505
506SELECT * FROM temp;
507
508END //
509
510DELIMITER ;
511
512
513
514CALL proc3("Grad Zagreb", 41);
515
516
517
518
519
520/* 6. U bazi autoradionica: 
521
522Napisati proceduru za unos novog radnika u tablicu radnik.
523
524Procedura preko parametra prima vrijednosti za sve atribute iz tablice radnik, 
525
526osim atributa sifRadnik. Prilikom unosa novog radnika, potrebno je:
527
528• radniku dodijeliti šifru (za jedan veća od najveće šifre radnika)
529
530Procedura mora provjeriti da li se radniku dodjeljuje plaća manja od najmanje plaće radnika u
531
532tom odjelu (koristiti proceduru iz prvog zadatka i pročitati podatak u novom atributu):
533
534• ako plaća novog radnika nije manja od najmanje plaće za taj odjel, potvrditi sve
535
536radnje (COMMIT)
537
538• ako je plaća novog radnika manja od najmanje plaće za taj odjel, opovrgnuti sve
539
540radnje (ROLLBACK)
541
542Napisati primjer poziva procedure */
543
544
545
546DROP PROCEDURE IF EXISTS proc4;
547
548DELIMITER //
549
550CREATE PROCEDURE proc4(IN ulazIme VARCHAR(50),
551
552IN ulazPrezime VARCHAR(50),
553
554IN ulazPbrStan INT,
555
556IN ulazSifOdjel INT,
557
558IN ulazKoefPlaca DOUBLE,
559
560IN ulazIznos DOUBLE)
561
562BEGIN
563
564DECLARE sifra INT;
565
566DECLARE najmanja DOUBLE;
567
568SET AUTOCOMMIT = 0;
569
570START TRANSACTION;
571
572SELECT MAX(sifRadnik)+1 INTO sifra FROM radnik;
573
574CALL procNajmanja(ulazSifOdjel);
575
576SELECT najmanjaPlaca INTO najmanja FROM odjel
577
578WHERE sifOdjel = ulazSifOdjel;
579
580INSERT INTO radnik
581
582VALUES (sifra, ulazIme,ulazPrezime,ulazPbrStan,
583
584ulazSifOdjel,ulazKoefPlaca,ulazIznos);
585
586IF ulazKoefPlaca*ulazIznos<najmanja THEN
587
588ROLLBACK;
589
590ELSE
591
592COMMIT;
593
594END IF;
595
596SET AUTOCOMMIT = 1;
597
598END //
599
600DELIMITER ;
601
602
603
604CALL proc4("Ivan", "Ivcevic", 44320, 2, 5, 1200);
605
606
607
608
609
610/* 7. U bazi studenti:
611
612Napisati funkciju za provjeru jačine unesene lozinke. Funkcija prima string
613
614koji će predstavljati lozinku, te nakon toga provjeriti:
615
616• ne smije se unositi string koji je kreći od 8 znakova
617
618(samostalno definirati ponašanje funkcije)
619
620• string se smije sastojati od brojki, malih i velikih slova,
621
622ostalih znakova (samostalno definirati ponašanje funkcije)
623
624
625
626Funkcija vraća poruku:
627
628• SLABO - ako se string sastoji isključivo od slova ili isključivo od brojki
629
630• SREDNJE - ako se string sastoji samo od slova i brojki
631
632• JAKO - ako se string sastoji od kombinacija slova, brojki i posebnih znakova
633
634Napisati primjer poziva funkcije. */
635
636
637
638DROP FUNCTION IF EXISTS func3;
639
640DELIMITER //
641
642CREATE FUNCTION func3(ulazLozinka VARCHAR(30)) RETURNS VARCHAR(50)
643
644BEGIN
645
646DECLARE izlaz VARCHAR(50);
647
648IF LENGTH(ulazLozinka) < 8 THEN
649
650RETURN "Prekratka lozinka!";
651
652ELSEIF (ulazLozinka REGEXP '[^0-9a-zA-Z!#$%&/()=?]+') THEN
653
654RETURN "Pogrešna lozinka!";
655
656ELSEIF (ulazLozinka REGEXP '^[a-zA-Z]+$') OR 
657
658(ulazLozinka REGEXP '^[0-9]+$') THEN
659
660RETURN "SLABO";
661
662ELSEIF (ulazLozinka REGEXP '[0-9]+') AND
663
664(ulazLozinka REGEXP '[a-zA-Z]+') AND
665
666(ulazLozinka REGEXP '[!#$%&/()=?*]+') THEN
667
668RETURN "JAKO";
669
670ELSEIF (ulazLozinka REGEXP '[0-9]+') AND
671
672(ulazLozinka REGEXP '[a-zA-Z]+') THEN
673
674RETURN "SREDNJE";
675
676
677
678END IF;
679
680END //
681
682DELIMITER ;
683
684SELECT func3("1234567abc&");
685
686
687
688
689
690/* 8. U bazi studenti:
691
692U tablicu studenti dodati novi atribut lozinka (tip podataka procjeniti u
693
694skladu s ostatkom zadatka).
695
696Napisati proceduru koja će primiti studentov JMBAG i lozinku.
697
698Koristeći funkciju iz prethodnog zadatka, provjeriti da li se unosi
699
700lozinka čija je jačina definirana kao srednja ili jaka, te ako jest,
701
702unijeti dotičnom studentu lozinku u bazu, ali zaštićenu sa MD5 algoritmom.
703
704Ako je pak lozinka preslaba, onemogućiti njen upis u bazu i ispisati
705
706odgovarajuću poruku.
707
708Napisati primjer poziva procedure */
709
710
711
712ALTER TABLE studenti ADD lozinka VARCHAR(50);
713
714
715
716DROP PROCEDURE IF EXISTS proc5;
717
718DELIMITER //
719
720CREATE PROCEDURE proc5(IN ulazJmbag VARCHAR(50), IN ulazLozinka VARCHAR(50))
721
722BEGIN
723
724DECLARE jacina VARCHAR(50);
725
726SELECT func3(ulazLozinka) INTO jacina;
727
728IF jacina = "JAKO" THEN
729
730UPDATE studenti
731
732SET lozinka = MD5(ulazLozinka)
733
734WHERE ulazJmbag = jmbag;
735
736ELSE
737
738SELECT "POGREÅ KA";
739
740END IF;
741
742END //
743
744DELIMITER ;
745
746
747
748CALL proc5("0013020125", "1234567891abc((");