bpiFGTee

1DROP TABLE IF EXISTS temp_tbl;
2DROP PROCEDURE IF EXISTS proc;
3DELIMITER $
4CREATE PROCEDURE proc(IN firmId INT)
5BEGIN
6	
7    DECLARE finished INT;
8    DECLARE firm_name VARCHAR(50);
9    DECLARE position_name VARCHAR(50);
10    DECLARE position_fee INT;
11    
12    DECLARE cursor_main CURSOR FOR
13    SELECT firms.name,positions.name,positions.fee from firms join firm_position on
14    firms.id = firm_position.firm_id join positions on
15    firm_position.position_id = positions.id where firms.id = 1;
16    
17    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
18    SET finished = 0;
19    
20    CREATE TABLE temp_tbl(
21     nameCandidate VARCHAR(50) not null,
22    nameFirm VARCHAR(50) not null,
23    position VARCHAR(50) not null,
24    expectedSalary DOUBLE not null,
25	nameFirm2 VARCHAR(50) not null,
26    fee DECIMAL not null
27    ) ENGINE = MEMORY;
28    
29    OPEN cursor_main;
30    
31    main_block: WHILE ( finished = 0 )
32    DO
33    FETCH cursor_main INTO firm_name,position_name,position_fee;
34    
35    IF ( finished = 1 )
36    THEN
37    LEAVE main_block;
38    ELSE 
39
40     CALL inner_proc(firm_name,position_name,position_fee);
41     
42    END IF;
43    END WHILE;
44
45	CLOSE cursor_main;
46    SELECT * from temp_tbl order by position,expectedSalary;
47    DROP TABLE temp_tbl;
48    
49
50END;
51$
52DELIMITER ;
53
54
55/**/
56DROP PROCEDURE IF EXISTS inner_proc;
57DELIMITER |
58CREATE PROCEDURE inner_proc(IN firm_name VARCHAR(50),IN position_name VARCHAR(50), IN position_fee INT)
59BEGIN
60	
61	DECLARE candidated_firm_name VARCHAR(50);
62    DECLARE candidate_name VARCHAR(50);
63    DECLARE candidated_position_name VARCHAR(50);
64    DECLARE exp_salary DOUBLE;
65    DECLARE finished INT;
66    
67    DECLARE cursor_inner CURSOR FOR 
68    SELECT firms.name as FirmName, positions.name as PositionName , biography.name as CandidateName , 
69    biography.expectedSalary as expectedSalary from firms join firm_position on 
70    firms.id = firm_position.firm_id join positions on positions.id = firm_position.firm_id 
71    join firm_pos_bio on firm_pos_bio.firm_pos_id = firm_position.id join
72    biography on biography.id = firm_pos_bio.bio_id WHERE positions.name = position_name;
73    
74    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
75    SET finished = 0;
76    
77    OPEN cursor_inner;
78    
79    inner_block: WHILE ( finished = 0 )
80    DO
81    FETCH cursor_inner INTO candidated_firm_name,candidated_position_name,candidate_name,exp_salary;
82    
83    IF ( finished = 1 )
84    THEN
85    LEAVE inner_block;
86    ELSE 
87		IF(candidated_firm_name != firm_name AND exp_salary < 1.2 * position_fee )
88        THEN
89		 INSERT INTO temp_tbl(nameCandidate,nameFirm,position,expectedSalary,nameFirm2,fee) VALUES
90         (candidate_name,candidated_firm_name,candidated_position_name,exp_salary,firm_name,position_fee);
91		ELSE IF ( candidated_firm_name = firm_name )
92        THEN
93        INSERT INTO temp_tbl(nameCandidate,nameFirm,position,expectedSalary,nameFirm2,fee) VALUES
94         (candidate_name,candidated_firm_name,candidated_position_name,exp_salary,firm_name,position_fee);
95		ELSE 
96        BEGIN 
97        END;
98        END IF;
99        END IF;
100     
101     
102    END IF;
103    END WHILE;
104
105	CLOSE cursor_inner;
106
107
108
109END;
110|
111DELIMITER ;