CREATE OR REPLACE PACKAGE school_api AS
    v_current_date DATE;
    PROCEDURE discount_cost;
    PROCEDURE remove_student (
        s_id   student.student_id%TYPE,
        p_ri   VARCHAR2 DEFAULT 'R'
    );
    FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE;
END school_api;
/

CREATE OR REPLACE PACKAGE BODY school_api AS

    PROCEDURE remove_student (
        s_id   student.student_id%TYPE,
        p_ri   VARCHAR2 DEFAULT 'R'
    ) IS
    BEGIN
        IF p_ri = 'R' THEN
            DELETE FROM student
            WHERE
                student_id = s_id;

        ELSIF p_ri = 'C' THEN
--        clean grade
            DELETE FROM grade
            WHERE
                student_id = s_id;
--        clean enrollment

            DELETE FROM enrollment
            WHERE
                student_id = s_id;
--        clean student

            DELETE FROM student
            WHERE
                student_id = s_id;

        END IF;
    END remove_student;

    PROCEDURE discount_cost IS

        CURSOR c_group_discount IS
        SELECT DISTINCT
            s.course_no,
            c.description
        FROM
            section      s,
            enrollment   e,
            course       c
        WHERE
            s.section_id = e.section_id
        GROUP BY
            s.course_no,
            c.description,
            e.section_id,
            s.section_id
        HAVING
            COUNT(*) >= 8;

    BEGIN
        FOR r_group_discount IN c_group_discount LOOP
            UPDATE course
            SET cost = cost *.95
            WHERE
                course_no = r_group_discount.course_no;

            dbms_output.put_line('A 5% discount has been given to'
                                 || r_group_discount.course_no
                                 || r_group_discount.description);
        END LOOP;
    END discount_cost;

    FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE IS
        v_new_instid instructor.instructor_id%TYPE;
    BEGIN
        SELECT
            instructor_id_seq.NEXTVAL
        INTO v_new_instid
        FROM
            dual;

        RETURN v_new_instid;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Error!');
    END new_instructor_id;

END school_api;
/

SET SERVEROUTPUT ON

DECLARE
    v_student_id   NUMBER := &v_student_id;
    v_pr_i         VARCHAR2(1) := '&v_pr_i';
BEGIN
    school_api.remove_student(v_student_id, v_pr_i);
    dbms_output.put_line(v_student_id);
END;