AtP7aZdm

1DELIMITER //
2	CREATE PROCEDURE transakcja(IN budzet FLOAT, IN zawod VARCHAR(30))
3	BEGIN
4		DECLARE psl char(11);
5		DECLARE salary float;
6		DECLARE job varchar(30);
7		DECLARE sum int;
8		DECLARE done INT DEFAULT FALSE;
9		DECLARE kursor CURSOR FOR (SELECT PESEL, pensja, zawód FROM pracownicy); 
10		DECLARE CONTINUE HANDLER
11		FOR NOT FOUND SET done = 1;
12		CREATE TEMPORARY TABLE board(answer varchar(30) NOT NULL); 
13		SET sum = 0;
14
15		
16		SET AUTOCOMMIT = 0;
17		START TRANSACTION;
18		OPEN kursor;
19			read_loop: LOOP
20				FETCH kursor INTO psl, salary, job;
21				IF (job = zawod) THEN 
22					SET sum = sum + salary; 
23				END IF;
24				
25				IF (done) THEN 
26					LEAVE read_loop; 
27				END IF;
28				
29				IF (sum > budzet) THEN 
30					LEAVE read_loop; 
31					ROLLBACK; 
32				END IF;
33				
34				IF (job = zawod) THEN 
35					INSERT INTO board VALUES (CONCAT("********", SUBSTRING(psl,9,3))); 
36				END IF;
37			END LOOP;
38		CLOSE kursor;
39		COMMIT; 
40		SELECT * FROM board;
41		DROP TEMPORARY TABLE IF EXISTS board;
42	END; // 
43DELIMITER ;
44
45CALL transakcja(10000, malarz)