tuvFn5Va

1DROP FUNCTION IF EXISTS peselCypher;
2DELIMITER $$
3CREATE FUNCTION peselCypher(pesel CHAR(11))
4RETURNS CHAR(11)
5BEGIN
6
7	DECLARE newPesel CHAR(11);
8    DECLARE tmpChar CHAR(3);
9    
10    
11    SET tmpChar = SUBSTRING(pesel, 9, 3);
12	SET newPesel = concat('********' , tmpChar);
13    RETURN newPesel;
14	
15END $$
16DELIMITER ;
17
18
19
20DROP PROCEDURE IF EXISTS balanceTransaction;
21DELIMITER $$
22CREATE PROCEDURE balanceTransaction (IN job VARCHAR(15), IN balance INT) 
23BEGIN
24
25
26 DECLARE endCursor SMALLINT UNSIGNED;
27 DECLARE myPesel CHAR(11);
28 DECLARE salary INT;
29 
30 
31 DECLARE k1 CURSOR FOR SELECT PESEL, pensja FROM pracownicy WHERE zawod=job;
32 
33 
34 
35	DECLARE CONTINUE HANDLER FOR SQLSTATE '45000'
36	BEGIN
37		ROLLBACK;
38	END;
39    
40	DECLARE CONTINUE HANDLER FOR NOT FOUND
41    BEGIN
42		SET endCursor = 1;
43	END;
44    
45	
46    
47	DROP TEMPORARY TABLE IF EXISTS RunningTotal;
48	CREATE TEMPORARY TABLE RunningTotal (PESELL CHAR(11) NOT NULL, Statuss VARCHAR(15));
49
50
51
52 START TRANSACTION;
53 OPEN k1;
54
55 simple_loop: LOOP
56
57 FETCH k1 INTO myPesel, salary;
58
59 IF endCursor = 1 THEN
60	LEAVE simple_loop;
61 END IF;
62 
63 
64 SET balance = balance - salary;
65 IF balance < 0 THEN
66	signal sqlstate '45000' set message_text = 'error...';
67	LEAVE simple_loop;
68 END IF;
69 INSERT INTO RunningTotal VALUES (peselCypher(myPesel), 'wyplacono');
70 
71 END LOOP simple_loop;
72
73
74COMMIT;
75
76
77SELECT * FROM RunningTotal;
78     
79END$$
80DELIMITER ;
81
82
83call balanceTransaction('informatyk' , 1300000);