not_registered           exception;

-- Declare values for status.

conflicting_classes number := -2;
unsuccessful        number := -1;
normal              number := 0;

-- *******************************************************
-- Function register_for_class
--
function register_for_class (arg_student_ID IN varchar2,
                             arg_class_ID   IN varchar2)
  return number is

status number;
counter number;

begin

-- Determine if the student isn't already registered for this class.

select count(*) into counter
from student_schedule
where
student_id = arg_student_id and
class_id   = arg_class_id;

if counter > 0 then
--
-- The student is already registered for this class.
--
  raise already_registered;
else
--
-- The student isn't registered for this class.
-- Determine if there is a schedule conflict.
--
  if schedule_conflict(arg_student_id, arg_class_id) = 0 then
    insert into student_schedule
    (student_id, class_id)
    values
    (arg_student_id, arg_class_id);
  else
    raise schedule_conflict_exists;
  end if;
end if;

status := normal;

return status;

exception
  when schedule_conflict_exists then
    raise_application_error (-20001, `Schedule conflict exists');
  when already_registered then

                                                 continues

Listing 10.10. continued

    raise_application_error (-20002,
Â'Student is already registered for class');
  when others then
    null;
end;

-- *******************************************************
-- Function schedule_conflict
--
function schedule_conflict (arg_student_ID IN varchar2,
                            arg_class_ID   IN varchar2)
  return number is

-- Declare a cursor to look for other classes with the same schedule
-- as this one.

cursor get_other_classes is
       select SS.Class_ID
       from Student_Schedule SS, Class C
       where
       SS.Class_ID = C.Class_ID and
       (C.Semester, C.School_Year, C.Schedule_ID) =
       (select Semester, School_Year, Schedule_ID
        from Class
        where
        Class_ID = arg_class_ID);

Conflicting_Class_ID  Class.Class_ID%type;
status  number;

begin

-- Need to look at the other classes in the student's schedule
-- for the same semester and school year.

for get_other_classes_Rec in get_other_classes loop

  fetch get_other_classes into Conflicting_Class_ID;
  exit when get_other_classes%notfound;

end loop;

close get_other_classes;

if get_other_classes%rowcount > 0 then
  status := conflicting_classes;
else
  status := normal;
end if;

return status;

end;
-- *******************************************************

-- Procedure drop_class
--
procedure drop_class (arg_student_ID IN varchar2,
                      arg_class_ID   IN varchar2,
                      status     OUT number) is

counter  number;

begin

-- Verify that this class really is part of the student's schedule.

select count(*) into counter
from student_schedule
where
student_id = arg_student_id and
class_id   = arg_class_id;

if counter = 1 then
delete from student_schedule
where
student_id = arg_student_id and
class_id   = arg_class_id;
end if;

end;

-- *******************************************************
-- Procedure assign_instructor
--
procedure assign_instructor (arg_class_ID      IN varchar2,
                             arg_instructor_ID IN varchar2,
                             status        OUT number) is

counter           number;

begin

-- Determine if this instructor is associated with the department
-- that offers this class.

select count(*)
into counter
from Instructor I, Class C
where
C.Class_ID = arg_class_ID and
I.Instructor_ID = arg_instructor_ID and
C.Department_ID = I.Department_ID;

if counter = 0 then
  status := unsuccessful;
else
--
-- Assign this instructor to this class.
--
  update class

                                               continues

Listing 10.10. continued

  set
  Instructor_ID = arg_instructor_ID
  where
  Class_ID = arg_class_ID;

  status := normal;

end if;

end;

-- *******************************************************
-- Procedure assign_grade
--
procedure assign_grade (arg_student_ID IN  varchar2,
                        arg_class_ID   IN  varchar2,
                        arg_grade      IN  varchar2,
                        status         OUT number) is
counter number;

begin

--  Determine if the student is registered for this class.

select count(*) into counter
from student_schedule
where
student_id = arg_student_id and
class_id   = arg_class_id;

if counter = 0 then
--
-- The student is not taking this class.
--
  raise not_registered;
else
--
--  Assign the grade for this class.
--
  update student_schedule
  set
  grade = arg_grade
  where
  student_id = arg_student_id and
  class_id   = arg_class_id;
end if;

exception
  when not_registered then
    raise_application_error (-21003,
Â'Student not registered for class');
  when others then
    null;

end;

-- *******************************************************
-- Function student_GPA
--
function student_GPA (arg_student_ID IN varchar2)
   return number is

GPA number;

begin

--
-- Calculate the average grade point for this student based on all
-- classes for which a grade has been assigned.

select avg(decode(grade, `A+', 4.25, `A', 4, `A-', 3.75,
                         `B+', 3.25, `B', 3, `B-', 2.75,
                         `C+', 2.25, `C', 2, `C-', 1.75,
                         `D+', 1.25, `D', 1, `D-', 0.75, `F', 0))
into GPA
from student_schedule
where
student_id = arg_student_id;

return GPA;

end;

end;

Once the Flugle package body has been created, you can invoke a function in the package. Take a look at Listing 10.11. The UPDATE statement is used to set the instructor for Class 104200 to null. The SELECT statement that follows it proves that there is no instructor assigned for the class. Next, an anonymous block is used to call Assign_Instructor to assign Instructor E491 to class 104200. The status is 0, which indicates that the procedure was successful. Lastly, the final SELECT statement indicates that the instructor has indeed been assigned to the class.

Listing 10.11. Referencing a procedure in a package from an anonymous PL/SQL block.

SQL> set serveroutput on
SQL>
SQL> update class
  2  set
  3  Instructor_ID = null
  4  where
  5  class_ID = `104200';

                                                  continues

Listing 10.11. continued

1 row updated.

SQL> select Instructor_ID
  2  from Class
  3  where
  4  Class_ID = `104200';

INSTRUCTOR_ID
--------------------

SQL> declare
  2
  2  status          number;
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5
  5  Flugle.Assign_Instructor (`104200', `E491', status);
  6
  6  dbms_output.put_line(`Status: ` || to_char(status));
  7
  7  end;
  8  /
Status: 0

PL/SQL procedure successfully completed.

SQL> select Instructor_ID
  2  from Class
  3  where
  4  Class_ID = `104200';

INSTRUCTOR_ID
--------------------
E491

Designing a Package for Use by Database Triggers

The procedures and functions contained in a package can be referenced from SQL*Plus scripts, PL/SQL subprograms, client application scripts (such as Oracle Forms or PowerBuilder)—as well as database triggers. However, a database trigger can't call any stored procedure, function, or packaged subprogram that contains a COMMIT, ROLLBACK, or SAVEPOINT statement. Therefore, if you want the flexibility of calling a package's subprograms from a database trigger, be sure that none of the procedures and functions in the package commit or roll back transactions. See Day 11, "More Programming Techniques with PL/SQL," for more information about database triggers.