VuMsAG5P

1DROP PROCEDURE klasificirajKvar;
2DELIMITER //
3CREATE PROCEDURE klasificirajKvar(IN zadaniOdjel VARCHAR(50))
4BEGIN
5	DECLARE kraj INT DEFAULT 0; 
6	DECLARE sif, br INT DEFAULT NULL;
7	DECLARE naziv VARCHAR(50) DEFAULT NULL;
8	DECLARE k CURSOR FOR SELECT sifKvar, nazivKvar FROM kvar WHERE sifOdjel=(SELECT sifOdjel FROM odjel WHERE nazivOdjel=zadaniOdjel);
9	DECLARE CONTINUE HANDLER FOR NOT FOUND SET kraj=1;
10	DROP TEMPORARY TABLE IF EXISTS tempTabla;
11	CREATE TEMPORARY TABLE tempTabla(naziv VARCHAR(50), broj INT, ucestalo VARCHAR(50));
12	OPEN k;
13	petlja:LOOP
14	FETCH k INTO sif, naziv;
15	IF(kraj=1) THEN LEAVE petlja;
16	END IF;
17	SELECT COUNT(*) INTO br FROM nalog WHERE sifKvar=sif;
18	IF(br>=20) THEN INSERT INTO tempTabla VALUES (naziv, br, "često");
19	ELSEIF(br>0 AND br<20) THEN INSERT INTO tempTabla VALUES (naziv, br, "povremeno");
20	ELSE INSERT INTO tempTabla VALUES (naziv, br, "nikad");
21	END IF;
22	END LOOP;
23	CLOSE k;
24	SELECT * FROM tempTabla;
25END //
26DELIMITER ;
27	
28CALL klasificirajKvar("Mehanika");
29
30/*===============================================================================================*/
31
32DROP FUNCTION nadjiRadnike;
33DELIMITER //
34CREATE FUNCTION nadjiRadnike(posta INT) RETURNS INT DETERMINISTIC
35BEGIN
36	DECLARE kraj, broj1, broj2 INT DEFAULT 0; 
37	DECLARE sif INT DEFAULT NULL;
38	DECLARE ime,prezime VARCHAR(50) DEFAULT NULL;
39	DECLARE k CURSOR FOR SELECT sifRadnik, imeRadnik, prezimeRadnik FROM radnik WHERE pbrStan=posta;
40	DECLARE CONTINUE HANDLER FOR NOT FOUND SET kraj=1;
41	DROP TEMPORARY TABLE IF EXISTS tempTabla;
42	CREATE TEMPORARY TABLE tempTabla(sifra INT, ime VARCHAR(50), prezime VARCHAR(50));
43	OPEN k;
44	petlja:LOOP
45	FETCH k INTO sif, ime, prezime;
46	IF(kraj=1) THEN LEAVE petlja;
47	END IF;
48		INSERT INTO tempTabla VALUES(sif, ime, prezime);
49	END LOOP;
50	CLOSE k;
51	RETURN (SELECT COUNT(*) FROM tempTabla);
52END //
53DELIMITER ;
54	
55SELECT nadjiRadnike(10000);
56SELECT * FROM tempTabla;