
CREATE OR REPLACE PACKAGE enrollments_package
IS
procedure enroll_student_in_class(p_s_id IN enrollments.stu_id%TYPE,p_c_id IN enrollments.class_id%type);
procedure drop_student_from_class (p_s_id IN enrollments.stu_id%TYPE,p_c_id IN enrollments.class_id%type); 
procedure student_class_list (p_s_id IN enrollments.stu_id%TYPE);
procedure student_class_list ;
end enrollments_package;

CREATE OR REPLACE PACKAGE BODY enrollments_package IS
procedure enroll_student_in_class(p_s_id IN enrollments.stu_id%TYPE,p_c_id IN enrollments.class_id%type)
as
v number(2);
begin
INSERT INTO enrollments
(stu_id, class_id, enrollment_date, status)
VALUES (p_s_id, p_c_id, SYSDATE,'Enrolled');
select stu_id into v from enrollments where stu_id=p_s_id;
exception
WHEN too_many_rows THEN
        dbms_output.put_line('the student is already enrolled.');
END enroll_student_in_class;

procedure student_class_list (p_s_id IN enrollments.stu_id%TYPE) 
as
CURSOR s_class_cur IS
SELECT enrollment_date, class_id, status
FROM enrollments
WHERE stu_id=p_s_id
AND enrollment_date BETWEEN ADD_MONTHS(sysdate,-72) AND SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE ('STUDENT '||p_s_id||' is enrolled in the following classes within the most recent 6 years:' );
FOR s_class_rec IN s_class_cur 
LOOP
DBMS_OUTPUT.PUT_LINE(' On: '||s_class_rec.enrollment_date||' the student enrolled the class with the id:'||s_class_rec.class_id||' and with status '||s_class_rec.status);
END LOOP;
END student_class_list;


procedure student_class_list
as
cursor all_class_cur is
SELECT stu_id, enrollment_date, class_id, status
FROM enrollments
WHERE enrollment_date BETWEEN ADD_MONTHS(sysdate,-72) AND SYSDATE;
for all_class_rec in all_class_cur
LOOP
DBMS_OUTPUT.PUT_LINE(' Student: '||all_class_rec.stu_id||' is inrolled in '||all_class_rec.enrollment_date||' in the class with the id:'||all_class_rec.class_id||' and with status '||all_class_rec.status);
END LOOP;
END student_class_list;


procedure drop_student_from_class (p_s_id IN enrollments.stu_id%TYPE,p_c_id IN enrollments.class_id%type)
as
nu_exista exception;
BEGIN
DELETE FROM enrollments
WHERE stu_id=p_s_id
AND class_id=p_c_id;
IF (SQL%ROWCOUNT<>0) THEN DBMS_OUTPUT.PUT_LINE('The student with the id '||p_s_id||' was deleted from class id '||p_c_id);
else raise nu_exista;
END IF;
exception
when nu_exista then dbms_output.put_line('exceptia este: the student is not in the class');
END drop_student_from_class;

end enrollments_package;