sj7JNxGz

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 table(answer varchar(30) NOT NULL); 
13		SET sum = 0;
14		SET AUTOCOMMIT = 0;
15		START TRANSACTION;
16		OPEN kursor;
17			read_loop: LOOP
18				FETCH kursor INTO psl, salary, job;
19				IF (job = zawod) THEN 
20					SET sum = sum + salary; 
21				END IF;
22				
23				IF (done) THEN 
24					LEAVE read_loop; 
25				END IF;
26				
27				IF (sum > budzet) THEN 
28					LEAVE read_loop; 
29					ROLLBACK; 
30				END IF;
31				
32				IF (job = zawod) THEN 
33					INSERT INTO table VALUES (CONCAT("********", SUBSTRING(psl,9,3))); 
34				END IF;
35			END LOOP;
36		CLOSE kursorek;
37		COMMIT; 
38		SELECT * FROM table;
39		DROP TEMPORARY TABLE IF EXISTS table;
40	END; // 
41DELIMITER ;