DwEcCqAs

1DELIMITER //
2	CREATE PROCEDURE wyplata(IN budzet float, IN zawod varchar(30))
3	BEGIN
4		DECLARE psl char(11);
5		DECLARE job varchar(30);
6		DECLARE salary float;
7		DECLARE budget float;
8		DECLARE i int;
9		DECLARE done INT DEFAULT FALSE;
10		
11		DECLARE kursor CURSOR FOR (SELECT PESEL, zawód, pensja FROM pracownicy);
12		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
13		
14		CREATE TEMPORARY TABLE board(answer varchar(11), paid varchar(11));
15	
16		SET i = 0;
17		SET budget = (SELECT COUNT(pensja) FROM pracownicy WHERE pracownicy.zawód = zawod);
18		
19		SET AUTOCOMMIT = 0;
20		START TRANSACTION;
21		
22		OPEN kursor;
23			xloop: LOOP
24				FETCH kursor INTO psl, job, salary;
25				
26				IF ((salary < budzet) AND (job = zawod)) THEN
27					SET budzet = budzet - salary;
28					INSERT INTO board (`answer`, `paid`) 
29					VALUES ((CONCAT("********", SUBSTRING(psl,9,3))), 'wypłacono');
30					SET i = i + 1;
31				END IF;
32				
33				IF (done) THEN
34					LEAVE xloop;
35				END IF;
36			END LOOP;
37		
38			IF (i <> budget) THEN
39				ROLLBACK;
40		
41			ELSE
42				COMMIT;
43			END IF;
44				
45		CLOSE kursor;
46		
47		SELECT * FROM board; 
48		DROP TEMPORARY TABLE IF EXISTS board;
49	END; //
50DELIMITER ;