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 %
  

Alternate method

CREATE DEFINER=`root`@`localhost` PROCEDURE `ordersproc`()
BEGIN
    DECLARE lcl_pcode VARCHAR(15);
    DECLARE lcl_pvendor VARCHAR(50);
    DECLARE lcl_qtyInstock INT;
    declare fetch_complete integer default 0;

	
    DECLARE mycur CURSOR FOR
    SELECT pcode, pvendor, qtyInstock
    FROM products;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    begin
    SET fetch_complete = 1;
    end;

    OPEN mycur;
    readloop: LOOP
        FETCH mycur INTO lcl_pcode, lcl_pvendor, lcl_qtyInstock;


        IF fetch_complete = 1 THEN
            LEAVE readloop;
        END IF;

        IF lcl_qtyInstock < 2000 THEN
            INSERT INTO orders (product_code, vendor, status)
            VALUES (lcl_pcode, lcl_pvendor, 'Immediate');
        ELSEIF lcl_qtyInstock > 2000 AND lcl_qtyInstock < 3000 THEN
            INSERT INTO orders (product_code, vendor, status)
            VALUES (lcl_pcode, lcl_pvendor, 'Needed soon');
        END IF;
    END LOOP readloop;

    CLOSE mycur;
END