I'm working on a uni project and I have to create a procedure from which I can get:
- A list of all the appropriate candidates for a job according to the score they have
- A list of the rejected ones and
- If the whole process is not finished yet display a message.
This is my code, I think there is something wrong with the if statments but I don't understand exactly why, am I using some other statment wrong?
DELIMITER $
CREATE PROCEDURE choose_candidate(IN id_job INT(4))
BEGIN
DECLARE CandName VARCHAR(12);
DECLARE CandPers INT;
DECLARE CandEduc INT ;
DECLARE CandExp INT ;
DECLARE CandInter INT;
DECLARE CandScore INT;
DECLARE finishedflag INT;
DECLARE Finished INT;
DECLARE Flag INT;
IF ( ( interview.personality IS NULL) OR ( interview.experience IS NULL ) OR ( interview.education IS NULL ) ) THEN
DECLARE candlist_cursorIII CURSOR FOR
SELECT cname
FROM interview
WHERE id_job=interview.inter_job;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Flag=1;
OPEN candlist_cursorIII;
SET Flag=0;
FETCH candlist_cursorIII INTO CandName;
WHILE (Flag=0) DO
SELECT CandName AS 'Evaluation not finished';
FETCH candlist_cursorIII INTO CandName;
END WHILE;
CLOSE candlist_cursorIII;
ELSE IF ( ( interview.personality != 0 ) AND ( interview.education != 0 ) AND ( interview.experience !=0 ) ) THEN
DECLARE candlist_cursor CURSOR FOR
SELECT cname, AVG(personality), education, experience, COUNT(cname), SUM(personality+education+experience)
FROM interview
WHERE id_job=interview.inter_job GROUP BY cname ORDER BY SUM(personality+education+experience) DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finishedflag=1;
OPEN candlist_cursor;
SET finishedflag=0;
FETCH candlist_cursor INTO CandName, CandPers, CandEduc, CandExp, CandInter, CandScore;
WHILE (finishedflag=0) DO
SELECT CandName AS 'NAME', CandPers AS 'PERSONALITY', CandEduc AS 'EDUCATION', CandExp AS 'EXPERIENCE', CandInter 'NUMBER_OF_INTERVIEWS', CandScore AS 'SCORE';
FETCH candlist_cursor INTO CandName, CandPers, CandEduc, CandExp, CandInter, CandScore;
END WHILE;
CLOSE candlist_cursor;
ELSE IF ( ( interview.personality = 0 ) OR ( interview.education = 0 ) OR ( interview.experiece = 0 ) ) THEN
DECLARE candlist_cursorII CURSOR FOR
SELECT cname
FROM interview
WHERE id_job=interview.inter_job;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished=1;
OPEN candlist_cursorII;
SET Finished=0;
FETCH candlist_cursorII INTO CandName;
WHILE (Finished=0) DO
SELECT CandName AS 'NAME', CONCAT_WS (/, no prior experience, inadequeate education, failed the interview ) AS 'Rejection Reason';
FETCH candlist_cursor INTO CandName;
END WHILE;
CLOSE candlist_cursorII;
END IF;
END$
DELIMITER ;
Aucun commentaire:
Enregistrer un commentaire