5iZCLcmm

1DELIMITER $$
2CREATE PROCEDURE payment(IN amount int, IN profession varchar(50))
3BEGIN    
4DECLARE done BOOL DEFAULT FALSE;
5DECLARE salary INT;
6DECLARE pes VARCHAR(11);
7DECLARE summary INT DEFAULT 0;
8
9DECLARE employee_cursor CURSOR FOR (SELECT RIGHT(PESEL,3), pensja FROM Pracownicy WHERE zawod=profession);
10DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
11
12DROP TEMPORARY TABLE IF EXISTS tmp1;
13CREATE TEMPORARY TABLE tmp1(pesel char(11), status varchar(20) DEFAULT 'wyplacono');
14
15SET AUTOCOMMIT =0;
16START TRANSACTION;
17    OPEN employee_cursor;
18    readLoop : LOOP
19        FETCH employee_cursor INTO pes,salary;
20        IF done THEN
21            LEAVE readLoop;
22        END IF;
23
24        SET summary = summary + salary;
25
26        IF( summary > amount ) THEN
27            ROLLBACK;
28        END IF;
29
30        INSERT INTO tmp1(pesel) VALUES(CONCAT('********',pes));
31
32    END LOOP;
33    CLOSE employee_cursor;
34    COMMIT;
35    SELECT * from tmp1;    
36END $$
37DELIMITER ;
38	
39INSERT INTO tmp1(pesel) VALUES(CONCAT('********',pes));