CREATE OR REPLACE PACKAGE school_api as
v_current_date DATE;
PROCEDURE Discount_Cost;
FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE;
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;
BEGIN
SELECT trunc(sysdate, ' dd')
INTO v_current_date
FROM dual;
END school_api;
/