09:49 15-08-2023

CREATE PROCEDURE cursor_proc()

step-1:
-- Create a cursor to iterate over the records in the marks table
DECLARE cur CURSOR FOR SELECT names, marks from name_and_marks;
DECLARE done INT DEFAULT 0;

-- Declare variables to store the values from the name_and_marks table
 DECLARE lcl_name VARCHAR(50);
    DECLARE lcl_marks INT;

-- exception is raised
DECLARE CONTINUE HANDLER FOR NOT FOUND

-- when not found exception is raised
-- DECLARE done INT DEFAULT 0;

begin

set done =1;

end


step-2:
-- Open the cursor
OPEN cur;

step-3:
-- Fetch the first record from the cursor
 read_loop: LOOP
        FETCH cur INTO lcl_name, lcl_marks;

IF done THEN
            LEAVE read_loop;
        END IF;



   IF lcl_marks > 80 THEN
            select concat(lcl_name," has distinction") as grade;
        ELSEIF lcl_marks > 70 and lcl_marks < 80 then
            select concat(lcl_name," has First Class") as grade;
        ELSEIF lcl_marks > 60 and lcl_marks < 70 then
            select concat(lcl_name," has Second Class") as grade;
        ELSEIF lcl_marks > 40 and lcl_marks <60 THEN
            select concat(lcl_name," has Third Class")  as grade;
        ELSE
            select 'Fail' as grade;
        END IF;

END LOOP read_loop;

step-4
 CLOSE cur;
END //


to practice:

-- products table look at qty in stocks and take action
-- Employee table bonus for each employee at the end of 6 months
take into account experience, performance and calc hike %