CREATE OR REPLACE PROCEDURE calculate_performance(
    from_year IN INTEGER,
    targ_year IN INTEGER) AS

    old_avg_mark marks.value___%TYPE;
    current_subj_id NUMBER;
    avg_mark_dif NUMBER;

    new_subj_id NUMBER;
    new_subj_name subject.name%TYPE;
    new_year NUMBER;
    new_avg_mark marks.value___%TYPE;

    CURSOR year_marks_cursor(
        from_year IN INTEGER,
        targ_year IN INTEGER) IS
            SELECT marks.subject_id, SUBSTR(groups.NAME, -4, 4) AS "Year", AVG(marks.value___)
            FROM marks
            join people ON people.id = marks.student_id
            join groups ON groups.id = people.GROUP_ID
            GROUP BY marks.subject_id, SUBSTR(groups.NAME, -4, 4)
            HAVING SUBSTR(groups.NAME, -4, 4) >= from_year AND SUBSTR(groups.NAME, -4, 4) <= targ_year
            ORDER BY marks.subject_id;

BEGIN
    BEGIN
    old_avg_mark := 0;
    current_subj_id := NULL;
    OPEN year_marks_cursor(from_year, targ_year);
    LOOP
        FETCH year_marks_cursor INTO new_subj_id, new_year, new_avg_mark;
        EXIT WHEN year_marks_cursor%NOTFOUND;

        IF current_subj_id IS NULL THEN
            current_subj_id := new_subj_id;
        ELSIF new_subj_id <> current_subj_id THEN
            old_avg_mark := 0;
            current_subj_id := new_subj_id;
        END IF;

        avg_mark_dif := new_avg_mark - old_avg_mark;

        old_avg_mark := new_avg_mark;
        SELECT subject.name INTO new_subj_name
        FROM subject
        WHERE subject.id = new_subj_id;

        dbms_output.put_line(new_subj_name||'|'||new_year||'|'||new_avg_mark||'|'||avg_mark_dif);
        END LOOP;
        CLOSE year_marks_cursor;
    END;
END;


BEGIN
    calculate_performance(2000, 2019);
END;