dPPawDr7

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