CREATE OR REPLACE PACKAGE school_api as
v_current_date DATE;
PROCEDURE Discount_Cost;
FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE;
PROCEDURE remove_student (
        s_id   student.student_id%TYPE,
        p_ri   VARCHAR2 DEFAULT 'R'
    );
END school_api;
/
CREATE OR REPLACE PACKAGE BODY school_api AS
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;
 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;

BEGIN
SELECT trunc(sysdate, ' dd')
INTO v_current_date
FROM dual;
END school_api;
/
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;