DBMSL Ass 6 - Cursor

SET SERVEROUTPUT ON;

CREATE TABLE O_RollCall (
    roll    NUMBER          PRIMARY KEY,
    name    VARCHAR2(100),
    dept    VARCHAR2(50)
);

CREATE TABLE N_RollCall (
    roll    NUMBER          PRIMARY KEY,
    name    VARCHAR2(100),
    dept    VARCHAR2(50)
);

INSERT INTO O_RollCall VALUES (1, 'Adam Jacobs', 'CS');
INSERT INTO O_RollCall VALUES (2, 'Meredith Kosinski', 'EE');
INSERT INTO O_RollCall VALUES (3, 'Charlie Kirk', 'Civil');
INSERT INTO O_RollCall VALUES (4, 'Clark Kent', 'Mech');
INSERT INTO O_RollCall VALUES (5, 'Mike Dean', 'EE');
INSERT INTO O_RollCall VALUES (6, 'Alex Smith', 'Physics');

INSERT INTO N_RollCall VALUES (7, 'Brian Johnson', 'Math');
INSERT INTO N_RollCall VALUES (8, 'Jude Law', 'Chemical');
INSERT INTO N_RollCall VALUES (9, 'Teresa Engel', 'CS');
INSERT INTO N_RollCall VALUES (10, 'Dakota Johnson', 'CS');
INSERT INTO N_RollCall VALUES (11, 'Brandon Steel', 'Chemical');
INSERT INTO N_RollCall VALUES (12, 'Hugh Gass', 'Aerospace');
INSERT INTO N_RollCall VALUES (2, 'Meredith Kosinski', 'EE');
INSERT INTO N_RollCall VALUES (4, 'Clark Kent', 'Mech');

DECLARE
    CURSOR MyCursor (p_roll NUMBER) IS
        SELECT roll, name, dept
        FROM N_RollCall
        WHERE roll = p_roll;

    v_roll NUMBER;
    v_name VARCHAR2(100);
    v_dept VARCHAR2(100);
    v_exists NUMBER := 0;

BEGIN
    FOR rec IN (SELECT roll, name, dept FROM N_RollCall) LOOP
        OPEN MyCursor(rec.roll);
        FETCH MyCursor INTO v_roll, v_name, v_dept;

        IF MyCursor%FOUND THEN
            SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
                INTO v_exists FROM O_RollCall WHERE roll = rec.roll;
            IF v_exists = 1 THEN
                DBMS_OUTPUT.PUT_LINE('Roll: ' || rec.roll || ' already exists. Skipping...');
            ELSE
                INSERT INTO O_RollCall VALUES (rec.roll, rec.name, rec.dept);
                DBMS_OUTPUT.PUT_LINE('Roll: ' || rec.roll || ' Name: ' || rec.name || ' inserted');
            END IF;
        END IF;

        CLOSE MyCursor;
    END LOOP;
END;


SELECT * FROM O_RollCall;