Which of the choices implements a function named plch_avg that calculates the average of all elements in a collection of the above type so that the following block displays "3.5" after execution?
We compared the TABLE operator approach to algorithms one might write themselves to compute the average. One of these, marked as correct, is:
CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t) RETURN NUMBER IS l_index PLS_INTEGER := numbers_in.FIRST; l_average NUMBER := 0; BEGIN WHILE l_index IS NOT NULL LOOP l_average := l_average + numbers_in (l_index)/numbers_in.count; l_index := numbers_in.NEXT (l_index); END LOOP; RETURN l_average; END;Several players objected and believe that this choice should be marked as incorrect, since it will not always produce the expected result. For example, if I pass a nested table of 9 rows (values 1 through 9), this function returns:
5.00000000000000000000000000000000000001This is certainly true, and if we asked you to identify algorithms that would work under all circumstances, we would be wrong to mark this as correct. We would also be wrong to mark choice 8476 as correct, since an empty collection would cause the function to fail with a divide-by-zero exception.
But we asked you to identify choices so that the following block displays "3.5". It certainly does that, and so we believe that this choice should be scored as correct.