##  Examples of package variable "constants".

    Select E.emp_no, E.emp_name,
           sum(S.days) sick_days, 
           sum(H.days) holidays
    From   holiday_leave H,
           sick_leave S,
           emp E
    Where  E.emp_no = :emp_no
    And    E.emp_no = S.emp_no (+)
    And    E.emp_no = H.emp_no (+)
    Group  
        By E.emp_no, E.emp_name;



## This SQL will return the wrong result and should be coded as :

    FUNCTION Sum_Sick_Leave (emp  IN number) return number 
    AS
      tot_days  number := 0;
      CURSOR C1 IS
      SELECT sum(days) 
      FROM   sick_leave
      WHERE  emp_no = emp;
    BEGIN
      Open  C1;
      Fetch C1 into tot_days;
      Close C1;
      return (tot_days);
    END;

    Function Sum_Holiday_Leave (emp IN number) return number
    AS
      tot_days  number := 0;
      CURSOR C1 IS
      SELECT sum(days) 
      FROM   holiday_leave
      WHERE  emp_no = emp;
    BEGIN
      Open  C1;
      Fetch C1 into tot_days;
      Close C1;
      return (tot_days);
    END;

    Select E.emp_no, E.emp_name,
           sum_sick_leave(E.emp_no)    sick_days, 
           sum_holiday_leave(E.emp_no) holidays
    From   emp E
    Where  E.emp_no = :emp_no



##  The SQL can be better improved by incorporating it into a stored
##  Package procedure.

    PACKAGE Sum_Emp
    AS
      Function Sum_Sick_Leave        (emp IN number) return number;
      Function Sum_Sick_Occasions    (emp IN number) return number;
      Function Sum_Holiday_Leave     (emp IN number) return number;
      Function Sum_Holiday_Occasions (emp IN number) return number;

      pragma restrict_references (Sum_Sick_Leave,        WNDS, WNPS);
      pragma restrict_references (Sum_Sick_Occasions,    WNDS, WNPS);
      pragma restrict_references (Sum_Holiday_Leave,     WNDS, WNPS);
      pragma restrict_references (Sum_Holiday_Occasions, WNDS, WNPS);
    END;

    PACAKGE body Sum_Emp
    AS
      last_sick_emp      number;
      sick_days          number;
      sick_occasions     number;
      last_holiday_emp   number;
      holidays           number;
      holidays_occasions number;

      CURSOR C1 (emp IN number) IS
      SELECT sum(days), count(*)
      FROM   sick_leave
      WHERE  emp_no = emp;

      CURSOR C2 (emp IN number) IS
      SELECT sum(days), count(*)
      FROM   holiday_leave
      WHERE  emp_no = emp;

      FUNCTION Sum_Sick_Leave (emp  IN number) return number 
      AS
      BEGIN
        if nvl(Last_Sick_Emp,-1) != emp then
          Open  C1;
          Fetch C1 into sick_days, sick_occasions;
          Close C1;
          Last_Sick_Emp := Emp;
        end if;
        return (sick_days);
      END;

      FUNCTION Sum_Sick_Occasions (emp  IN number) return number 
      AS
      BEGIN
        if nvl(Last_Sick_Emp,-1) != emp then
          Open  C1;
          Fetch C1 into sick_days, sick_occasions;
          Close C1;
          Last_Sick_Emp := Emp;
        end if;
        return (sick_occasions);
      END;

      FUNCTION Sum_Holiday_Leave (emp  IN number) return number 
      AS
      BEGIN
        if nvl(Last_Holiday_Emp,-1) != emp then
          Open  C2;
          Fetch C2 into holidays, holiday_occasions;
          Close C2;
          Last_Holiday_Emp := Emp;
        end if;
        return (holidays);
      END;

      FUNCTION Sum_Holiday_Occasions (emp  IN number) return number 
      AS
      BEGIN
        if nvl(Last_Holiday_Emp,-1) != emp then
          Open  C2;
          Fetch C2 into holidays, holiday_occasions;
          Close C2;
          Last_Holiday_Emp := Emp;
        end if;
        return (holiday_occasions);
      END;
    END;


    Select E.emp_no, E.emp_name,
           sum_emp.sum_sick_leave(E.emp_no)        sick_days, 
           sum_emp.sum_sick_loccasions(E.emp_no)   sick_occasions, 
           sum_emp.sum_holiday_leave(E.emp_no)     holidays
           sum_emp.sum_holiday_occasions(E.emp_no) holiday_occasions
    From   emp E
    Where  E.emp_no = :emp_no