RETURNIG MULTIPLE VALUES INTO A SINGLE LINE FUNCTIONS

RETURNIG MULTIPLE VALUES INTO A SINGLE LINE FUNCTIONS:
---------------------------------------------------

One of the major drawbacks of inline SQL functions is the fact that they can return only one value. What do we do when we need to fetch more than one value? Calling the inline function more than once per row would be very expensive and would defeat the purpose -- or would it? Actually, by encapsulating the function into a package, we can exploit some package variables to call the function twice but execute it only once. For example:

SELECT E.emp_no, E.emp_name,
       SUM(S.days)    sick_days,
       COUNT(S.rowid) sick_occasions,
       SUM(H.days)    holidays,
       COUNT(H.rowid) holiday_occasions
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 (+)
                            ----- Sick ----   --- Holidays --
Result : Emp_No  Emp_Name   Days Occasions   Days Occasions
         123456  Tom Jones   12      8         80      8
This SQL statement is still incorrect, but translating it to inline functions is a bit more complex.

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;
PACKAGE 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_occasions(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
                            ----- Sick ----   --- Holidays --
Result : Emp_No  Emp_Name   Days Occasions   Days Occasions
         123456  Tom Jones    6      4         20      2
Note:  Packages that contain in-line SQL functions must define those functions as read-only via the RESTRICT_REFERENCES pragma. Inline functions can never perform update operations, and this pragma defines the function as read-only to the outside world. Packaged functions that occur in the SELECT list need pragma WNDS (described below). Functions that appear within the WHERE clause require WNPS.